使用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?-h292.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?-h292.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;

更多关于云服务器，域名注册，虚拟主机的问题，请访问西部数码官网：www.west.cn