HowTo: Restore MySQL Database From the Command Line

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
 
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

How to configure MySQL / MariaDB to listen publicly so that remote connections will work

Introduction   In order to connect to MySQL or MariaDB from a computer outside of the server,...

How do I disable ONLY_FULL_GROUP_BY in MySQL?

To disable ONLY_FULL_GROUP_BY in MySQL, remove ONLY_FULL_GROUP_BY from the sql-mode setting in...