ProxySQL — MySQL Group Replication

-- in my.cnf on ALL nodes:
-- [mysqld]
-- server-id = 1                  (unique per node)
-- gtid_mode = ON
-- enforce_gtid_consistency = ON
-- binlog_checksum = NONE
-- log_slave_updates = ON
-- plugin_load_add = group_replication.so
-- group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
-- group_replication_start_on_boot = OFF
-- group_replication_local_address = "192.168.1.100:33061"
-- group_replication_group_seeds = "192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
-- group_replication_bootstrap_group = OFF

-- On first node only — bootstrap the group
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- On other nodes:
START GROUP_REPLICATION;

-- Check group status
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- Run on PRIMARY node (replicates to others)

-- Monitor user
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT SELECT ON performance_schema.* TO 'monitor'@'%';
GRANT SELECT ON sys.* 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;

-- Connect to ProxySQL Admin

-- Monitor credentials
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 nodes to a single hostgroup (ProxySQL auto-sorts them)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 'MGR Node 1');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (10, '192.168.1.101', 3306, 'ONLINE', 'MGR Node 2');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (10, '192.168.1.102', 3306, 'ONLINE', 'MGR Node 3');

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

-- Configure Group Replication hostgroups
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, comment)
VALUES
  (10, 30, 20, 40, 1, 1, 0, 0, 'MGR Single Primary');

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

-- Add 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, comment)
VALUES (1, 1, '^SELECT .* FOR UPDATE', 10, 1, 'Locking reads → Primary');
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (2, 1, '^SELECT', 20, 1, 'SELECTs → Secondaries');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- Check how ProxySQL sorted the nodes
SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers
ORDER BY hostgroup_id;

-- Expected:
-- HG10: Primary node (ONLINE)
-- HG20: Secondary nodes (ONLINE)
-- HG30: backup writers (usually empty in single-primary)
-- HG40: offline/recovering nodes

-- Check ProxySQL sees MGR members
SELECT hostname, port, time_start_us, success_time_us, read_only
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC LIMIT 10;

-- Test automatic failover
-- Stop the primary, ProxySQL auto-detects new primary within seconds