MySQL Restore: Restoring from a Dump File
Summary: in this tutorial, you will learn how to restore an SQL dump file by using the mysql tool.
Setting up a sample database
First, create a new database called mydb:
CREATE DATABASE mydb;
Code language: SQL (Structured Query Language) (sql)
Second, use the mydb database:
USE mydb;
Code language: SQL (Structured Query Language) (sql)
Third, create a new table tests with one column:
CREATE TABLE tests(
id INT PRIMARY KEY
);
Code language: SQL (Structured Query Language) (sql)
Fourth, insert some rows into the tests table:
INSERT INTO tests(id)
VALUES(1),(2),(3);
Code language: SQL (Structured Query Language) (sql)
Finally, use the mysqldump to dump the mydb database:
mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\mydb.sql --databases mydb
Code language: SQL (Structured Query Language) (sql)
Note that you must have the c:\backup directory available on your computer or server.
Restoring an SQL dump file using the SOURCE command
To restore the mydb.sql SQL dump file, you follow these steps:
First, connect to MySQL server:
C:\>mysql -u root -p
Enter password: ********
Code language: SQL (Structured Query Language) (sql)
Second, drop the mydb database:
mysql>drop database mydb;
Code language: SQL (Structured Query Language) (sql)
Third, use the SOURCE command to load the dump file:
mysql>source c:\backup\mydb.sql
Code language: SQL (Structured Query Language) (sql)
The command created a database mydb, select the database, and execute other SQL statements. In addition, it showed all the possible warnings and errors.
It is recommended that you use the SOURCE command to restore a dump file because the SOURCE command returns very detailed information including warnings and errors.
In this tutorial, you have learned step by step how to restore a dump file by using the mysql tool.