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