介绍
pymysqlbinlog 是分析/解析binlog的开源工具, 使用GPL-3.0 license
项目地址: https://github.com/ddcw/pymysqlbinlog
功能:
- 分析binlog, 得到大事务信息, 表使用情况, DML使用情况等.
- 解析binlog, 解析binlog得到正向/回滚SQL.
特点
- 简单方便: 纯python3编写, 无依赖包
- 安全: 离线解析
- 开源协议: GPL-3.0 license
- 功能多: 分析/解析binlog, 支持库/表/时间/pos/gtid等匹配
- 支持范围广: 支持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
其它
这个工具差不多写了快一个月了, 写得断断续续的…
后面应该会出一个视频来详细介绍的. 毕竟我也经常用.
