介绍

pymysqlbinlog 是分析/解析binlog的开源工具, 使用GPL-3.0 license

项目地址: https://github.com/ddcw/pymysqlbinlog

功能:

1. 分析binlog, 得到大事务信息, 表使用情况, DML使用情况等.
2. 解析binlog, 解析binlog得到正向/回滚SQL.

特点

1. 简单方便: 纯python3编写, 无依赖包
2. 安全: 离线解析
3. 开源协议: GPL-3.0 license
4. 功能多: 分析/解析binlog, 支持库/表/时间/pos/gtid等匹配
5. 支持范围广: 支持mysql5.7/8.x 所有数据类型.

使用演示

为了方便展示, 我这里尽量使用sql格式, 但是默认的元数据minimal格式不记录字段名字, 所以就先设置为full. 数据解析的时候建议使用 base64格式

set global binlog_row_metadata = full;

数据类型测试/正向SQL

建议使用源码, 我也懒得去编译了 -_-

如果是基于表/库级匹配, 则会破坏事务的完整性. 基于gtid/时间等则不会破坏事务完整性.

python main.py /data/mysql_3314/mysqllog/binlog/m3314.000037 --sql

(venv) 20:59:24 [root@ddcw21 pymysqlbinlog]#python main.py /data/mysql_3314/mysqllog/binlog/m3314.000037 --sql
DELIMITER /*!*/;
ROLLBACK  /*!*/;
SET @@session.sql_mode=1168113696 /*!*/;
SET @@session.character_set_client=255, @@session.collation_connection=255, @@session.collation_server=255 /*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
USE ibd2sql /*!*/;
BEGIN/*!*/;
DELETE FROM `ibd2sql`.`ddcw_alltype_table` where `id`=46 and `int_col`=6690308 and `tinyint_col`=37 and `smallint_col`=6943 and `mediumint_col`=9393 and `bigint_col`=1432 and `float_col`=9907.0 and `double_col`=-923.0 and `decimal_col`=-3483.0 and `date_col`='2006-10-7' and `datetime_col`='2005-7-17 14:37:27' and `timestamp_col`='2023-1-19 0:11:39' and `time_col`='10:49:15' and `year_col`=1994 and `char_col`='ldzWuPsGkaaTblaVyhSqzcGvqPLtjMxw' and `varchar_col`='DNmlAuvoDlvedfErBIFWYtlNEBMsJcdCVWfAFRRtYHsSUsfTQZsgADOFXRIVhPhJFtzhWaxKZxtgaCpKsYoKyb' and `binary_col`='29' and `varbinary_col`='47' and `bit_col`=1 and `enum_col`=1 and `set_col`=2 and `josn_type`='{"aa": "c", "bb": {"dd": 1}}'/*!*/;
COMMIT /* 1767 added by ddcw pymysqlbinlog*/ /*!*/;
SET @@session.sql_mode=1168113696 /*!*/;
SET @@session.character_set_client=255, @@session.collation_connection=255, @@session.collation_server=255 /*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
USE ibd2sql /*!*/;
BEGIN/*!*/;
DELETE FROM `ibd2sql`.`ddcw_blob7` where `id`=3 and `c_lb`='测试中文jjwvbtdXCRBrmAFZPAhVLulKmjmdGKtXoJYJWisNfDHQdNBVDhwqhDDiOqUYiqZhslMKYpCEriVfNLDktOkcNILrfaCVayvSzKlbHbmmFMoINaNFHAHyLXUBPUFcruHZfIZHZQQlnRYtdHKqamSNxDYGcstliVBwwsZmwvwnDiLXWDTKOFGWXxegOfgGzXjCkcmOJDPcWqSIBFmwCCbqWKXiAIQpXmlswFUNFESnARmtdicJmPXXfwqOtaAwRgQFCbteLmPPVWnfAHAKXvPJjDbjBLMoqDTTABCqcRiMwfGUghbQzjOIITkorHlmmdJsNixhRqpHuFXdwqYJYzORQKycRfmRvuGxcYoPsdYwpHUVSXLePNQEuIGJAMMNrgzZFmfgfTDUOTGmzATeDyKWffJIjmhGhJzJmBHZtNNzLhniDvDUVogtkSZLhXSBbqTErbFUHaRIYYcuOTpQUYJnDnbSVQuQmNZuYRAUZBSbPkleYIVBjLGmxtYIxqKOQhVApQMVjsZQuRDoPQRXizWiYiPdeCAKbKJKDpZSLYiLvGZMemMqwvCtSWtpWeaiGSziPZbBpuIUdrQXdODOCdeLLZMcMELAukRDVinLMBpbjNcWPeVWPbYsUYIrzbNqUUFTTiWKioCRwmjbhRYxOFnSjNoRZLRDbQGGwCZgBoOQSzKGjxBNcmHIKAqdwadhHDKIpTRFGNAVUUGOTmzmdTETTqvoRbkrsEpiniuMgGNdQKZywbaAkFzsvqaDYDkVnpBvonmbReEUCJJpajHeTHVOveoHrGEKdzrgjSkjpNCaJWkwaczRtMRerrxGaufjfekBklxuktpnPxbiEjCZcOUE' and `c_lt`='测试中文tVZyzlLUqQbcLSplJbnLaYEJwbPlFhJKWwCDUuKfQtVqCucCIuPPGAjpXUUdqhcWDquSHLpJYMBFyAbaTErcrMDESXLLCSYOVFqqdsKrKAYmYwbekvHRbcvAplarEbMWQknPOkrQLurxkgxrdBLNVGofJVWHrHisWjSdxORWsaGbJfVyehlTIXTxIymoGOoERzFtXIlGSUsUHDfegrdpDHJeVnkHFAyjdhXowMuZuLBhdNzbyxiUijdJuJOpMhArijXPhIohvsmhkyUgjmNPHTEOniWogugzMUjErquNZLVMFYuOHCpYNMZjLcBPrURJghtwVwTrYTAnoyivOycmdctZRSURHRxipxPGmqpEyhedPGwHxsUSdHwerHewXCEIQihcZeUBgRpeSuEhMWbHlYZNKkqRTneXkGdNDIUErhGgPkTQLVvXqzAesZPUknENlxvpyvCMRpImwDwVzRWclTDLniFGMJHdsCvzMOlEHMiOEVEftgqoPJzexqaSUWxtpvwUaGAgJQTgeGLBFCwYbWxyYcCUjfZWImvEkBUeewoVFCNOqfdFcUClaxBxCIcXzRnTeaEX' and `c_ml`='测试中文dhUkagqUXmTcwSDqwRPyKjvVqFYqTurlbhYqNTGwYcClOonlc' and `c_mb`='测试中文hXBHkmlnWcNdAnePMiIpBptWAwDVglhidVJuupDcuOKXdVzNMSqrpH' and `c_t`='测试中文xfOHyJfVQdtUMnqzWBtfRoJPhSkUcQwERqcxiWkBtWniayQmahWrCbdHrVzDSlkmRn' and `c_b`='测试中文UgrsrKERuMQfSuBksmXbbrpDkecxLsYz' and `c_tb`='测试中文kP' and `c_tt`='测试中文nPRxFxjzT'/*!*/;
COMMIT /* 1768 added by ddcw pymysqlbinlog*/ /*!*/;
SET @@session.sql_mode=1168113696 /*!*/;
SET @@session.character_set_client=255, @@session.collation_connection=255, @@session.collation_server=255 /*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
USE ibd2sql /*!*/;
BEGIN/*!*/;
DELETE FROM `ibd2sql`.`ddcw_geometry` where `a`=0x000000000101000000000000000000F03F0000000000000040 and `b`=0xE610000001010000000000000000000040000000000000F03F and `c`=0x00000000010200000004000000000000000000F03F000000000000F03F000000000000004000000000000000400000000000000840000000000000084000000000000000400000000000000040 and `d`=0x0000000001030000000200000005000000000000000000000000000000000000000000000000000000000000000000084000000000000008400000000000000840000000000000084000000000000000000000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F and `aa`=0x000000000107000000020000000101000000000000000000F03F000000000000F03F0102000000020000000000000000000040000000000000004000000000000008400000000000000840 and `bb`=0x0000000001040000000200000001010000000000000000004E4000000000000038C001010000000000000000003C4000000000004053C0 and `cc`=0x00000000010500000002000000010200000003000000000000000000F03F000000000000F03F00000000000000400000000000000040000000000000084000000000000008400102000000020000000000000000001040000000000000104000000000000014400000000000001440 and `dd`=0x0000000001060000000100000001030000000200000005000000000000000000000000000000000000000000000000000000000000000000084000000000000008400000000000000840000000000000084000000000000000000000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F/*!*/;
COMMIT /* 1769 added by ddcw pymysqlbinlog*/ /*!*/;
DELIMITER ;

解析为回滚SQL

python main.py /data/mysql_3314/mysqllog/binlog/m3314.000037 --sql --rollback

我们可以看到刚才的那个delete 已经变为了insert

数据验证

其实讲row_event的时候就验证过了的…

mysql -h127.0.0.1 -P3314 -p123456 -e 'checksum table db1.sbtest1;'
mysql -h127.0.0.1 -P3314 -p123456 -e "delete from db1.sbtest1 limit 10;"
python main.py /data/mysql_3314/mysqllog/binlog/m3314.000039 --rollback | mysql -h127.0.0.1 -P3314 -p123456
mysql -h127.0.0.1 -P3314 -p123456 -e 'checksum table db1.sbtest1;'

binlog分析

注意: 会列出所有的trx信息, 所以可能会很大… (后面我再调一下这玩意…)

python main.py /data/mysql_3314/mysqllog/binlog/m3314.000039 --analyze -o /tmp/t20240504_10.md

其它

这个工具差不多写了快一个月了, 写得断断续续的…

后面应该会出一个视频来详细介绍的. 毕竟我也经常用.