ProxySQL: Splitting Reads and Writes Across Servers

What ProxySQL Is and Why You Need It

ProxySQL is an SQL-aware proxy server that sits between the application and MySQL or MariaDB servers. It accepts client connections on port 6033, parses every query, and decides which server should receive it. The main job of ProxySQL is splitting reads and writes: write queries (INSERT, UPDATE, DELETE) go to the master, while SELECT queries are distributed among replicas.

Without a proxy, this logic has to live inside the application code: keeping two connections and manually picking one based on the query type. ProxySQL removes that burden from the application and moves it to a separate layer that can be changed without touching the code.

How ProxySQL Splits Reads and Writes

Three entities form the core: mysql_servers, mysql_users, and mysql_query_rules. Servers are grouped into hostgroups — usually group 10 for writes (master) and group 20 for reads (replicas). Query rules are regular-expression patterns that check the query text and assign it a hostgroup.

• A query starting with SELECT is routed to the read hostgroup.
• A query containing SELECT ... FOR UPDATE goes to the write hostgroup, to avoid reading stale data.
• INSERT, UPDATE, DELETE, and DDL always go to the write hostgroup.

Installing ProxySQL on Ubuntu and Debian

ProxySQL is installed from a separate repository; it is usually absent from the standard Ubuntu repositories. The package is taken from the ProxySQL project's release page on GitHub. After installation, the service listens on two ports: 6032 for administration and 6033 for client connections.

wget -O /tmp/proxysql.deb repo.proxysql.com/deb/proxysql_2.6.0-ubuntu22_amd64.deb
dpkg -i /tmp/proxysql.deb
systemctl enable --now proxysql
mysql -u admin -padmin -h 127.0.0.1 -P 6032

Configuring Servers and Hostgroups

After logging into the admin interface, you add servers and users. A user's password in ProxySQL must match the password on the real MySQL server — the proxy does not keep a separate credential store, it only forwards the connection further.

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '10.0.0.11', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '10.0.0.12', 3306);
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app', 'secret', 10);
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;

Query Rules: Routing Queries to the Right Servers

Rules are added to the mysql_query_rules table and applied in order of their numbers. The first match wins, so specific rules are placed above general ones. Test the setup before switching production traffic over through master-slave replication, so the proxy sees an up-to-date list of replicas.

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

How to Check That Balancing Works

After loading the rules, watch the proxy's statistics: how many queries went to each hostgroup and whether there are replica connection errors. If you have GTID replication set up, ProxySQL can automatically detect a master change through the ProxySQL Cluster module or a checker script.

• The stats_mysql_query_digest table shows which queries and which hostgroup are hit most often.
• The mysql_server_connect_log table records connection errors to the servers.
• The SHOW MYSQL STATUS command in the 6032 interface prints active connection counters.

Common Mistakes When Setting Up ProxySQL

Most problems come not from the proxy itself, but from the environment around it.

• A user's password in mysql_users does not match the password on the MySQL server — the connection fails with an access error.
• Forgetting to run LOAD ... TO RUNTIME — changes are visible in the configuration but are not applied to traffic.
• A replica falls behind the master, and a SELECT lands on it — the application reads stale data. Monitoring replication lag helps here.
• The max_connections limit on the MySQL server is lower than the total load from the proxy — the number of Too many connections errors grows.

Summary: ProxySQL Rollout Checklist

• Install ProxySQL and verify access to ports 6032 and 6033.
• Add servers to mysql_servers with correct hostgroup_id values.
• Sync users and passwords with the real MySQL servers.
• Write query rules from specific cases to general ones and load them into runtime.
• Set up replica status and lag checks before going live.