Using ProxySQL Binlog Reader to Monitor MySQL GTID Replication

The author, a senior database expert with years of experience in MySQL, PostgreSQL, and MongoDB, presents the ProxySQL Binlog Reader component—a lightweight MySQL client that continuously fetches GTID numbers from each MySQL instance to help ProxySQL determine whether master and replica data are consistent.

ProxySQL Binlog Reader was created to address the challenge of stale reads in read/write‑splitting architectures, where three common strategies exist: sending all requests to the master, sending transaction‑level writes to the master, or sending all reads to replicas. Since replicas can lag, GTID comparison provides a reliable way to assess replication delay.

Component Background

By simulating a MySQL replica, the reader pulls only the latest GTID from each instance, which is resource‑efficient. ProxySQL then reads these GTIDs to decide if data can be safely served from a replica.

Demo Environment

Three instances (MySQL 5.7.34, port 5734) are used:

ProxySQL host: 192.168.2.111

Master: 192.168.2.120

Slave: 192.168.2.121

ProxySQL’s admin port is 6032, traffic port 6033, and the gtid_port in mysql_servers is set to 57341.

<mysql:admin:5.5.30>select hostgroup_id, hostname,port,status,gtid_port from mysql_servers;<br/>+--------------+---------------+------+--------+-----------+<br/>| hostgroup_id | hostname      | port | status | gtid_port |<br/>+--------------+---------------+------+--------+-----------+<br/>| 1            | 192.168.2.120 | 5734 | ONLINE | 57341     |<br/>| 2            | 192.168.2.121 | 5734 | ONLINE | 57341     |<br/>+--------------+---------------+------+--------+-----------+<br/>2 rows in set (0.00 sec)

A dedicated user for the reader is created and granted replication privileges:

<mysql: ytt:5.7.34-log> create user binlog_reader identified by 'read';<br/>Query OK, 0 rows affected (0.01 sec)<br/><br/><mysql: ytt:5.7.34-log> grant replication client, replication slave on *.* to binlog_reader;<br/>Query OK, 0 rows affected (0.00 sec)

The reader is started on each MySQL instance, matching the gtid_port value:

# Master:<br/>root@ytt-large:/tmp# proxysql_binlog_reader -ubinlog_reader -pread -P5734 -h 192.168.2.120 -l57341 -L /tmp/proxysql_mysqlbinlog_reader.log<br/><br/># Slave:<br/>root@ytt-normal:/tmp# proxysql_binlog_reader -ubinlog_reader -pread -P5734 -h 192.168.2.121 -l57341 -L /tmp/proxysql_mysqlbinlog_reader.log

Log output confirms successful startup and continuous GTID reading:

root@ytt-large:/tmp# tail -f proxysql_mysqlbinlog_reader.log<br/>Starting ProxySQL MySQL Binlog<br/>Sucessfully started<br/>Angel process started ProxySQL MySQL Binlog process 28841<br/>2022-07-18 15:11:04 [INFO] proxysql_binlog_reader version 2.0-3-gd8e0140<br/>2022-07-18 15:11:04 [INFO] Initializing client...<br/>2022-07-18 15:11:04 [INFO] Last executed GTID: '00005734-0000-0000-0000-000000005734:1-2'<br/>2022-07-18 15:11:04 [INFO] Reading binlogs...

root@ytt-normal:/tmp# tail -f proxysql_mysqlbinlog_reader.log<br/>Starting ProxySQL MySQL Binlog<br/>Sucessfully started<br/>Angel process started ProxySQL MySQL Binlog process 22710<br/>2022-07-18 15:11:59 [INFO] proxysql_binlog_reader version 2.0-3-gd8e0140<br/>2022-07-18 15:11:59 [INFO] Initializing client...<br/>2022-07-18 15:11:59 [INFO] Last executed GTID: '00005734-0000-0000-0000-000000005734:1-2'<br/>2022-07-18 15:11:59 [INFO] Reading binlogs...

MySQL session lists show two additional DUMP threads (one per instance) created for the reader:

# Master:<br/>Id: 125<br/>User: binlog_reader<br/>Host: 192.168.2.120:54668<br/>Command: Binlog Dump GTID<br/>State: Master has sent all binlog to slave; waiting for more updates<br/><br/># Slave:<br/>Id: 1426<br/>User: binlog_reader<br/>Host: 192.168.2.121:54992<br/>Command: Binlog Dump GTID<br/>State: Master has sent all binlog to slave; waiting for more updates

If replication issues occur, corresponding messages appear in the reader’s logs.

The article concludes by noting that this introduction covers the component’s background and usage, with more details to follow in a future post.

Additional information about the SQLE tool, a SQL audit platform from the same community, is provided with links to its repository, documentation, and release page.