MariaDB Read-Write Split: Route Reads to Replicas (2026)

MariaDB’s read-write split, where you route read queries to replicas, is a surprisingly blunt instrument for scaling, often over-engineered when a simpler approach would suffice.

Let’s see it in action with a common setup: a primary MariaDB instance handling writes and a couple of read replicas. We’ll use a proxy like ProxySQL to manage the routing.

First, configure your my.cnf on the primary and replicas. On the primary:

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_slave_pos = 1

On the replicas, they’ll look similar but with different server-id and read_only enabled:

[mysqld]
server-id = 2 # or 3 for the second replica
log_bin = mysql-bin
binlog_format = ROW
read_only = 1
gtid_slave_pos = 1

The gtid_slave_pos is crucial for ensuring replicas can catch up if they disconnect. Now, set up replication. On each replica:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='<primary_ip_address>',
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='repl_password',
    SOURCE_PORT=3306,
    SOURCE_AUTO_POSITION=1;
START REPLICA;

Now, let’s introduce ProxySQL. Install it on a separate machine or on one of the database servers. Its configuration is key. We’ll define our hosts and then rules for routing.

In proxysql.cnf, under [mysqld_process], you’d have something like:

mysql-interfaces: 0.0.0.0:6033

This tells ProxySQL to listen for MySQL client connections on port 6033.

Next, we need to tell ProxySQL about our MariaDB servers. Connect to ProxySQL’s admin interface (port 6032) and run these commands:

LOAD MYSQL SERVERS TO RUNTIME;
SET mysql-servers.hostgroup_id = 10; -- Primary group
INSERT INTO mysql_servers (hostname, hostgroup_id, port) VALUES ('<primary_ip_address>', 10, 3306);

SET mysql-servers.hostgroup_id = 20; -- Replica group
INSERT INTO mysql_servers (hostname, hostgroup_id, port) VALUES ('<replica1_ip_address>', 20, 3306);
INSERT INTO mysql_servers (hostname, hostgroup_id, port) VALUES ('<replica2_ip_address>', 20, 3306);

LOAD MYSQL SERVERS TO RUNTIME;

Now, the routing rules. We want reads to go to hostgroup 20 (replicas) and writes to hostgroup 10 (primary).

LOAD MYSQL QUERY RULES TO RUNTIME;
-- Rule for reads
INSERT INTO mysql_query_rules (rule_id, active, destination_hostgroup, condition_statements) VALUES
(1, 1, 20, 'SELECT|SHOW|DESCRIBE|EXPLAIN');

-- Rule for writes (default if no other rule matches)
INSERT INTO mysql_query_rules (rule_id, active, destination_hostgroup, default_action) VALUES
(2, 1, 10, 'PASS');

LOAD MYSQL QUERY RULES TO RUNTIME;

With this, any query starting with SELECT, SHOW, DESCRIBE, or EXPLAIN will be sent to hostgroup 20 (the replicas). All other queries (inserts, updates, deletes) will go to hostgroup 10 (the primary).

The mental model here is that ProxySQL acts as an intelligent switchboard. It intercepts all incoming connections on its mysql-interfaces port and, based on configured rules, forwards the query to the appropriate backend server. The hostgroup_id is just a label; what matters are the mysql_query_rules that map query patterns to these hostgroups.

The surprising part is how granular you can get with condition_statements. You can match specific tables, schemas, or even user accounts. For instance, if you have a reporting database that’s a replica of your main production database, you could route all queries from a specific reporting application user to that replica, even if other users are still hitting the primary for their reads.

-- Example: Route queries from user 'reporter' to hostgroup 30 (another replica)
INSERT INTO mysql_query_rules (rule_id, active, destination_hostgroup, condition_user) VALUES
(3, 1, 30, 'reporter');
LOAD MYSQL QUERY RULES TO RUNTIME;

This allows for very specific read scaling strategies beyond just "all reads go to replicas."

What most people don’t realize is that ProxySQL, by default, doesn’t do any query rewriting or modification. It’s purely a router. If you have complex queries that involve joins between tables that might reside on different replicas (which is impossible in a standard replication setup), ProxySQL won’t magically fix that. It simply sends the query to the designated hostgroup. The actual execution happens on the target server.

Once you have this in place, your next immediate challenge will be managing connection pooling and ensuring replica lag doesn’t cause stale reads for critical operations.