Shulou(Shulou.com)06/01 Report--

Brothers, long time no see! Recently, I have been busy with the creation of new books, so I have no time to update my blog. I don't know how you are doing in this period of time. I hope you can help you by reading my article.

Lao Zhang, I also spent a lot of time recording some database video courses, if you are interested, you can also study, do not miss ah!

Take the time today to share with you an article on the use of the binlog2sql flashback tool to recover mistakenly deleted data. We all know that the role of binlog is to backup and restore and complete the master-slave replication function of MySQL. Mysqlbinlog tools can be used to recover data based on point-in-time or location offset. It is common to encounter erroneous deletion and correction of data in the production environment. We all know that Oracle databases have flashbacks, and MySQL itself doesn't have flashbacks, but we can use binlog2sql to do this.

We all know that binlog uses event as a unit to record the data information of database changes, and flashback can help us to reproduce the operation before the change data information. In other words, for insert operations, delete statements are generated, whereas for delete operations, insert statements are generated. For update operations, the opposite update statement is also generated. This tool can only be used in row mode in binlog format.

The following is an actual combat exercise:

Download address of binlog2sql tool: https://github.com/danfengcao/binlog2sql

Step 1: the environment is ready to install a list of various dependent toolkits

Python-pip, PyMySQL, python-mysql-replication,wheel argparse

Step 2: extract the binlog2sql software with the following command

Unzip binlog2sql-master.zipcd binlog2sql-masterpip install-r requirements.txt

Step 3: use the python binlog2sql.py-- help command to see the use of important parameters

-B,-- flashback generates rollback statements-- binlog files that start-file needs to parse-- start location of start-position parsing binlog-- end location of stop-position parsing binlog-- start-datetime parsing starts with binlog at which point in time, format must be datetime--stop-datetime to which time binlog stops parsing, format must be datetime-d,-- databases only outputs sql-t of target db,-- tables only outputs sql of target tables.

Step 4: start simulating data deletion

First delete the data in the t table under the zs library

Root@db 14:26: [zs] > select * from t +-+ | id | name | address | +-- + | 1 | aaa | bj | | 2 | bbb | sh | 3 | ccc | gz | 4 | ddd | sy | 5 | eee | fj | +-+ 5 rows in set Sec) root@db 14:26: [zs] > delete from t Query OK, 5 rows affected (0.04 sec) root@db 14:27: [zs] > select * from tincture empty set (0.00 sec)

Step 5: need to create a flashback user

Create user 'zs_test'@'%' identified by' 123456 grant select,replication slave,replication client on *. * to 'zs_test'@'%'; flush privileges

Step 6: determine the current binlog file and position location

Root@db 14:41: [zs] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-- -+ | mysql-binlog.000002 | 2091 | e10f0ead-d595-11e7-82cb-080027cd683a:1-8 | +- -+

You can see that the current binlog is: mysql-binlog.000002

Position offset: 2091

Step 7: need to estimate the time, the time range of misoperation should be between 02:20 and 02:30 in the afternoon. The command is as follows:

Python binlog2sql.py-h292.168.56.102-P3306-uzs_test-p123456-dzs-tt-- start-file='mysql-binlog.000002'-- start-datetime='2017-11-30 14 dzs 20ve00'-stop-datetime='2017-11-30 14purl 3000'

Output result:

DELETE FROM zs.t WHERE address='bj' AND id=1 AND name='aaa' LIMIT 1; # start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='sh' AND id=2 AND name='bbb' LIMIT 1; # start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='gz' AND id=3 AND name='ccc' LIMIT 1; # start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='sy' AND id=4 AND name='ddd' LIMIT 1; # start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='fj' AND id=5 AND name='eee' LIMIT 1; # start 1214 end 1427 time 2017-11-30 14:27:46

We know from the parsing results that the location of misoperation of sql is between 1214 and 1427, so that we can further filter and use flashback mode to generate rollback sql.

The command is as follows:

Python binlog2sql.py-h292.168.56.102-P3306-uzs_test-p123456-dzs-tt-- start-file='mysql-binlog.000002'-- start-position=1214-- stop-position=1427-B > t_rollback.sql

View the flashback export file:

[root@node3 binlog2sql] # cat t_rollback.sql INSERT INTO `zs`.`t` (`address`, `id`, `name`) VALUES ('fj', 5,' eee'); # start 1214 end 1427 time 2017-11-30 14:27:46INSERT INTO `zs`.`t` (`address`, `id`, `name`) VALUES ('sy', 4,' ddd'); # start 1214 end 1427 time 2017-11-30 14:27:46INSERT INTO `zs`.`t` (`address`, `id`, `name`) VALUES ('gz', 3,' ccc') # start 1214 end 1427 time 2017-11-30 14:27:46INSERT INTO `zs`.`t` (`address`, `id`, `name`) VALUES ('sh', 2,' bbb'); # start 1214 end 1427 time 2017-11-30 14:27:46INSERT INTO `zs`.`t` (`address`, `id`, `name`) VALUES ('bj', 1,' aaa'); # start 1214 end 1427 time 2017-11-30 14:27:46

Step 8: apply the rollback file and recover the data. The command is as follows:

/ usr/local/mysql/bin/mysql-uroot-proot123 zs

Select * from | name | address | +-- + | 1 | aaa | bj | | 2 | bbb | sh | | 3 | ccc | gz | | 4 | ddd | sy | 5 | eee | fj | +-+ 5 rows in set (0.00 sec)

Verify that the data recovery is successful!

Although the tool is small, but the function is very powerful, we should be good at discovering the resources around us and using these weapons to help us learn MySQL database! In the future, Lao Zhang will continue to share new practical information for everyone's reference!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.