ProxySQL — Galera Cluster (PXC) Setup

# Install PXC on all nodes
yum install -y percona-xtradb-cluster

# /etc/my.cnf on Node 1:
# [mysqld]
# server-id=1
# wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
# wsrep_cluster_name=pxc-cluster
# wsrep_cluster_address=gcomm://192.168.1.100,192.168.1.101,192.168.1.102
# wsrep_node_name=pxc-node1
# wsrep_node_address=192.168.1.100
# wsrep_sst_method=xtrabackup-v2
# pxc_strict_mode=ENFORCING

# Bootstrap first node
systemctl start mysql@bootstrap.service

# Start other nodes normally
systemctl start mysql

-- On any PXC node (syncs to all)

-- Monitor user (ProxySQL needs wsrep status access)
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT SELECT ON performance_schema.* TO 'monitor'@'%';
GRANT PROCESS ON *.* TO 'monitor'@'%';

-- ProxySQL cluster check user
CREATE USER 'proxysql_user'@'%' IDENTIFIED BY 'ProxyPass123!';
GRANT USAGE ON *.* TO 'proxysql_user'@'%';

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

FLUSH PRIVILEGES;

-- Connect to ProxySQL Admin

-- Set 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';

-- Enable Galera monitoring (check wsrep_local_state)
UPDATE global_variables SET variable_value='1000'
WHERE variable_name='mysql-monitor_galera_healthcheck_interval';
UPDATE global_variables SET variable_value='800'
WHERE variable_name='mysql-monitor_galera_healthcheck_timeout';

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

-- Add PXC nodes
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 1, 'PXC Node 1 - Writer');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, comment)
VALUES (10, '192.168.1.101', 3306, 'ONLINE', 1, 'PXC Node 2');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, comment)
VALUES (10, '192.168.1.102', 3306, 'ONLINE', 1, 'PXC Node 3');

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

-- Configure Galera hostgroups
INSERT INTO mysql_galera_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, 1, 0);

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

-- Add users
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;

-- Check node placement
SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers ORDER BY hostgroup_id;

-- Check wsrep status ProxySQL sees
SELECT hostname, port, time_start_us, wsrep_local_state, primary_partition, read_only, error
FROM monitor.mysql_server_galera_log
ORDER BY time_start_us DESC LIMIT 20;

-- Check Galera cluster state on MySQL node
SHOW STATUS LIKE 'wsrep%';

-- Key wsrep variables:
-- wsrep_cluster_size    = 3 (all nodes in cluster)
-- wsrep_local_state     = 4 (SYNCED = healthy)
-- wsrep_cluster_status  = Primary
-- wsrep_connected       = ON