本文共 11840 字，大约阅读时间需要 39 分钟。
是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做特别处理#逆序 #注意:
#表结构与现在的表结构必须相同[谨记]#由于row模式是幂等的，并且恢复是一次性，所以只提取sql，不提取BEGIN/COMMIT#只能对INSERT/UPDATE/DELETE进行处理#线上误操作后，一定要立刻对操作的表进行加锁，避免其他数据继续写入
MySQL的版本是mysql5.6.36
cat /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sockdefault-character-set=utf8[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-binmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = fullcharacter-set-server = utf8
不建议直接使用mysql的root账户来操作，建议授权一个最小权限的用户来操作
登录mysql创建最小的授权用户admin user需要的最小权限集合：select, super/replication client, replication slaveGRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
权限说明
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 admin@'127.0.0.1' identified by 'admin';
[root@git-server opt]# perl binlog-rollback.pl -help: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 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 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'
操作报错如下：
[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'
解决办法：
[root@git-server opt]# grep mysqlbinlog binlog-rollback.pl #通过mysqlbinlog -v 解析binlog生成可读的sql文件 $MYSQLBINLOG = qq{mysqlbinlog --no-defaults -v}; ###添加上参数--no-defaults 解决问题 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)
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 -iWarning: 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;
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操作恢复完成
MySQL [zixun3]> update zx_scores set titles='班长';Query OK, 11 rows affected (0.00 sec)Rows matched: 12 Changed: 11 Warnings: 0MySQL [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)
[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 -iWarning: Using a password on the command line interface can be insecure.
由于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)
完成恢复
简单吧，同时此工具还可以结合位置点和时间点进行恢复，请关注后面的博文。
转载于:https://blog.51cto.com/wujianwei/2119063