自研 MySQL Binlog 分析程序介绍

前言

帮研发解决问题时，有一些需要分析 Binlog 日志的场景，虽然不多，但是都比较重要。本篇文章介绍如何从二进制日志中发现问题。目前实现 4 种功能：

• 分析是否有大事务；
• 分析是否有长期未提交的会话；
• 分析事务的生成频次；
• 分析表的变更频次；

BinlogShow

自研 Binlog 分析脚本，通过解析 Binlog Event 实现事务分析，但不解析 DML Event 中更详细的内容。

➜  Desktop python3 BinlogShow.py --binlog=./mysql-bin.174208 --outfile=./event.csv

         ____  _       _             ____  _
        | __ )(_)_ __ | | ___   __ _/ ___|| |__   _____      __
        |  _ \| | '_ \| |/ _ \ / _` \___ \| '_ \ / _ \ \ /\ / /
        | |_) | | | | | | (_) | (_| |___) | | | | (_) \ V  V /
        |____/|_|_| |_|_|\___/ \__, |____/|_| |_|\___/ \_/\_/
                               |___/

loading.....
File Export directory: ./event.csv
complete ok!
=========================  Statistical Analysis  =========================
2022-06-02 02:00:30   262601
2022-06-02 02:00:40   109077

TOP TABLE Value Counts:
sbtest1                 41789
sbtest2                 39308
sbtest3                 35200
sbtest4                 18884
Name: ld_table_name, dtype: int64

使用方法：

依赖三方模块：Pandas

python3 BinlogShow.py --binlog=./mysql-bin.174208 --outfile=./BinlogShow.csv

参数解析：

• –binlog：MySQL 二进制日志的路径
• –outfile：导出分析表格的地址和文件名
• –interval：事务生成频次的时间间隔，默认 1s

输出解析：

1. 事务生成频次

每秒中事务频次分析，时间间隔是可以调整的，如果你想调整它，可以 --interval=1s 调整。下面使用测试库的 Binlog 文件，可以看到每秒的事物量最高 3 万。

Frequency analysis: 
2022-06-02 02:00:30   29594
2022-06-02 02:00:31   31273
2022-06-02 02:00:32   33801
2022-06-02 02:00:33   30835
2022-06-02 02:00:34   28992
2022-06-02 02:00:35   22274
2022-06-02 02:00:36   20414
2022-06-02 02:00:37   22711
2022-06-02 02:00:38   23210
2022-06-02 02:00:39   19497
2022-06-02 02:00:40   18508
2022-06-02 02:00:41   20594
2022-06-02 02:00:42   18415
2022-06-02 02:00:43   21333
2022-06-02 02:00:44   20650
2022-06-02 02:00:45   9577

2. 表的更新频次

输出表的更新频次 TOP 可以看出具体哪些表改动频次最大。

TOP table Value Counts: 
sku                    111467
promotion_price         94145
sbtest1                 41789
sbtest2                 39308
sbtest3                 35200
sbtest4                 18884

3. 分析是否有大事务

程序默认会将最大的事物打印出来，如果一个事务占用 100M 假设一行数据占用 110 字节，那么修改数据量大约为 100 万行。故认为大于 20MB 为一个大事务，也就是修改约 20 万行。

Biggest transaction:  82.71 KB

4. 分析是否有长事务

有大于 1 秒未提交的事物，程序会直接输出。

Not found long transaction.

如果程序输出：

Found long transaction, Please check column$commit_second.

表示数据库有未及时提交事物的情况，可以跟进程序导出的表格，详细定位到相关事务。

导出表格

程序会生成一个表格，详细记录了事务的信息，分析模块也是基于这份数据生成的。分析报告是宏观的，通过表格可以定位事务级别的问题，供用户自定义分析。

下载地址

这里是下载地址 BinlogShow 有需求自提。