MySQL主从复制与读写分离实战：ProxySQL代理路由与故障切换配置

MySQL主从复制与读写分离是数据库高可用架构的基础方案。主库负责写操作，从库负责读操作，通过复制机制保持数据同步。ProxySQL作为MySQL协议层代理，根据SQL类型自动路由到主库或从库，并支持故障自动切换。本文介绍MySQL主从复制搭建、ProxySQL读写分离配置和主库故障切换方案。

MySQL主从复制搭建配置

MySQL主从复制基于binlog实现，主库将变更写入二进制日志，从库拉取并回放。主库配置：

# /etc/my.cnf (主库 - 192.168.1.20)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
binlog_expire_logs_seconds = 604800
max_binlog_size = 256M

# 创建复制用户
# mysql> CREATE USER 'repl'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'Repl@2026';
# mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';
# mysql> FLUSH PRIVILEGES;

从库配置并启动复制：

# /etc/my.cnf (从库 - 192.168.1.21)
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
read-only = ON
relay-log = relay-bin
relay_log_recovery = ON

# 启动GTID复制
# mysql> CHANGE REPLICATION SOURCE TO
#     SOURCE_HOST='192.168.1.20',
#     SOURCE_USER='repl',
#     SOURCE_PASSWORD='Repl@2026',
#     SOURCE_AUTO_POSITION=1;
# mysql> START REPLICA;
# mysql> SHOW REPLICA STATUS\G

# 关键指标检查：
# Replica_IO_Running: Yes
# Replica_SQL_Running: Yes
# Seconds_Behind_Master: 0
# Auto_Position: 1

GTID模式下每个事务有全局唯一ID，从库自动定位复制位点，避免了传统binlog位点管理的复杂性。新增从库时不需要先锁定主库获取位点，只需配置SOURCE_AUTO_POSITION=1即可自动同步。

ProxySQL安装与基础配置

ProxySQL是高性能MySQL代理，支持读写分离、连接池、查询缓存、故障切换等功能。Docker部署：

docker run -d --name proxysql \
  -p 6033:6033 -p 6032:6032 \
  -v /opt/proxysql/proxysql.cnf:/etc/proxysql.cnf \
  proxysql/proxysql:2.5.5

通过Admin接口配置后端MySQL服务器：

-- 连接ProxySQL管理端口
mysql -h127.0.0.1 -P6032 -uadmin -padmin

-- 添加主库（写节点）
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections)
VALUES (10, '192.168.1.20', 3306, 200);

-- 添加从库（读节点）
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections)
VALUES (20, '192.168.1.21', 3306, 200);
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections)
VALUES (20, '192.168.1.22', 3306, 200);

-- 配置监控用户
SET GLOBAL mysql-monitor_username='monitor';
SET GLOBAL mysql-monitor_password='Monitor@2026';

-- 配置后端健康检查
UPDATE mysql_servers SET max_connections=200 WHERE hostgroup_id IN (10, 20);
SET GLOBAL mysql-have_ssl=0;

-- 保存并加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

读写分离规则配置

ProxySQL通过查询规则（query rules）实现读写分离，根据SQL类型将请求路由到不同hostgroup：

-- 插入读写分离规则
-- 规则1: SELECT走从库(hostgroup 20)
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);  -- SELECT FOR UPDATE走主库

INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 20, 1);  -- 普通SELECT走从库

-- 规则3: 写操作走主库(hostgroup 10)
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (3, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)', 10, 1);

-- 规则4: 事务内所有操作走主库
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply, flagIN, flagOUT)
VALUES (4, 1, '^BEGIN', 10, 1, 0, 100);

INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply, flagIN, flagOUT)
VALUES (5, 1, '.*', 10, 1, 100, 100);

INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply, flagIN)
VALUES (6, 1, '^COMMIT', 10, 1, 100);

-- 加载规则
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

配置应用连接ProxySQL的用户账号：

-- ProxySQL用户配置
INSERT INTO mysql_users(username, password, default_hostgroup, active)
VALUES ('app_user', 'App@2026', 10, 1);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- 应用连接ProxySQL
-- mysql -h192.168.1.100 -P6033 -uapp_user -p
-- 写操作自动路由到主库，读操作自动路由到从库

ProxySQL故障切换与主库切换配置

ProxySQL内置Galera和Group Replication的故障检测和切换支持。对于普通主从复制，通过scheduler脚本实现主库故障自动切换：

-- 配置主库故障检测
-- 使用proxysql_galera_checker或自定义脚本
-- 当主库不可用时，将从库提升为新的写hostgroup

-- 方法1: 使用scheduler脚本自动切换
INSERT INTO scheduler(active, interval_ms, filename, arg1, arg2, arg3)
VALUES (1, 3000, '/opt/proxysql/failover_script.sh', 10, 20, '/var/log/proxysql/failover.log');

LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;

failover脚本示例：

#!/bin/bash
# /opt/proxysql/failover_script.sh
WRITE_HG=$1   # 10
READ_HG=$2    # 20
LOG=$3

MYSQL_CMD="mysql -h127.0.0.1 -P6032 -uadmin -padmin -N -e"

# 检查主库是否存活
WRITE_SERVER=$($MYSQL_CMD "SELECT hostname FROM mysql_servers WHERE hostgroup_id=$WRITE_HG AND status='ONLINE' LIMIT 1")

if [ -z "$WRITE_SERVER" ]; then
    # 主库不可用，从读组选一个提升为写组
    NEW_MASTER=$($MYSQL_CMD "SELECT hostname FROM mysql_servers WHERE hostgroup_id=$READ_HG AND status='ONLINE' ORDER BY connections_used DESC LIMIT 1")

    if [ -n "$NEW_MASTER" ]; then
        # 将新主库加入写组
        $MYSQL_CMD "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES ($WRITE_HG, '$NEW_MASTER', 3306)"
        # 从读组移除
        $MYSQL_CMD "DELETE FROM mysql_servers WHERE hostgroup_id=$READ_HG AND hostname='$NEW_MASTER'"
        # 在新主库上设置read-only=OFF
        mysql -h"$NEW_MASTER" -uroot -pRootPass -e "SET GLOBAL read_only=OFF; SET GLOBAL super_read_only=OFF;"
        # 加载配置
        $MYSQL_CMD "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"
        echo "$(date): Failover to $NEW_MASTER" >> $LOG
    fi
fi

从库延迟监控与优化

主从复制延迟是读写分离架构的主要风险，从库延迟会导致读操作读到旧数据。ProxySQL通过监控Seconds_Behind_Master自动调整路由：

-- 配置复制延迟检查
SET GLOBAL mysql-monitor_replication_lag_interval = 1000;  -- 检查间隔1秒
SET GLOBAL mysql-monitor_replication_lag_timeout = 300;    -- 延迟超5分钟标记为异常

-- 延迟超过阈值时，读操作自动回退到主库
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (10, 1, '^SELECT', 10, 1);  -- 优先级低于规则2，仅在从库全部不可用时生效

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

减少从库延迟的方法：开启并行复制（slave_parallel_workers=8, slave_parallel_type=LOGICAL_CLOCK）、使用物理复制（如基于Percona XtraBackup的快速从库搭建）、从库使用SSD存储加速binlog回放。对于强一致性要求的读操作，使用SELECT FOR UPDATE或在应用层指定走主库。