ProxySQL MySQL MySQL · DBA · High Availability

ProxySQL — MySQL InnoDB Cluster

Configure ProxySQL with MySQL InnoDB Cluster. Create the cluster with MySQL Shell, set up Group Replication hostgroups in ProxySQL and test automatic primary failover.

MySQL InnoDB Cluster + ProxySQL

MySQL InnoDB Cluster is MySQL's official high availability solution built on top of MySQL Group Replication, MySQL Shell, and MySQL Router. ProxySQL can be used as an alternative to MySQL Router to provide more advanced query routing, connection pooling, and caching capabilities with InnoDB Cluster.

InnoDB Cluster Architecture with ProxySQL

Applications
     |
  ProxySQL :6033
     |
  ┌──┴────────────────┐
  HG10 (Writer)   HG20 (Readers)
  |                   |
  Primary         Secondary 1
  Node            Secondary 2
  |                   |
  └───── InnoDB Cluster (Group Replication) ────┘

Step 1 — Create InnoDB Cluster

BASH — Create InnoDB Cluster

# Using MySQL Shell to create InnoDB Cluster
mysqlsh

# In MySQL Shell:
shell.connect('root@192.168.1.100:3306')

var cluster = dba.createCluster('myCluster', {
  multiPrimary: false,
  adoptFromGR: false
})

# Add secondary nodes
cluster.addInstance('root@192.168.1.101:3306')
cluster.addInstance('root@192.168.1.102:3306')

# Check cluster status
cluster.status()

Step 2 — Create Required MySQL Users

SQL — Create MySQL Users

-- Run on Primary node

-- Monitor user
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT SELECT ON performance_schema.* TO 'monitor'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';

-- App user
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppPass123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';

FLUSH PRIVILEGES;

Step 3 — Configure ProxySQL

SQL — ProxySQL Config for InnoDB Cluster

-- Connect to ProxySQL Admin

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

-- Add all InnoDB Cluster nodes
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 'InnoDB Primary');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (10, '192.168.1.101', 3306, 'ONLINE', 'InnoDB Secondary 1');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (10, '192.168.1.102', 3306, 'ONLINE', 'InnoDB Secondary 2');

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

-- Use Group Replication hostgroups (InnoDB Cluster uses Group Replication underneath)
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 (10, 30, 20, 40, 1, 1, 0, 0);

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

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

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

Verify Setup

SQL — Verify

-- Check server placement (ProxySQL auto-detects primary/secondary)
SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers
ORDER BY hostgroup_id;

-- Check Group Replication member status from MySQL
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- Monitor read_only changes
SELECT hostname, port, read_only
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC LIMIT 10;

Primary Failover Test

BASH — Failover Test

# Stop primary MySQL to test failover
systemctl stop mysql   # on primary node

# InnoDB Cluster elects new primary automatically
# ProxySQL detects read_only change and routes writes to new primary

# Check new primary in MySQL Shell
mysqlsh --js -e "var c=dba.getCluster(); c.status()"

# Check ProxySQL sees new primary
mysql -u admin -padmin -h 127.0.0.1 -P 6032   -e "SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers ORDER BY hostgroup_id;"