MySQL主从复制与读写分离实战：GTID复制配置与ProxySQL路由分发

MySQL主从复制是数据库高可用和读写分离的基础架构。通过将主库的变更同步到从库，读请求分散到多个从库执行，主库专注写入，显著提升系统吞吐量。GTID（Global Transaction Identifier）复制相比传统基于binlog位置的复制，提供全局唯一事务标识，简化复制拓扑管理并避免位置偏移问题。

GTID复制原理与主库配置

GTID为每个事务分配全局唯一ID，格式为server_uuid:transaction_id。从库通过GTID自动定位同步位置，无需手动指定binlog文件名和位置。主库配置：

# /etc/my.cnf (Master)
[mysqld]
server-id = 1
gtid_mode = ON
enforce-gtid-consistency = ON
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
log_slave_updates = ON
expire_logs_days = 7

enforce-gtid-consistency强制GTID一致性，禁止执行不支持GTID的语句（如CREATE TABLE … SELECT）。log_slave_updates让从库也记录binlog，支持级联复制。binlog_row_image = MINIMAL只记录变更列，减少binlog体积。

创建复制用户：

CREATE USER 'repl'@'10.0.2.%' IDENTIFIED BY 'Repl@2026!Secure';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.2.%';
FLUSH PRIVILEGES;

从库配置与复制启动

从库配置GTID模式并指向主库：

# /etc/my.cnf (Slave)
[mysqld]
server-id = 2
gtid_mode = ON
enforce-gtid-consistency = ON
log-bin = mysql-bin
log_slave_updates = ON
relay-log = relay-bin
read_only = ON
super_read_only = ON
replicate_wild_do_table = mydb.%
replicate_wild_ignore_table = mysql.%,information_schema.%,performance_schema.%

read_only阻止普通用户写入，super_read_only阻止SUPER权限用户写入，确保从库数据与主库一致。replicate_wild_do_table限制只复制指定库，减少不必要的数据传输。

启动GTID复制：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.1.11',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'Repl@2026!Secure',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;
SHOW REPLICA STATUS\G

SOURCE_AUTO_POSITION = 1启用GTID自动定位，从库根据已执行的GTID集合自动从主库拉取缺失事务。GET_SOURCE_PUBLIC_KEY用于MySQL 8.0+的caching_sha2_password认证插件。

验证复制状态关键指标：

SHOW REPLICA STATUS\G

-- 关键字段：
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Master: 0
-- Retrieved_Gtid_Set: 3a3c..01:1-500
-- Executed_Gtid_Set: 3a3c..01:1-500
-- Last_IO_Error: (空)
-- Last_SQL_Error: (空)

复制延迟排查与优化

Seconds_Behind_Master反映从库延迟秒数，但该指标存在缺陷——当IO线程断开时显示NULL而非真实延迟。更准确的方式对比GTID集合：

-- 主库已执行GTID
SHOW MASTER STATUS\G  -- Executed_Gtid_Set

-- 从库已执行GTID
SHOW REPLICA STATUS\G  -- Executed_Gtid_Set

-- 差值即为未同步的事务数

常见延迟原因及优化方向：

大事务：单个事务包含大量DML操作，从库单线程执行该事务期间无法并行。MySQL 8.0的基于WRITESET的并行复制可缓解：

# 从库配置
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
binlog_transaction_dependency_tracking = WRITESET
replica_preserve_commit_order = ON

LOGICAL_CLOCK模式允许同一组中无冲突的事务并行执行。WRITESET依赖追踪替代默认的COMMIT_ORDER，对修改不同行的并行事务识别更精确。

从库硬件不足：从库CPU/IO性能低于主库时复制跟不上。监控从库的Com_insert/update/delete速率与主库对比。

无主键表：ROW格式binlog下，从库执行UPDATE/DELETE需要通过主键定位行。无主键表导致全表扫描，性能急剧下降。排查无主键表：

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES T
WHERE NOT EXISTS (
  SELECT 1 FROM information_schema.STATISTICS S
  WHERE S.TABLE_SCHEMA = T.TABLE_SCHEMA
    AND S.TABLE_NAME = T.TABLE_NAME
    AND S.INDEX_NAME = 'PRIMARY'
)
AND T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

ProxySQL读写分离配置

ProxySQL作为SQL代理层，根据SQL类型自动路由到主库或从库，应用层无需感知主从拓扑。安装后通过Admin接口配置：

-- 配置MySQL后端节点
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)
VALUES
  (1, '10.0.1.11', 3306, 1000, 200),  -- 写组: 主库
  (2, '10.0.2.11', 3306, 500, 200),   -- 读组: 从库1
  (2, '10.0.2.12', 3306, 500, 200);   -- 读组: 从库2

-- 配置路由规则
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
  (1, 1, '^SELECT.*FOR UPDATE', 1, 1),  -- SELECT FOR UPDATE走主库
  (2, 1, '^SELECT', 2, 1),               -- 普通SELECT走从库
  (3, 1, '^(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP)', 1, 1);  -- 写操作走主库

-- 配置复制监控用户
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent)
VALUES ('app_user', 'App@2026!', 1, 1);

-- 加载配置到运行时并持久化
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL USERS TO DISK;

transaction_persistent = 1确保同一事务内的所有语句路由到同一后端，避免事务跨库导致数据不一致。

应用连接ProxySQL的6033端口，ProxySQL自动处理路由分发。连接字符串无需区分读写地址：

# 应用配置
spring.datasource.url=jdbc:mysql://proxysql:6033/mydb
spring.datasource.username=app_user
spring.datasource.password=App@2026!

ProxySQL健康检查与故障转移

ProxySQL内置MySQL Galera和Group Replication的健康检查。对于普通主从复制，需要配置mysql_galera_hostgroups或使用scheduler脚本实现故障检测。一个简单的健康检查脚本：

-- 在ProxySQL中配置监控
SET mysql-monitor_enabled = 'true';
SET mysql-monitor_connect_interval = '1000';
SET mysql-monitor_ping_interval = '1000';
SET mysql-monitor_read_only_interval = '1000';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

mysql-monitor_read_only_max_timeout_count控制连续多少次检查失败后将节点标记为OFFLINE。当从库read_only变为ON时ProxySQL自动将其从读组移除，恢复后自动加回。

查看监控状态：

SELECT * FROM mysql_server_connect_log ORDER BY time_start DESC LIMIT 10;
SELECT * FROM mysql_server_ping_log ORDER BY time_start DESC LIMIT 10;
SELECT * FROM mysql_server_read_only_log ORDER BY time_start DESC LIMIT 10;

这些日志表记录每个后端节点的连接、Ping和只读状态变化历史，用于排查网络抖动或节点故障导致的读写分离异常。