MySQL数据库Binlog解析工具--binlog2sql

2022-07-19 11:05:41

概述

作为DBA，binlog2sql是一项必须掌握的工具。binlog2sql是一个开源的Python开发的MySQL Binlog解析工具，能够将Binlog解析为原始的SQL，也支持将Binlog解析为回滚的SQL，以便做数据恢复。

gihub:https://github.com/danfengcao/binlog2sql

一、闪回原理简析

开始之前，先说说闪回。我们都知道 MySQL binlog 以 event 为单位，记录数据库的变更信息，这些信息能够帮助我们重现这之间的所有变化，也就是所谓的闪回。
binlog 有三种可选的格式：

statement：基于 SQL 语句的模式，binlog 数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
mixed：混合模式，根据语句来选用是 statement 还是 row 模式；
row：基于行的模式，记录的是行的完整变化。安全，但 binlog 会比其他两种模式大很多；
利用 binlog 做闪回，需要将 binlog 格式设置为 row，因为我们需要最详尽的信息来确定操作之后数据不会出错。
既然 binlog 以 event 形式记录了所有的变更信息，那么我们把需要回滚的 event，从后往前回滚回去即可。
回滚操作：
对于 delete 操作，我们从 binlog 提取出 delete 信息，反向生成 insert 回滚语句；
对于 insert 操作，反向生成 delete 回滚语句；
对于 update 操作，根据信息生成反向的 update 语句；

二、基本要求

1、MySQL server必须设置以下参数:

[mysqld]
#二进制配置
server-id = 1
#log-bin = mysql-bin
log-bin = /pasdata/log/mysql-bin.log
log-bin-index =/pasdata/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

2、最小权限集合

select, super/replication client, replication slave

--建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'%' identified by 'binlog2sql';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'localhost' identified by 'binlog2sql';
flush privileges;

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

三、binlog2sql参数选项

1、mysql连接选项，与mysql客户端的选项一致

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

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。

3、解析范围控制

–start-file 起始解析文件，只需文件名，无需全路径。必须。（注意，–start-file直接填binlog名称就可以，不要加路径）
–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、对象过滤

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

四、常用命令

1、解析出标准SQL

在没有加 --only-dml 参数的情况下，会连ddl语句也解析出来，加上 --only-dml 参数，看到只解析dml语句。

python binlog2sql.py -ubinlog2sql  -pbinlog2sql  -ddatabase_name   -t   table1 table2  --start-file='mysql-bin.000007' 
--start-datetime='2019-12-xx 06:00:00' --stop-datetime='2019-12-xx 11:30:00'  >/tmp/binlog.txt

2、解析出回滚SQL

python binlog2sql.py  --flashback -h127.0.0.1 -P 3306   -ubinlog2sql -pbinlog2sql -ddatabase_name -t table1 table2 
--start-file='mysql-bin.000007' --start-position=763 --stop-position=1147 >/tmp/rollback.txt

3、定位误操作SQL的binlog位置

mysqlbinlog --base64-output=decode-rows -v --start-datetime='2019-12-04 05:00:00'  /data/log/mysql-bin.000007 > /opt/binlog.sql

说明：配置文件使用了binlog_format= row，查看数据库binlog内容时候就看不到增删改查的具体语句，都是经过64位编码转换后的内容，所以需要加参数–base64-output=decode-rows转换。

五、优缺点

1、限制（对比mysqlbinlog）

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

2、优点（对比mysqlbinlog）

纯Python开发，安装与使用都很简单
自带flashback、no-primary-key解析模式，无需再装补丁
flashback模式下，更适合闪回实战
解析为标准SQL，方便理解、筛选
代码容易改造，可以支持更多个性化解析

六、生产环境数据异地恢复实验

场景：某个数据库库没做备份，开发发现有一张表数据有点问题，想要将表恢复到早上6点时的数据
思路：
1）将生产环境数据库迁移到测试环境，同时复制mysql-binlog.index和所有的binlog到测试环境数据库，注意测试环境数据库配置要指定生产环境复制过来的binlog位置
2）在测试环境安装binlog2sql工具
3）通过binlog2sql转换故障时间段的binlog生成回退sql，在测试环境回退数据
环境：centos7.3、python2.7.9

1、生产环境数据迁移

1.1、迁移

--生产环境
mysqldump -uroot -p pas_prod>/opt/pas_prod.sql
--测试环境
mysql -uroot -p pas_prod</opt/pas_prod.sql

1.2、测试环境配置

#二进制配置
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
server-id = 1
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7

1.3、复制binlog

复制生产环境binlog，测试环境配置指定到binlog位置

2、测试环境（外网环境）安装binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

3、在测试环境授权

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'%' identified by 'binlog2sql';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'localhost' identified by 'binlog2sql';

4、解析binlogsql

python /root/binlog2sql/binlog2sql/binlog2sql.py -hxxx.123 -ubinlog2sql -p binlog2sql -dpas_prod -t t_att_overtime_application
--start-file='mysql-bin.000007' --start-datetime='2019-12-04 05:00:00' >/tmp/binlog.sql![

在这里确定要回退的binlog和对应的时间范围

5、生成回滚sql

python /root/binlog2sql/binlog2sql/binlog2sql.py  --flashback  -h172.16.10.123 -ubinlog2sql -p binlog2sql -dpas_prod \
-t t_att_overtime_application --start-file='mysql-bin.000007' --start-datetime='2019-12-04 05:00:00' >/tmp/rollback.sql

确认后在测试环境执行即可。

七、内网环境部署binlog2sql

在外网一条命令就解决了，那么如果是在内网环境应该如何安装binlog2sql呢？
环境：centos7.3 python2.7
下载地址：链接: https://pan.baidu.com/s/15nqszEwojdRP3t0s5e6Yig 提取码: 8gus 复制这段内容后打开百度网盘手机App，操作更方便哦

1、安装依赖包

cd binlog2sql/binlog2sql_dependencies
--安装setuptool
tar -xvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11/
python setup.py install
--安装pip
tar -xvf pip-9.0.1.tar.gz 
cd pip-9.0.1/
python setup.py install
--安装其他依赖包
pip install *.whl mysql-replication-0.9.tar.gz

至此，所有依赖包安装完毕。

2、授权

在目的库授权

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'%' identified by 'binlog2sql';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'localhost' identified by 'binlog2sql';

3、binlog2sql使用

cd binlog2sql/binlog2sql
python /root/binlog2sql/binlog2sql/binlog2sql.py -hxxx.69 -ubinlog2sql -p binlog2sql -dfsl_prod -t v_wfl_query_info  --start-file='mysql-bin.000120'  --only-dml >/tmp/binlog.sql

码农公寓

概述