当前位置：首页 > article >正文

MySQL-binlog2sql闪回工具介绍与回滚实战

article 2025/2/11 3:26:17

文章目录

- - - 1.概述
    - 2.安装
    - 3.运行参数
    - 4.使用示例
    - - 解析指定类型sql，生成原始sql
      - 解析指定库表，生成原始sql
      - 解析指定库表，生成回滚sql
      - 解析所有insert语句，并去除主键
    - 5.回滚实战
    - - 1.首先创建一个表，插入几条数据
      - 2.模拟误操作，删除全表
      - 3.在binlog中定位误操作sql的位置
      - 4.通过binlog2sql解析binlog，生成回滚sql
      - 5.将数据导入mysql，回滚成功

1.概述

binlog2sql是一个MySQL binlog解析工具，通过不同的选项，可以得到原始sql、回滚sql和去除主键的插入sql。

主要用途:

快速回滚
主从切换后新master丢数据的修复

使用要求：

开启binlog、binlog记录格式设置为row

[mysqld]
log_bin = on
log_bin_basename=/data/mysql5/binlog/mysql3310/binlog
#记录修改的每一行
binlog_format = row
#记录完整的列信息 update记录更新前后所有字段的值 delete会记录删除前所有字段的值 insert会记录插入后所有字段的值
binlog_row_image = full

2.安装

github地址: https://github.com/danfengcao/binlog2sql

下载zip包后解压，它是一个python项目，需要python环境运行

在这里插入图片描述

3.运行参数

解析模式	描述
-K	生成的insert语句去除主键
–stop-never	持续解析
-B	生成回滚SQL

解析范围	描述
–start-file	开始binlog，必须指定
–stop-file	结束binlog，默认与–start-file相同
–start-position	开始偏移量
–stop-position	结束偏移量
–start-datetime	开始时间
–sop-datetime	结束时间

过滤条件	描述
-d	解析指定数据库，多个用空格分隔
-t	解析指定表，多个用空格分隔
–only-dml	只解析dml语句
–sql-type	只解析指定类型sql，包括insert、delete、update

4.使用示例

解析指定类型sql，生成原始sql

python binlog2sql.py -h127.0.0.1 -P3310  -uroot -p'123456' --start-file=binlog.000001 --sql-type=delete > delete.sql

解析指定库表，生成原始sql

python binlog2sql.py -h127.0.0.1 -P3310  -uroot -p'123456' --start-position=2504 --stop-position=2815 --start-file=binlog.000001 -d db02 -t user2 > origin.sql

解析指定库表，生成回滚sql

#解析binlog.000001中偏移量2504到2815之间的binlog，生成闪回sql到flashback.sql文件中
python binlog2sql.py -h127.0.0.1 -P3310  -uroot -p'123456' --start-position=2504 --stop-position=2815 --start-file=binlog.000001 -d db02 -t user2 -B > flashback.sql

解析所有insert语句，并去除主键

python binlog2sql.py -h127.0.0.1 -P3310  -uroot -p'123456' --start-file=binlog.000001 --stop-file=binlog.000001 -K --sql-type=insert > insert.sql

5.回滚实战

1.首先创建一个表，插入几条数据

在这里插入图片描述

当前db02.user2表有5条数据

2.模拟误操作，删除全表

在这里插入图片描述

3.在binlog中定位误操作sql的位置

在这里插入图片描述

当前只有一个binlog，能够直接定位日志就在这个binlog中。如果有多个，需要根据执行的时间判断误操作sql在哪个binlog中。

执行如下命令，通过mysqlbinlog解析binlog，查询误操作sql

/opt/mysql3310/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv /data/mysql5/binlog/mysql3310/binlog.000001 | egrep -i -C 20 'delete from `db02`.`user2`'

在这里插入图片描述

可以看到删除全表的sql，binlog中记录的是多个单行删除语句，偏移量起始位置为3971到4344

4.通过binlog2sql解析binlog，生成回滚sql

python /opt/binlog2sql/binlog2sql/binlog2sql.py -h127.0.0.1 -P3310  -uroot -p'#13974186851Pyf' --start-position=3971 --stop-position=4344 --start-file=binlog.000001 -d db02 -t user2 -B > flashback.sql