Detailed explanation of the method of incremental recovery by Mysql
Introduction to the experiment
Scenarios where incremental recovery is generally applicable:
1. Artificial sql statement destroys the database.
2. A system failure before the next full backup results in the loss of database data
3. In the master-slave architecture, the master database data failed.
Recovery steps for data changed after losing a full backup
1. First make a full backup to ensure that a fully backed up sql file is generated.
Mysql > select * from yx # Database +-+-+ | name | score | +-+-+ | zhangsan | 100.00 | lisi | 90.00 | wangwu | 80.00 | | zhaoliu | zhaoliu | +-+-+ 4 rows in set (0.00 sec) [root@promote data] # mysqldump-u Root-p test > / opt/test.sql # full backup of the database
2. Use flush-logs to generate a new binary log file to save the database operation statements.
[root@promote data] # mysqladmin-u root-p flush-logs # generates binaries
Enter password:
[root@promote data] # ls
Auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.index sys
Ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 performance_schema test
3. Insert a record in the database, and then perform the flush-logs operation to 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)
[root@promote data] # mysqladmin-u root-p flush-logs # generates binaries
Enter password:
[root@promote data] # ls
Auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.000002 performance_schema test
Ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 mysql-bin.index sys
4. Use delete to delete the data you just inserted. Data is lost 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 binaries for recovery operation
[root@promote data] # mysqlbinlog-- no-defaults mysql-bin.000001 | mysql- u root-p
6. Check the contents of the database and find the deleted data. Indicates that the data was recovered successfully.
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 for losing all data after a full backup
1. Delete the table yx using drop, and the simulated 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 to perform a full backup and restore operation.
[root@promote data] # mysql-u root-p test
< /opt/test.sql mysql>Use test
Database changed
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)
3. Use binaries for incremental backup operations.
[root@promote data] # mysqlbinlog-- no-defaults mysql-bin.000001 | mysql- u root-p
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 based on time and location
Use binary logs to achieve local point-in-time and location recovery. If you need to insert two pieces of data into the database, but due to misoperation, delete a piece of data between the two insert statements, and this data should not be deleted. Recovery needs to be based on point-in-time and location.
-start-datetime=datetime
Start reading from the first event in the binary log whose date time is equal to or later than the datetime parameter.
-stop-datetime=datetime
Stop reading from the first event in the binary log whose date time is equal to or later than the datetime parameter.
-start-position=N
Start reading from the event when the first position in the binary log is equal to the N parameter.
-stop-position=N
Stop reading from the event when the first 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. Recovery based on point in time. 18-07-03 21:56:04 is the error statement node, 18-07-03 21:56:11 the second correct statement node
[root@promote 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=0
Use `test` / *! * /
SET timestamp 1530626135
Insert into yx values ('test01',87)
/ *! * /
# at 406
# 180703 21:55:35 server id 1 end_log_pos 437 CRC32 0xdd7913a3 Xid = 392
Com _ MIT _ blank /
# at 437
# 180703 21:56:04 server id 1 end_log_pos 502 CRC32 0x0d09bd0b Anonymous_GTID last_committed=1 sequence_number=2
SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com
# at 502
# 180703 21:56:04 server id 1 end_log_pos 581 CRC32 0xe6040c79 Query thread_id=46 exec_time=0 error_code=0
SET 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 error_code=0
SET timestamp 1530626164
Delete from yx where name='zhangsan'
/ *! * /
# at 691
# 180703 21:56:04 server id 1 end_log_pos 722 CRC32 0x4a742173 Xid = 393
Com _ MIT _ blank /
# at 722
# 180703 21:56:11 server id 1 end_log_pos 787 CRC32 0x6d0b47d8 Anonymous_GTID last_committed=2 sequence_number=3
SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com
# at 787
# 180703 21:56:11 server id 1 end_log_pos 866 CRC32 0x97e2deb7 Query thread_id=46 exec_time=0 error_code=0
SET 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 error_code=0
SET timestamp 1530626171
Insert into yx values ('test02',99)
[root@promote 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)
[root@promote data] # mysqlbinlog-- no-defaults-- stop-datetime='18-07-03 21 no-defaults 56 stop-datetime='18 04' mysql-bin.000003 | mysql- u root-p # end node
Enter password:
[root@promote data] # mysqlbinlog-- no-defaults-- start-datetime='18-07-03 21 no-defaults 56 start-datetime='18 11' mysql-bin.000003 | mysql- u root-p # restart node
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. Location-based recovery, where 581 is the node of the error statement and 866 is the node of the second correct statement
[root@promote 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.01sec)
[root@promote data] # mysqlbinlog-- no-defaults-- stop-position='581' mysql-bin.000003 | mysql- u root-p
Enter password:
[root@promote data] # mysqlbinlog-- no-defaults-- start-position='866' 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)
