Understanding ProxySQL Configuration Tables for MySQL HA (Read/Write Splitting and Failover)

After introducing ProxySQL's role in MySQL primary‑replica and group replication architectures for read/write splitting and failover, this chapter dives into the detailed configuration tables that were omitted previously.

ProxySQL built‑in database list

ytt:admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

The main database is the runtime library, disk is the persistent store, stats holds statistics, monitor stores monitoring data, and stats_history archives statistics.

1. Backend host metadata table – mysql_servers

This table stores metadata for backend MySQL instances; all subsequent operations rely on it.

Key fields include:

hostgroup_id : identifier of the host group to which the instance belongs.

gtid_port : port used by the Proxy Binlog Reader component.

status : instance state (online, offline_soft, offline_hard, shunned).

weight : priority of the instance within its group.

compression : whether the connection is compressed (1 = enabled).

max_connections : maximum connections allowed through ProxySQL.

max_replication_lag : delay threshold that moves an instance to shunned state.

Example of runtime mysql_servers records showing three shunned replicas:

ytt:admin> select hostgroup_id,hostname,port,status,max_connections from runtime_mysql_servers where hostgroup_id in (1,2);
+--------------+-----------+------+---------+-----------------+
| hostgroup_id | hostname  | port | status  | max_connections |
+--------------+-----------+------+---------+-----------------+
| 2            | 127.0.0.1 | 3341 | SHUNNED | 1000            |
| 2            | 127.0.0.1 | 3342 | SHUNNED | 1000            |
| 2            | 127.0.0.1 | 3340 | SHUNNED | 1000            |
+--------------+-----------+------+---------+-----------------+
3 rows in set (0.00 sec)

After starting the three replica nodes, their status changes to ONLINE :

ytt:admin> select hostgroup_id,hostname,port,status,max_connections from runtime_mysql_servers where hostgroup_id in (1,2);
+--------------+-----------+------+--------+-----------------+
| hostgroup_id | hostname  | port | status | max_connections |
+--------------+-----------+------+--------+-----------------+
| 2            | 127.0.0.1 | 3341 | ONLINE | 1000            |
| 1            | 127.0.0.1 | 3341 | ONLINE | 1000            |
| 2            | 127.0.0.1 | 3342 | ONLINE | 1000            |
| 2            | 127.0.0.1 | 3340 | ONLINE | 1000            |
+--------------+-----------+------+--------+-----------------+
4 rows in set (0.00 sec)

2. User metadata table – mysql_users

This table stores traffic‑user credentials. Important fields:

transaction_persistent : 1 to route the whole transaction to the default hostgroup, 0 to route per‑SQL.

default_hostgroup : default hostgroup for queries without explicit rules.

frontend and backend : map the user to ProxySQL (frontend) and MySQL (backend) instances.

Example showing a user that maps to both frontend and backend:

ytt:admin> select username,active,default_hostgroup,frontend,backend from mysql_users where username = 'dev_user';
+----------+--------+-------------------+----------+---------+
| username | active | default_hostgroup | frontend | backend |
+----------+--------+-------------------+----------+---------+
| dev_user | 1      | 1                 | 1        | 1       |
+----------+--------+-------------------+----------+---------+
1 row in set (0.00 sec)
ytt:admin> select username,active,default_hostgroup,frontend,backend from runtime_mysql_users where username = 'dev_user';
+----------+--------+-------------------+----------+---------+
| username | active | default_hostgroup | frontend | backend |
+----------+--------+-------------------+----------+---------+
| dev_user | 1      | 1                 | 0        | 1       |
| dev_user | 1      | 1                 | 1        | 0       |
+----------+--------+-------------------+----------+---------+
2 rows in set (0.00 sec)

3. Replication hostgroup table – mysql_replication_hostgroups

Defines the writer and reader hostgroup IDs and the variable used to detect read‑only status.

ytt:admin> select * from mysql_replication_hostgroups;
+------------------+------------------+-----------------+---------------------------------+
| writer_hostgroup | reader_hostgroup | check_type      | comment                         |
+------------------+------------------+-----------------+---------------------------------+
| 1                | 2                | super_read_only | MySQL Replication failover test |
+------------------+------------------+-----------------+---------------------------------+
1 row in set (0.00 sec)

4. Group replication hostgroup table – mysql_group_replication_hostgroups

Similar to the previous table but for group replication, adding offline_hostgroup , backup_writer_hostgroup , and max_writers for multi‑writer scenarios.

ytt:admin> select writer_hostgroup,backup_writer_hostgroup,reader_hostgroup from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup |
+------------------+-------------------------+------------------+
| 3                | 4                       | 5                |
+------------------+-------------------------+------------------+
1 row in set (0.00 sec)

5. Query rule table – mysql_query_rules

Controls read/write splitting. Important columns:

active : enable the rule.

username : user to which the rule applies.

schemaname : database name.

match_pattern : regex or pattern for matching queries.

proxy_port and destination_hostgroup : allow routing based on listening port.

Configure ProxySQL to listen on three ports (6033 for admin, 6401 for primary‑replica, 6402 for group replication) and add two port‑based rules:

ytt:admin> SET mysql-interfaces='0.0.0.0:6033;0.0.0.0:6401;0.0.0.0:6402';
Query OK, 1 row affected (0.00 sec)
ytt:admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 140 rows affected (0.02 sec)
ytt:admin> INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6401,1,1), (2,1,6402,3,1);
Query OK, 2 rows affected (0.00 sec)
ytt:admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ytt:admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.08 sec)

Current rule set:

ytt:admin> select rule_id,active,username,schemaname,match_pattern,destination_hostgroup,proxy_port from runtime_mysql_query_rules;
+---------+--------+--------------+------------+---------------+-----------------------+------------+
| rule_id | active | username     | schemaname | match_pattern | destination_hostgroup | proxy_port |
+---------+--------+--------------+------------+---------------+-----------------------+------------+
| 1       | 1      | NULL         | NULL       | NULL          | 1                     | 6401       |
| 2       | 1      | NULL         | NULL       | NULL          | 3                     | 6402       |
| 13      | 1      | dev_user     | ytt        | ^select       | 2                     | NULL       |
| 14      | 1      | dev_user_mgr | ytt        | ^select       | 4                     | NULL       |
+---------+--------+--------------+------------+---------------+-----------------------+------------+
4 rows in set (0.00 sec)

Verification: connecting as dev_user to port 6401 routes queries to hostgroup 1, while dev_user_mgr on port 6402 routes to hostgroup 3.

root@ytt-ubuntu:~# mysql -udev_user -pdev_user -P6401 -h ytt-ubuntu -e "use ytt;select count(*) from t1";
+----------+
| count(*) |
+----------+
|        5 |
+----------+
root@ytt-ubuntu:~# mysql -udev_user_mgr -pdev_user_mgr -P6402 -h ytt-ubuntu -e "use ytt;select count(*) from t1";
+----------+
| count(*) |
+----------+
|        1 |
+----------+

Audit table in ProxySQL confirms the routing:

ytt:admin> select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest where schemaname = 'ytt';
+-----------+------------+--------------+-------------------------+------------+
| hostgroup | schemaname | username     | digest_text             | count_star |
+-----------+------------+--------------+-------------------------+------------+
| 3         | ytt        | dev_user_mgr | select count(*) from t1 | 1          |
| 1         | ytt        | dev_user     | select count(*) from t1 | 1          |
+-----------+------------+--------------+-------------------------+------------+
2 rows in set (0.00 sec)

With these configurations, ProxySQL can effectively work with MySQL HA setups, providing transparent read/write splitting and automatic failover.