1. MySQL主从配置：高可用架构的基石实践

上周刚帮一家电商平台解决了数据库单点故障引发的服务中断问题，核心方案就是MySQL主从复制。这种架构不仅能实现故障快速切换，还能分担主库压力，是每个DBA必须掌握的生存技能。今天我就用最直白的语言，把主从配置的完整流程和避坑要点拆解给你看。

主从复制的本质就像出版社和印刷厂的关系：主库（Master）是出版社，负责原创内容生产；从库（Slave）是各地印刷厂，实时同步主库数据。当主库宕机时，从库可以立即顶上，保证业务连续性。实际部署时，我们通常会让从库承担报表查询、备份等读密集型任务，主库专注处理写操作。

2. 环境准备与基础配置

2.1 服务器规划建议

生产环境我推荐采用至少三台服务器：

• 主库：16核32G内存 + SSD（写密集型）
• 从库1：8核16G内存 + SSD（读负载均衡）
• 从库2：8核16G内存 + HDD（备份专用）

测试环境可以用单机多实例模拟，通过不同端口区分：

# 主库实例 mysqld --port=3306 --server-id=1 # 从库实例 mysqld --port=3307 --server-id=2

2.2 配置文件关键参数

主库my.cnf必须包含：

[mysqld] server-id = 1 log_bin = mysql-bin binlog_format = ROW binlog_row_image = FULL sync_binlog = 1

从库配置差异点：

server-id = 2 # 必须全局唯一 relay_log = mysql-relay-bin read_only = ON # 防止从库误写入

警告：server-id配置错误会导致复制链路无法建立，曾经有团队因为所有机器都用默认值1，导致数据混乱。

3. 主从建立全流程实操

3.1 主库用户权限配置

创建专用复制账号（别用root！）：

CREATE USER 'repl'@'%' IDENTIFIED BY 'S7r0ngP@ss'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES;

3.2 数据一致性保障方案

方案A：全新环境

1. 主库执行FLUSH TABLES WITH READ LOCK
2. 记录二进制位置SHOW MASTER STATUS
3. 导出数据mysqldump -uroot -p --all-databases > full.sql
4. 解锁主库UNLOCK TABLES

方案B：在线热迁移使用Percona XtraBackup工具：

innobackupex --user=root --password=xxx --slave-info /backup/

3.3 从库初始化关键命令

CHANGE MASTER TO MASTER_HOST='主库IP', MASTER_USER='repl', MASTER_PASSWORD='S7r0ngP@ss', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; START SLAVE;

验证复制状态：

SHOW SLAVE STATUS\G

重点关注：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0

4. 生产环境进阶调优

4.1 复制过滤规则

避免同步不必要的库：

# 主库配置 binlog-do-db = important_db binlog-ignore-db = test # 从库配置 replicate-wild-do-table = important_db.% replicate-ignore-db = temp_%

4.2 延迟监控方案

配置心跳表检测延迟：

CREATE TABLE heartbeat ( id INT PRIMARY KEY, ts DATETIME NOT NULL ); -- 主库定时任务 INSERT INTO heartbeat VALUES (1, NOW()) ON DUPLICATE KEY UPDATE ts=NOW(); -- 从库查询延迟 SELECT TIMESTAMPDIFF(SECOND, ts, NOW()) AS delay FROM heartbeat WHERE id=1;

4.3 半同步复制配置

主库安装插件：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled=1; SET GLOBAL rpl_semi_sync_master_timeout=10000; # 10秒超时

从库配置：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled=1;

5. 故障排查手册

5.1 常见错误代码处理

5.2 复制中断恢复流程

1. 临时跳过错误（慎用！）：

STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE;

2. 数据一致性修复：

pt-table-checksum --replicate=test.checksums pt-table-sync --replicate=test.checksums h=主库IP --execute

5.3 监控指标阈值建议

• 延迟时间：>30秒触发告警
• IO线程状态：非Yes立即排查
• SQL线程错误：记录错误日志
• 网络流量：突发下降需检查

6. 主从切换演练方案

6.1 计划内切换步骤

1. 主库设置只读：

SET GLOBAL read_only=ON; FLUSH TABLES WITH READ LOCK;

2. 从库提升为主：

STOP SLAVE; RESET MASTER; SET GLOBAL read_only=OFF;

3. 应用修改连接串

6.2 故障自动切换设计

使用Orchestrator工具实现：

# 配置示例 Detection: ReplicationLagQuery: "SELECT ABS(Seconds_Behind_Master) FROM SHOW SLAVE STATUS" FailMasterPromotionOnLagMinutes: 5

7. 性能压测数据参考

在AWS c5.2xlarge机型测试结果：

8. 版本兼容性备忘录

• MySQL 5.7到8.0的主从复制需要设置：

slave_type_conversions=ALL_NON_LOSSY

• GTID模式启用条件：

gtid_mode=ON enforce_gtid_consistency=ON

9. 安全加固要点

1. 复制账号限制IP：

ALTER USER 'repl'@'%' REQUIRE SSL;

2. 加密复制通道：

master_ssl=1 ssl_ca=/path/to/ca.pem

3. 定期轮换密码：

ALTER USER 'repl'@'%' IDENTIFIED BY 'NewP@ss2023';

10. 架构演进路线

1. 单主单从 → 链式复制
2. 传统复制 → GTID复制
3. 异步复制 → 半同步 → 组复制
4. 主从架构 → 分片集群

最近在金融项目中使用MySQL Group Replication时发现，当网络抖动超过5秒就会触发节点驱逐。建议在云环境部署时，把group_replication_member_expel_timeout调大到30秒以上。