How to Back Up and Restore All Databases in MySQL

Summary: in this tutorial, you will learn how to make a backup of all the databases on a MySQL Server using the mysqldump program and restore them using the mysql program.

Creating sample databases

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Next, show all the databases:

show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The command returns four system databases.

Then, create three databases called testdb1, testdb2, and testdb3:

CREATE DATABASE testdb1;
CREATE DATABASE testdb2;
CREATE DATABASE testdb3;Code language: SQL (Structured Query Language) (sql)

After that, show all the databases again:

show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
| testdb3            |
+--------------------+
7 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Finally, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Backing up all databases

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

Second, use the following command to back up all databases on the MySQL server:

mysqldump -h localhost -u root -p --all-databases > D:\backup\all_databases.sqlCode language: SQL (Structured Query Language) (sql)

Let’s break down the command:

• mysqldump: This is the command-line utility for MySQL that allows you to dump all databases into a file.
• -h localhost: This option specifies the hostname where the MySQL server is running. In this case, it’s set to “localhost,” indicating that the MySQL server is on the same machine as the command is being executed.
• -u root: This option specifies the MySQL user to be used for the connection. In this case, it’s set to “root,” which is a common default superuser account in MySQL.
• -p: This option prompts the user for the MySQL password. After entering the command, you will be prompted to enter the password for the specified user (in this case, the root user).
• –all-databases: This option tells mysqldump to dump all databases on the MySQL server, not just a specific one. It includes the structure and data for all databases including the system databases.
• > D:\backup\all_databases.sql: This part of the command uses the output redirection symbol (>) to send the output of the mysqldump command to a file. In this case, the file is specified as D:\backup\all_databases.sql. The file will contain the SQL statements necessary to recreate all databases and their data.

Third, examine the all_database.sql file in the D:\backup directory.

Accidentally removing a database

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, drop the testdb1 and testdb2 databases:

drop database testdb1;
drop database testdb2;Code language: SQL (Structured Query Language) (sql)

Third, show all the databases:

 show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb3            |
+--------------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Finally, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Suppose you want to restore the testdb1 and testdb2 from the backup created by the mysqldump program.

Restoring all databases

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

Second, use the mysql program to restore all databases from the backup created by the mysqldump program:

mysql -h localhost -u root -p < D:\backup\all_databases.sqlCode language: SQL (Structured Query Language) (sql)

Third, connect to the MySQL server:

mysql -h localhost -u root -pCode language: SQL (Structured Query Language) (sql)

Finally, show all the databases:

show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
| testdb3            |
+--------------------+
7 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the testdb1 and testdb2 databases have been restored successfully.

Summary

• Use the mysqldump with the --all-databases option to dump all databases on a MySQL server into a file.
• Use the mysql program to restore all the databases from a backup file created by the mysqldump program.