binlog-rollback.pl Online Recovery update and delete without conditional error operation SQL

I. Introduction of binlog-rollback.pl Tools

is a Perl-developed scripting tool that primarily generates a reverse DML SQL statement:

#基于row模式的binlog，生成DML(insert/update/delete)的rollback语句#通过mysqlbinlog -v 解析binlog生成可读的sql文件#提取需要处理的有效sql#"### "开头的行.如果输入的start-position位于某个event group中间，则会导致"无法识别event"错误#将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行#INSERT: INSERT INTO => DELETE FROM, SET => WHERE#UPDATE: WHERE => SET, SET => WHERE#DELETE: DELETE FROM => INSERT INTO, WHERE => SET#用列名替换位置@{1,2,3}#通过desc table获得列顺序及对应的列名#特殊列类型value做特别处理#逆序

#注意:

#表结构与现在的表结构必须相同[谨记]#由于row模式是幂等的，并且恢复是一次性，所以只提取sql，不提取BEGIN/COMMIT#只能对INSERT/UPDATE/DELETE进行处理#线上误操作后，一定要立刻对操作的表进行加锁，避免其他数据继续写入

Second, MySQL server must set the following parameters:

cat /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sockdefault-character-set=utf8[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = fullcharacter-set-server = utf8

Third, this tool uses the need to create a MySQL administrative user to operate

It is not recommended to use the root account of MySQL directly, it is recommended to authorize a least privileged user to operate
Log in to MySQL to create the smallest authorized user admin
Minimum set of permissions required by User:

select, super/replication client, replication slaveGRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

Permission Description

select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句super/replication client：两个权限都可以，需要执行‘SHOW MASTER STATUS‘, 获取server端的binlog列表replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

MySQL [(none)]> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [email protected]‘127.0.0.1‘ identified by ‘admin‘;

Four, binlog-rollback.pl tool parameter Introduction

[[email protected] opt]# perl binlog-rollback.pl-help<error>:p Lease input Binlog file==================== ======================================================================command Line Options:--help # out : Print Help Info-f,--srcfile # in:binlog file. [Required]-O,--outfile # out:output SQL file. [Required]-H,--host # In:host. Default ' 127.0.0.1 '-u,--user # In:user. [Required]-P,--password # In:password. [Required]-P,--port # In:port. Default ' 3306 '--start-datetime # in:start datetime--stop-datetime # in:stop datetime--sta   Rt-position # In:start Position--stop-position # in:stop position-d,--database # in : Database, Split comma-t,--table # in:table, split comma. [Required] set-d-I,--ignore # in:ignore binlog check contain DDL (create| alter|drop| RENAME)--debug # in:print Debug informationsample:shell> perl binlog-rollback.pl-f ' Mysql-bin. 000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd ' shell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' User '-P ' pwd '-i shell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '--debug s Hell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-h ' 192.168.1.2 '-u ' user '-P ' pwd '-p 3307 Shell > Perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '--start-position=107 shell> per   L binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '--start-position=107--stop-position=10000 Shell> perl binlog-rollback.pl-f ' mysql-bin.000001 '-o '/tmp/t.sql '-u ' user '-P ' pwd '-d ' db1,db2 ' shell> perl Binlog-rollback.pl-f ' mysql-bin.0000* '-o '/tmp/t.sql '-u ' user '-P ' pwd '-d ' db1,db2 '-t ' tb1,tb2 '

The operation error is as follows:

[[email protected] opt]#  perl binlog-rollback.pl -f ‘/data/mysql/data/mysql-bin.000002‘ -o ‘/tmp/t.sql‘ -u ‘admin‘ -p ‘admin‘ mysqlbinlog: unknown variable ‘default-character-set=utf8‘mysqlbinlog: unknown variable ‘default-character-set=utf8‘mysqlbinlog: unknown variable ‘default-character-set=utf8‘

Workaround:

[[email protected] opt]# grep mysqlbinlog binlog-rollback.pl #通过mysqlbinlog -v 解析binlog生成可读的sql文件    $MYSQLBINLOG = qq{mysqlbinlog --no-defaults -v}; ###添加上参数--no-defaults  解决问题

Five, test delete, and update error operation after the SQL extraction and data recovery 5.1delete Delete forgot to add conditional recovery test

MySQL [zixun3]> SELECT * from zx_scores;+----+--------+------+----------+-----------+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Pvt |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Ensign |     3 |         2000 |  1 | | 5 |    Lieutenant |     4 |         3000 |  1 | | 6 |    Captain |     5 |         4000 |  1 | | 7 |    Major |     6 |         5000 |  1 | | 8 |    Lieutenant Colonel |     7 |         6000 |  1 | | 9 |    Colonel |     8 |         9000 | 1 | | 10 |    Major General |    9 |         14000 | 1 | | 11 |   Lieutenant |    10 |         19000 | 1 | | 12 |   Admiral |    11 |         24000 | 1 | | 15 |   Generals |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec) MySQL [zixun3]> Show Master status\g******** 1. Row *************************** file:mysql-bin.000003 position:592 binlog_do_db:binlog_ignore_ Db:executed_gtid_set:1 row in Set (0.00 sEC) MySQL [zixun3]> flush logs; Query OK, 0 rows affected (0.10 sec) MySQL [zixun3]> Show Master status\g*************************** 1. Row *************************** file:mysql-bin.000004 position:120 binlog_do_db:binlog_ignore_ Db:executed_gtid_set:1 row in Set (0.00 sec)

Delete without any conditions, resulting in accidental deletion of

MySQL [zixun3]> Delete from zx_scores; Query OK, Rows Affected (0.00 sec) MySQL [zixun3]> [[email protected] opt]# perl binlog-rollback.pl-f '/data/m ysql/data/mysql-bin.000004 '-o '/tmp/insert.sql '-u ' admin '-P ' admin '-H 127.0.0.1-iwarning:using a password on the COM Mand line interface can is insecure. [[email protected] opt]# cat/tmp/insert.sql insert INTO ' zixun3 '. ' Zx_scores ' SET ' id ' =15, ' titles ' = ' General ', ' icon ' =12 , ' Integral ' =29000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =12, ' titles ' = ' Admiral ', ' icon ' =11, ' integral ' = 24000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =11, ' titles ' = ' Will ', ' icon ' =10, ' integral ' = 19000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =10, ' titles ' = ' Major General ', ' icon ' =9, ' integral ' =14000, ' IsDefault ' =1;i Nsert into ' zixun3 '. ' Zx_scores ' SET ' id ' =9, ' titles ' = ' Colonel ', ' icon ' =8, ' integral ' =9000, ' IsDefault ' =1;insert into ' zixun3 ' . ' Zx_scores ' SET ' id ' =8, ' titles ' = ' Lieutenant Colonel ', ' icon ' =7, ' integral ' =6000, ' IsDefault ' =1;insert into ' Zixun3 '. ' Zx_scores ' SET ' id ' =7, ' titles ' = ' major ', ' icon ' =6, ' integral ' =5000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_ Scores ' Set ' ID ' =6, ' titles ' = ' Captain ', ' icon ' =5, ' integral ' =4000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' set ' id ' = 5, ' Titles ' = ' Lieutenant ', ' icon ' =4, ' integral ' =3000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =4, ' titles ' = ' Ensign ', ' Icon ' =3, ' integral ' =2000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =3, ' titles ' = ' monitor ', ' icon ' =2, ' integral ' =1000, ' IsDefault ' =1;insert into ' zixun3 '. ' Zx_scores ' SET ' id ' =2, ' titles ' = ' private ', ' icon ' =1, ' integral ' =0, ' IsDefault ' =1 ;

MySQL [zixun3]> Source/tmp/insert.sql

MySQL [zixun3]> select *  from zx_scores;+----+--------+------+----------+-----------+| id | titles | icon | integral | isdefault |+----+--------+------+----------+-----------+|  2 | 列兵   |    1 |        0 |         1 ||  3 | 班长   |    2 |     1000 |         1 ||  4 | 少尉   |    3 |     2000 |         1 ||  5 | 中尉   |    4 |     3000 |         1 ||  6 | 上尉   |    5 |     4000 |         1 ||  7 | 少校   |    6 |     5000 |         1 ||  8 | 中校   |    7 |     6000 |         1 ||  9 | 上校   |    8 |     9000 |         1 || 10 | 少将   |    9 |    14000 |         1 || 11 | 中将   |   10 |    19000 |         1 || 12 | 上将   |   11 |    24000 |         1 || 15 | 大将   |   12 |    29000 |         1 |+----+--------+------+----------+-----------+12 rows in set (0.00 sec)

Delete operation Recovery complete

5.2update update forgot to add conditional data recovery

  MySQL [zixun3]> update zx_scores set titles= ' monitor '; Query OK, one rows affected (0.00 sec) rows matched:12 changed:11 warnings:0mysql [zixun3]> select * from Zx_scores ;+----+--------+------+----------+-----------+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Monitor |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Monitor |     3 |         2000 |  1 | | 5 |    Monitor |     4 |         3000 |  1 | | 6 |    Monitor |     5 |         4000 |  1 | | 7 |    Monitor |     6 |         5000 |  1 | | 8 |    Monitor |     7 |         6000 |  1 | | 9 |    Monitor |     8 |         9000 | 1 | | 10 |    Monitor |    9 |         14000 | 1 | | 11 |   Monitor |    10 |         19000 | 1 | | 12 |   Monitor |    11 |         24000 | 1 | | 15 |   Monitor |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec)  

[[email protected] opt]#  perl binlog-rollback.pl -f ‘/data/mysql/data/mysql-bin.000004‘ -o ‘/tmp/update.sql‘ -u ‘admin‘ -p ‘admin‘ -h 127.0.0.1 -iWarning: Using a password on the command line interface can be insecure.

Since all SQL domain names are recorded in mysql-bin.000004, and the binlog-rollback.pl tool is filtering all DML statements insert,update,delete in mysql-bin.000004, So we need to filter out the individual update statements.

[[email protected] opt]# grep-i update/tmp/update.sql >/tmp/3.sql[[email protected] opt]# cat/tmp/3.sql UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =15, ' titles ' = ' General ', ' icon ' =12, ' integral ' =29000, ' IsDefault ' =1 WHERE ' id ' =15 and ' Les ' = ' monitor ' and ' icon ' =12 and ' integral ' =29000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =12, ' titles ' = ' Admiral ', ' icon ' =11, ' integral ' =24000, ' IsDefault ' =1 WHERE ' id ' =12 and ' Les ' = ' monitor ' and ' icon ' =11 and ' integral ' =24000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =11, ' titles ' = ' Will ', ' icon ' =10, ' integral ' =19000, ' IsDefault ' =1 WHERE ' id ' =11 and ' Les ' = ' monitor ' and ' icon ' =10 and ' integral ' =19000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =10, ' titles ' = ' Major General ', ' icon ' =9, ' integral ' =14000, ' IsDefault ' =1 WHERE ' id ' =10 and ' titl Es ' = ' monitor ' and ' icon ' =9 and ' integral ' =14000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =9, ' titles ' = ' Colonel ', ' icon ' =8, ' integral ' =9000, ' IsDefault ' =1 WHERE ' id ' =9 and ' titles ' = ' monitor ' and ' icon ' =8 and ' INtegral ' =9000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =8, ' titles ' = ' Lieutenant Colonel ', ' icon ' =7, ' integral ' =6000, ' IsDefault ' =1 WHERE ' id ' =8 and ' titles ' = ' monitor ' and ' icon ' =7 and ' integral ' =6000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =7, ' titles ' = ' major ', ' icon ' =6, ' integral ' =5000, ' IsDefault ' =1 WHERE ' id ' =7 and ' titles ' = ' monitor ' and ' icon ' =6 and ' integral ' =5000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =6, ' titles ' = ' Captain ', ' icon ' =5, ' integral ' =4000, ' IsDefault ' =1 WHERE ' id ' =6 and ' titles ' = ' monitor ' and ' icon ' =5 and ' integral ' =4000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =5, ' titles ' = ' Lieutenant ', ' icon ' =4, ' integral ' =3000, ' IsDefault ' =1 WHERE ' id ' =5 and ' titles ' = ' monitor ' and ' icon ' =4 and ' integral ' =3000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =4, ' titles ' = ' Ensign ', ' Icon ' =3, ' integral ' =2000, ' IsDefault ' =1 WHERE ' id ' =4 and ' titles ' = ' monitor ' and ' icon ' =3 and ' integral ' =2000 and ' isdefault ' = 1; UPDATE ' zixun3 '. ' Zx_scores ' SET ' id ' =2, ' titles ' = ' private ', ' icon ' =1, ' integral ' = 0, ' IsDefault ' =1 WHERE ' id ' =2 and ' titles ' = ' monitor ' and ' icon ' =1 and ' integral ' =0 ' and ' isdefault ' = 1; MySQL [zixun3]> source//tmp/3.sql; MySQL [zixun3]> SELECT * from zx_scores;+----+--------+------+----------+-----------+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Pvt |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Ensign |     3 |         2000 |  1 | | 5 |    Lieutenant |     4 |         3000 |  1 | | 6 |    Captain |     5 |         4000 |  1 | | 7 |    Major |     6 |         5000 |  1 | | 8 |    Lieutenant Colonel |     7 |         6000 |  1 | | 9 |    Colonel |     8 |         9000 | 1 | | 10 |    Major General |    9 |         14000 | 1 | | 11 |   Lieutenant |    10 |         19000 | 1 | | 12 |   Admiral |    11 |         24000 | 1 | | 15 |   Generals |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec)

Complete recovery

Simple, and this tool can also be combined with location points and point-in-time recovery, please follow the post.

binlog-rollback.pl Online Recovery update and delete without conditional error operation SQL