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!

MariaDB causal reads

ProxySQL 3.0.12 adds MariaDB support for GTID-based causal routing; the feature is also available in 3.1.12 and 4.0.12. It lets a read use a replica only when reported replication progress covers a tracked write from the same application connection.

This guide assumes a working MariaDB primary/replica topology, with writes routed to hostgroup 10 and replicas in hostgroup 20. Replication, database accounts, monitoring, and ordinary ProxySQL routing must already work. Replace the example addresses, account names, rule ID, and table with your deployment’s values.

Prepare MariaDB and a reader for each server

Each participating MariaDB server needs binary logging with row-based events. Replicas must log replicated updates too, so their binary log represents applied transactions. Check directly on each MariaDB server:

SHOW GLOBAL VARIABLES
WHERE Variable_name IN
      ('log_bin','binlog_format','log_slave_updates','gtid_strict_mode','server_id');
SELECT @@GLOBAL.gtid_binlog_pos;

Configure log_bin=ON, binlog_format=ROW, and log_slave_updates=ON, unique server IDs, and a consistent GTID-domain policy for the topology. Use gtid_strict_mode=ON for a topology that enforces increasing GTID sequences. Make required server configuration changes using your normal MariaDB restart procedure before proceeding. GTIDs identify replication history; matching a domain does not make an unrelated server a valid replica.

Install the external ProxySQL binlog reader 2.5.0 package for your operating system on each database host. The reader is a separate service from ProxySQL. Give it a dedicated MariaDB account on each server, restricting the account’s host to its actual source address:

CREATE USER 'gtid_reader'@'READER_SOURCE_HOST'
IDENTIFIED BY 'REPLACE_WITH_READER_PASSWORD';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.*
TO 'gtid_reader'@'READER_SOURCE_HOST';

For a reader on the replica host, an example foreground command is:

proxysql_binlog_reader \
  -h db-replica1.internal -P 3306 \
  -u gtid_reader -p 'REPLACE_WITH_READER_PASSWORD' \
  -l 6020 -f \
  --ssl-mode=REQUIRED --ssl-verify-server-cert=1 \
  --ssl-ca=/etc/proxysql-mysqlbinlog/mariadb-ca.pem

-P selects MariaDB’s SQL port; -l selects the reader’s reporting listener. A bare -l 6020 binds all IPv4 interfaces; use -l 10.0.0.12:6020 to bind a specific private interface. Restrict access to that port to ProxySQL hosts. Supply the issuing CA and a server name covered by the certificate. Run each reader under your service supervisor with its own endpoint and listener. No MariaDB flavor flag is needed.

Connect ProxySQL to the readers

ProxySQL contacts a reader at the mysql_servers.hostname and gtid_port for that database endpoint. If both hosts run readers on port 6020, update the existing server rows through ProxySQL Admin, normally port 6032:

UPDATE mysql_servers SET gtid_port=6020
WHERE hostgroup_id=10 AND hostname='db-primary.internal' AND port=3306;

UPDATE mysql_servers SET gtid_port=6020
WHERE hostgroup_id=20 AND hostname='db-replica1.internal' AND port=3306;

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

SELECT hostname, port, gtid_executed, events
FROM stats_mysql_gtid_executed;

Repeat for every eligible server and relevant hostgroup membership. Confirm each reader’s endpoint is reachable and its reported position advances after new committed writes replicate. An empty MariaDB binary log has no initial GTID; the reader waits for the first nonempty position. A listening socket alone does not prove progress is being reported.

MariaDB positions use domain-server-sequence, such as 0-17-402. Routing compares the sequence watermark within the domain. Reader-fed statistics can show a server component of zero, such as 0-0-402; that display does not mean the replica has server ID zero.

Track the application’s own writes

On ProxySQL Admin:

SET mysql-default_session_track_gtids='OWN_GTID';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

For MariaDB backends, ProxySQL requests tracking of last_gtid and reads it from successful responses. Other tracked system variables are preserved. This does not add a blocking GTID lookup after every write.

Keep the write and dependent read on the same frontend connection to ProxySQL. A different connection from an application pool does not inherit this session’s tracked position. Commit the write first. A transaction-persistent session already inside a transaction can remain on its existing hostgroup rather than switch to a replica.

Route the dependent read

Assume the existing checkout user defaults to writer hostgroup 10 and the application reads order history with the following shape. Add a rule before broader rules that finish processing that query:

INSERT INTO mysql_query_rules
    (rule_id, active, username, match_pattern,
     destination_hostgroup, gtid_from_hostgroup, apply)
VALUES
    (100, 1, 'checkout',
     '^SELECT order_id, status FROM orders WHERE customer_id = [0-9]+ ORDER BY order_id[[:space:]]*;?[[:space:]]*$',
     20, 10, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Choose an unused rule ID and adapt the match to actual application SQL. destination_hostgroup selects replicas; gtid_from_hostgroup supplies this session’s tracked position from the writer group. Ensure no matching rule on this query’s processing path supplies a positive cache_ttl. A cached answer can otherwise bypass the intended replica freshness check.

Verify and handle waiting

For an existing orders table and an unused order ID, run on one application connection with autocommit enabled:

INSERT INTO orders (order_id, customer_id, status)
VALUES (1001, 42, 'received');
PROXYSQL INTERNAL SESSION;
SELECT order_id, status FROM orders WHERE customer_id = 42 ORDER BY order_id;

In the diagnostic JSON, check the writer entry in backends for a tracked gtid. On Admin, compare reader progress in stats_mysql_gtid_executed and per-server Queries_GTID_sync in stats_mysql_connection_pool.

If there is no tracked write position, the rule has no session GTID to enforce. If no eligible replica covers an available position, ProxySQL waits for an eligible backend; it does not automatically relax the condition. Backend acquisition is bounded by mysql-connect_timeout_server_max, in milliseconds (default 10000). Expiry can return error 9001, Max connect timeout reached while reaching hostgroup. This is distinct from the reader’s own replication-stream read timeout and from query execution timeouts.

Check stalled replication, reader logs, reader connectivity, and reported GTID progress before increasing timeouts. This reader-only route does not add a primary fallback for normal replication lag. An unusable tracked value can instead be routed back to its source writer hostgroup when routing permits it; it is not silently treated as a fresh-enough replica read.

For dependent reads on a new application connection, use a deliberate writer route or an application design that carries an explicit consistency requirement. This feature does not promise read-your-writes across unrelated sessions or restore state lost during failover.