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     |
+----+-------+

posted @ 2018-09-10 15:46 Ziroro 阅读(780) 评论(0) 编辑收藏举报

刷新页面返回顶部

Ziroro

MySQL闪回技术——binlog2sql工具

一、闪回技术的意义

二、binlog2sql原理

三、安装 binlog2sql

3.1 下载地址

3.2 解压

3.3 安装pip

3.4 安装 binlog2sql 依赖的 python 包

四、相关选项

4.1 mysql连接配置

4.2 解析模式

4.3 解析范围控制

4.4 对象过滤

五、测试用例

5.1 查看当前数据

5.2 误删除数据

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

5.4 binlog2sql 生成回滚数据

公告

　3.1 下载地址

　3.2 解压

　3.3 安装pip

　3.4 安装 binlog2sql 依赖的 python 包

　4.1 mysql连接配置

　4.2 解析模式

　4.3 解析范围控制

　4.4 对象过滤

　5.1 查看当前数据

　5.2 误删除数据

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

　5.4 binlog2sql 生成回滚数据