MySQL闪回技术——binlog2sql工具

一、闪回技术的意义

　　在没有闪回技术之前，如果一个不小心，手一抖，有时会误删数据或者误更新数据。

　　通常遇到这种事，传统恢复方法是利用备份重搭实例，再应用 binlog 到误操作的前一刻。再把数据导出来，导入到生产中。此法费时费力，甚至需要停机维护，并不适合快速回滚。

　　MySQL闪回(flashback)利用直接进行回滚到误操作的前一刻。DBA兄弟再也不需要每天练10公里了。

二、binlog2sql原理

　　基于 row 格式 binlog 以 event 形式记录了所有的变更信息。对操作的 SQL 进行反转，例如：

• insert 对应 delete

 原始：DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='小赵';
 回滚：INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '小赵');

• delete 对应 insert

 原始：INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '小钱');
 回滚：DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='小钱';

• UPDATE 则是回滚 sql 应该交换 SET 和 WHERE 的值。

 原始：UPDATE `test`.`user` SET `id`=3, `name`='小李' WHERE `id`=3 AND `name`='小孙';
 回滚：UPDATE `test`.`user` SET `id`=3, `name`='小孙' WHERE `id`=3 AND `name`='小李';

三、安装 binlog2sql

　　根据官方告知，已测试环境

• Python 2.7, 3.4+
• MySQL 5.6, 5.7
• 参数
  • log-bin = mysql-bin.log
  • binlog_format = row
  • binlog_row_image= full
  • binlog_row_query_logs_events = 1（非必要，建议打开）
• MySQL用户权限
  • select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
  • super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
  • replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

　3.1 下载地址

　　　　https://github.com/danfengcao/binlog2sql

　3.2 解压

　　　　unzip binlog2sql-master.zip

　　　　cd binlog2sql-master

　3.3 安装pip

　　　　curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py

　　　　python get-pip.py

　3.4 安装 binlog2sql 依赖的 python 包

　　　　pip install -r requirements.txt

四、相关选项

　4.1 mysql连接配置

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

　4.2 解析模式

　　　　--stop-never 持续解析binlog。可选。，默认False，同步至执行命令时最新的binlog位置。

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

　　　　-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。

　　　　--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。

　4.3 解析范围控制

　　　　--start-file 起始解析文件，只需文件名，无需全路径 。必须。

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

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

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

　　　　--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

　　　　--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

　4.4 对象过滤

　　　　-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。

　　　　-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。

　　　　--only-dml 只解析dml，忽略ddl。可选。默认False。

　　　　--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

五、测试用例

　5.1 查看当前数据

select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | aaaaa |
|  2 | b     |
|  3 | c     |
|  4 | d     |
+----+-------+

　5.2 误删除数据

delete from t1;
Query OK, 4 rows affected (0.00 sec)

　5.3 找到误操作SQL那一刻的pos

　　首先根据误删除的SQL，找到是哪一个 binlog 文件，假如是 bin.00002，解析binlog文件

mysqlbinlog --base64-output=decode-rows -vv bin.000002 > 1.sql

# at 2230
#180910  2:20:31 server id 883306  end_log_pos 2268 CRC32 0x113791f5    Rows_query
# delete from t1
# at 2268
#180910  2:20:31 server id 883306  end_log_pos 2318 CRC32 0x5e048107    Table_map: `vcyber`.`t1` mapped to number 121
# at 2318
#180910  2:20:31 server id 883306  end_log_pos 2385 CRC32 0xdfc5f064    Delete_rows: table id 121 flags: STMT_END_F
### DELETE FROM `vcyber`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaaaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### DELETE FROM `vcyber`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### DELETE FROM `vcyber`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### DELETE FROM `vcyber`.`t1`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */

　　因为我开了 binlog_row_query_log_events 这个参数，所以会把操作的SQL也记录到 binlog文件中，那么我在binlog中直接搜索 delete from t 就可以了，然后再往上找 # at ****** 就可以找到准确的pos点。

　5.4 binlog2sql 生成回滚数据

python binlog2sql.py -u'root' -p'abc_1234' -t't1' --start-file='bin.000002' --start-position=2230 -B > rollback.sql
cat rollback.sql 
INSERT INTO `vcyber`.`t1`(`id`, `name`) VALUES (4, 'd'); #start 2230 end 2385 time 2018-09-10 02:20:31
INSERT INTO `vcyber`.`t1`(`id`, `name`) VALUES (3, 'c'); #start 2230 end 2385 time 2018-09-10 02:20:31
INSERT INTO `vcyber`.`t1`(`id`, `name`) VALUES (2, 'b'); #start 2230 end 2385 time 2018-09-10 02:20:31
INSERT INTO `vcyber`.`t1`(`id`, `name`) VALUES (1, 'aaaaa'); #start 2230 end 2385 time 2018-09-10 02:20:31

　　-u：账户

　　-p：密码

　　-t：指定需要解析的表

　　--start-file：指定解析的binlog文件

　　-start-position：开始删除前的文职

　　-B：回滚SQL

　　5.5 应用SQL

source rollback.sql
select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | aaaaa |
|  2 | b     |
|  3 | c     |
|  4 | d     |
+----+-------+