BinLog2sql 数据库闪回

使用背景

        在进行项目研发，系统维护、项目监控等操作 时项目软件、开发人员、DBA总会与数据库进行打交道，但总会在不经意的情况下对数据造成修改、删除等操作。等造成损失时，才发现数据库数据已经不完整。如果出现以上问题，怎么办，请看下文！

BinLog2sql是什么

binlog2sql是大众点评开源的一款用于解析binlog的工具

BinLog2sql的功能

从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

BinLog2sql使用前提

MySQL 是否开启日志

show variables like '%log_bin%';

BinLog2sql的安装(Linux)

项目地址：https://github.com/danfengcao/binlog2sql

1.安装Git

yum -y install git

2.安装Pip (如果无法安装pip,自行百度安装pip方法)

yum -y install epel-release

yum -y installgit  python-pip

3.安装binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

pip install -r requirements.txt

BinLog2sql的参数说明

mysql连接配置

-h host; -P port; -u user; -p password

解析模式

--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。可选。默认为空。

BinLog2sql的使用

查看binlog信息

连接MySQL：mysql -u  -p

查看binlog信息：shouw master status;

 

查看binlog信息

---

查看sql执行日志

python binlog2sql.py -h192.168.1.2 -utestusername -ptestpsd -dtestdb -ttesttb --start-file='mysql-bin.000051' > delete_log.sql

语句解析：

连接192.168.1.2数据库，

用户名：testusername 

密码：testpsd 

数据库：testdb 

表：testtb   （可不使用）

当前日志文件信息：mysql-bin.000051

输出到：delete_log.sql

 

查看sql执行日志

查看sql执行日志

---

生成回滚Sql （添加-B命令）

python binlog2sql.py -h192.168.1.2 -utestusername -ptestpsd -dtestdb -ttesttb --start-file='mysql-bin.000051' -B > delete_log_rollback.sql 

解析：通过以上条件 执行回滚命令，将操作过的sql 反向生成sql.输出到delete_log_rollback.sql 

---

执行回滚Sql

mysql -utestusername -ptestpsd < delete_log_rollback.sql

注意事项

1. binlog2sql 可通过时间节点进行sql筛选

2.binlog2sql 可通过--start-position  --stop-position 来进行语句定位筛选

3. 可使用cat 、vi、vim等工具查看提取出来的sql语句

个人经验

再使用反向生成sql时有时会无法成功，只能通过操作其他方式进行找回之前的数据，

背景：每日凌晨都有进行自动备份。最多丢失1天数据。

根据现实情况进行方案设计，通过binlog2sql 获取执行的sql，获取相关表的 insert 和 update 语句，通过自行编写小程序进行字符整理，得到可执行的sql (insert or update) 后，通过新增和修改 重新生成数据

相关资源

1.flashback

2.MyFlash

3.mysqlbinlog