MySQL incremental backup and breakpoint recovery

Introduction
Incremental backup means that after a full or last incremental backup, each subsequent backup needs only to back up the files that were added or modified compared to the previous one. This means that the object of the first incremental backup is the addition and modification of the file that is produced after the full preparation, and the second incremental backup object is the addition and modification of the file resulting from the first incremental backup, and so on.
Purpose
Resolves a long, slow recovery problem in a full backup, taking an incremental backup
features
Excellent: No duplication of data, small amount of backup, short time
Short: Incremental backups that require the last full backup and full backup to recover, requiring incremental backups to be repeated one after the other, cumbersome to operate
Implementation Method
Incremental backups are implemented indirectly via MySQL binary logs:
Binary logs save all updated or potentially updated data
Binary logs start logging at MySQL startup and new log files are recreated
The flush logs method needs to be executed periodically to recreate the log, generating a sequence of binary files
Lab Environment:
A CENTOS7 virtual machine with a mysql5.7 database installed
Operation Process:
One, incremental backup
1. Add a binary log to the configuration file

Vim/etc/my.cnf
[Mysqld] #在此模块下添加
Log-bin=mysql-bin #二进制日志

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, in the database free to create database, table, as an experimental template

4. Full backup of the school database

Mysqldump-uroot-pabc123 School >/opt/school.sql

5, refresh the log, generate a new log, note: Before the operation of the database is written in the log numbered 000001, the newly generated 000002 log is empty

mysqladmin-uroot-pabc123 Flush-logs #刷新日志
Mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000001 #查看000001日志
Mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000002 #查看000002日志
#--base64-output=decode-rows-v: Avoid garbled files, the previous view command can also be used

6. New Add MySQL database operation, then refresh log, make incremental backup

Use school;
Mysql>indert into info (id,name,score) VALUES (3, ' AAA ', 78);
Mysql>delete from info where name= ' Tom '; #误操作
mysql> INSERT into info (id,name,score) VALUES (4, ' BBB ', 64);
Mysqladmin-uroot-pabc123 flush-logs #刷新日志, generated 000003
#此次增量备份已记录到000002日志文件中

7. Delete Info table

Mysql-uroot-pabc123-e ' use School;drop table info;

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

Mysql-uroot-pabc123 School </opt/school.sql #完全备份还原

Mysqlbinlog--no-defaults mysql-bin.000002 | Mysql-uroot-p

Then, there is a problem, if there is a mistake, restore the backup error, and do not achieve the ideal backup, to achieve the true purpose. In this case, a broken chain recovery (a special use of the recovery incremental backup) is required, i.e. only the correct database operation is restored at restore time.

Second, broken chain recovery
You need to revert to a state that has not been restored to an incremental backup before you can perform a broken chain recovery.

Based on point in time

2018-09-04 16:43:52 Error operation time (start load at this point in time end)
2018-09-04 16:44:32 correct operation time (start loading again from this point in time) Start loading again from this point in time
Mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000002 #查看000002日志

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 Last Known good execution position--stop-position
At 667 the next time the correct execution position--start-position
Mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000002 #查看000002日志

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

MySQL incremental backup and breakpoint recovery