HowTo: Restore MySQL Database From the Command Line
Do not use phpMyAdmin to restore your MySQL database if the MySQL database is large.
phpMyAdmin has limit on total upload size and, there is also maximum execution time which may cause browser to time out.
The solution of how to restore large MySQL database from the sql dump file is to use Linux/Unix command line.
Restoring an existent Database
If you need to restore a database that has already been existed on the server, than you have to use ‘mysqlimport‘ command.
The syntax for ‘mysqlimport’:
$ mysqlimport -u [username] -p[password] [dbname] [backupfile.sql]
Note: There is no space between -p and the password.
Example:
$ mysqlimport -u root -pSeCrEt customers_db customers_db_backup.sql
Restoring a new Database
1. Create an appropriately named Database on the target server.
Example:
mysql> CREATE DATABASE customers_db;
2. Load the sql dump file using the ‘mysql‘ command:
$ mysql -u [username] -p[password] [db_to_restore] < [backupfile.sql]
Example:
$ mysql -u root -pSeCrEt customers_db < customers_db_backup.sql
If the sql dump file is compressed, you can do the following:
$ gunzip < [backupfile.sql.gz] | mysql -u [username] -p[password] [dbname]
Example:
$ gunzip < customers_db_backup.sql.gz | mysql -u root -pSeCrEt customers_db
