Overview
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 a replica where the required transaction has already executed.
The reader reports the transactions executed on each configured database server. ProxySQL uses those positions to select a server that has applied the write a read depends on. If no server in the destination hostgroup has reached the required position, the read can wait and time out. Writer fallback requires an appropriate routing and topology policy; it is not automatically added to a reader-only destination.
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.
For MariaDB, use ProxySQL 3.0.12 or newer with binlog reader 2.5.0 and follow the MariaDB causal-read guide. It covers MariaDB server settings, reader setup and routing. The requirements below describe MySQL.
Requirements
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 Oracle’s or Percona’s MySQL Server. MariaDB uses a different tracking mechanism; follow the MariaDB guide linked above instead of these MySQL settings.
Setup
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
Install
Current version can be found at the GitHub release page: https://github.com/sysown/proxysql_mysqlbinlog/releases/latest Packages for the following distros are provided:
Docker image
A choice of multiple images can be found in the offcial proxysql/proxysql-mysqlbinlog DockerHub repository
docker pull proxysql/proxysql-mysqlbinlog:latest
Amazon AMI
comming soon.
Compile
clone out the repository from github
git clone https://github.com/sysown/proxysql_mysqlbinlog.git cd proxysql_mysqlbinlog && make
Configuration
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>
example:
proxysql_binlog_reader -h 127.0.0.1 -u root -proot -P 3306 -l 3307 -L /var/log/proxysql_binlog_reader.log
MySQL config
these options need to be added to the configuration:
gtid_mode=ON enforce_gtid_consistency session_track_gtids=OWN_GTID
ProxySQL config
execute these commands on the admin interface:
UPDATE mysql_servers SET gtid_port = 3307; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;