MySQL Incremental backup and recovery

MySQL incremental backup and Recovery experiment Introduction

Incremental recovery scenarios that are generally applicable:

1, the human SQL statement destroys the database

2. Loss of database data due to a system failure before the next full backup

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

Recovery steps for data changed after a full backup is lost

1. First make a full backup to ensure that a full backup of the SQL file is generated.

mysql> select * from yx;   #完全备份前数据库+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 |+----------+--------+4 rows in set (0.00 sec)[[email protected] data]# mysqldump -u root -p test > /opt/test.sql    #对数据库完全备份

2. Use Flush-logs to generate a new binary log file to save the database operation statements.

[[email protected] data]# mysqladmin -u root -p flush-logs  #生成二进制文件Enter password: [[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.index     sysib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  performance_schema  test

3, insert a record in the database, then perform flush-logs operation, generate a new binary incremental backup file.

mysql> insert into yx(name,score) values(‘tom‘,87);Query OK, 1 row affected (0.00 sec)mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 || tom      |  87.00 |+----------+--------+5 rows in set (0.00 sec)[[email protected] data]# mysqladmin -u root -p flush-logs  #生成二进制文件Enter password: [[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.000002  performance_schema  testib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  mysql-bin.index   sys

4. Delete the data you just inserted using delete. Data loss after simulating a full backup.

mysql> delete from yx where name=‘tom‘;Query OK, 1 row affected (0.00 sec)mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 |+----------+--------+4 rows in set (0.00 sec)

5. Use binary file for recovery operation

[[email protected] data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p

6, to view the contents of the database, deleted data has. Indicates that the data recovery was successful.

mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 || tom      |  87.00 |+----------+--------+5 rows in set (0.00 sec）

Recovery steps to lose all data after a full backup

1. Use drop to delete table YX, the simulation data is completely lost

mysql> drop table yx;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.00 sec)

2, first use the MySQL command for a full backup recovery operation.

[[email protected] data]# mysql -u root -p test < /opt/test.sql mysql> use test;Database changedmysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 |+----------+--------+4 rows in set (0.00 sec)

3. Use binary files for incremental backup operation.

[[email protected] data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -pmysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 || tom      |  87.00 |+----------+--------+5 rows in set (0.00 sec)

Recovery based on point-in-time and location

Using binary log to achieve local time point and location recovery, if you need to insert two data into the database, but due to misoperation, two inserted statements in the middle of the deletion of a piece of data, and this data should not be deleted, this time, it is necessary to restore based on the point and location.

–start-datetime=datetime
Starts reading from the binary log of events that are equal to or later than the DateTime parameter in the 1th date time.

–stop-datetime=datetime
Stop reading from the binary log for events that are equal to or later than the DateTime parameter in the 1th date time.

–start-position=n
Start reading from the event that the 1th position in the binary log equals the n parameter.

–stop-position=n
Stops reading from the event that the 1th position in the binary log is equal to and greater than the N parameter.

mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 |+----------+--------+5 rows in set (0.00 sec)mysql> insert into yx values(‘test01‘,87);Query OK, 1 row affected (0.00 sec)mysql> delete from yx where name=‘zhangsan‘;Query OK, 1 row affected (0.00 sec)mysql> insert into yx values(‘test02‘,99);Query OK, 1 row affected (0.17 sec)mysql> select * from yx;+---------+-------+| name    | score |+---------+-------+| lisi    | 90.00 || wangwu  | 80.00 || zhaoliu | 99.00 || test01  | 87.00 || test02  | 99.00 |+---------+-------+6 rows in set (0.00 sec)

1, based on time-point recovery. 18-07-03 21:56:04 is the error statement node, 18-07-03 21:56:11 The second sentence the correct statement node

[[email protected] data]# mysqlbinlog--no-defaults--base64-output=decode-rows mysql-bin.000003# at 298#180703 21:55:35 Server ID 1 end_log_pos 406 CRC32 0x257c67ab Query thread_id=46 exec_time=0 error_code=0use ' test '/*!*/; SET Timestamp=1530626135/*!*/;insert into YX values (' test01 ', 43)/*!*/;# at 406#180703 21:55:35 server ID 1 end_log_pos 7 CRC32 0xdd7913a3 Xid = 392commit/*!*/;# at 437#180703 21:56:04 Server ID 1 end_log_pos 502 CRC32 0x0d09bd0b Anonymous _gtid last_committed=1 sequence_number=2set @ @SESSION.    gtid_next= ' ANONYMOUS '/*!*/;# at 502#180703 21:56:04 server ID 1 end_log_pos 581 CRC32 0xe6040c79 Query thread_id=46 Exec_time=0 Error_code=0set timestamp=1530626164/*!*/; begin/*!*/;# at 581#180703 21:56:04 Server ID 1 end_log_pos 691 CRC32 0x2d99f699 Query thread_id=46 exec_time=0 Err Or_code=0set timestamp=1530626164/*!*/;d elete from Yx where name= ' Zhangsan '/*!*/;# at 691#180703 21:56:04 server ID 1 end _log_pos 722 CRC32 0x4a742173 Xid = 393commit/*!*/;# at 722#180703 21:56:11 Server ID 1 end_log_pos 787 CRC32 0x6d0b47d8 Anonymous_gtid last_committed=2 s Equence_number=3set @ @SESSION.    gtid_next= ' ANONYMOUS '/*!*/;# at 787#180703 21:56:11 Server ID 1 end_log_pos 866 CRC32 0X97E2DEB7 Query thread_id=46 Exec_time=0 Error_code=0set timestamp=1530626171/*!*/; begin/*!*/;# at 866#180703 21:56:11 Server ID 1 end_log_pos 974 CRC32 0x9e24e8af Query thread_id=46 exec_time=0 Err Or_code=0set Timestamp=1530626171/*!*/;insert into YX values (' test02 ', 99)

[[email protected] data]# mysql -u root -p test < /opt/test.sql   #先进行完全恢复mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 |+----------+--------+4 rows in set (0.00 sec)[[email protected] data]# mysqlbinlog --no-defaults --stop-datetime=‘18-07-03 21:56:04‘ mysql-bin.000003 | mysql -u root -p   #结束节点Enter password: [[email protected] data]# mysqlbinlog --no-defaults --start-datetime=‘18-07-03 21:56:11‘ mysql-bin.000003 | mysql -u root -p   #重新开始节点Enter password: mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 || test01   |  87.00 || test02   |  99.00 |+----------+--------+6 rows in set (0.00 sec)

2, based on location recovery, where 581 is the node of the error statement, 866 is the node of the second correct sentence

[[email protected] data]# mysql -u root -p test < /opt/test.sqlmysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 |+----------+--------+4 rows in set (0.01 sec)[[email protected] data]# mysqlbinlog --no-defaults --stop-position=‘581‘ mysql-bin.000003 | mysql -u root -pEnter password: [[email protected] data]# mysqlbinlog --no-defaults --start-position=‘866‘ mysql-bin.000003 | mysql -u root -pEnter password: mysql> select * from yx;+----------+--------+| name     | score  |+----------+--------+| zhangsan | 100.00 || lisi     |  90.00 || wangwu   |  80.00 || zhaoliu  |  99.00 || test01   |  87.00 || test02   |  99.00 |+----------+--------+6 rows in set (0.00 sec)

MySQL Incremental backup and recovery