Since the release 2.0 of ProxySQL it is possible to track executed GTID in real-time from all the MySQL servers in a replication topology. Adaptive query routing based on GTID tracking allows to provide causal reads, and ProxySQL can route reads to the slave where the needed GTID event was already executed.
MySQL Binlog Reader allows ProxySQL to know in real time which GTID was been executed on every MySQL server, slaves and master itself. Thanks to this, when a client executes a reads that needs to provide causal consistency reads, ProxySQL immediately knows on which server the query can be executed. If for whatever reason the writes was not executed on any slave yet, ProxySQL will know that the write was executed on master and send the read there.
For best results, the MySQL Binlog Reader should be running as close as possible to the MySQL database its reading from.
This solutions scales very well with limited network usage, and is being already tested in production environments.
Casual reads using GTID is only possible if:
- ProxySQL version 2.0+ is used (older versions do not support it)
- the backend is MySQL version 5.7.5 or newer. Older versions of MySQL do not have capability to use this functionality.
- replication binlog format is ROW
- GTID is enabled (that sounds almost obvious).
- backends are either Oracle’s or Percona’s MySQL Server: MariaDB Server does not support
session_track_gtids, but I hope it will be available soon.
There are multiple options how to setup the MySQL Binlog Reader, each with its own applicability and limitations
- install a package
- use the offical docker container
- use the Amazon EC2 AMI
- compile from source
Current version can be found at the GitHub release page:
Packages for the following distros are provided:
A choice of multiple images can be found in the offcial proxysql/proxysql-mysqlbinlog DockerHub repository
docker pull proxysql/proxysql-mysqlbinlog:latest
clone out the repository from github
git clone https://github.com/sysown/proxysql_mysqlbinlog.git cd proxysql_mysqlbinlog && make
MySQL Binlog config
configured via option arguments at startup
-h <mysql host> -u <mysql user> -p <mysql password> -P <mysql port> -l <listen port> -L <log file>
proxysql_binlog_reader -h 127.0.0.1 -u root -proot -P 3306 -l 3307 -L /var/log/proxysql_binlog_reader.log
these options need to be added to the configuration:
gtid_mode=ON enforce_gtid_consistency session_track_gtids=OWN_GTID
execute these commands on the admin interface:
UPDATE mysql_servers SET gtid_port = 3307; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;