Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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?

share|improve this question
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
add comment

4 Answers

up vote 3 down vote accepted

Why dont you concat all the files into one single file and import using

mysql -u username -p < dump.sql

Create one file using mysqldump -u username -p –all-databases > dump.sql and restore using mysql -u username -p < dump.sql

share|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
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
    
-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
add comment

You may restore databases with the help of sql 2005 repair database

http://www.sqlserverrepairtoolbox.com repairs data types, views, procedures, tables, keys, indexes and other objects

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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