# MySQL incremental backup and recovery

Incremental backup concept:

Backup files or content that have been added or changed since the last backup

Features of incremental backups:

Advantages: No duplication of data, backup volume is small, time is short.

Disadvantage: All incremental backups are required after the last full and full backup to recover, and all incremental backups are re-pushed back-by-side for a cumbersome operation.

MySQL does not provide an incremental backup method directly, but it can be indirectly implemented via MySQL's binary log (binary logs). The meaning of the binary log to the backup is as follows:

(1) The binary log saves all updates or operations that may update the database.

(2) The binary log starts recording after starting the MySQL server and re-creates the new log file after the file reaches the size set by max_binlog_size or receives the Flush-logs command.

(3) You only need to execute the Fulsh-logs method periodically to recreate the new log, generate a sequence of binaries, and save these logs to a safe place in a timely manner to complete an incremental backup of the time period.

Incremental backup

1. To perform an incremental MySQL backup, first turn on the binary logging feature. To open the binary log method:

(1) Add the Log-binfilepath entry in the [Mysqld] entry in the MySQL configuration file, such as Log-bin=mysql-bin, and then restart the Mysqld service.

[[email protected] ~]# vim /etc/my.cnf[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8log-bin=mysql-bin            //添加项//pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1[[email protected] ~]# systemctl restart mysqld.service     //重启MySQL服务//

(2) using the Mysqladmin option Flush-logs generate a new binary file mysql-bin.000001, so that after inserting new data, the new binaries correspond to the contents of the database changes.

[[email protected] data]# mysqladmin -uroot -p flush-logs    //增量备份//[[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  kgc    mysql-bin.000001  performance_schemaib_buffer_pool  ib_logfile0  ibtmp1       mysql  mysql-bin.index   sys

Incremental recovery

When data errors occur, you should choose whether to use full backup or incremental recovery, depending on the actual situation. The scenarios for incremental recovery are:

(1) An artificial SQL statement destroys the database.

(2) A system failure causes database data loss before the next full standby.

(3) in the master-slave architecture, the main library data has failed.

1. Data recovery steps changed after a full backup is lost

(1) First enter the MySQL database and create the school library and info table.

[[email protected] data]# mysql -uroot -p   //登录mysql数据库//mysql> create database school;     //创建新的数据库//Query OK, 1 row affected (0.01 sec)mysql> show databases;        //查看数据库//+--------------------+| Database           |+--------------------+| information_schema || kgc                || mysql              || performance_schema || school             || sys                |+--------------------+6 rows in set (0.00 sec)mysql> use school;        //进入数据库//

(2) Insert 1 data into the database and exit the MySQL database. Use mysqldump to fully back up the school library.

mysql> create table info(name varchar(10),score decimal(5,2)); //创建info表//mysqlmysql> show tables;   //查看表信息//+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> insert into info(name,score) values (‘tom‘,88);  //插入数据//mysql> select * from info;  //查看数据记录//+------+-------+| name | score |+------+-------+| tom  | 88.00 |+------+-------+1 row in set (0.00 sec)[[email protected] data]# mysqldump -uroot -p school > /opt/school.sql //完全备份school库//[[email protected] data]# ls /opt/school.sql

(3) Use Flush-log to generate a new binary file to hold the database operation statements after.

[[email protected] data]# mysqladmin -uroot -p flush-logs[[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  kgc    mysql-bin.000001  mysql-bin.index     schoolib_buffer_pool  ib_logfile0  ibtmp1       mysql  mysql-bin.000002  performance_schema  sys

(4) Insert 1 data into the database again and use Flush-log to generate a new binary file to save the database operation statements.

mysql> insert into info(name,score) values (‘abc01‘,77);  //插入数据//Query OK, 1 row affected (0.03 sec)mysql> select * from info;+-------+-------+| name  | score |+-------+-------+| tom   | 88.00 || abc01 | 77.00 |+-------+-------+2 rows in set (0.00 sec)[[email protected] data]# mysqladmin -uroot -p flush-logs  //增量备份//[[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  kgc    mysql-bin.000001  mysql-bin.000003  performance_schema  sysib_buffer_pool  ib_logfile0  ibtmp1       mysql  mysql-bin.000002  mysql-bin.index

(6) Delete the inserted 1 data using Delete, that is, assume that the data after the full backup is lost.

mysql> delete from info where name=‘abc01‘;   //删除数据//mysql> select * from info;        //查看数据记录//+------+-------+| name | score |+------+-------+| tom  | 88.00 |+------+-------+

(7) Incremental recovery using a binary file.

[[email protected] data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p //增量恢复//[[email protected] data]# mysql -uroot -p    //登录MySQL数据库//mysql> use school;             //进入school库//mysql> select * from info;    //查看数据记录//+-------+-------+| name  | score |+-------+-------+| tom   | 88.00 || abc01 | 77.00 |+-------+-------+

2. Point-in-time and location-based recovery

The use of binary log can be implemented based on time-point and location recovery, for example, due to the deletion of a table by mistake, the full recovery is no use, because there is still a false statement inside the log, we need to revert to the state before the wrong operation, and then skip the error operation of the statement, and then restore the statement behind the operation.

Suppose you need to insert two data into the database, but because of a mistake, the middle of the two INSERT statement deletes a piece of data, and this data should not be deleted.

mysql> insert into info(name,score) values (‘test01‘,77); //插入数据test01//mysql> delete from info where name=‘tom‘;                //误删除tom数据//mysql> insert into info(name,score) values (‘test02‘,77);    //插入数据test02//mysql> select * from info;      //查看数据记录//+--------+-------+| name   | score |+--------+-------+| jack   | 88.00 || test01 | 77.00 || test02 | 77.00 |+--------+-------+3 rows in set (0.00 sec)

(1) An incremental backup, the correct INSERT statement is saved in the binary file numbered 000002, and a DELETE statement that should not be executed is also saved.

[[email protected] data]# mysqladmin -uroot -p flush-logs  //增量备份//[[email protected] data]# lsauto.cnf        ib_logfile1  mysql-bin.000001  performance_schemaib_buffer_pool  ibtmp1       mysql-bin.000002  schoolibdata1         kgc          mysql-bin.000003  sysib_logfile0     mysql        mysql-bin.index

(2) Look at the binary file number 000002, find the location of the misoperation and the correct location for the operation, easy to recover.

[[email protected] data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002   //查看二进制文件# at 617     //位置标记//#180704 17:12:01 server id 1  end_log_pos 660 CRC32 0xe2f72a72  Delete_rows: table id 118 flags: STMT_END_F   //误操作的起始时间//### DELETE FROM `school`.`info`   //误删的数据//### WHERE###   @1=‘tom‘###   @2=88.00# at 884   //位置标记//#180704 17:12:08 server id 1  end_log_pos 930 CRC32 0xd17eb525  Write_rows: table id 118 flags: STMT_END_F  //操作正确的起始时间//### INSERT INTO `school`.`info`     //插入的数据//### SET###   @1=‘test02‘###   @2=77.00

(3) Log in to the MySQL database to delete the original error deleted data information table, using MySQL Restore school library.

mysql> drop table info;   //删除info表//mysql> show tables;Empty set (0.00 sec)[[email protected] opt]# mysql -u root -p school < /opt/school.sql  //恢复school库//[[email protected] opt]# mysql -uroot -pmysql> show tables;+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;+------+-------+| name | score |+------+-------+| tom  | 88.00 |                  //数据tom恢复//| jack | 88.00 |+------+-------+2 rows in set (0.00 sec)

(4) Point-in-time recovery

Use the Mysqlbinlong plus--stop-datetime option to indicate at which point in time the end, the subsequent error action statements do not execute, the--start-datetime option means to execute the following statements, together with them can skip the error operation of the statement, complete the recovery work. It is important to note that the date format saved in the binary file needs to be adjusted to be split with "-".

[[email protected] data]# mysqlbinlog --no-defaults --stop-datetime=‘2018-07-04 17:12:01‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p[[email protected] opt]# mysql -u root -pmysql> use school;mysql> select * from info;+--------+-------+| name   | score |+--------+-------+| tom    | 88.00 || jack   | 88.00 || test01 | 77.00 |        //test01数据恢复//+--------+-------+3 rows in set (0.00 sec)[[email protected] data]# mysqlbinlog --no-defaults --start-datetime=‘2018-07-04 17:12:08‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -pmysql> select * from info;+--------+-------+| name   | score |+--------+-------+| tom    | 88.00 || jack   | 88.00 || test01 | 77.00 || test02 | 77.00 |       //test02数据恢复//+--------+-------+4 rows in set (0.00 sec)

(5) Location-based recovery

is using point-in-time recovery, which can occur at a point in time with both the right operation and the wrong operation, location-based is a more accurate way to recover.

① deleting test01 and test02 data

mysql> delete from info where name=‘test01‘;mysql> delete from info where name=‘test02‘;mysql> select * from info;+------+-------+| name | score |+------+-------+| tom  | 88.00 || jack | 88.00 |+------+-------+2 rows in set (0.00 sec)

② Open the binary file of number 000002 and find the location of the misoperation.

# at 563    //上一次正确操作节点//#180704 17:12:01 server id 1  end_log_pos 617 CRC32 0x5b03a315  Table_map: `school`.`info` mapped to number 118# at 617   //误操作//#180704 17:12:01 server id 1  end_log_pos 660 CRC32 0xe2f72a72  Delete_rows: table id 118 flags: STMT_END_F### DELETE FROM `school`.`info`### WHERE###   @1=‘tom‘###   @2=88.00# at 660   //下一次正确操作节点//#180704 17:12:01 server id 1  end_log_pos 691 CRC32 0x1230c738  Xid = 37COMMIT/*!*/;

③ the position after the first INSERT statement is 563,--stop-position is set to 563, which means that the first INSERT statement is executed after the end. The position after the second INSERT statement is 660,--start-position set to 660, which means that the second INSERT statement is executed, and the deletion statement is skipped, which is the purpose of recovering the data.

[[email protected] opt]# mysqlbinlog --no-defaults --stop-position=‘563‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p[[email protected] opt]# mysqlbinlog --no-defaults --start-position=‘660‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p[[email protected] opt]# mysql -uroot -p   //登录mysql数据库//mysql> use school;      //进入school库//mysql> select * from info;     //查看数据记录//+--------+-------+| name   | score |+--------+-------+| tom    | 88.00 || jack   | 88.00 || test01 | 77.00 || test02 | 77.00 |+--------+-------+4 rows in set (0.00 sec)

# MySQL incremental backup and recovery