binlog-rollback.pl 在线恢复update 和delete不加条件误操作sql

一、binlog-rollback.pl工具介绍

是perl开发的脚本工具,此工具主要是生成反向的DML sql语句:

#基于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做特别处理
#逆序
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.

#注意:

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

二、MySQL server必须设置以下参数:

MySQL的版本是mysql5.6.36

cat /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
character-set-server = utf8
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

三、此工具使用需要创建一个mysql的管理用户来操作

不建议直接使用mysql的root账户来操作,建议授权一个最小权限的用户来操作
登录mysql创建最小的授权用户admin
user需要的最小权限集合:

select, super/replication client, replication slave
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
  • 1.
  • 2.

权限说明

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

MySQL [(none)]> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO admin@'127.0.0.1' identified by 'admin';
  • 1.
  • 2.

四、binlog-rollback.pl工具参数介绍

[root@git-server opt]# perl binlog-rollback.pl -help
<Error>:please 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
	--start-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 information

Sample :
   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
   shell> 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> perl 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'
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

操作报错如下:

[root@git-server 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'
  • 1.
  • 2.
  • 3.
  • 4.

解决办法:

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

五、测试delete,和update误操作后的sql语提取和数据的恢复

5.1delete删除忘记添加条件恢复测试

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)

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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.

delete删除时,不加任何条件,导致误删除

MySQL [zixun3]> delete  from zx_scores;
Query OK, 12 rows affected (0.00 sec)

MySQL [zixun3]> 

[root@git-server opt]#  perl binlog-rollback.pl -f '/data/mysql/data/mysql-bin.000004' -o '/tmp/insert.sql' -u 'admin' -p 'admin' -h 127.0.0.1 -i
Warning: Using a password on the command line interface can be insecure.


[root@git-server opt]# cat /tmp/insert.sql 
INSERT INTO `zixun3`.`zx_scores` SET `id`=15, `titles`='大将', `icon`=12, `integral`=29000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=12, `titles`='上将', `icon`=11, `integral`=24000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=11, `titles`='中将', `icon`=10, `integral`=19000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=10, `titles`='少将', `icon`=9, `integral`=14000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=9, `titles`='上校', `icon`=8, `integral`=9000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=8, `titles`='中校', `icon`=7, `integral`=6000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=7, `titles`='少校', `icon`=6, `integral`=5000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=6, `titles`='上尉', `icon`=5, `integral`=4000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=5, `titles`='中尉', `icon`=4, `integral`=3000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=4, `titles`='少尉', `icon`=3, `integral`=2000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=3, `titles`='班长', `icon`=2, `integral`=1000, `isdefault`=1;
INSERT INTO `zixun3`.`zx_scores` SET `id`=2, `titles`='列兵', `icon`=1, `integral`=0, `isdefault`=1;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.

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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.

delete操作恢复完成

5.2update更新时忘记加条件限制的数据恢复

MySQL [zixun3]> update zx_scores set titles='班长';
Query OK, 11 rows affected (0.00 sec)
Rows matched: 12  Changed: 11  Warnings: 0

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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
[root@git-server 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 -i
Warning: Using a password on the command line interface can be insecure.
  • 1.
  • 2.

由于mysql-bin.000004 中记录了所有的sql域名,而且binlog-rollback.pl 次工具是过滤mysql-bin.000004 中所有的DML语句 insert,update,delete,所以需要把单独的update语句过滤出来

[root@git-server opt]# grep -i update /tmp/update.sql >/tmp/3.sql
[root@git-server opt]# cat /tmp/3.sql 
UPDATE `zixun3`.`zx_scores` SET `id`=15, `titles`='大将', `icon`=12, `integral`=29000, `isdefault`=1 WHERE `id`=15 AND `titles`='班长' AND `icon`=12 AND `integral`=29000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=12, `titles`='上将', `icon`=11, `integral`=24000, `isdefault`=1 WHERE `id`=12 AND `titles`='班长' AND `icon`=11 AND `integral`=24000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=11, `titles`='中将', `icon`=10, `integral`=19000, `isdefault`=1 WHERE `id`=11 AND `titles`='班长' AND `icon`=10 AND `integral`=19000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=10, `titles`='少将', `icon`=9, `integral`=14000, `isdefault`=1 WHERE `id`=10 AND `titles`='班长' AND `icon`=9 AND `integral`=14000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=9, `titles`='上校', `icon`=8, `integral`=9000, `isdefault`=1 WHERE `id`=9 AND `titles`='班长' AND `icon`=8 AND `integral`=9000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=8, `titles`='中校', `icon`=7, `integral`=6000, `isdefault`=1 WHERE `id`=8 AND `titles`='班长' AND `icon`=7 AND `integral`=6000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=7, `titles`='少校', `icon`=6, `integral`=5000, `isdefault`=1 WHERE `id`=7 AND `titles`='班长' AND `icon`=6 AND `integral`=5000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=6, `titles`='上尉', `icon`=5, `integral`=4000, `isdefault`=1 WHERE `id`=6 AND `titles`='班长' AND `icon`=5 AND `integral`=4000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=5, `titles`='中尉', `icon`=4, `integral`=3000, `isdefault`=1 WHERE `id`=5 AND `titles`='班长' AND `icon`=4 AND `integral`=3000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=4, `titles`='少尉', `icon`=3, `integral`=2000, `isdefault`=1 WHERE `id`=4 AND `titles`='班长' AND `icon`=3 AND `integral`=2000 AND `isdefault`=1;
UPDATE `zixun3`.`zx_scores` SET `id`=2, `titles`='列兵', `icon`=1, `integral`=0, `isdefault`=1 WHERE `id`=2 AND `titles`='班长' 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 | 列兵   |    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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.

完成恢复

简单吧,同时此工具还可以结合位置点和时间点进行恢复,请关注后面的博文。

posted @ 2018-05-22 14:56  勤奋的蓝猫  阅读(5)  评论(0编辑  收藏  举报  来源