|
|
|
|
移动端

利用binlog2sql快速闪回误删除数据 - 别拿豆包不当干粮

今儿抽出时间,给大家分享一篇利用binlog2sql闪回工具,来恢复误删除的数据。我们都知道binlog的作用是备份恢复和完成MySQL的主从复制功能。利用mysqlbinlog工具可以进行基于时间点或者位置偏移量的数据恢复工作,在生产环境中遇到误删除,改错数据的情况,那是常有的事儿。

作者:张甦来源:51CTO博客|2018-01-22 13:01

开发者盛宴来袭!7月28日51CTO首届开发者大赛决赛带来技术创新分享

利用binlog2sql快速闪回误删除数据 - 别拿豆包不当干粮

今儿抽出时间,给大家分享一篇利用binlog2sql闪回工具,来恢复误删除的数据。我们都知道binlog的作用是备份恢复和完成MySQL的主从复制功能。利用mysqlbinlog工具可以进行基于时间点或者位置偏移量的数据恢复工作,在生产环境中遇到误删除,改错数据的情况,那是常有的事儿。我们都知道Oracle数据库有闪回功能,而MySQL本身没有自带闪回,但我们可以使用binlog2sql来完成这项工作。

我们都知道binlog是以event作为单位,来记录数据库变更的数据信息,闪回就是可以帮助我们重现这些变化数据信息之前的操作。也就是说对于insert操作,会生成delete语句,反之delete操作,会生成insert语句。对于update操作,也会生成相反的update语句。这款工具只能使用在binlog格式为row模式下。

下面进行实战演练:

binlog2sql工具的下载地址: https://github.com/danfengcao/binlog2sql

第一步:环境准备安装各种依赖的工具包列表

  1. python-pip , 
  2.  
  3. PyMySQL , 
  4.  
  5. python-mysql-replication, 
  6.  
  7. wheel argparse 

第二步:解压binlog2sql软件,命令如下

  1. unzip  binlog2sql-master.zip 
  2.  
  3. cd binlog2sql-master 
  4.  
  5. pip install –r requirements.txt 

第三步:通过python binlog2sql.py --help命令,来查看重要参数的使用;

-B, --flashback 生成回滚语句

--start-file 需要解析的binlog文件

--start-position 解析binlog的起始位置

--stop-position解析binlog的结束位置

--start-datetime 从哪个时间点的binlog开始解析,格式必须为datetime

--stop-datetime 到哪个时间点的binlog停止解析,格式必须为datetime

-d, --databases 只输出目标db的sql

-t, --tables 只输出目标tables的sql

第四步:开始模拟数据删除

首先删除掉zs库下,t表中的数据

  1. root@db 14:26:  [zs]> select * from t; 
  2.  
  3. +----+------+---------+ 
  4.  
  5. | id | name | address | 
  6.  
  7. +----+------+---------+ 
  8.  
  9. |  1 | aaa  | bj      | 
  10.  
  11. |  2 | bbb  | sh      | 
  12.  
  13. |  3 | ccc  | gz      | 
  14.  
  15. |  4 | ddd  | sy      | 
  16.  
  17. |  5 | eee  | fj      | 
  18.  
  19. +----+------+---------+ 
  20.  
  21. rows in set (0.00 sec)  
  22.  
  23.  
  24. root@db 14:26:  [zs]> delete from t; 
  25.  
  26. Query OK, 5 rows affected (0.04 sec)  
  27.  
  28.  
  29. root@db 14:27:  [zs]> select * from t; 
  30.  
  31. Empty set (0.00 sec) 

第五步:需要创建一个闪回用户

  1. create user 'zs_test'@'%' identified by '123456'
  2.  
  3. grant select,replication slave,replication client on *.* to 'zs_test'@'%' ; 
  4.  
  5. flush privileges

第六步:确定当前binlog文件和position位置

  1. root@db 14:41:  [zs]> show master status; 
  2.  
  3. +---------------------+----------+--------------+------------------+------------------------------------------+ 
  4.  
  5. | File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        | 
  6.  
  7. +---------------------+----------+--------------+------------------+------------------------------------------+ 
  8.  
  9. | mysql-binlog.000002 |     2091 |              |                  | e10f0ead-d595-11e7-82cb-080027cd683a:1-8 | 
  10.  
  11. +---------------------+----------+--------------+------------------+------------------------------------------+ 

可以看到当前binlog是:mysql-binlog.000002

位置偏移量:2091

第七步:需要预估下时间,误操作的时间范围应该在下午2点20分到2点30分之间。命令如下:

  1. python binlog2sql.py -h192.168.56.102 -P3306 -uzs_test -p123456 -dzs -tt --start-file='mysql-binlog.000002' --start-datetime='2017-11-30 14:20:00' --stop-datetime='2017-11-30 14:30:00' 

输出结果:

  1. DELETE FROM zs.t WHERE address='bj' AND id=1 AND name='aaa' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46 
  2.  
  3. DELETE FROM zs.t WHERE address='sh' AND id=2 AND name='bbb' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46 
  4.  
  5. DELETE FROM zs.t WHERE address='gz' AND id=3 AND name='ccc' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46 
  6.  
  7. DELETE FROM zs.t WHERE address='sy' AND id=4 AND name='ddd' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46 
  8.  
  9. DELETE FROM zs.t WHERE address='fj' AND id=5 AND name='eee' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46 

从解析结果中我们了解到,误操作sql的位置是在1214~1427之间;这样就可以进一步过滤,使用flashback模式生成回滚sql;

命令如下:

  1. python binlog2sql.py -h192.168.56.102 -P3306 -uzs_test -p123456 -dzs -tt --start-file='mysql-binlog.000002' --start-position=1214 --stop-position=1427 -B >t_rollback.sql 

查看闪回导出文件:

  1. [root@node3 binlog2sql]# cat t_rollback.sql  
  2.  
  3. INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('fj', 5, 'eee'); #start 1214 end 1427 time 2017-11-30 14:27:46 
  4.  
  5. INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('sy', 4, 'ddd'); #start 1214 end 1427 time 2017-11-30 14:27:46 
  6.  
  7. INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('gz', 3, 'ccc'); #start 1214 end 1427 time 2017-11-30 14:27:46 
  8.  
  9. INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('sh', 2, 'bbb'); #start 1214 end 1427 time 2017-11-30 14:27:46 
  10.  
  11. INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('bj', 1, 'aaa'); #start 1214 end 1427 time 2017-11-30 14:27:46 

第八步:应用回滚文件,恢复数据。命令如下:

  1. /usr/local/mysql/bin/mysql -uroot -proot123 zs < t_rollback.sql 

第九步:检验恢复数据是否成功

  1. root@db 15:09:  [zs]> select * from t; 
  2.  
  3. +----+------+---------+ 
  4.  
  5. | id | name | address | 
  6.  
  7. +----+------+---------+ 
  8.  
  9. |  1 | aaa  | bj      | 
  10.  
  11. |  2 | bbb  | sh      | 
  12.  
  13. |  3 | ccc  | gz      | 
  14.  
  15. |  4 | ddd  | sy      | 
  16.  
  17. |  5 | eee  | fj      | 
  18.  
  19. +----+------+---------+ 
  20.  
  21. rows in set (0.00 sec) 

验证恢复数据成功!

工具虽小,但功能很强大,我们要善于发现周围的资源,利用这些武器,来帮助我们学习MySQL数据库!今后老张会继续分享新的干货,供大家学习参考!

【编辑推荐】

  1. 回顾2017年发布的这10个新数据库系统
  2. MySQL主从同步架构中你不知道的“坑”
  3. MySQL的前缀索引及Oracle的类似实现
  4. 【必看】Oracle与MySQL的区别对比
  5. 利用ProxySQL实现MySQL的读写分离
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网管第一课——计算机网络原理

本书是《网管第一课》系列图书中的第一本,是专门针对高校和培训机构编写的,其主要特点是内容细而精、针对性强。书中内容全是经过精心挑选...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊