How to Create a Physical Backup and Restore a MySQL Database

Introduction

In MySQL, you can backup your data either by using a logical or a physical backup. The former makes MySQL dump file. On the other hand, a physical MySQL backup is a raw copy of all files and directories containing the database information. You can create MySQL logical backups by using mysqldump or by installing automated tools such as automysqlbackup. In this guide, you'll create a physical MySQL backup.

Prerequisites

Before you begin, ensure you have a non-root user with sudo privileges and a MySQL database server.

Locate the Data Directory

1. Log in to MySQL server with your root password.

    $ sudo mysql -u root -p
   

2. The following SQL will reveal the data directory of your MySQL instance.

    mysql> select @@datadir;
   

3. The output is similar to this.

    +-----------------+
    | @@datadir       |
    +-----------------+
    | /var/lib/mysql/ |
    +-----------------+
    1 row in set (0.00 sec)

Alternate method

Locate the MySQL data path without logging to the MySQL server.

$ sudo mysqld --verbose --help | grep ^datadir

The output is similar to this.

datadir    /var/lib/mysql/

Backup the Database

1. Stop the MySQL service.

    $ sudo systemctl stop mysql
   

2. Create a directory to store the MySQL file backup. Create a parent directory and sub-directories with the current date. This naming scheme allows you to determine the last backup date.

    $ sudo mkdir -p /var/mysql_backups/2020_07_13_backup
   

3. Copy the database file with cp.

    $ sudo cp -R /var/lib/mysql/. /var/mysql_backups/2020_07_13_backup
   

4. Start the database server.

    $ sudo systemctl start mysql

Restore the Database

1. Stop the MySQL service.

    $ sudo systemctl stop mysql
   

2. Rename the current MySQL data directory. This step preserves a backup copy of the current state.

    $ sudo mv /var/lib/mysql /var/lib/mysql_old
   

3. Create a new MySQL data directory.

    $ sudo mkdir /var/lib/mysql
   

4. Restore the MySQL backup.

    $ sudo cp -R /var/mysql_backups/2020_07_13_backup/. /var/lib/mysql
   

5. Change the ownership of /var/lib/mysql to the mysql user.

    $ sudo chown -R mysql:mysql /var/lib/mysql
   

6. Start the database server.

    $ sudo systemctl start mysql

Test the Backup

1. Test the backup by logging to the MySQL server as root.

    $ sudo mysql -u root -p
   

2. Verify the database schema is correct.

    mysql> SHOW DATABASES;
   

3. Your databases are displayed. For example:

    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+

Conclusion

In this guide, you've created a physical backup of your MySQL database. This method is ideal for large databases and makes restoration easier when reinstalling a database server.