Analyzing MySQL Binlog with analysis_binlog: Environment Setup, Testing, and Results

Overview

As a MySQL DBA, inspecting and analyzing binlog files is a routine task. Many times you need to query the DML statistics of each table within a specific time range, but the binlog files are numerous or you have multiple business lines with similar requirements.

The need is simple, yet the operation can be cumbersome, so this article presents a test that addresses this scenario.

If you face similar pain points or frequently need to parse binlogs in bulk, this article may be helpful.

Environment Information

Environment Preparation

1. centos1

(1) Install MySQL

hostname
ip a|grep 192
cat /proc/cpuinfo | grep processor
free -m
df -h|grep data
/usr/<span style="color:#e6c07b;">local</span>/mysql80/bin/mysql -uroot -p1234567890 -h192.168.1.10 -P3306 -e "select version()"

Note: The actual MySQL installation steps are omitted.

2. centos2

(1) Install sysbench

sysbench --version

Note: The installation steps are omitted. For CentOS 6, configure yum as follows:

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo-backup

vi /etc/yum.repos.d/CentOS-Base.repo

[base]
name=CentOS-6
failovermethod=priority
#baseurl=https://vault.centos.org/6.9/os/x86_64/
baseurl=http://mirrors.sohu.com/centos/6.10/os/x86_64/
gpgcheck=0

yum clean all && yum makecache

(2) Prepare test script

https://gitee.com/mo-shan/script/blob/master/sysbench.sh   # obtain or write your own script

3. centos3

(1) analysis_binlog

analysis_binlog is an open‑source project that can efficiently analyze binlog files. Its main features include:

Count DML operations per table and show the last modification time.

Convert business table binlog to SQL.

Parallel parsing of multiple binlog files.

# yum –y install git # install git if missing
mkdir -p /data/git && cd /data/git && git clone https://gitee.com/mo-shan/analysis_binlog.git
# https://gitee.com/mo-shan/analysis_binlog/tree/master # see installation details there

Common parameters:

--mysqlbinlog-path or –mpath   # path to mysqlbinlog tool, defaults to system PATH
--binlog-file or –bfile       # comma‑separated list of binlog files
--threads or –w               # number of threads for parallel parsing
--binlog2sql or –sql          # convert binlog to SQL

(2) Prepare MySQL

You do not need to install MySQL itself, but the analysis tool depends on the mysqlbinlog utility, so you need a MySQL package of the same version.

Testing

1. Prepare data

Use the test script to create five tables and insert 10,000 rows each. First create a test database.

/usr/<span style="color:#e6c07b;">local</span>/mysql80/bin/mysql -uroot -p1234567890 -h192.168.1.10 -P3306
create database if not exists mstest;  # database name matches the sysbench script

2. Flush binlog

To make the results clearer, flush the MySQL binlog before testing.

/usr/<span style="color:#e6c07b;">local</span>/mysql80/bin/mysql -uroot -p1234567890 -h192.168.1.10 -P3306
flush binary logs;
show binary logs;

3. MySQL stress test

Run sysbench with ten concurrent threads. The Lua script used only performs write operations, so SELECTs are not logged. The test runs for 60 seconds and produces 2,860 DML statements (2,860 transactions).

4. Parse binlog

(1) Copy binlog files

Copy the binlog files to the analysis servers (centos1 and centos3). This test parses a single file: 3306-binlog.000011.

(2) Parse binlog – count DML

The tool reports 2,860 UPDATE statements, corresponding to 2,860 transactions.

(3) Parse binlog – binlog2sql

For each table you can generate the corresponding SQL statements.

(4) Parse binlog – parallel

The tool can parse multiple binlog files concurrently.

Summary

This article introduced how to use the analysis_binlog tool to meet the initial requirement of counting DML operations and extracting SQL from MySQL binlogs.

The tool offers many optional parameters; use --help to view the help manual.

analysis_binlog trades memory for speed and supports parallel analysis, making it efficient but unsuitable for production servers where memory consumption could be high.

If you encounter any issues, feel free to leave a comment on the Git repository or open an issue.