binlog2sql

database

binsql2log是大众点评开源的用于解析MySQL binlog的工具, 项目地址: https://github.com/danfengcao/binlog2sql

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

0x00 用途

  • 数据快速回滚(闪回)
  • 主从切换后新master丢数据的修复
  • 从binlog生成标准SQL,带来的衍生功能

开发团队已经测试过的环境:

  • Python 2.7, 3.4+
  • MySQL 5.6, 5.7

0x01 安装

安装binlog2sql前先安装pip:

这里没有安装git,直接下载了压缩包

yum -y install epel-release

yum install python-pip

# 下载binlog2sql并解压,安装

wget -O binlog2sql-master.zip https://github.com/danfengcao/binlog2sql/archive/refs/heads/master.zip

unzip binlog2sql-master.zip

cd binlog2sql-master

pip install -r requirements.txt

安装之后, 可以看一下目录结构. 可执行的pyhton文件在 binlog2sql/binlog2sql.py.

0x02 配置

mysql服务端配置

MySQL server必须:

  • 开启 binlog
  • 设置 binlog_format 为 row
  • 设置 binlog_row_image 为 full 或 noblog (默认为full)

[mysqld]

server_id = 1

log_bin = /var/log/mysql/mysql-bin.log

max_binlog_size = 1G

binlog_format = row

binlog_row_image = full

账号权限设置

账号需要的最小权限: select, super/replication client, replication slave. 权限说明:

  • select:需要读取 server 端 information_schema.COLUMNS 表,获取表结构的元信息,拼接成可视化的sql语句
  • super/replication client:两个权限都可以,需要执行 SHOW MASTER STATUS, 获取server端的binlog列表
  • replication slave:通过 BINLOG_DUMP 协议获取 binlog 内容的权限

建议参考以下命令:

CREATE USER `binlog2sql@localhost` IDENTIFIED BY "password";

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO your_user_name;

FLUSH privileges;

0x03 基本用法

show master status可查看binlog文件列表

解析出标准SQL

python binlog2sql.py -h host -P port -u username -p "your_password" -d database_name -t table_name1 table_name2 --start-file="mysql-bin.000002"

解析出回滚SQL

python binlog2sql.py --flashback -h host -P port -u username -p "your_password" -d database_name -t table_name1 --start-file="mysql-bin.000002" --start-position=763 --stop-position=1147

选项说明:

  • mysql连接配置

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

  • 解析模式

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

  • 解析范围控制

--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"。可选。默认不过滤。

  • 对象过滤

-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。可选。默认为增删改都解析。用了此参数但没填任何类型,则三者都不解析。

实例操作

USE test;

delimiter $$

CREATE TABLE `students`(

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`age` tinyint NOT NULL,

`create_time` int NOT NULL,

`update_time` int NULL DEFAULT 0,

PRIMARY KEY(`id`)

)engine=InnoDB;

INSERT INTO `students`(`id`,`name`,`age`,`create_time`)VALUES

(1,"aben",18,UNIX_TIMESTAMP("2021-4-19 22:00")),

(2,"sky",20,UNIX_TIMESTAMP("2021-4-19 22:02")),

(3,"array",16,UNIX_TIMESTAMP("2021-4-19 22:03")),

(4,"sunny",17,UNIX_TIMESTAMP("2021-4-19 22:04"));

$$

delimiter ;

我们来删除一笔数据:

DELETE FROM test.students WHERE name="aben";

  1. 查看binlog状态, 使用mysql的命令: show master status:

  2. 我们可以估计一下大概是在哪一个log文件里面, 我们就用最新的log文件来查找:

python binlog2sql/binlog2sql.py -h 127.0.0.1 -P 3306 -u binlog2sql -p "password" -d test -t students --start-file="mysql-bin.000006" --start-datetime="2021-4-19 22:45:00" --stop-datetime="2021-4-19 22:48:00"> 1.sql

得到的文件1.sql的内容:

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 18, 1618840800, 1, "aben"); #start 867 end 1181 time 2021-04-19 22:45:51

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 20, 1618840920, 2, "sky"); #start 867 end 1181 time 2021-04-19 22:45:51

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 16, 1618840980, 3, "array"); #start 867 end 1181 time 2021-04-19 22:45:51

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 17, 1618841040, 4, "sunny"); #start 867 end 1181 time 2021-04-19 22:45:51

DELETE FROM `test`.`students` WHERE `update_time`=0 AND `age`=18 AND `create_time`=1618840800 AND `id`=1 AND `name`="aben" LIMIT 1; #start 1212 end 1460 time 2021-04-19 22:46:36

  1. 从上面的结果得知, 删除操作的位置在1212 ~ 1460之间, 再根据位置进一步过滤,使用flashback模式生成回滚sql,检查回滚sql是否正确(注:真实环境下,此步经常会进一步筛选出需要的sql。结合grep、编辑器等)

python binlog2sql/binlog2sql.py -h 127.0.0.1 -P 3306 -u binlog2sql -p "password" -d test -t students --start-file="mysql-bin.000006" --start-position=1212 --stop-position=1460 -B > rollback.sql

得到的结果:

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 18, 1618840800, 1, "aben"); #start 1212 end 1460 time 2021-04-19 22:46:36

  1. 我们现在就可以使用这个sql来了回滚数据了

mysql -u root -p < rollback.sql

闪回详细介绍可参见example目录下《闪回原理与实战》

0x04 对比mysqlbinlog

  • mysql server必须开启,离线模式下不能解析
  • 参数 binlog_row_image 必须为FULL,暂不支持MINIMAL
  • 解析速度不如mysqlbinlog

0x05 一段来自他人的总结

  1. 它本身的核心代码比较少,主要是在pymysqlreplication的基础上进行了二次开发。

    pymysqlreplication实现了MySQL复制协议,可捕捉不同类型的EVENT事件。

    具体可参考:https://github.com/noplay/python-mysql-replication

  2. 个人感觉,直接解析文本格式的binlog,也未尝不是一个好办法。

    理由如下:

    1> binlog2sql强烈依赖于MySQL复制协议,如果复制协议发生改变,则该工具将不可用。

     虽然,复制协议发生改变的可能性很小(一般都会保持向前兼容),但相对而言,自带的mysqlbinlog肯定更懂binlog,基于mysqlbinlog解析后的结果进行处理,

    可完全屏蔽复制协议等底层细节。

    2> 用python来解析文本格式的binlog,本身也不是件难事。

      譬如,update语句在binlog中的对应的文本

    在得到表结构的情况下,基本上可离线解析。

以上是 binlog2sql 的全部内容, 来源链接: utcz.com/z/535530.html

回到顶部