How to use mysqlbinlog for point-in-time data recovery
This article will explain in detail how to use mysqlbinlog to do point-in-time data recovery. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | mysql |
| | performance_schema |
| | test |
+-+
4 rows in set (0.00 sec)
Mysql > use test
Database changed
Mysql > show tables
Empty set (0.00 sec)
Mysql > show binary logs
+-+ +
| | Log_name | File_size |
+-+ +
| | mysql-bin.000001 | 120 | |
+-+ +
1 row in set (0.00 sec)
Mysql >
Mysql >
Mysql > flush logs
Query OK, 0 rows affected (0.16 sec)
Mysql > show binary logs
+-+ +
| | Log_name | File_size |
+-+ +
| | mysql-bin.000001 | 167|
| | mysql-bin.000002 | 120 | |
+-+ +
2 rows in set (0.00 sec)
Create a new table chenfeng and insert three records:
Mysql > create table chenfeng (T1 int not null primary key,t2 varchar (50), T3 datetime)
Query OK, 0 rows affected (0.13 sec)
Mysql > insert into chenfeng values (1, "beijing", now ())
Query OK, 1 row affected (0.02 sec)
Mysql > insert into chenfeng values (2, "shanghai", now ())
Query OK, 1 row affected (0.02 sec)
Mysql > insert into chenfeng values (3, "zhengzhou", now ())
Query OK, 1 row affected (0.03 sec)
Mysql > select * from chenfeng
+-- +
| | T1 | T2 | T3 | |
+-- +
| | 1 | beijing | 2017-01-25 15:33:54 |
| | 2 | shanghai | 2017-01-25 15:34:08 |
| | 3 | zhengzhou | 2017-01-25 15:34:23 |
+-- +
3 rows in set (0.00 sec)
Now let's perform a delete misoperation to delete all the data:
Mysql > delete from chenfeng
Query OK, 3 rows affected (0.04 sec)
First check binlog to generate 002.sql:
Mysqlbinlog mysql-bin.000002 > 002.sql
Check the 002.sql and extract only part of the delete:
BEGIN
/ *! * /
# at 1094
# 170125 15:37:10 server id 1 end_log_pos 1188 CRC32 0x53d8348f Query thread_id=197 exec_time=0 error_code=0
SET timestamp 1485329830
Delete from chenfeng
/ *! * /
# at 1188
# 170125 15:37:10 server id 1 end_log_pos 1219 CRC32 0xfe067937 Xid = 25
Com _ MIT _ blank /
DELIMITER
# End of log file
ROLLBACK / * added by mysqlbinlog * /
/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/
You can see that we made a delete misoperation at 15:37:10 at time 2017-01-25. Now you need to use mysqlbinlog to recover the data before this point in time:
# mysqlbinlog mysql-bin.000002-- stop-date='2017-01-25 15 resume.sql 37 resume.sql
After executing the resume.sql content, it is found that the data has been restored:
Mysql > select * from chenfeng
+-- +
| | T1 | T2 | T3 | |
+-- +
| | 1 | beijing | 2017-01-25 15:33:54 |
| | 2 | shanghai | 2017-01-25 15:34:08 |
| | 3 | zhengzhou | 2017-01-25 15:34:23 |
+-- +
3 rows in set (0.00 sec)
This is the end of the article on "how to use mysqlbinlog for point-in-time data recovery". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
