DB-유

Read/Write Splitting with ProxySQL

· mysql, proxysql, replication

I had a master with two replicas sitting mostly idle because the application sent every query to one host. Getting apps to use replicas is annoying: most frameworks either don't split reads and writes or need code changes, and none know when a replica has fallen behind. ProxySQL sits in the middle, speaks the MySQL protocol on both sides, and picks a server per query based on rules you define and backend health.

I'll set up ProxySQL 2.0 in front of a MySQL 5.7 or 8.0 replication topology: writes to the master, reads to the replicas, lagging replicas dropped automatically. You need a working master with a replica (both threads running in SHOW SLAVE STATUS), a host for ProxySQL (I usually put it on the app server itself; CentOS 7 or Ubuntu 18.04), and a monitoring user and an application user with identical passwords on every MySQL server.

Install ProxySQL

From the Percona repository (works on both distributions):

# CentOS 7
sudo yum install proxysql2
# Ubuntu 18.04
sudo apt-get install proxysql2
sudo systemctl enable --now proxysql

ProxySQL listens on 6033 for application traffic and on 6032 for its admin interface, which is itself a MySQL-protocol interface with default credentials admin/admin bound to localhost:

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

Users on the MySQL side

On the master (it replicates to the replicas):

CREATE USER 'monitor'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY 'M0nit0r-P@ss';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.1.%';
CREATE USER 'app'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY 'App-P@ss';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.1.%';

ProxySQL 2.0 authenticates backends with mysql_native_password, so on MySQL 8.0 specify the plugin explicitly as above.

Hostgroups and servers

In the ProxySQL admin shell, hostgroup 10 is the writer, 20 the readers:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_replication_lag)
VALUES (10, '10.0.1.20', 3306, 1000, 0),
       (20, '10.0.1.21', 3306, 1000, 10),
       (20, '10.0.1.22', 3306, 1000, 10);

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'shop cluster');

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='M0nit0r-P@ss' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_replication_lag_interval';

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

max_replication_lag = 10 shuns a replica when Seconds_Behind_Master exceeds 10 and brings it back automatically. mysql_replication_hostgroups makes ProxySQL check read_only on every server and move it between the writer and reader groups accordingly, so after a failover where you set read_only=OFF on the promoted replica, traffic follows without touching ProxySQL. Make sure replicas have read_only = 1 in my.cnf. Note that the master is not in hostgroup 20 here; add it with a low weight if you want it to take a share of reads.

The application user and query rules

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app', 'App-P@ss', 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 master, which is the safe default. transaction_persistent = 1 keeps every statement inside an explicit transaction on the same server, so a SELECT after a BEGIN does not go to a replica.

Now route reads:

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;

Rules are evaluated in rule_id order and apply = 1 stops at the first match, so SELECT ... FOR UPDATE is caught by rule 100 and sent to the master before rule 200 sends other SELECTs to the replicas.

Point the application at it

Change the application's database host to the ProxySQL host and port 6033. Nothing else changes; the user and password are the ones inserted in mysql_users. This is the part I like best, since no code changes are needed.

mysql -h 10.0.1.10 -P 6033 -u app -pApp-P@ss shop -e 'SELECT @@hostname'

Run that several times; the hostname should alternate between the two replicas.

Did it work?

In the admin interface:

SELECT hostgroup_id, hostname, status, ConnUsed, Queries, Latency_us FROM stats_mysql_connection_pool;
SELECT hostgroup, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;
SELECT * FROM monitor.mysql_server_replication_lag_log ORDER BY time_start_us DESC LIMIT 5;

stats_mysql_query_digest shows every digest with the hostgroup it went to; SELECTs should be on 20, everything else on 10. Stop replication on one replica (STOP SLAVE) and watch it become SHUNNED in stats_mysql_connection_pool after the lag threshold; START SLAVE brings it back to ONLINE.

Things that went wrong

  • ERROR 1045 (28000): ProxySQL Error: Access denied for user 'app'@'10.0.1.30' (using password: YES) from the application: the user is not in mysql_users, or LOAD MYSQL USERS TO RUNTIME was not run. Check SELECT * FROM runtime_mysql_users;.
  • ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 20 after 10000ms: no server in the reader group is ONLINE. Check stats_mysql_connection_pool and monitor.mysql_server_connect_log for the reason (wrong monitor password, firewall, or all replicas shunned for lag).
  • All replicas moved to the writer hostgroup: read_only is OFF on them, so the read_only check promoted them. Set read_only=1 on every replica.
  • A read immediately after a write returns stale data: expected with asynchronous replication. Either set transaction_persistent and wrap the read in the same transaction, add a rule sending that specific digest to hostgroup 10, or use mysql-monitor_replication_lag_use_percona_heartbeat with pt-heartbeat for finer lag control. ProxySQL 2.0 also offers causal reads via GTID tracking with mysql_servers.gtid_port and the proxysql_binlog_reader process, which is worth a look once the basics work.
  • Monitor: connection failed in /var/lib/proxysql/proxysql.log: the monitor user was created for 10.0.1.% but ProxySQL connects from another address, or the monitor user lacks REPLICATION CLIENT.

Start with the two rules above, keep the default hostgroup on the master, and refine from stats_mysql_query_digest once you see what the application really sends. Don't try to be clever on day one.