13

I have exported all my databases to a file using phpMyAdmin.

Unfortunately I don't have a working phpMyAdmin in my destination system.

How should I restore all of them at once using one line command?

  • 1
    Did you take a look into the file? It should/could contain commands to create and switch to the databases as needed. – Tom Regner Oct 9 '12 at 12:42
  • Yes, databases are created and then they have been selected using USE database_name command. – smhnaji Oct 9 '12 at 12:45
  • Then Unni and laurents answers are correct. – Tom Regner Oct 9 '12 at 13:14
18

Why don't you concat all the files into one single file and import/restore using

mysql -u username -p < dump.sql

Create one file using

mysqldump -u username -p --all-databases > dump.sql
| improve this answer | |
  • The OP already has a single file and he needs to restore the files so he can't dump them anymore I suppose. – laurent Oct 9 '12 at 12:59
  • @laurent Yes got it. Using mysql -u username -p < dump.sql will load the multiple mysql database. – devav2 Oct 9 '12 at 13:05
  • yes, sure it will – laurent Oct 9 '12 at 13:07
  • 2
    it did not work for me. it says i need to select a databse – Aryeh Armon Jul 7 '15 at 9:22
3

If you backed up many database to 1 file, I suppose you backed up the create database statements in the same file. If not you only need to add the create database and use database statements to your file at the proper places.

After that, the command to load the file to mysql is:

mysql -p < sqlfile.sql

-p is to ask for your password.

You can use -u username if you need to use another user.

| improve this answer | |
1

mysql command to restore sql file is :

mysql DATABASE_NAME < SQL_FILENAME.sql

first make sure that file is sql. Incase it ends with gz file extension, you need to uncompress using command :

gunzip SQL_FILE.sql.gz
| improve this answer | |
  • 1
    -1 . As I said in the above, there are MULTIPLE databases stored in the file and I want all of them to be restored at once. – smhnaji Oct 9 '12 at 12:38
  • can you export each database separately in phpmyadmin and restore it at source server ? or use linux stream editors such as sed or awk to split them in different file. – Unni Oct 9 '12 at 12:47
  • 1
    no need, he can import all the databases from a single file – laurent Oct 9 '12 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.