How to Restore MySQL Dump Using mysql Command

Summary: in this tutorial, you will learn how to restore a MySQL dump file by using the mysql tool.

The mysqldump is a command-line utility used for backing up MySQL databases by generating SQL statements that can recreate the database.

The mysqldump program allows you to create snapshots of your databases, making it an important tool for data backup.

To restore the database from a dump file created by the mysqldump program, you use the mysql program.

The mysql program reads the SQL statements from a dump file and executes them to recreate the database.

Here are the steps for restoring a database from a dump file:

First, open the Command Prompt on Windows or Terminal program on Unix-like systems (macOS, Linux, Ubuntu, etc.).

Second, execute the following command to restore databases from a dump file:

mysql -h hostname -P port -u username -p database_name < dump_file.sqlCode language: CSS (css)

Let’s break down the command:

• mysql: This is the command-line client for MySQL.
• -h hostname: This option specifies the hostname or IP address of the MySQL server you want to connect to. Replace “hostname” with the actual address.
• -P port: This option specifies the port number on which the MySQL server is running. Replace “port” with the actual port number.
• -u username: This option specifies the MySQL username you want to use to connect to the server. Replace “username” with your MySQL username.
• -p: This option prompts you for the MySQL user password. After entering the command, you’ll be prompted to enter the password.
• database_name: This is the name of the MySQL database you want to restore the data into. Replace “database_name” with the actual name of your target database.
• < dump_file.sql: This part of the command is used for input redirection. It takes the contents of the SQL dump file (“dump_file.sql”) and uses them as input for the mysql command. This is how the data and structure from the dump file are loaded into the specified MySQL database.

Note that you can execute the mysql from your computer to restore a dump file or from the remote MySQL server.

Restoring MySQL dump example

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, dump the classicmodels sample database to the file D:\backup\classicmodels.sql using the mysqldump program.

mysqldump -u root -p classicmodels > D:\backup\classicmodels.sqlCode language: CSS (css)

Third, connect to the MySQL server:

mysql -u root -p

Fourth, switch the current database to classicmodels database:

use classicmodels;Code language: PHP (php)

Fifth, delete all rows from the payments table:

delete from payments;Code language: JavaScript (javascript)

Sixth, exit the mysql program:

exitCode language: PHP (php)

Seventh, restore the classicmodels database from classicmodels.sql dump file:

mysql -u root -p classicmodels < d:\backup\classicmodels.sqlCode language: CSS (css)

Eight, connect to the mysql:

mysql -u root -p

Ninth, switch the current database to classicmodels:

use classicmodels;Code language: PHP (php)

Finally, count the rows from the payments table:

SELECT COUNT(*) FROM payments;

Output:

+----------+
| COUNT(*) |
+----------+
|      273 |
+----------+
1 row in set (0.02 sec)Code language: JavaScript (javascript)

It returns 27 rows, meaning that you have successfully restored the classicmodels database from the dump file.

Troubleshooting

When restoring from a dump file, you may encounter the following error:

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ' ■-'.Code language: JavaScript (javascript)

Likely, the encoding of the dump file is not utf8.

To fix it, you can open the dump file in a text editor e.g., Notepad on Windows or Nano on Unix-like systems, and save the file using the utf8 encoding.

Summary

• Use the mysql command to restore a dump file created by the mysqldump program.