MySQL Incremental Backup and Recovery

1. Incremental backup features

• The advantages of incremental backup are no duplicate data, small backup volume and short time. The disadvantage is also obvious, all incremental backups after the last full backup and full backup are required to recover, and all incremental backups are backed up one by one, which is more cumbersome

• MySQL does not provide a direct incremental backup method, but incremental backup can be achieved indirectly through MySQL binarylogs

• The meaning of binary logs for backup is as follows:

(1) Binary logs hold all operations that update or possibly update the database
(2) Binary logs begin to be logged after MySQL server is started, and new log files are recreated after the file reaches the size set by max_binlog_size or after receiving the flush logs command
(3) An incremental backup of a time period can be completed by executing the flushlogs method regularly to recreate new logs, generate binary file sequences, and save these logs in a safe place in time.

2. Examples

• 1 Turn on binary logging

[root@mysql ~]#vim /etc/my.cnf log-bin=mysql-bin #Open the binary log file named mysql-bin binlog_format = MIXED #Specifies that the record format for binlog is MIXED [root@mysql ~]#systemctl restart mysqld.service #View binary log files [root@mysql /usr/local/mysql/data]#ls -l /usr/local/mysql/data/mysql-bin.* -rw-r----- 1 mysql mysql 154 10 February 2614:53 /usr/local/mysql/data/mysql-bin.000001 -rw-r----- 1 mysql mysql 19 10 February 2614:53 /usr/local/mysql/data/mysql-bin.index

Binary logs have three different record formats: STATEMENT (SQL statement based), ROW (line based), MIXED (mixed mode), and STATEMENT by default.
(1) STATEMENT (based on SQL statement):
Each sql involved in the modification is recorded in the binlog
Disadvantages: Logging is too large, such as the sleep() function, last_insert_id ()>, and problems with schema logging such as user-defined fuctions (udf), master-slave replication, etc.
Summary: Add, delete and change checks are recorded using sql statements. If high concurrency is used, errors may occur, time differences or delays may occur, recovery may not be what we want, you may delete or modify first, or the reverse may occur. Low accuracy

(2) ROW (line-based)
Record changes only, not sql context
Disadvantages: If you encounter update...set...where true, binlog data will become larger and larger
Summary: update and delete work with multiple rows of data to record changes in rows, not sql context, such as sql statement to record a row, but ROW may record 10 rows, but high accuracy, high concurrency due to the amount of operation, low performance and large record

(3) MIXED recommends using statement in general and ROW in function storage.

(2) Choose a period of less server load or less user access for backup every week

#Make a full backup of the table mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql #Make a full backup of the library mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql #Execute with planned tasks crontab -e 30 1 * * 3 mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql 30 1 * * 3 mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql #Full backup of databases and tables every Wednesday at 1:30 a.m.

(3) Incremental backup operations can be performed daily to generate new binary log files

ls /usr/local/mysql/data mysqladmin -uroot -p123456 flush-logs

(4) Insert new data to simulate the increase or change of data

mysql> insert into IT_member values('Operations and Maintenance Engineer','Zhang San',22,'8','Undergraduate',7,20000); Query OK, 1 row affected (0.01 sec) mysql> select * from IT_member; +--------------------+-----------+--------+----------+--------+--------+----------+ | post | Full name | Age | staff ID | Education | Years | salary | +--------------------+-----------+--------+----------+--------+--------+----------+ | Cloud Computing Engineer | Gu Lei | 18 | 1 | Undergraduate | 6 | 18888.00 | | Cloud Computing Engineer | Cui Peiwen | 19 | 2 | Undergraduate | 7 | 19999.00 | | Operations and Maintenance Engineer | Zhang San | 22 | 8 | Undergraduate | 7 | 20000.00 | +--------------------+-----------+--------+----------+--------+--------+----------+ 3 rows in set (0.00 sec)

Generate a new binary file and view its contents

cd /usr/local/mysql/data/ mysqladmin -uroot -p123456 flush-logs

Note: The database operation of step 4 above will be saved in the mysql-bin.000002 file, after which we tested that the operation to delete the library will be saved in the mysql-bin.000003 file (in case we still delete the library when we restore based on the mysql-bin.000002 log)

#Copy the binary 02 recording the changes to the / opt directory cp mysql-bin.000002 /opt/ cd /opt/ #Use 64-bit encoding to decode and read details by line mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

1. Incremental recovery scenarios

When sending data incorrectly, you should choose whether to use full or incremental backup recovery depending on your actual situation

Scenarios for incremental backup:
1. Artificial SQL statements destroy the database
(2) Missing database data due to sending system failures before next full-time
(3) In the master-slave architecture, the master database data sent a failure

There are two types of data loss:
1. Only data changed after full backup is lost
Lost all data after full backup

2. Recovery of data changed after loss of full backup

#Add Table Content mysql> insert into gl values ('wangliu',4); mysql> insert into gl values ('wangw',5); #Intercepting log files mysqladmin -u root -p123456 flush-logs [root@mysql /usr/local/mysql/data]#ls #Generate 03, add data operation saved in 02 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 ...... #Delete the two inserted pieces of data to simulate data loss after a full backup mysql> delete from gl where id=4; mysql> delete from gl where id=5; #Recovery using binary files mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -p

3. Lose all data after full backup

#Create a new database and data table mysql> create database class; mysql> use class; mysql> create table gl(name char(8),ID int not null,primary key (ID)); mysql> insert into gl values('zhangsan','1'); ...... mysql> insert into gl values('laal','6'); mysql> select * from gl where id; +----------+----+ | name | ID | +----------+----+ | zhangsan | 1 | | wangwu | 2 | | wangliu | 3 | | lisi | 4 | | wb | 5 | | laal | 6 | +----------+----+ #Full backup mysqldump -uroot -p123456 class gl > /opt/class_gl_$(date +%F).sql #Refresh Log mysqladmin -u root -p flush-logs #Continue adding information to database tables insert into gl values('ssa',7); insert into gl values('sa',8); mysql> select * from gl; +----------+----+ | name | ID | +----------+----+ | zhangsan | 1 | | wangwu | 2 | | wangliu | 3 | | lisi | 4 | | wb | 5 | | laal | 6 | | ssa | 7 | | sa | 8 | +----------+----+ #Delete library class, simulate failure mysql> drop database class; #Restore data based on full backup of 000001, restore 000002, 000003 in turn, and verify the results mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -p mysqlbinlog --no-defaults mysql-bin.000003 | mysql -uroot -p

4. Recovery based on time and location

Binary logs allow point-in-time and location-based recovery, such as deleting a table due to a misoperation. Full recovery is not useful at this time because there are still misoperation statements in the log. What we need is to restore to the state before the misoperation, skip the misoperation statements, and restore the statements after the misoperation.

4.1 Point-in-Time Recovery

Point-based recovery is to import a binary file of a starting time into the database, skipping a point in time at which an error occurred to recover the data
Use mysqlbinlog with the'-stop-datetime'option to indicate at what point in time the statement that was mistakenly followed does not execute
The start-datetime option indicates that the following statement is executed
Using them together, you can skip the statement of misoperation and complete the recovery.
It is important to note that the date format saved in the binary file needs to be adjusted to be split with'-'

#Restore user "lier" data mysql -uroot -p123456 -e "truncate table class.gl;" #Empty table data mysql -uroot -p123123 -e "select * from class.gl;" mysqlbinlog --no-defaults --stop-datetime='2021-10-27 0:39:20' mysql-bin.000002 |mysql -uroot -p123456 mysql -uroot -p123456 -e "select * from class.gl;"

4.2 Location-based Recovery

Location-based recovery is the use of point-in-time recovery. Location-based recovery is a more precise way to recover from a point in time when both the correct and the wrong operations exist.

#Decode and read the details of binary file 02 (incremental backup) by line using a 64-bit encoding mechanism mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001

#Restore only pre-778 data, that is, do not restore lier data mysql -uroot -p123456 -e "select * from class.gl;" mysql -uroot -p123456 -e "truncate table class.gl;" mysql -uroot -p123456 -e "select * from class.gl;" mysqlbinlog --no-defaults --stop-position='778' mysql-bin.000001 | mysql -uroot -p123456 mysql -uroot -p123456 -e "select * from class.gl;"

#Restore "lier" data only mysql -uroot -p123456 -e "select * from class.gl;" mysqlbinlog --no-defaults --start-position='955' mysql-bin.000001 | mysql -uroot -p123456 mysql -uroot -p123456-e "select * from class.gl;"

5. Ideas for specifying enterprise backup strategy

• Specify an enterprise backup strategy based on the frequency of actual reading and writing of the enterprise database and the importance of the data
• Frequent data updates should result in more frequent backups
• Data is more important, backup when appropriate updates are available
• Be prepared during periods of low database pressure, such as once a week, then daily
• Depending on the size of the company, small and medium-sized companies can be prepared once a day, large companies can be prepared once a week, supplemented once a day, and try to achieve master-slave replication architecture for the enterprise

• Incremental backup using split logs
• Incremental backups need to be performed one by one based on the time of the log files
• Recovery using a time-and location-based approach allows more accurate data recovery

Add new comment

0 comments