Mysqldump backup and xtrabackup backup best practices


Write it at the front

1. Backup type

(1) According to the file after backup

a. Physical backup: direct backup of database files, commonly used LVM logical volume backup, or direct copy of compressed database data directory
b. Logical backup: use the backup tool to export the data in the database as database SQL script or data file

(2) According to whether to stop the database service

a. Cold standby: the database service needs to be stopped during the backup, which will cause unreadable and writable
b. Warm backup: during the backup period, the database service does not need to be stopped, but the table needs to be locked, so it can only be read and not writable
c. Hot standby: during the backup period, the database service is completely unaffected and readable and writable

(3) According to the backup cycle and backup content

a. Full backup: back up all data in the database instance at one time
b. Incremental backup: if a full backup has just taken place, incremental backup refers to the data added between the full backup and the current point in time;

If the last incremental backup was an incremental backup, the incremental backup refers to the data that was added between the last incremental and the current point in time

c. Differential backup: only the data that has changed since the last full backup is backed up. Because it consumes more disk space than incremental backup, it uses less

2. Backup strategy

(1) Full backup + incremental backup
(2) Full backup + differential backup
3. Introduction to backup tools

(1) Mysqldump: a logical backup tool based on MySQL client, which can realize warm backup. You can use options such as – u, – P, – h to backup data on remote database
(2) Mysqlhotcopy: a physical tool based on MySQL client, which can only be cold standby. During the backup process, you need to specify basic parameters such as – u, – P, – H, – S. the meanings of these basic parameters are the same as those corresponding to MySQL command
(3) Lvm2: a physical backup tool based on LVM. Because it is realized by taking a snapshot of the logical volume, the backup speed is extremely fast and can be completed in an instant. It can be roughly considered as a hot backup
(4) Xtrabackup: an open source MySQL backup tool under percona, which can realize online hot backup and warm backup
In addition, there are some other backup tools, such as mysqldumper, phpMyAdmin and so on. If you are interested, you can check the information and learn by yourself.

The above quotation:…
Because before, I only had a preliminary understanding and simple understanding of database backup, and there were few hands-on operations. In the process of work and learning, I will gradually encounter some things about the use and backup and recovery of database, so this matter will be put on the agenda. As the saying goes, it’s easy to feel on paper, but we should practice it. Don’t say much. Let’s start now.

Xtrabackup backup practice

Xtrabackup: an open source MySQL backup tool under percona, which can realize online hot backup and warm backup

1. Xtrabackup installation
1.1. Download the percona xtrabackup — RPM installation package on the server
wget -c…
1.2. Yum can be used after installation (the latest version of version 8.0 has been installed before, but there is no innobackupex command, so it cannot be operated; therefore, version 2.4 is installed)

2. Database backup
Database backup reference link:…
Here I practice full backup and incremental backup, which is a little more complicated than mysqldump backup.

Also test mysqldump. If you use mysqldump – uroot – pxxx — all Databases > first all.sql After that, save all the data SQL. When the data directory of the database is deleted, it cannot be recovered (restore using MySQL – uroot – pxxx< all.sql )If you just drop the library, you can use MySQL – uroot – pxxx< all.sql Recovery.
Xtrabackup can still perform full recovery operation even if the data directory of the database is deleted.

be careful: during the test, the root user can be used for backup operation without specifying the socket file.

This method of full backup and incremental backup is amazing, and it feels very good.
However, from the current operation point of view, the backup is not as flexible as mysqldump. It seems that only full backup and incremental backup are available. You cannot back up a single database or table to myslqdump. (see later)

Mysqldump backup practice
Reference link:…

Full backup
1. After the installation, the MySQL service and the mysqldump command will be generated together, so no additional installation is required.
1.1 stop database service
1.2 mysqldump -uroot -pXXX –all-databases >all.sql
In this way, the database can be fully backed up
1.3 mysql -uroot -pXXX < all.sql ; start database service
This allows full recovery.

Incremental backup
MySQL has no incremental backup operation, because it needs to use binlog for full backup.
See… It’s been tested. It’s excellent.