Configuring ProxySQL with MySQL Replication and Group Replication for Read/Write Splitting and Automatic Failover

ProxySQL is a widely used MySQL middleware that provides features such as query caching, query rewriting, read/write splitting, and data sharding. This article explains how to combine ProxySQL with MySQL Replication and MySQL Group Replication to achieve basic read/write splitting and failover capabilities.

First, a ProxySQL instance (admin port 6032, traffic port 6033) and six MySQL instances are installed on the same host. The three replication instances listen on ports 3340‑3342, and the three Group Replication instances listen on ports 3343‑3345.

All six MySQL servers are registered in ProxySQL’s mysql_servers table, assigning hostgroup_id 1 to the replication servers and hostgroup_id 3 to the Group Replication servers.

Admin> select hostgroup_id, hostname,port,status from mysql_servers;
+--------------+-----------+------+--------+
| hostgroup_id | hostname  | port | status |
+--------------+-----------+------+--------+
| 1            | 127.0.0.1 | 3340 | ONLINE |
| 1            | 127.0.0.1 | 3341 | ONLINE |
| 1            | 127.0.0.1 | 3342 | ONLINE |
| 3            | 127.0.0.1 | 3343 | ONLINE |
| 3            | 127.0.0.1 | 3344 | ONLINE |
| 3            | 127.0.0.1 | 3345 | ONLINE |
+--------------+-----------+------+--------+

Two types of MySQL users are created in both the replication and Group Replication environments: a monitoring user ( proxysql_monitor ) with limited privileges, and a development user ( dev_user for replication, dev_user_mgr for Group Replication) with full DML rights.

MySQL localhost:3343 ssl SQL > create user proxysql_monitor@'127.0.0.1' identified by 'proxysql_monitor';
grant replication client,session_variables_admin,system_variables_admin,select on *.* to proxysql_monitor@'127.0.0.1';

MySQL localhost:3343 ssl SQL > create user dev_user@'127.0.0.1' identified by 'dev_user';
grant insert,delete,update,select,create on ytt.* to dev_user@'127.0.0.1';

MySQL localhost:3343 ssl SQL > create user dev_user_mgr@'127.0.0.1' identified by 'dev_user_mgr';
grant insert,delete,update,select,create on ytt.* to dev_user_mgr@'127.0.0.1';

These users are inserted into ProxySQL’s mysql_users table, with transaction_persistent=1 to keep transactions on the primary.

Admin> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values ('dev_user','dev_user',1,1,1),('dev_user_mgr','dev_user_mgr',1,3,1);

Read/write splitting is configured by adding rules to mysql_query_rules that route any statement beginning with SELECT to the appropriate replica hostgroup (2 for replication, 4 for Group Replication).

Admin> INSERT INTO mysql_query_rules(username,schemaname,active,match_pattern,destination_hostgroup,apply) VALUES ('dev_user','ytt',1,'^select',2,1),('dev_user_mgr','ytt',1,'^select',4,1);

All changes are loaded into runtime and persisted to disk using the series of load ... to runtime and save ... to disk commands.

Admin> load mysql servers to runtime;
Admin> load mysql users to runtime;
Admin> load mysql variables to runtime;
Admin> load mysql query rules to runtime;
Admin> save mysql servers to disk;
Admin> save mysql users to disk;
Admin> save mysql variables to disk;
Admin> save mysql query rules to disk;

Verification is performed by connecting as the development users to ProxySQL’s traffic port (6033), creating a test table, inserting a row, and querying it. The stats_mysql_query_digest table shows that write statements are sent to the primary hostgroup and reads to the replica hostgroup.

Admin> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest where schemaname = 'ytt';

Automatic failover for traditional replication is enabled by inserting a row into mysql_replication_hostgroups that maps writer hostgroup 1 to reader hostgroup 2 and monitors the super_read_only variable.

Admin> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment) values (1,2,'super_read_only','MySQL Replication failover test');

Failover is tested by promoting a replica (port 3342) to primary using MySQL Shell; ProxySQL detects the change via the monitored variables and updates its internal routing accordingly.

For Group Replication, a similar configuration is applied using mysql_group_replication_hostgroups , defining separate hostgroups for writer, backup writer, reader, and offline nodes.

Admin> insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (3,4,5,6,1,1,1,1000);

After promoting a secondary node (port 3344) to primary via MySQL Shell, ProxySQL automatically reflects the new primary in its mysql_server_group_replication_log and mysql_servers tables.

Admin> select hostname, port,viable_candidate,read_only,transactions_behind,error from mysql_server_group_replication_log where port in (3343,3344,3345) order by time_start_us desc limit 3;

The article concludes that the presented steps provide a basic but functional ProxySQL‑based HA solution for MySQL, with more advanced configurations left for future articles.