专栏首页二狗的DBA之路使用binlog2sql恢复数据

使用binlog2sql恢复数据

误删除数据的话,oracle里面我们可以使用闪回功能找回误操作的数据。

在MySQL里面,如果我们有延迟从库的话,也可以找回之前的数据,但是有时候不太好使(因为追数据到误操作前的准确的时间点有时候也不太好把握)。

对于误操作数据的闪回,我们一般推荐 binlog2sql 或者MyFlash(美团点评开源的)

本篇文章, 我们介绍下 binlog2sql的用法:

binlog2sql 【首级推荐使用】

官网:https://github.com/danfengcao/binlog2sql

注意: binlog必须是row格式,并且是FULL类型的记录。

安装: 

yum update nss curl libcurl -y   # centos6需要升级下这个包,不然没法去github拉代码
cd /root/
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

授权:

> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'flashback'@'192.168.11.20' identified by 'admin';

> USE testdb;

[testdb] > SELECT * FROM t_stud WHERE name LIKE 'Y%';

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     5 | Yu Wutong     |  26 | M      |       3 |         1 |

|    10 | Lee Lingshan  |  19 | F      |       3 |      NULL |

|    11 | John Chengzhi |  23 | M      |       6 |      NULL |

+-------+---------------+-----+--------+---------+-----------+

[testdb] > UPDATE t_stud SET age=100 WHERE name LIKE 'Y%';    -- 看到有3行受到影响

Query OK, 3 rows affected (0.01 sec)

Rows matched: 3  Changed: 3  Warnings: 0

[testdb] > SELECT * FROM t_stud WHERE name LIKE 'Y%';

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     5 | Yu Wutong     | 100 | M      |       3 |         1 |

|    10 | Lee Lingshan  | 100 | F      |       3 |      NULL |

|    11 | John Chengzhi | 100 | M      |       6 |      NULL |

+-------+---------------+-----+--------+---------+-----------+

解析出标准SQL:

cd /root/ 
先用mysqlbinlog找到误操作的那个地方binlog文件及位移点,然后使用下面命令解析:
python /root/binlog2sql/binlog2sql/binlog2sql.py -h192.168.11.20 -P3306 -uflashback -p'admin' -d testdb -t t_stud --start-file='mysql-bin.000040' --start-position=10030 --stop-position=10334

1 解析出回滚SQL:

cd /root/ 
python /root/binlog2sql/binlog2sql/binlog2sql.py --flashback -h192.168.11.20 -P3306 -uflashback -p'admin' -d testdb -t t_stud --start-file='mysql-bin.000040' --start-position=10030 --stop-position=10334

解析出的结果类似这样:

UPDATE `testdb`.`t_stud` SET `StuID`=11, `Name`='John Chengzhi', `Age`=23, `Gender`='M', `ClassID`=6, `TeacherID`=NULL WHERE `StuID`=11 AND `Name`='John Chengzhi' AND `Age`=100 AND `Gender`='M' AND `ClassID`=6 AND `TeacherID` IS NULL LIMIT 1; #start 10030 end 10334 time 2018-07-15 14:17:58
UPDATE `testdb`.`t_stud` SET `StuID`=10, `Name`='Lee Lingshan', `Age`=19, `Gender`='F', `ClassID`=3, `TeacherID`=NULL WHERE `StuID`=10 AND `Name`='Lee Lingshan' AND `Age`=100 AND `Gender`='F' AND `ClassID`=3 AND `TeacherID` IS NULL LIMIT 1; #start 10030 end 10334 time 2018-07-15 14:17:58
UPDATE `testdb`.`t_stud` SET `StuID`=5, `Name`='Yu Wutong', `Age`=26, `Gender`='M', `ClassID`=3, `TeacherID`=1 WHERE `StuID`=5 AND `Name`='Yu Wutong' AND `Age`=100 AND `Gender`='M' AND `ClassID`=3 AND `TeacherID`=1 LIMIT 1; #start 10030 end 10334 time 2018-07-15 14:17:58

2 修复下数据,去掉结尾的#及后面的内容:

sed -i.bak  's/#.*//g' /root/rollback.sql

3 将数据恢复到数据库中:

use testdb ;
UPDATE `testdb`.`t_stud` SET `StuID`=11, `Name`='John Chengzhi', `Age`=23, `Gender`='M', `ClassID`=6, `TeacherID`=NULL WHERE `StuID`=11 AND `Name`='John Chengzhi' AND `Age`=100 AND `Gender`='M' AND `ClassID`=6 AND `TeacherID` IS NULL LIMIT 1;
UPDATE `testdb`.`t_stud` SET `StuID`=10, `Name`='Lee Lingshan', `Age`=19, `Gender`='F', `ClassID`=3, `TeacherID`=NULL WHERE `StuID`=10 AND `Name`='Lee Lingshan' AND `Age`=100 AND `Gender`='F' AND `ClassID`=3 AND `TeacherID` IS NULL LIMIT 1; 
UPDATE `testdb`.`t_stud` SET `StuID`=5, `Name`='Yu Wutong', `Age`=26, `Gender`='M', `ClassID`=3, `TeacherID`=1 WHERE `StuID`=5 AND `Name`='Yu Wutong' AND `Age`=100 AND `Gender`='M' AND `ClassID`=3 AND `TeacherID`=1 LIMIT 1;

执行完后,再次看下数据,可以看到已经恢复好了。

[testdb] > select * from t_stud where name like 'Y%';                                                                                                                                                   

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     5 | Yu Wutong     |  26 | M      |       3 |         1 |

|    10 | Lee Lingshan  |  19 | F      |       3 |      NULL |

|    11 | John Chengzhi |  23 | M      |       6 |      NULL |

+-------+---------------+-----+--------+---------+-----------+

补充:

解析模式:

--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。

-K, --no-primary-key 对INSERT语句去除主键。可选。

-B, --flashback 生成回滚语句,可解析大文件,不受内存限制,每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。

解析范围控制:

--start-file 起始解析文件。必须。

--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。

--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never,此选项失效。

--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never,此选项失效。

--start-datetime 从哪个时间点的binlog开始解析,格式必须为datetime,如'2016-11-11 11:11:11'。可选。默认不过滤。

--stop-datetime 到哪个时间点的binlog停止解析,格式必须为datetime,如'2016-11-11 11:11:11'。可选。默认不过滤。

对象过滤:

-d, --databases 只输出目标db的sql。可选。默认为空。

-t, --tables 只输出目标tables的sql。可选。默认为空。

注意: 

提取出来的sql 实际上是倒序的。

例如误操作的sql是如下2步:  2018101916 ---> 11 ---> 12

step0: 原始t1表的 work_id='2018101916' 

step1: update test.t1 set work_id='11' where id=32 limit 1 ;  # 简单实验,update不更新其它列的数据

step2: update test.t1 set work_id='22' where id=32 limit 1 ;  # 简单实验,update不更新其它列的数据

闪回出来的sql是这样的:  12 ---> 11 ---> 2018101916

UPDATE `test`.`t1` SET `id`=32, `work_id`='11', `username`='admin' LIMIT 1;

UPDATE `test`.`t1` SET `id`=32, `work_id`='2018101916', `username`='admin' LIMIT 1;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • postgresql 提升主库的操作

    我们目前的postgresql是主从流复制模式,standby没有对外提供服务,只当做热备用。

    二狗不要跑
  • proxysql cluster 的搭建

    https://proxysql.com/blog/proxysql-cluster

    二狗不要跑
  • mysqlbinlog备份时候报错Sanity check failed

    ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 17...

    二狗不要跑
  • C# 谁改了我的代码

    其中的 Foo 是其他的函数,大家可以猜到输出是 lindexi ,但是,实际上把Foo调用函数添加之后,输出是 Lindexi 被大写了。那么这时 Foo 做...

    林德熙
  • Python中提供的各种队列结构

    Python标准库queue提供了LILO队列类Queue、LIFO队列类LifoQueue、优先级队列类PriorityQueue,标准库collection...

    Python小屋屋主
  • Codeforces Round #540 (Div. 3) D. Coffee and Coursework(二分)

    版权声明:欢迎转载,若转载,请标明出处,如有错误,请指点,也欢迎大佬们给出优化方法 https://blog.csdn.net/Charles_Zaqd...

    Ch_Zaqdt
  • Python中的队列结构及其用法

    Python标准库queue提供了LILO队列类Queue、LIFO队列类LifoQueue、优先级队列类PriorityQueue,标准库collection...

    Python小屋屋主
  • OpenATS续篇:搭建自己的卫星地球站

    ? 继上篇文章:DIY天线自动追踪系统OpenATS 的发表已经一年多了,这一年多的时间都没有更新这个小项目,因为我本人真的是太懒了。 其实OpenATS存在...

    FB客服
  • 从壹开始学习NetCore 44 ║ 最全的 netcore 3.0 升级实战方案

    言归正传,? 从2018年8月就开始听说 netcore 要准备3.0了,? 到了近期 v3.0.0-preview9 的发布(截止目前,3.0RC1已经发布...

    梁规晓
  • Codeforces Round #541 (Div. 2) C. Birthday(思维)

    题目链接:https://codeforces.com/contest/1131/problem/C

    Ch_Zaqdt

扫码关注云+社区

领取腾讯云代金券