MySQL incremental backup and breakpoint recovery script example

Introduction

Incremental backup means that after a full backup or the last incremental backup, each subsequent backup only needs to back up the files that have been added or modified compared to the previous one. This means that the object of the first incremental backup is the added and modified files generated after the full backup; the object of the second incremental backup is the added and modified files generated after the first incremental backup, and so on.

Purpose

To solve the problem of long time and slow recovery in full backup, incremental backup is adopted

Features

Advantages: No duplicate data, small backup volume, short time: Need to restore from the last full backup and the incremental backup after the full backup, need to restore the incremental backups one by one repeatedly, cumbersome operation

Implementation

Indirect incremental backup through mysql binary log:
The binary log saves all updated or potentially updated data. The binary log starts recording when MySQL starts, and a new log file is created. The flush logs method needs to be executed regularly to recreate the log and generate a binary file sequence.

Experimental environment:

A centos7 virtual machine with mysql5.7 database installed

Operation process:

1. Incremental backup

1. Add binary log to the configuration file

vim /etc/my.cnf
[mysqld] #Add under this module
log-bin=mysql-bin #Binary log

2. Restart the service and view the binary log

systemctl restart mysqld.service
cd /usr/local/mysql/data/
mysqlbinlog --no-defaults mysql-bin.000001

3. Create databases and tables freely in the database as experimental templates

4. Make a complete backup of the school database

mysqldump -uroot -pabc123 school > /opt/school.sql

5. Refresh the log and generate a new log. Note: the previous database operation is written to the log numbered 000001, and the newly generated log numbered 000002 is empty.

mysqladmin -uroot -pabc123 flush-logs #Flush logs
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 #View 000001 log
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 #View 000002 log
#--base64-output=decode-rows -v: To avoid garbled characters in the file, the previous viewing command can also be used

6. Add new MySQL database operations, then refresh the logs and perform incremental backups

use school;
mysql>indert into info (id,name,score) values ​​(3,'aaa',78);
mysql>delete from info where name='tom'; #Error
mysql> insert into info (id,name,score) values ​​(4,'bbb',64);
mysqladmin -uroot -pabc123 flush-logs #Flush logs, generate 000003
#This incremental backup has been recorded in log file 000002

7. Delete the info table

mysql -uroot -pabc123 -e 'use school;drop table info;'

8. Restore the full backup, then restore the incremental backup

mysql -uroot -pabc123 school < /opt/school.sql #Full backup and restore

mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -p

Well, here there is a problem. If an error occurs, an error will occur when restoring the backup, and the ideal backup cannot be achieved to achieve the real purpose. At this time, broken-chain recovery is required (a special method of restoring incremental backups), that is, only correct database operations are restored during restoration.

2. Broken Link Recovery

Before operating a broken link recovery, you need to restore to the state without incremental backup and restore, as shown in the figure

Based on time point

2018-09-04 16:43:52 Error operation time (the beginning of the loading ends at this time point)
2018-09-04 16:44:32 Correct operation time (start loading again from this time point) Start loading again from this time point

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 #View 000002 log

mysqlbinlog --no-defaults --stop-datetime='2018-09-04 16:43:52' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-datetime='2018-09-04 16:44:32' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p

Based on location point

at 565 The last correct execution position --stop-position
at 667 Next correct execution position --start-position

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 #View 000002 log

mysqlbinlog --no-defaults --stop-position='565' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='667' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p