--- title: "How to Set Up ProxySQL Read/Write Splitting on Ubuntu 24.04" description: "Install ProxySQL on Ubuntu 24.04 and put it in front of a MySQL primary and replicas to split reads and writes, pool connections and monitor query routing." category: "Database Advanced" language: en source: https://cubepath.com/docs/database-advanced/proxysql-for-mysql-load-balancing updated: 2026-09-25 --- # 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: ```bash sudo apt update sudo apt install curl lsb-release ``` Download the ProxySQL 2.7 repository key into `/etc/apt/keyrings`: ```bash 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: ```bash 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: ```bash sudo apt update sudo apt install proxysql mysql-client ``` Enable and start the service, then check it: ```bash sudo systemctl enable --now proxysql sudo systemctl status proxysql proxysql --version ``` ```output 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: ```bash 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: ```sql 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: ```bash sudo mysql ``` Create both users, allowing connections only from the ProxySQL server, and replace the passwords: ```sql 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: ```bash mysql -u monitor -p -h 10.0.0.22 -e "SELECT @@hostname, @@read_only;" ``` ```output +------------+-------------+ | @@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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers ORDER BY hostgroup_id; ``` ```output +--------------+-----------+--------+ | 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: ```sql 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: ```sql 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: ```bash 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: ```bash mysql -u appuser -p -h 127.0.0.1 -P 6033 -e "SELECT @@hostname;" ``` ```output +-------------+ | @@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: ```bash 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: ```sql SELECT hostgroup, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10; ``` ```output +-----------+------------------------------+------------+ | 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: ```sql 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.