When it comes to database backup, there are generally two types: logical backup and physical backup. A logical backup produces a single backup file, while a physical backup includes all files and directory information of the database. Taking MySQL as an example, MySQL's logical backup can be achieved using the mysqldump tool. This guide introduces methods for creating and restoring a physical backup of MySQL.

Creating a Backup

First, log into the database using the MySQL root password.

$ sudo mysql -u root -p

Use the following command to find the directory where MySQL is installed.

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

Alternatively, execute this command to directly locate the MySQL directory.

$ sudo mysqld --verbose --help | grep ^datadir
datadir    /var/lib/mysql/

Before creating a physical backup, stop the MySQL database.

$ sudo systemctl stop mysql

Create a directory named after the current date to store the MySQL physical backup.

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

Use the cp command to copy all database files into this directory.

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

Finally, start the MySQL database.

$ sudo systemctl start mysql

This describes the process of creating a physical backup. Next, we will discuss how to restore a database from a physical backup.

Restoring a Backup

First, stop the MySQL database.

$ sudo systemctl stop mysql

Rename the current MySQL directory and create a new directory.

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

Copy the physical backup into the newly created directory.

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

Adjust the ownership and permissions of the files and directories.

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

Start the MySQL database.

$ sudo systemctl start mysql

Finally, log in to the database using the root password to test if the restoration was successful.

$ sudo mysql -u root -p
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

This outlines the operational method for creating a physical backup of MySQL. Compared to logical backups, physical backups are more suitable for large databases and are more convenient for reinstallation and recovery.