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

Binlog2sql Quick rollback

Open source tool binlog2sql:

First, make sure that your MySQL server has binlog enabled and set the following parameters: (binlog + row mode)

[mysqld] server-id = 1 log_bin = / var/log/mysql/mysql-bin.log max_binlog_size = 1000m binlog-format = row

If binlog is not enabled and SQL is not rolled back in advance, it is really impossible to roll back quickly. It is strongly recommended to enable binlog for MySQL that stores important business data.

Then install the open source tool binlog2sql. Binlog2sql is an easy-to-use binlog parsing tool, one of which is to generate a rollback SQL.

Git clone https://github.com/danfengcao/binlog2sql.git pip install-r requirements.txt

Rollback steps:

Log in to mysql to view the current binlog file

Mysql > show master logs; +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 12262268 | mysql-bin.000002 | 132776 | +-+-+

The latest binlog file is mysql-bin.000002. We relocate the binlog location of the misoperated SQL.

$python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-pendant admin'- dtest-t f-- start-file='mysql-bin.000002'

Output:

DELETE FROM `test`.`f` WHERE `did` = 18 AND `updateTime` = '2016-12-06 1218' AND `uid` = 1 LIMIT 1; # start 4 end 314 DELETE FROM `test`.`f`WHERE `did` = 19 AND `updateTime` = '2016-12-06 12AND 55' AND `uid` = 2 LIMIT 1; # start 4 end 314 DELETE FROM `test`.`f` WHERE `did` = 20 AND `updateTime` = '2016-12-07 14start 000058' AND `uid` = 3 LIMIT 1; # start 4 end 314 DELETE FROM `test`.`f` WHERE `did` = 21 AND `updateTime` = 2016-12-14070.00 AND `uid4 = 1start 314; end 314

Generate a rollback sql and check that the rollback sql is correct

$python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-pendant admin'- dtest-t f-- start-file='mysql-bin.000002'-- start-pos=4-- end-pos=314-B output:

INSERT INTO `test`.`f` (`did`, `updateTime`, `uid`) VALUES (21, '2016-12-07 1414 INSERT INTO 01start 0011, 4); # start 4 test`.`f` (`did`, `updateTime`, `uid`) VALUES (20,' 2016-12-07 14000058, 3); # start 4 end 314 INSERT INTO `test`.`f` (`did`, `updateTime`, `uid`) VALUES (19, '2016-12-12-1206 55V) # start 4 end 314 INSERT INTO `test`.`f` (`did`, `updateTime`, `uid`) VALUES (18, '2016-12-06 12 INSERT INTO 28 start 18, 1);

Verify that the rollback sql is correct and execute the rollback statement. Log in to mysql and the data is rolled back successfully.

$python binlog2sql.py-h227.0.0.1-P3306-uadmin-paired admins'- dtest-t f-- start-file='mysql-bin.000002'-- start-pos=4-- end-pos=314-B | mysql- h227.0.0.1-P3306-uadmin-paired admins' mysql > select * from f +-+ | uid | did | updateTime | +-+ | 1 | 18 | 2016-12-06 12:28:18 | | 2 | 19 | 2016-12-06 12:55:56 | | 3 | 20 | 2016-12-07 14 : 00:58 | | 4 | 21 | 2016-12-07 14:01:00 | +-+

common problem

1. Some people may ask, how can I roll back quickly if I misoperate my DDL? For example, drop has a big table.

It is difficult to do this because even in row mode, DDL operations do not record changes in each row of data to binlog, so DDL cannot be rolled back through binlog. To achieve DDL rollback, you must back up the old data before performing the DDL. It is true that someone realized a quick rollback of DDL by modifying the mysql server source code, and I found Ali's xiaobin lin and submitted a patch. But as far as I know, few Internet companies in China have applied this feature. For the reason, I think the most important thing is to be lazy, there is no need to do this low-frequency function, the secondary reason is to add some extra storage.

Does 2.mysql have any other rollback tools besides binlog2sql?

Of course there is. Ali Peng Lixun added the feature of flashback to mysqlbinlog, which should be the earliest flashback function of mysql. Peng solved the rollback of DML and explained the design idea of using binlog for DML flashback. The DDL rollback feature was also proposed and implemented by the Ali team. These two functions are innovative, and the flashback tools that have emerged since then are basically imitations of the above two. In addition, Qunar's open source Inception is a set of MySQL automated operation and maintenance tools, which is heavier. It supports DML rollback, which is not rolled back from binlog, but also from backup. It also supports DDL rollback table structure, and the data cannot be rolled back.

Excerpt from:

Http://www.jb51.net/article/99553.htm

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.