MySQL backup summary

MySQL backup summary

One, BinLog binary log

1. BinLog log content

  1. Any operation that causes changes to the MySQL server (addition, deletion, modification)
  2. The replication function depends on this log (MySQL master-slave)
  3. Master Slave servers by the server from the master copy complete binary log, the log held in the relay prior to performing relay logthe.
  4. Slave servers can usually turn off the binary log to improve performance.

2. The manifestation of BinLog log file

1) In the default /usr/local/mysql/data/directory, there is a mysql-bin.000001 mysql-bin.000002binary file

[[email protected] ~]# sed -i '/\[mysqld]/a log-bin=mysql-bin' /etc/my.cnf
[[email protected] ~]# sed -i '/\[mysqld]/a server-id=1' /etc/my.cnf
[[email protected] ~]# head -3 /etc/my.cnf
Insert picture description here

When will the binary log be regenerated:

  1. Restart the MySQL service:systemctl restart mysqld
  2. In the MySQL database:flush logs;
  3. According to max_binlog_sizeset up a MySQL binaries maximum, the highest value will be created automatically.
[[email protected] ~]# systemctl restart mysqld
[[email protected] ~]# ls -l /usr/local/mysql/data/ | grep 'mysql-bin'
Insert picture description here
  • Note: If you need to delete the binary log, do not delete the binary file directly, as this will mess up MySQL management.

1) View the binary log file being used

mysql> show master status;
Insert picture description here

2) Manually scroll the binary log file

mysql> flush logs;
Insert picture description here

3) Display all binary log files

mysql> show binary logs;
Insert picture description here

4. MySQL binary file reading tool

格式:mysqlbinlog [参数] log-files

There are four options for the following parameters:

  • --start-position: Used to specify the starting position of the binary log file;: --stop-positionUsed to specify the ending position of the binary log file.
  • --start-datetime: Used to specify the start date of the binary log file;: --stop-datetimeUsed to specify the end date of the binary log file.

Use mysqlbinlogtools to make incremental backups of MySQL: MySQL incremental backup and recovery

5. Binary log format

  • By the bin_log_format={ statement | row | mixed }definition of ( 5.7the previous version is statement, 5.7after version row)

1) statement:

  • Based on the sentence, the sentence that generates the data is recorded.
  • Disadvantage: If the inserted information is a function, the execution result may be different at different time points. Such asinsert into test values(CURRENT_DATE())

2) row:

  • Based on row data, the disadvantage is that sometimes the amount of data is too large.

3) mixed:

  • Mixed mode, when to use the MySQL own decisions statementand rowpattern.

View the current binary logging format

mysql> show variables like 'binlog_format';
Insert picture description here
  • If you want to modify it permanently, you need to: sed -i '/\[mysqld]/a binlog_format=模式' /etc/my.cnfto modify it.

6. Backup type

1) According to whether the MySQL service is online at the time of backup

  • Cold standby: Stop the MySQL service, and read and write operations cannot be performed.
  • Warm preparation: A shared lock is applied globally, which can only be read but not written.
  • Hot backup: keep the MySQL service off, and read and write as usual.

2) According to the interface at the time of backup

  • Physical Backup: Copy data files directly, pack them and archive them. Simply put, it is to package the MySQL data storage path.
  • Logical Backup (Logical Backup): Extract data and save it in a SQL script, which mysqldumpis a logical backup.

Respective characteristics:

  • Physical: No additional tools are required, just archive commands directly, but the cross-platform capability is relatively poor; if the amount of data exceeds dozens of gigabytes, it is suitable for physical backup.
  • Logic: It is easy to import, just read SQL statements directly; logical backup recovery time is slow and takes up a lot of space; the accuracy of floating-point numbers cannot be guaranteed; the index needs to be rebuilt after the database is restored.

7. According to the backup of the entire data or the changed data

1) Full backup : each time the data is fully backed up, that is, the entire database is backed up (it is the basis of differential backup and incremental backup)

  • Advantages: The backup and restore operation is simple and convenient.
  • Disadvantages: There is a lot of duplication of data; it takes up a lot of space; the backup and recovery time is long.

2) Differential backup :

  • Back up all the files that have been modified since the last full backup. The starting point of the backup time is from the last full backup, and the amount of backup data is getting larger and larger.
  • When restoring data, you only need to restore the last full backup and the most recent differential backup.

3) Incremental backup : Only those files that have been modified after the last full backup or incremental backup will be backed up.

  • Advantages: small space occupation and fast backup speed.
  • Disadvantage: If the backup data in the middle is damaged, it will cause data loss.

8. The three most commonly used backup tools for MySQL

1) mysqldump: Normally, it is backed up when the data is small; single-threaded recovery is slower.

  • InnoDB: Hot standby, warm standby.
  • MyISAM, Aria: Warm preparation.

2) xtrabackup(usually innobackupextool): large data backup MySQL; is a physical backup speed.

  • InnoDB: hot backup, incremental backup.
  • MyISAM: Warm backup, does not support incremental backup, only full backup.

3) lvm-snapshot: A tool close to hot backup, because it needs to request a global lock first, then create a snapshot, and release the global lock after the snapshot is created;

  • Using cp tartools such as physical backup;
  • The backup and restore speed is faster.
  • It is difficult to implement incremental backups, and it takes a while to request a global request, especially on a busy server.

The comparison between the two is as follows:

Tool namemysqldumpxtrabackup
Backup methodLogical backupPhysical backup
Data storage methodSQL scriptbinary file
Whether to support hot backupYesYes
Whether to support incremental backupYesYes
Backup processWill lock the tableDon't lock the table
Whether it affects normal businessGreater impactLess affected
Backup and restore performanceTakes a long timeLess time consuming
take up spaceOccupies little spaceTakes up a lot of space

Second, use the LVM-Snapshot tool to backup

Note based on snapshot backup:

  1. The transaction log and the data log must be on the same volume.
  2. Before creating a snapshot volume, you must request a MySQL global lock; release the lock after the snapshot is created.
  3. After requesting the global lock, do a log roll; do binary log file and position mark (manually)

1. Prepare LVM volume and migrate MySQL data to LVM volume

1) Create a partition

[[email protected] ~]# fdisk -l /dev/sdb
Insert picture description here

2) Create PV, VG, LVM, and format

[[email protected] ~]# pvcreate /dev/sdb1 /dev/sdb2 /dev/sdb3
[[email protected] ~]# vgcreate test /dev/sdb1 /dev/sdb2 /dev/sdb3
[[email protected] ~]# lvcreate -L +10G -n zhangsan test
[[email protected] ~]# mkfs.xfs /dev/test/zhangsan
Insert picture description here

3) Create a directory and mount the logical disk

[[email protected] ~]# mkdir /var/zhangsan
[[email protected] ~]# mount /dev/test/zhangsan /var/zhangsan/
[[email protected] ~]# df -hT
Insert picture description here

4) Confirm that the service is closed, and then migrate the data to /var/zhangsan

[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# cd /usr/local/mysql/data/
[[email protected] data]# tar -cf - . | tar xf - -C /var/zhangsan/

5) remounted zhangsanto the home directory of the MySQL database/usr/local/mysql/data

[[email protected] ~]# umount /var/zhangsan/
[[email protected] ~]# mount /dev/test/zhangsan /usr/local/mysql/data/

6) Audit permissions and start the service

[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# mysql -uroot -p123123
mysql> show master status\G
mysql> flush logs;
mysql> flush tables with read lock;
[[email protected] ~]# lvcreate -L +4G -s -n mysql /dev/test/zhangsan 
[[email protected] ~]# mysql -uroot -p123123 -e "unlock tables;"

7) Open MySQL binary log and restart MySQL service

[[email protected] ~]# sed -i '/\[mysqld]/a log-bin=mysql-bin' /etc/my.cnf
[[email protected] ~]# sed -i '/\[mysqld]/a server-id=1' /etc/my.cnf
[[email protected] ~]# systemctl restart mysqld
mysql> create database Coco;
mysql> use Coco;
mysql> create table test(ID int);
mysql> insert into test values(1),(2),(3);
[[email protected] ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/

Simulate database failure

[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# umount /dev/test/zhangsan


[[email protected] ~]# mount /dev/test/zhangsan /usr/local/mysql/data/
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# mysql -uroot -p123123 -e "select * from Coco.test;"
Insert picture description here

Three, use the XtraBackup tool to backup

Percona XtraBackup (PXB for short) is a backup tool developed by Percona for physical hot backup of MySQL databases. It supports MySQL, Percona Server and MariaDB, and is all open source.

1. Principle

  • Communication method: The interaction and coordination between the two tools are realized by controlling the creation and deletion of files.
  • The main documents are:xtrabackup_suspended_1 xtrabackup_suspended_2 xtrabackup_log_copied

When backing up xtrabackup_suspended_2to coordinate these two tools process as follows:

  1. ibStart xbafter the process, will always wait for xbcompletion of backup InnoDBfiles, the way is to wait for xb_suspended_2the file to be created.
  2. xbIn preparation finished InnoDBafter the data, create the file in the specified directory, and then wait for the file to be ibdeleted.
  3. ibThe file is detected xb_suspended_2after it has been created, it continues to go down.
  4. ibIn the non-completion of the backup InnoDBafter the table, delete xb_suspended_2the file, so that you notice xbcan continue, and then wait xb_log_copiedto be created.
  5. xbDetected xb_suspended_2after the deleted files, you can continue down the.

Backup process:

  • innobackupexAfter the start, it will first forka process to start the xtrabackupprocess, and then wait for the xtrabackupcompletion of the backup InnoDBdata files;
  • When xtrabackupwhen backup finished, will be notified by creating a file innobackupexprocess, then innobackupexthe process will go to a non-backup InnoDBdata files.
Insert picture description here
  • Redo File: It is the log generated by the storage engine layer (InnoDB), mainly to ensure the reliability of the data.
  • Ibd File: The table space opened by the InnoDB engine to store table data and indexes.

2. Install XtraBackup tool

[[email protected] ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5
[[email protected] ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm
[[email protected] ~]# tar xf percona-xtrabackup2.4.5-Linux-x86_64.tar.gz -C /usr/src/
[[email protected] ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/
[[email protected] percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/

XtraBackup mainly contains two tools:

xtrabackup( PerlScript):

  • For backing up InnoDB tables, non-InnoDB tables cannot be backed up, and there is no interaction with MySQL Server.

innobackupex( C/C++Compiled binary):

  • To back up non-InnoDB table, but will call xtrabackupa command to back up InnoDB tables;
  • And MySQL Server will send commands to interact, such as adding a read lock ftwrl, access to sites show slave statusand so on.

It simply is innobackupexthe xtrabackuptop layer of the package do.

Common optionseffect
--hostDesignated host
--userSpecify username
--passwordSpecify password
--portDesignated port
--databasesSpecify the database
--incrementalCreate incremental backup
--incremental-basedirSpecify the directory containing the full backup
--incremental-dirSpecify the directory containing incremental backups
--apply-logPreprocess the backup
--redo-onlyDo not roll back uncommitted transactions
--copy-backRestore the backup directory

2. Full backup and recovery

[[email protected] ~]# innobackupex --user=root --password=123123 /backups

View backup data

[[email protected] ~]# ls /backups/
[[email protected] ~]# ls -l /backups/2021-05-19_22-31-36/
Insert picture description here


  • backup-my.cnf: Backup configuration option information file.
  • xtrabackup_binlog_info: The binary log file currently being used by MySQL and the location information file of the binary log file at this time.
  • xtrabackup_checkpoints: Backup type, status and LSN status information file.
  • xtrabackup_logfile: Backup log file.

Delete the original data

[[email protected] ~]# rm -rf /usr/local/mysql/data/*

Combine data to make data files in a consistent state

[[email protected] ~]# innobackupex --apply-log /backups/2021-05-19_22-31-36/

Data recovery

[[email protected] ~]# innobackupex --copy-back /backups/2021-05-19_22-31-36/
[[email protected] ~]# ls -l /usr/local/mysql/data/
[[email protected] ~]# chown -R mysql:mysql /usr/local/mysql/data/
Insert picture description here

to sum up:

  1. innobackupex Full backup, and specify the backup directory path.
  2. Before restoring, you need to use --apply-logthe parameter to merge data files, ensure data consistency requirements.
  3. When restored, the direct use --copy-backparameters to recover, should be noted that, in my.cnfthe data file to specify the path to the directory.

3. Incremental backup and recovery

[[email protected] ~]# innobackupex --user=root --password=123123 /backups
  • Note: The premise of incremental backup is that a full backup is required (because it has been done above, it is no longer configured)
[[email protected] ~]# mysql -uroot -p123123
mysql> create database Coco;
mysql> use Coco;
mysql> create table test(ID int);
mysql> insert into test values(1),(2),(3);

Use innobackupexincremental backups

[[email protected] ~]# innobackupex --user=root --password=123123 \
--incremental /backups/ \

View backup data

[[email protected] ~]# ll /backups/
[[email protected] ~]# cat /backups/2021-05-19_23-00-37/xtrabackup_checkpoints
[[email protected] ~]# cat /backups/2021-05-19_23-23-45/xtrabackup_checkpoints
Insert picture description here

Delete all data in the database

[[email protected] ~]# rm -rf /usr/local/mysql/data/*

Consolidate complete data catalogs to ensure data consistency

[[email protected] ~]# innobackupex --apply-log --redo-only /backups/2021-05-19_23-00-37/

Consolidate incremental backup data into the full data directory

[[email protected] ~]# innobackupex --apply-log --redo-only /backups/2021-05-19_23-00-37/ \
Insert picture description here

Data recovery

[[email protected] ~]# innobackupex --copy-back /backups/2021-05-19_23-00-37/
[[email protected] ~]# chown -R mysql:mysql /usr/local/mysql/data/
[[email protected] ~]# mysql -uroot -p123123 -e "select * from Coco.test;"
Insert picture description here

to sum up:

  1. Incremental backup requires the use --incrementalparameter to specify which directory to be backed up, use the incremental-dirparameter to specify the full backup directory.
  2. When data backup, it uses --apply-log redo-onlyparameters to merge the first full backup data directory data, ensure data consistency bountiful directory.
  3. Then the incremental backup data using the --incremental-dirparameter data to merge them into all equipment.
  4. Finally, restore the data through the full backup data. Note that if there are multiple incremental backups, they need to be merged into the full backup data one by one before restoring.