catalogue
1. Concept of incremental backup
2. Advantages and disadvantages of incremental backup
3. Significance of binary log backup
2. Recovery of changed data after loss of full backup
3. Recover all data lost after full backup
4. Point in time and location based recovery
preface
MySQL incremental backup and recovery method, which can reduce the size of backup files and speed up backup and recovery on the basis of full backup.
1, MySQL incremental backup
1. Concept of incremental backup
It is easy to see the problem of full backup. Every time, all data contents are backed up. There are a lot of duplicate data in the backed up data, and the time of full backup and recovery is very long. The solution to the problem of full backup is to use incremental backup, which is to backup the files or contents added or changed since the last backup.
2. Advantages and disadvantages of incremental backup
Advantages of incremental backup: no duplicate data, small amount of backup and short time.
Disadvantages of incremental backup: all incremental backups can be restored only after the last full backup and full backup. Moreover, it is cumbersome to reverse restore all incremental backups one by one.
3. Significance of binary log backup
The binary log holds all operations that update or possibly update the database.
The binary log starts recording after the MySQL server is started and the file reaches max_binlog_size or the log file re created after receiving the flush logs command.
You only need to periodically execute the flush logs method to recreate new logs, generate binary file sequences, and save these logs in time to a safe place to complete an incremental backup for a period of time.
4. Realize incremental backup
To perform incremental MySQL backup, you must first enable the binary log function. Enable binary log function.
[root@localhost ~]# vim /etc/my.cnf ... log-bin=mysql-bin binlog_format = MIXED #Optional, specify that the recording format of binary log is MIXED (MIXED input) [root@localhost ~]# systemctl restart mysqld.service
Create the backup folder / opt/binlog, and save all backup files in this folder.
[root@localhost ~]# mkdir /opt/binlog [root@localhost ~]# cd /usr/local/mysql/data/ [root@localhost ~]# ls #Check out the binary folder [root@localhost data]# cp mysql-bin.000001 /opt/binlog/ [root@localhost data]# mysqlbinlog --no-defaults /opt/mysql-bin.000001 #View binary log files #--Base64 output = decode rows: use 64 bit encoding mechanism to decode and read rows #-v: Show details #--No defaults: default character set (no UTF-8 error will be reported) PS: You can export the decoded file as txt Format for easy reference mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 > /opt/mysql-bin.000001.txt
For daily incremental backup operations, use the option flush of mysqladmin_ Logs generates a new binary file, so that after inserting new data, the new binary file corresponds to the changed contents of the database.
[root@localhost opt]# mysqladmin -uroot -p123456 flush-logs [root@localhost opt]# ls /usr/local/mysql/data/ mysql-bin.000001 mysql-bin.000002 #Newly generated binary
When we back up the member table under info, the maximum number is. 00000 2, and subsequent database operations will be saved under. 00000 3.
Note: the maximum number of MySQL bin file before generation is 2, and after generation is 3. Subsequent database operations will be saved to the binary file with number 3.
2, Incremental recovery
Incremental recovery is more cumbersome than full recovery. Each incremental backup is an individual, and the data is not repeated, so it needs to be controlled more accurately.
1. Incremental recovery mode
General recovery: recover all the backup binary log contents.
Location based recovery: the database may have both wrong and correct operations at a certain point in time. You can skip the wrong operation based on the accurate location. The node before the wrong node stops at the location of the last correct operation.
Recovery based on point in time: data recovery is realized by skipping an error point in time, stopping at the error point in time and starting at the next correct point in time.
2. Recovery of changed data after loss of full backup
Simulate the deletion of two pieces of inserted data, that is, assume that the data after full backup is lost.
When using binary files for recovery, you should pay attention to the recovery order. First recover the first generated binary files, and then execute them in turn.
3. Recover all data lost after full backup
When all data is lost after full backup and incremental backup, you need to restore the full backup and all incremental backup files one by one.
Simulate the deletion of the data before the full backup, that is, the data of the member table before the full backup and the data after the full backup are lost.
First, use the mysql command to perform a full backup operation.
Then use binary files for incremental recovery. Pay attention to the recovery order. First recover the first generated binary files, and then execute them in turn.
4. Point in time and location based recovery
The binary log can be used to realize recovery based on time point and location. For example, due to the accidental deletion of a table, it is useless to completely recover at this time, because there are misoperation statements in the log. What we need is to recover to the state before the misoperation, skip the misoperation statements, and then recover the statements after the operation.
Two pieces of data are inserted into the database, but due to misoperation, one piece of data is deleted by mistake in the two insert statements. This data needs to be recovered.
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001
(1) Point in time based recovery
It is to import the binary log at a certain starting time into the database, so as to skip a certain point in time when an error occurs to realize data recovery. Use mysqlbinlog plus -- stop datemine option to indicate the time point at which to end. The following misoperation statements will not be executed. The - start datetime option means to execute the following statements. Combined with them, you can skip the misoperation statements and complete the recovery work.
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2021-10-27 17:26:03' --stop-datetime='2021-10-27 18:26:21' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456
(2) Location based recovery
First, make sure to skip the statement deleted by misoperation to achieve the purpose of recovery. First find the location of misoperation.
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='3078' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456 #Restore only data before operation ID "3078" [root@localhost data]# mysqlbinlog --no-defaults --start-position='3190' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456 #Restore only data before operation ID "3190"
summary
MySQL does not directly provide an incremental backup tool, which needs to be operated with the help of binary log files.
Incremental backup is performed by splitting logs.
Incremental recovery needs to be recovered one by one according to the time sequence of log files.
With time - and location-based recovery, data can be recovered more accurately.