ProxySQL - MySQL Binlog Reader

MySQL Binlog reader and parser to be used with ProxySQL and MySQL.

MySQL Binlog reader is a service that runs on MySQL host, tracks GTIDs and provides them to ProxySQL for use in adaptive query routing. https://www.proxysql.com/blog/proxysql-gtid-causal-reads

Requirements

• bulding natively requires a proper build enviroment, consult your distro documentation.
• building packages requires docker compose, builds inside a docker container
• building docker images requires docker buildx

install docker as per instructions:

https://docs.docker.com/engine/install/

Building

Currently supported distros are:

• CentOS 10, 9, 8, 7
• Debian 13, 12, 11, 10, 9
• Ubuntu 24, 22, 20, 18, 16

e.g.: to build an executable and a package for Ubuntu 24 do:

cd proxysql_mysqlbinlog
make ubuntu24

executable and package can be found in ./binaries

-rw-r--r-- 1 root root 1679864 Nov  24 15:56 proxysql-mysqlbinlog_2.3-ubuntu24_amd64.deb
-rwxr-xr-x 1 root root 5717720 Nov  24 15:54 proxysql_binlog_reader-2.3-ubuntu24

Packages

Prebuilt v2.3 packages for each supported distro are attached to every release:

• proxysql-mysqlbinlog-2.3-1-centos10.x86_64.rpm
• proxysql-mysqlbinlog-2.3-1-centos9.x86_64.rpm
• proxysql-mysqlbinlog_2.3-debian13_amd64.deb
• proxysql-mysqlbinlog_2.3-debian12_amd64.deb
• proxysql-mysqlbinlog_2.3-ubuntu24_amd64.deb
• proxysql-mysqlbinlog_2.3-ubuntu22_amd64.deb

Every current release also contains native ARM64 packages for Debian 13 and Ubuntu 24.

https://github.com/sysown/proxysql_mysqlbinlog/releases/latest

Containers

Ready to use v2.3 docker images, published to the GitHub Container Registry (GHCR):

• 2.3-centos10 == centos10 == centos
• 2.3-centos9 == centos9
• 2.3-debian13 == debian13 == debian == latest == 2.3
• 2.3-debian12 == debian12
• 2.3-ubuntu24 == ubuntu24 == ubuntu
• 2.3-ubuntu22 == ubuntu22

The Debian 13 (latest, debian) and Ubuntu 24 (ubuntu) image tags are multi-architecture manifests for linux/amd64 and linux/arm64. The older distro image tags remain AMD64-only.

Semantic tag versioning is used, ommited version implies latest.

https://github.com/sysown/proxysql_mysqlbinlog/pkgs/container/proxysql-mysqlbinlog

use docker pull ghcr.io/sysown/proxysql-mysqlbinlog:latest to pull the latest docker image

Container configuration

The image entrypoint runs proxysql_binlog_reader in the foreground, configured via environment variables:

Variable	Maps to	Default
MYSQL_HOST	-h	127.0.0.1
MYSQL_PORT	-P	3306
MYSQL_USER	-u	root
MYSQL_PASSWORD	-p	root
LISTEN_PORT	-l	6020
UPDATE_FREQ_MS	-t	0
BATCHING	-b	1
SSL_MODE	--ssl-mode	REQUIRED
SSL_VERIFY_SERVER_CERT	--ssl-verify-server-cert	1
SSL_CA	--ssl-ca	unset
SSL_CAPATH	--ssl-capath	unset
SSL_CERT	--ssl-cert	unset
SSL_KEY	--ssl-key	unset
SSL_CIPHER	--ssl-cipher	unset
TLS_VERSION	--tls-version	unset

e.g.:

docker run -d --name binlog-reader -p 6020:6020 \
  -e MYSQL_HOST=mysql1 -e MYSQL_USER=root -e MYSQL_PASSWORD=secret \
  ghcr.io/sysown/proxysql-mysqlbinlog:latest

HowTo

on each MySQL or MariaDB server instance run the proxysql_binlog_reader, e.g:

./proxysql_binlog_reader -h 127.0.0.1 -u root -p rootpass -P 3306 -l 6020 -f

MariaDB backends

The reader also supports MariaDB. No flag is needed: the backend is detected from the server version banner, and the snapshot is read from @@gtid_binlog_pos instead of the MySQL Executed_Gtid_Set column, which MariaDB does not provide.

./proxysql_binlog_reader -h 127.0.0.1 -u root -p rootpass -P 3306 -l 6020 -f \
  --ssl-mode=DISABLED

MariaDB reports positions as domain-server-sequence (0-1-270), but that native spelling is not what the reader sends. On the wire a MariaDB id is domain:start-end (0:1-270), produced by GTID_Set::to_string(). A MySQL UUID keeps its dashed form followed by :intervals. The two spellings are not interchangeable, so do not parse a position out of @@gtid_binlog_pos and expect it to match the reader's output.

A MariaDB domain id is a canonical decimal uint32. The reader rejects a non-canonical spelling, so 0 and 00 never name the same domain, and a value above UINT32_MAX fails rather than being truncated.

MariaDB reports an empty @@gtid_binlog_pos until its first GTID. That is a valid empty position, not an error: the reader waits for the first non-empty position before it opens the stream, rather than failing at startup.

TLS connections

TLS is required and server-certificate verification is enabled by default. For a verified connection, provide the CA that issued the MySQL server certificate:

./proxysql_binlog_reader -h mysql1 -u reader -p secret -P 3306 -l 6020 -f \
  --ssl-mode=REQUIRED --ssl-verify-server-cert=1 \
  --ssl-ca=/etc/proxysql-mysqlbinlog/mysql-ca.pem

For a self-signed development or private test server, encryption can remain required while certificate and host trust checks are disabled. This is an insecure, test/private-only decision:

./proxysql_binlog_reader -h mysql1 -u reader -p secret -P 3306 -l 6020 -f \
  --ssl-mode=REQUIRED --ssl-verify-server-cert=0

Replication heartbeat and read timeout

The reader asks the MySQL server to emit replication heartbeats while the binlog stream is idle, and caps how long a single read may block:

• --heartbeat-period: replication heartbeat period, in seconds (default 5)
• --read-timeout: replication read timeout, in seconds (default 60)

./proxysql_binlog_reader -h mysql1 -u reader -p secret -P 3306 -l 6020 -f \
  --heartbeat-period=5 --read-timeout=60

Both values must be positive decimal whole seconds, and the read timeout must be at least three times the heartbeat period — otherwise the reader exits with a usage error before connecting, because a timeout shorter than a heartbeat would trip on a quiet source. A too-large read timeout slows down the detection of a lost connection, so keep it within a few heartbeat periods of the value that suits your replication latency.

The read timeout is also capped at 2147483 seconds, because Connector/C holds the timeout as a millisecond count in a signed int; a larger value would wrap into a bogus timeout.

When a read fails (for example a silently dropped link, surfaced as a read timeout or a lost server connection) the reader exits and the existing supervisor loop restarts it; in the default daemon mode the angel process respawns the reader, and a restart happening twice in the same second is delayed by one second to avoid a tight crash loop.

Arguments

• -h: MySQL host
• -u: MySQL username
• -p: MySQL password
• -P: MySQL port
• -l: listener address: PORT (IPv4 wildcard), IPV4:PORT, or [IPV6]:PORT; numeric addresses only
• -f: run in foreground - all logging goes to stdout/stderr
• -L: path to log file
• -t: optional update throttling, in milliseconds (default 0 - update on every event)
• -b: update batching, 0 or 1 (default 1); set to 0 for ProxySQL servers older than v3.0.8
• -B: optional maximum network buffer size, in bytes
• -v: output build version
• --ssl-mode: DISABLED, PREFERRED, or REQUIRED (default REQUIRED)
• --ssl-verify-server-cert: verify the server certificate, 0 or 1 (default 1)
• --ssl-ca, --ssl-capath: optional CA file or directory
• --ssl-cert, --ssl-key: optional client certificate and private key
• --ssl-cipher, --tls-version: optional TLS cipher list and protocol version
• --heartbeat-period: replication heartbeat period, in seconds (default 5)
• --read-timeout: replication read timeout, in seconds (default 60); must be positive, at least three times the heartbeat period, and at most 2147483

Container images expose the same TLS settings through SSL_MODE and SSL_VERIFY_SERVER_CERT (defaults REQUIRED and 1), with optional SSL_CA, SSL_CAPATH, SSL_CERT, SSL_KEY, SSL_CIPHER, and TLS_VERSION.

Integration testing

The Docker TAP suite uses dbdeployer sandboxes for MySQL 5.7, 8.0, 8.4, 9.0, and 9.4, and a separate MariaDB 10.11 service for the MariaDB GTID snapshot and stream tests. After building the reader and TAP tests, run one version during local iteration with:

MYSQL_VERSIONS=84 test/infra/start-test.sh

Configuration

configure ProxySQL mysql_servers with coresponding gtid_port for each server, and also mysql_replication_hostgroups:

INSERT INTO mysql_servers (hostgroup_id,hostname,gtid_port,port,max_replication_lag,comment) VALUES (10,'mysql1',6020,3306,1,'mysql1');
INSERT INTO mysql_servers (hostgroup_id,hostname,gtid_port,port,max_replication_lag,comment) VALUES (20,'mysql2',6020,3306,1,'mysql2');
INSERT INTO mysql_servers (hostgroup_id,hostname,gtid_port,port,max_replication_lag,comment) VALUES (20,'mysql3',6020,3306,1,'mysql3');
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (10,20,'gtid_replication');
LOAD MYSQL SERVERS TO RUNTIME;

monitor the ProxySQL stats_mysql_gtid_executed table for executed GTIDs:

SELECT hostname,gtid_executed FROM stats_mysql_gtid_executed\G

*************************** 1. row ***************************
     hostname: mysql1
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-146301
*************************** 2. row ***************************
     hostname: mysql2
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-146300,8a093f5f-4258-11e8-8037-0242ac130004:1-5
*************************** 3. row ***************************
     hostname: mysql3
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-146301,8a0ac961-4258-11e8-8003-0242ac130003:1-5

More

https://proxysql.com/blog/proxysql-gtid-causal-reads/