How to Restore a MySQL Database with Command Line

In MySQL, you can use the mysql command to restore the database from a dump file.

mysqldump is a command-line utility used to generate a MySQL logical database backup as a single .sql file with a set of SQL statements. The utility helps you dump MySQL tables, multiple databases, or their objects. Keep in mind that it is not possible to back up MySQL databases or data to separate .sql files with the mysqldump utility

Step 1: Create the database

In the command prompt, connect to the MySQL server on which you want to create the database and run the mysql command:

Note: The database name should be the same as the database you want to restore.

mysql> create database sakila;

This will create the database into which you will be able to import database structure and data from the dump file.

Step 2: Restore the database

Now, we can restore the MySQL database from the dump file. The syntax for the command is as follows:

mysql -u [user name] –p [target_database_name] < [dumpfilename.sql]

The parameters include:

• -u [user name]is a username to connect to a MySQL server.
• -p is a password for the username you use to connect to the server.
• target_database_name is the name of the empty database into which you want to load data from the backup file.
• < is a parameter that refers to the process of a database restore.
• [dumpfilename.sql] is the path to the dump file.

Thus, to restore the sakila database, execute the following command:

mysql --host=dbfmylast --user=root --port=3306 -p sakila < D:\sakila.sql

where dbfmylast is the target server with the empty database into which we’ll load data from the sakila.sql dump file.

To check the result, run the following:

mysql> use sakila;
...
mysql> show tables;

Restore all databases in MySQL from the dump file using mysqldump

With the mysqldump utility, you can restore not only a single database but all databases on the server in bulk.

The syntax to restore all MySQL databases from a dump file is as follows:

mysql -u root -p < alldatabases.sql

where alldatabases.sql is a path to the dump file containing a backup of all databases on the server.

For example, we are going to restore all MySQL databases from the backup_all_databases.sql dump file. For this, we are running the following command:

mysql -u root -p < D:\backup_all_databases.sql