Migrate MySQL 8.0 to MariaDB or ClickHouse with the binlog_parse_sql Toolkit

Tool Overview

binlog_parse_sql parses MySQL 8.0 binary log events, converts them to executable SQL, and supports reverse insertion into MariaDB or incremental migration to ClickHouse.

Compatibility Issue

MySQL 8.0 binary‑log format is incompatible with MariaDB 10.5, causing GTID and binlog replication errors in both master‑slave directions.

Usage Scenarios

Real‑time parsing of MySQL 8.0 binlog to replicate into MariaDB (script binlog_parse_queue.py).

Data recovery after accidental table deletion using full snapshot plus binlog incremental recovery.

Real‑time parsing of MySQL 8.0 binlog to replicate into ClickHouse (script binlog_parse_clickhouse.py).

Implementation Principle

Two concurrent threads: one parses binlog events and pushes generated SQL statements into a queue; the other consumes the queue and executes the statements sequentially, preserving order.

MariaDB Migration

Installation :

pip3 install pymysql mysql-replication -i "http://mirrors.aliyun.com/pypi/simple" --trusted-host "mirrors.aliyun.com"

Run in foreground : python3 binlog_parse_queue.py Run in background :

nohup python3 binlog_parse_queue.py > from_mysql_to_mariadb.log 2>&1 &

The tool creates binlog_info.txt to record processed binlog filenames and positions for resume after failure.

Common error : LookupError: unknown encoding: utf8mb3. Fix : edit

/usr/local/python3/lib/python3.10/site-packages/pymysql/charset.py

and append the following lines:

_charsets.add(Charset(256, "utf8mb3", "utf8mb3_general_ci", "Yes"))
_charsets.add(Charset(257, "utf8mb3", "utf8mb3_bin", ""))

ClickHouse Migration

Installation :

pip3 install clickhouse-driver -i "http://mirrors.aliyun.com/pypi/simple" --trusted-host "mirrors.aliyun.com"

Note: the clickhouse-driver library requires OpenSSL 1.1.1+ for SSL support on Python 3.10+.

Schema conversion : edit mysql_to_clickhose_schema.py to set MySQL connection and target ClickHouse database, then run: python3 mysql_to_clickhose_schema.py For single‑table testing, use mysql_to_clickhose_schema_test.py.

Full data migration steps :

Export MySQL tables to CSV using mydumper (requires version 0.12.3‑3 for CSV output). Example:

/usr/bin/mydumper -h 192.168.192.180 -u hechunyang -p wdhcy159753 -P 3306 --no-schemas -t 12 --csv -v 3 --regex '^hcy.user$' -o ./

Import CSV into ClickHouse:

clickhouse-client --query="INSERT INTO hcy.user FORMAT CSV" < hcy.user.00000.dat

Alternatively, use mysql_to_clickhouse_sync.py to export up to 10 tables in parallel, pulling 1,000 rows per batch.

Prerequisite: target tables must have an auto‑increment primary key. If a table lacks a primary key, set the parameter sql_generate_invisible_primary_key to let the tool generate an invisible primary key automatically.

Running ClickHouse ETL Tool

Foreground: python3 binlog_parse_clickhouse.py Background:

nohup python3 binlog_parse_clickhouse.py > from_mysql_to_clickhouse.log 2>&1 &

Download

Source code and binaries are available at:

https://github.com/hcymysql/binlog_parse_sql