How to Back Up and Restore a Database in MySQL

Summary: in this tutorial, you will learn the steps of how to back up a database on a MySQL server using mysqldump program and restore it using the mysql program.

To back up a database on the MySQL server, you use the mysqldump program that comes by default with the MySQL client installation.

To restore a backup created by the mysqldump program, you use the mysql command-line program.

We’ll demonstrate how to back up and restore a database on a MySQL server.

Creating a sample database

First, connect to the MySQL server using the mysql program:

mysql -u root -pCode language: plaintext (plaintext)

Second, create a new database called hr:

CREATE DATABASE IF NOT EXISTS hr;Code language: SQL (Structured Query Language) (sql)

Third, switch the current database to hr:

USE hr;Code language: SQL (Structured Query Language) (sql)

Fourth, create a new table inside the hr database called employees:

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);Code language: SQL (Structured Query Language) (sql)

Fifth, insert some rows into the employees table:

INSERT INTO employees (name, email) 
VALUES
    ('John Doe', '[email protected]'),
    ('Jane Smith', '[email protected]'),
    ('Bob Johnson', '[email protected]'),
    ('Alice Jones', '[email protected]'),
    ('Charlie Brown', '[email protected]');Code language: SQL (Structured Query Language) (sql)

Finally, exit the mysql program.

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

Backing up a database

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

Second, execute the mysqldump program to back up the hr database:

mysqldump -h localhost -u root -p hr > D:\backup\hr.sqlCode language: plaintext (plaintext)

Let’s break down each part of the command:

• mysqldump: This is the command-line utility for MySQL that allows you to dump the contents of a database into a file.
• -h localhost: This option specifies the hostname where the MySQL server is running. In this case, it’s set to “localhost,” which means the MySQL server is on the same machine as the command is being executed. If you want to back up a database located on a remote server, please specify the hostname of the server.
• -u root: This option specifies the MySQL user 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).
• hr: This is the name of the MySQL database that you want to back up. Replace “hr” with the actual name of the database you want to back up.
• > D:\backup\hr.sql: This part of the command uses the output redirection symbol (>) to send the output of the mysqldump command to the file "D:\backup\hr.sql".

After entering a valid password, the mysqldump program will create a backup of the hr database and store it in the hr.sql file located in the D:\backup directory.

Accidentally deleting some rows from a table

First, connect to the MySQL:

mysql -u root -p

Second, switch the current database to the hr:

USE hr;Code language: SQL (Structured Query Language) (sql)

Third, delete the employee with id 3 from the employees table:

delete from employees where id = 3;Code language: SQL (Structured Query Language) (sql)

Suppose that this deletion is unintended and you want to restore the hr database to recover the data.

Restoring a database

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

Second, use the following command to restore the hr database from the backup file created by the mysqldump utility:

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

Here’s the breakdown of the command:

• mysql: This is the MySQL command-line client, which is used to interact with the MySQL server through the command line.
• -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).
• hr: This is the name of the MySQL database that you want to restore. Replace “hr” with the actual name of the database you want to restore.
• < D:\backup\hr.sql: This part of the command uses the input redirection symbol (<) to read the contents of the specified file (“D:\backup\hr.sql“) and pass them as input to the mysql command.

Third, connect to the MySQL server:

mysql -u root -p

Fourth, switch the current database to hr:

USE hr;Code language: SQL (Structured Query Language) (sql)

Finally, retrieve data from the employees table to verify the restoration:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------------+---------------------------+
| id | name          | email                     |
+----+---------------+---------------------------+
|  1 | John Doe      | [email protected]      |
|  2 | Jane Smith    | [email protected]    |
|  3 | Bob Johnson   | [email protected]   |
|  4 | Alice Jones   | [email protected]   |
|  5 | Charlie Brown | [email protected] |
+----+---------------+---------------------------+
5 rows in set (0.00 sec)Code language: plaintext (plaintext)

The output indicates that the hr database has been fully restored.

Summary

• Use the mysqldump program to back up a database on a MySQL server.
• Use the mysql program to restore a database from a backup file created by the mysqldump program.