ProxySQL MySQL MySQL · DBA · Replication

ProxySQL — MySQL Replication Setup

Complete guide to setting up MySQL standard replication (Master-Replica) with ProxySQL. Covers replication setup, ProxySQL config, read_only detection and lag management.

MySQL Replication + ProxySQL — Overview

MySQL standard replication (Master-Replica) combined with ProxySQL is the most common production setup. ProxySQL acts as the intelligent router — sending writes to the master and reads to replicas, providing both load distribution and high availability.

Architecture

Applications → ProxySQL :6033
                   │
          ┌────────┴────────┐
          │ Query Routing   │
          └────────┬────────┘
                   │
    ┌──────────────┼──────────────┐
    ▼              │              ▼
[Master HG10]      │      [Replicas HG20]
192.168.1.100      │      192.168.1.101
(Writes)           │      192.168.1.102
                   │      (Reads, Load balanced)
                   │
          MySQL Replication ──────────────►
          (Binlog streaming)

Step 1 — Set Up MySQL Replication

▶On MySQL Master

SQL — MySQL Master Setup

-- Enable binary logging in my.cnf:
-- [mysqld]
-- server-id = 1
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_format = ROW
-- gtid_mode = ON
-- enforce_gtid_consistency = ON

-- Create replication user
CREATE USER 'replicator'@'%' IDENTIFIED BY 'ReplicaPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;

▶On Each MySQL Replica

SQL — MySQL Replica Setup

-- in my.cnf:
-- [mysqld]
-- server-id = 2    (unique per replica)
-- log_bin = ON
-- read_only = ON   ← Important! ProxySQL uses this to detect replicas
-- relay_log = /var/log/mysql/relay-bin.log
-- gtid_mode = ON
-- enforce_gtid_consistency = ON

-- Configure replication (GTID-based)
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='replicator',
  MASTER_PASSWORD='ReplicaPass123!',
  MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS\G

💡 Note: Set read_only=ON on all replicas. ProxySQL monitor checks this variable to automatically sort servers into the correct hostgroup.

Step 2 — Complete ProxySQL Configuration

SQL — Complete ProxySQL Config

-- Connect to ProxySQL Admin
-- mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- 1. 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';
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='1000' WHERE variable_name='mysql-monitor_ping_interval';
UPDATE global_variables SET variable_value='1500' WHERE variable_name='mysql-monitor_read_only_interval';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 2. Add servers (all to writer HG — ProxySQL auto-sorts by read_only)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, max_replication_lag, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 0,  'MySQL Master');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, max_replication_lag, comment)
VALUES (10, '192.168.1.101', 3306, 'ONLINE', 10, 'MySQL Replica 1');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, max_replication_lag, comment)
VALUES (10, '192.168.1.102', 3306, 'ONLINE', 10, 'MySQL Replica 2');

-- 3. Configure replication hostgroups
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'Master-Replica R/W Split');

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

-- 4. 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;

-- 5. Query routing 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 → Master');
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (2, 1, '^SELECT', 20, 1, 'All SELECTs → Replicas');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Verification

SQL — Verify Replication + ProxySQL

-- After waiting ~5 seconds for monitor to check read_only:
-- Replicas should auto-move to HG20

SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers
ORDER BY hostgroup_id;

-- Expected output:
-- hostgroup_id | hostname      | port | status
-- 10           | 192.168.1.100 | 3306 | ONLINE  ← Master (read_only=OFF)
-- 20           | 192.168.1.101 | 3306 | ONLINE  ← Replica (read_only=ON)
-- 20           | 192.168.1.102 | 3306 | ONLINE  ← Replica (read_only=ON)

-- Test routing
-- Connect via ProxySQL: mysql -u appuser -pAppPass123! -h 192.168.1.10 -P 6033

-- Then check query digest to see which HG queries went to
SELECT hostgroup, digest_text, count_star
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 10;

Replication Lag Management

SQL — Lag Management

-- Set max allowed replication lag per replica server
UPDATE mysql_servers SET max_replication_lag = 10
WHERE hostgroup_id=20;

-- If lag exceeds 10 seconds, replica is SHUNNED
-- Check current lag:
SELECT hostname, port, replication_lag, error
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC LIMIT 10;

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