Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

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

  1. install a package
  2. use the offical docker container
  3. use the Amazon EC2 AMI
  4. 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;