Setting Up ProxySQL in Front of MySQL

I got tired of application-level read/write splitting. Every framework does it a little differently, and every one of them broke in a slightly different way the first time a replica lagged. ProxySQL sits between the application and MySQL, speaks the MySQL protocol on both sides, and routes each query by rules I define. It also multiplexes hundreds of application connections onto a few backend connections and pulls unhealthy servers out of rotation automatically.

Below I install ProxySQL 2.0 on CentOS 7, point it at one MySQL 5.7 master (10.0.0.10) and two replicas (10.0.0.11, 10.0.0.12) with replication already running, and set up read/write splitting so SELECT goes to the replicas and everything else to the master. The application changes one setting, the host it connects to. ProxySQL is light enough to share the application server. You need port 6033 open from the app to ProxySQL, and 3306 from ProxySQL to the databases.

Install

sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable proxysql release
sudo yum install -y proxysql2
sudo systemctl enable --now proxysql

ProxySQL listens on 6032 for its admin interface and 6033 for application traffic. The admin interface is itself a MySQL-protocol endpoint, so you configure it with the mysql client:

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

Change the admin password before anything else. The default admin/admin only works from localhost, but I still don't leave it:

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

That three-part pattern of UPDATE, LOAD ... TO RUNTIME, SAVE ... TO DISK applies to every configuration change in ProxySQL. Changes are staged in memory, activated with LOAD, and persisted to /var/lib/proxysql/proxysql.db with SAVE. I forgot the SAVE half more than once and lost config on a restart.

The monitoring user

ProxySQL checks backend health and replication lag with its own account. On the master (it replicates to the others):

CREATE USER 'proxysql_monitor'@'10.0.0.%' IDENTIFIED BY 'monitor-password';
GRANT REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'10.0.0.%';

Then in the admin interface:

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

Backends and hostgroups

Hostgroups are the routing targets. Writers go in hostgroup 10, readers in 20.

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_replication_lag)
VALUES (10, '10.0.0.10', 3306, 1000, 0),
       (20, '10.0.0.11', 3306, 1000, 10),
       (20, '10.0.0.12', 3306, 1000, 10);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

max_replication_lag = 10 tells ProxySQL to shun a replica whose Seconds_Behind_Master exceeds 10, and bring it back when it catches up. If you also want the master to serve reads when replicas are down, add it to hostgroup 20 too with a low weight.

For automatic failover based on read_only, register the pair in mysql_replication_hostgroups:

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

Now ProxySQL polls @@read_only on every server and moves any server with read_only=0 to the writer group and read_only=1 to the reader group. Promote a replica by flipping read_only and ProxySQL follows within a few seconds.

The application user

ProxySQL authenticates the application itself, then connects to the backend with the same credentials, so the user must exist in both places.

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

default_hostgroup = 10 means anything not matched by a rule goes to the writer. transaction_persistent = 1 keeps every statement of an explicit transaction on the server where it started, which is essential for correctness.

Query rules

Rules are evaluated in rule_id order. Send SELECT ... FOR UPDATE to the writer, then everything else beginning with SELECT to the readers:

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;

Anything that does not match (INSERT, UPDATE, DELETE, DDL, SET) falls through to default_hostgroup. Read-after-write consistency is the classic problem here: a SELECT immediately after an INSERT may hit a replica that has not applied it yet. For the queries where that matters, wrap them in a transaction (they stick to the writer thanks to transaction_persistent), or add a rule with a specific match_digest for that query routing it to hostgroup 10.

Where do the queries land?

Point a client at ProxySQL:

mysql -h proxysql.internal -P 6033 -u appuser -p'AppPassw0rd!' appdb \
  -e "SELECT @@hostname; START TRANSACTION; SELECT @@hostname; COMMIT;"

The first @@hostname should be a replica and the second the master. In the admin interface, check health and traffic:

SELECT hostgroup_id, hostname, status, ConnUsed, Queries, Latency_us
FROM stats_mysql_connection_pool;
SELECT hostgroup, SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
SELECT * FROM monitor.mysql_server_replication_lag_log ORDER BY time_start_us DESC LIMIT 6;

Every server should show ONLINE. Stop a replica's mysqld and watch it move to SHUNNED within a few seconds; start it again and watch it return.

When it goes wrong

ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 20 after 10000ms. No server in the reader hostgroup is ONLINE. Check mysql_server_connect_log in the monitor schema; usually the monitor user cannot connect, or the firewall blocks ProxySQL's host.

ERROR 1045 (28000): ProxySQL Error: Access denied for user 'appuser'@'10.0.0.50' (using password: YES). Either the user is not in mysql_users or the password differs. Note that ProxySQL 2.0 supports mysql_native_password hashes; MySQL 8.0's caching_sha2_password users must be altered to native.

All queries go to the master despite the rules. Check SELECT rule_id, hits FROM stats_mysql_query_rules;. If hits is zero, the digest does not match, often because the application sends select in lowercase and the rule uses ^SELECT with case sensitivity. Set mysql-query_processor_regex = 2 for case-insensitive matching or write the pattern as (?i)^SELECT.

Replica keeps flapping between ONLINE and SHUNNED. max_replication_lag is too tight for the workload. Raise it, or fix the lag with slave_parallel_workers on the replica.

Kill a replica on purpose before you rely on any of this. I'd rather see the shunning happen on a quiet afternoon than during an incident.