【MySQL运维】使用binlog2sql快速恢复数据

一、binlog2sql的作用

binlog2sql可以从MySQL的binlog中解析出需要的SQL，并且可以生成原始执行SQL、回滚SQL、去除主键的INSERT SQL等，使用这个工具就不用再人工去对binlog进行解析，闪回方便。但是需要注意的是仅能针对DML进行操作，因为DDL语句并不会在binlog中记录每行数据的变化。除了binlog2sql可以实现闪回外，美团也有开源一款myflush工具实现DML语句的回滚，有兴趣的可以自行了解。

二、安装binlog2sql

#安装pip工具，等下会通过pip来安装binlog2sql
yum  install  python-pip  
#下载binlog2sql
cd /usr/local/src/ && git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
#使用pip安装binlog2sql和依赖，相关依赖都在安装包里写好的requirements.txt中
pip install -r requirements.txt 

三、binlog2sql常用选项

1、MySQL连接配置选项

• -h host：数据库地址
• -P port：数据库端口
• -u user：数据库用户
• -p password：数据库用户密码
• -S socket：使用socket登陆的话sock文件路径

2、binlog解析模式选项

• --stop-never：对binlog进行持续解析，直到binlog更新结束，默认为false
• -K, --no-primary-key：对INSERT语句去除主键。默认为False
• -B, --flashback：解析完成后如果需要回滚，用该选项生成回滚SQL，可解析大文件，不受内存限制。与stop-never或no-primary-key不能同时添加
• --back-interval：-B模式下每打印一千行回滚SQL后需要SLEEP的秒数。0为不等待。
   

3、解析范围控制选项

• --start-file：起始解析的binlog文件。必须
• --start-position：起始解析位置。默认为start-file的起始位置。
• --stop-file：终止解析的binlog文件。默认与start-file为同个文件。若解析模式为stop-never，此选项失效。
• --stop-position：终止解析位置。默认为stop-file的最末位置。若解析模式为stop-never，此选项失效。
• --start-datetime： 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
• --stop-datetime： 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
   

4、过滤条件

• -d, --databases：只解析指定的数据库，多个库用空格隔开，如-d db1 db2。
• -t, --tables：只解析指定的表，多张表用空格隔开，如-t tbl1 tbl2。
• --only-dml：只解析dml，忽略ddl。默认False。
• --sql-type：只解析指定类型，支持INSERT, UPDATE, DELETE，默认为增删改语句都进行解析。多个类型用空格隔开，如--sql-type INSERT DELETE。

 

四、binlog2sql使用示例

1、在使用binlog2sql之前确定一个大概的误操作时间，定位到某个binlog并对该binlog进行解析。这次先不加--flashback，看看解析出来的binlog是啥

python binlog2sql/binlog2sql.py -h 10.3.0.229 -udba -p -P 3314 -d test_db -t test_table --start-file mysql-bin.001009  > 1.sql

 

2、通过第一步定位出来的position信息，生成回滚SQL，可以看到生成的回滚SQL中调换了删除和插入的顺序 

python binlog2sql/binlog2sql.py -h 10.3.0.229 -udba -p -P 3314 -d test_db -t test_table --start-file mysql-bin.001009  --start-position=xxxx --stop-position=xxxx --flashback > back.sql