MySQL主从复制实战：GTID模式配置与ProxySQL读写分离部署方案

MySQL主从复制是数据库高可用架构的基础组件，通过将主库的变更同步到从库实现数据冗余和读负载分担。GTID（Global Transaction Identifier）模式相比传统的基于binlog位置的复制，提供了全局事务标识和自动故障恢复能力，大幅降低了运维复杂度。配合ProxySQL作为中间层实现读写分离，应用程序无需感知后端拓扑变化，是MySQL性能调优和数据备份恢复方案中的标准组合。

MySQL GTID复制原理与主库配置

GTID是MySQL 5.6引入的特性，每个事务在主库上执行时分配一个全局唯一标识符，格式为server_uuid:transaction_id。从库通过GTID自动定位复制位置，无需手动指定binlog文件名和位置，故障切换时也不会丢失事务或重复执行。

-- 主库配置 /etc/my.cnf (MySQL 8.0)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW           # GTID要求ROW格式
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON        # 级联复制需要
binlog-expire-logs-seconds = 604800  # 7天
expire_logs_days = 7          # 兼容旧版本参数

-- 从库配置
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
read-only = ON                # 从库只读
super-read-only = ON          # 包括SUPER权限用户也只读
relay-log = relay-bin
relay-log-recovery = ON

-- 主库创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';

-- MySQL 8.0 使用 caching_sha2_password 认证
-- 复制用户建议使用 mysql_native_password 保证兼容性
ALTER USER 'repl'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 查看主库GTID状态
SHOW MASTER STATUS\G
/*
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 1234
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 550f89a0-1234-5678-9abc-def012345678:1-100
*/

gtid-mode和enforce-gtid-consistency必须同时启用。enforce-gtid-consistency为ON时，不支持CREATE TABLE … SELECT和事务内部使用临时表等不安全语句，部署前需要审计现有SQL。

从库搭建与复制状态监控

从库通过CHANGE MASTER TO语句配置复制源。GTID模式下可以自动从主库拉取缺失的事务，无需手动指定binlog位置。生产环境中建议使用 pt-table-checksum 和 pt-table-sync 工具定期校验主从数据一致性。

-- 从库配置复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'StrongPassword123!',
    MASTER_AUTO_POSITION = 1;   -- GTID自动定位

START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G
/*
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
             Slave_IO_Running: Yes    -- 必须为Yes
            Slave_SQL_Running: Yes    -- 必须为Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
        Seconds_Behind_Master: 0    -- 延迟秒数
    Auto_Position: 1                -- GTID模式
     Retrieved_Gtid_Set: 550f89a0-...:101-200
     Executed_Gtid_Set: 550f89a0-...:1-200
*/

-- 监控脚本：检查复制健康状态
SELECT
    @@server_id AS server_id,
    CHANNEL_NAME,
    SERVICE_STATE AS io_state,
    LAST_ERROR_NUMBER AS io_errno,
    LAST_ERROR_MESSAGE AS io_error,
    LAST_ERROR_TIMESTAMP AS io_error_time
FROM performance_schema.replication_connection_status;

SELECT
    SERVICE_STATE AS sql_state,
    LAST_ERROR_NUMBER AS sql_errno,
    LAST_ERROR_MESSAGE AS sql_error,
    SECONDS_BEHIND_MASTER AS delay_seconds
FROM performance_schema.replication_applier_status_by_worker;

-- 查看GTID执行情况
SELECT @@global.gtid_executed AS executed_gtids;
SELECT @@global.gtid_purged AS purged_gtids;

-- 跳过指定GTID事务（谨慎使用，仅在确认事务可安全跳过时）
-- STOP SLAVE;
-- SET GTID_NEXT = '550f89a0-1234-5678-9abc-def012345678:201';
-- BEGIN; COMMIT;
-- SET GTID_NEXT = 'AUTOMATIC';
-- START SLAVE;

复制延迟是主从架构中最常见的问题。Seconds_Behind_Master仅反映SQL线程的延迟，IO线程积压时该值可能显示为0但实际有大量未应用事务。通过监控Retrieved_Gtid_Set和Executed_Gtid_Set的差值可以更准确判断延迟。大事务、慢查询和锁等待是延迟的主要原因，生产环境中应通过分库分表方案减少单库压力。

ProxySQL安装配置与读写分离规则

ProxySQL是高性能的MySQL代理，支持读写分离、连接池、查询路由和多目标集群管理。与MySQL Connector/J的读写分离不同，ProxySQL在协议层工作，应用程序无需任何改动即可实现读写分离。

# 安装ProxySQL
# CentOS/RHEL
yum install proxysql -y
# Ubuntu/Debian
apt install proxysql -y

# 启动ProxySQL
systemctl start proxysql
systemctl enable proxysql

# 连接ProxySQL管理接口（默认端口6032，默认用户admin/admin）
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- 配置后端MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections) VALUES
(1, '192.168.1.100', 3306, 200),  -- 写组（主库）
(2, '192.168.1.101', 3306, 200),  -- 读组（从库1）
(2, '192.168.1.102', 3306, 200);  -- 读组（从库2）

-- 配置监控用户
SET mysql-monitor_username='monitor';
SET mysql-monitor_password='MonitorPass123!';

-- 配置主从复制监控
INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment)
VALUES (1, 2, '主从读写分离');

-- 配置后端用户（应用程序连接ProxySQL使用的用户）
INSERT INTO mysql_users(username, password, default_hostgroup, active) VALUES
('app_user', 'AppPass123!', 1, 1);  -- 默认路由到写组

-- 读写分离规则
-- 规则1：SELECT FOR UPDATE路由到写组
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 1, 1);

-- 规则2：普通SELECT查询路由到读组
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 2, 1);

-- 规则3：写操作路由到写组
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (3, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)', 1, 1);

-- 规则4：事务内所有操作路由到写组
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (4, 1, '^BEGIN|COMMIT|ROLLBACK|SET autocommit', 1, 1);

-- 加载配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

-- 保存配置到磁盘
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

-- 验证配置
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
SELECT rule_id, match_digest, destination_hostgroup FROM runtime_mysql_query_rules;

ProxySQL的连接池功能可以显著降低数据库连接建立的开销。对于短连接和高并发的Web应用，连接池可以将数据库QPS提升30%以上。配置参数 mysql-connection_delay_multiplex_ms 控制连接复用的延迟，mysql-threshold_resultset_size 控制结果集缓存大小。

复制延迟排查与故障切换处理

生产环境中复制中断和延迟是高频问题。通过performance_schema的复制表可以精确诊断问题根因。GTID模式下复制中断后，从库会自动尝试重新连接主库并从断点继续，但仍需人工介入处理数据冲突。

-- 排查复制延迟原因

-- 1. 检查大事务
SELECT * FROM information_schema.innodb_trx
ORDER BY trx_started ASC LIMIT 5;

-- 2. 检查慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 3. 检查从库负载
SHOW PROCESSLIST;

-- 4. 检查网络延迟
SELECT * FROM performance_schema.replication_connection_status;

-- 5. 计算GTID延迟差距
SELECT 
    Retrieved_Gtid_Set AS io_gtid,
    Executed_Gtid_Set AS sql_gtid,
    TIMESTAMPDIFF(SECOND, 
        STR_TO_DATE(SUBSTRING_INDEX(LAST_QUEUED, ':', -1), '%s'),
        NOW()
    ) AS io_delay_seconds
FROM performance_schema.replication_connection_status;

-- 故障切换：主库宕机时提升从库为主库
-- 在新主库（原从库）上执行
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- 在ProxySQL中更新服务器配置
-- 将新主库加入写组，移除原主库
UPDATE mysql_servers SET hostgroup_id = 1 WHERE hostname = '192.168.1.101';
DELETE FROM mysql_servers WHERE hostname = '192.168.1.100';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- 其他从库指向新主库
-- 在各从库上执行
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.101',
    MASTER_PORT = 3306,
    MASTER_AUTO_POSITION = 1;
START SLAVE;

对于需要更高可用性的场景，可以引入MHA（Master High Availability）或Orchestrator实现自动故障切换。Orchestrator支持基于GTID的拓扑管理和自动恢复，配合ProxySQL的故障检测可以实现分钟级的自动切换。数据迁移实战中，GTID模式使得在线切换主库更加安全，应用层通过ProxySQL代理无需修改连接配置，只感知到短暂的写入暂停。