How to Set Up ProxySQL Read/Write Splitting on Ubuntu 24.04

ProxySQL is a high-performance proxy for MySQL that understands the MySQL protocol. Applications connect to it as if it were a single database, and ProxySQL routes each query to the right backend based on rules you define, pools and reuses backend connections, and stops sending traffic to servers that fail health checks. In this tutorial you will install ProxySQL on Ubuntu 24.04 in front of an existing MySQL replication setup (one primary and two replicas), send writes to the primary and SELECT queries to the replicas, and verify the routing with ProxySQL's statistics tables.

Prerequisites

To follow this guide you need:

A server running Ubuntu 24.04 LTS for ProxySQL, such as a CubePath VPS, with a non-root user with sudo privileges. This guide uses the private IP 10.0.0.10.
A working MySQL 8 replication setup on a private network. This guide uses:
- 10.0.0.21: primary (read-write).
- 10.0.0.22 and 10.0.0.23: replicas, with read_only = ON and super_read_only = ON.
Administrative access to MySQL on the primary to create users. Users created on the primary replicate to the replicas.

How ProxySQL routes traffic

ProxySQL groups backend servers into numbered hostgroups. In this guide, hostgroup 10 is the writer (the primary) and hostgroup 20 is the readers (the replicas). A background monitor checks each server's read_only variable and keeps the primary in the writer hostgroup even after a failover. Query rules decide which hostgroup each query goes to; queries that match no rule go to the user's default hostgroup.

ProxySQL keeps its configuration in three layers: memory (what you edit through the admin interface), runtime (what is actually active) and disk (the SQLite database /var/lib/proxysql/proxysql.db). After each change you run LOAD ... TO RUNTIME to apply it and SAVE ... TO DISK to persist it.

Step 1 - Installing ProxySQL

Install the tools needed to add the repository:

sudo apt update
sudo apt install curl lsb-release

Download the ProxySQL 2.7 repository key into /etc/apt/keyrings:

sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL -o /etc/apt/keyrings/proxysql-2.7.x-keyring.gpg https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key.gpg

Add the repository for your Ubuntu release (noble), pinned to that key:

echo "deb [signed-by=/etc/apt/keyrings/proxysql-2.7.x-keyring.gpg] https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list

Install ProxySQL and the MySQL client, which you will use to talk to the admin interface:

sudo apt update
sudo apt install proxysql mysql-client

Enable and start the service, then check it:

sudo systemctl enable --now proxysql
sudo systemctl status proxysql
proxysql --version

ProxySQL version 2.7.x-...

ProxySQL listens on two ports: 6032 for the admin interface and 6033 for application traffic.

Step 2 - Securing the admin interface

The admin interface uses the default credentials admin / admin and only accepts that account from localhost. Connect to it:

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '

Change the admin password (replace your_admin_password), apply it and save it:

UPDATE global_variables SET variable_value = 'admin:your_admin_password' WHERE variable_name = 'admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Keep this session open for the next steps. From now on, reconnect with the new password.

Step 3 - Creating the monitor and application users in MySQL

ProxySQL needs two MySQL accounts: one to run health checks and one for the application. On the MySQL primary, open a MySQL shell as an administrative user:

sudo mysql

Create both users, allowing connections only from the ProxySQL server, and replace the passwords:

CREATE USER 'monitor'@'10.0.0.10' IDENTIFIED BY 'your_monitor_password';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.0.10';

CREATE USER 'appuser'@'10.0.0.10' IDENTIFIED BY 'your_app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'10.0.0.10';

REPLICATION CLIENT lets the monitor read replication status to measure lag. Both users replicate to the replicas automatically. Check from the ProxySQL server that the monitor account can reach every backend:

mysql -u monitor -p -h 10.0.0.22 -e "SELECT @@hostname, @@read_only;"

+------------+-------------+
| @@hostname | @@read_only |
+------------+-------------+
| db-replica1|           1 |
+------------+-------------+

Note

MySQL 8 uses the caching_sha2_password authentication plugin by default. ProxySQL 2.6 and later support it. If you run an older ProxySQL version, create the application user IDENTIFIED WITH mysql_native_password instead.

Step 4 - Adding the backend servers

Back in the ProxySQL admin session, tell the monitor which credentials to use:

UPDATE global_variables SET variable_value = 'monitor' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'your_monitor_password' WHERE variable_name = 'mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Register the three servers. The primary goes into the writer hostgroup and the replicas into the reader hostgroup. max_replication_lag stops sending reads to a replica that falls more than 10 seconds behind:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag) VALUES
  (10, '10.0.0.21', 3306, 0),
  (20, '10.0.0.22', 3306, 10),
  (20, '10.0.0.23', 3306, 10);

Declare 10 and 20 as a writer/reader pair. The monitor then moves servers between the two hostgroups based on their read_only value:

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'main cluster');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Verify that the monitor can connect to all servers and reads their read_only flags:

SELECT hostname, port, time_start_us, connect_error FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT hostname, port, read_only, error FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;

connect_error and error must be NULL, the primary must show read_only = 0 and the replicas read_only = 1. Then check the active configuration:

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers ORDER BY hostgroup_id;

+--------------+-----------+--------+
| hostgroup_id | hostname  | status |
+--------------+-----------+--------+
| 10           | 10.0.0.21 | ONLINE |
| 20           | 10.0.0.22 | ONLINE |
| 20           | 10.0.0.23 | ONLINE |
+--------------+-----------+--------+

Step 5 - Adding the application user

Applications authenticate against ProxySQL, which then uses the same credentials to open backend connections. Add the application user with the writer hostgroup as its default, so that anything not matched by a rule is safely sent to the primary:

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('appuser', 'your_app_password', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Step 6 - Configuring read/write splitting rules

Send plain SELECT statements to the readers, but keep SELECT ... FOR UPDATE on the primary because it takes locks. Rules are evaluated in rule_id order and apply = 1 stops evaluation at the first match:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 10, 1),
  (200, 1, '^SELECT', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

match_digest matches against the normalized query (with literal values replaced by ?) and is case-insensitive by default. Everything else (INSERT, UPDATE, DELETE, DDL and all statements inside explicit transactions that started on the primary) goes to hostgroup 10.

Important

Replicas are asynchronous. A read that immediately follows a write may hit a replica that has not applied it yet. For reads that must see the latest data, run them inside a transaction or add a more specific rule that routes them to hostgroup 10.

Step 7 - Testing the routing

Open port 6033 only to your application servers (here 10.0.0.30); the admin port 6032 stays local:

sudo ufw allow from 10.0.0.30 to any port 6033 proto tcp

From the ProxySQL server, run a read a few times through port 6033. It should alternate between the replicas:

mysql -u appuser -p -h 127.0.0.1 -P 6033 -e "SELECT @@hostname;"

+-------------+
| @@hostname  |
+-------------+
| db-replica2 |
+-------------+

Now run the same query inside a transaction. START TRANSACTION matches no rule, so it goes to the default hostgroup 10, and because mysql_users.transaction_persistent is enabled by default, every statement in that transaction stays on the primary:

mysql -u appuser -p -h 127.0.0.1 -P 6033 -e "START TRANSACTION; SELECT @@hostname; COMMIT;"

The hostname should be the primary's. In the admin session, look at the query digest statistics to see where each query pattern was sent:

SELECT hostgroup, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;

+-----------+------------------------------+------------+
| hostgroup | digest_text                  | count_star |
+-----------+------------------------------+------------+
| 20        | SELECT @@hostname            | 3          |
| 10        | SELECT @@hostname            | 1          |
| 10        | START TRANSACTION            | 1          |
| 10        | COMMIT                       | 1          |
+-----------+------------------------------+------------+

Check which rules are matching with SELECT rule_id, hits FROM stats_mysql_query_rules;.

Step 8 - Monitoring connection pooling

ProxySQL reuses backend connections across client connections. See how many connections it holds to each backend and how many queries each one served:

SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR, Queries FROM stats_mysql_connection_pool;

ConnUsed is connections currently serving a client, ConnFree idle connections kept for reuse and ConnERR failed connection attempts. A rising ConnERR or a server with status SHUNNED points to network or authentication problems on that backend. Use stats_mysql_query_digest regularly to find the slowest query patterns by sum_time.

Troubleshooting

Access denied for user 'appuser' on port 6033: the password in mysql_users does not match the MySQL user, or LOAD MYSQL USERS TO RUNTIME was not run. Compare with SELECT username, default_hostgroup FROM runtime_mysql_users;.

All queries go to hostgroup 10: the rules are not loaded to runtime, or the queries run inside a transaction started on the primary. Check runtime_mysql_query_rules and stats_mysql_query_rules.

A replica is SHUNNED: the monitor cannot connect or replication lag is above max_replication_lag. Look at monitor.mysql_server_connect_log and monitor.mysql_server_replication_lag_log.

Changes in /etc/proxysql.cnf have no effect: that file is only read when /var/lib/proxysql/proxysql.db does not exist yet. After the first start, configure ProxySQL through the admin interface as shown in this guide.

Conclusion

ProxySQL now pools connections to your MySQL cluster, sends writes and locking reads to the primary and spreads other reads across the replicas, while the monitor removes lagging or failed servers automatically. As next steps, point your application at port 6033, run two ProxySQL instances behind a floating IP or keepalived to remove the proxy as a single point of failure, and consider ProxySQL Cluster to synchronize configuration between them.

On this page