MySQL 5.7+ 主从同步配置与故障排查指南

结果与目标

通过合理配置 MySQL 的主从环境，实现数据的实时读写分离与灾备功能。确保 Master 节点的事务变更能被 Slave 节点按顺序应用，同时维持系统的稳定性。

准备条件

1. 基础信息：
   • Master 节点：假设 IP 为 10.1.123.197
   • Slave 节点：假设 IP 为 10.1.123.160
2. 配置前提：确保两台服务器间防火墙已放行 3306 端口；关闭 SELinux 或使用相应策略允许跨主机访问。
3. 时间同步：强烈建议在多台服务器上部署 NTP 服务，以保证事务执行的时间戳一致，避免由于时钟漂移导致的异常。

第1步：配置 Master（主库）

主库的核心任务是通过二进制日志（Binlog）记录所有对数据库有写操作的 SQL。修改配置文件 my.cnf。

# 在 [mysqld] 下新增以下内容
server-id = 1                      # 服务器唯一标识符
log-bin=mysql-bin                  # 开启 Binlog，并指定文件名前缀
log-bin-index=mysql-bin.index      # 指定 binlog index 文件
binlog_format=mixed                # 推荐设置为 mixed，结合语句与行模式
sync_binlog=1                      # 每次事务提交时同步到磁盘，防止断电丢失日志
max_allowed_packet = 32M           # 调整最大包大小，防止大字段写入失败

注意：根据业务中单条数据包的大小，适当调整 max_allowed_packet，例如可设为 100M。

第2步：配置 Slave（从库）

从库主要用于读取主库传过来的 Binlog，并通过 SQL 线程重放。同样修改 my.cnf。

# 在 [mysqld] 下新增以下内容
server-id=2                        # 必须与 Master 不同
relay-log-index = slave-relay-bin.index  
relay-log = slave-relay-bin        # 开启 Relay-Log
sync_master_info = 1               # 确保 Master Info 安全落盘
sync_relay_log = 1                 # 确保 Relay 日志安全落盘
sync_relay_log_info = 1

说明：以上关于 sync 参数的配置在旧版本（如 5.5）尤为关键。对于较新的 MySQL 8.0+，可通过 GTID（全局事务标识符）机制进一步简化配置。需明确是否需要严格过滤特定数据库：binlog-do-db（仅同步指定的库）和 binlog-ignore-db（忽略不需要同步的库），默认全部同步。

第3步：重启并配置复制连接

修改配置后，需要重启服务使参数生效：

service mysql restart

进入 MySQL CLI，在主库上创建一个专门用于同步的用户：

-- 创建用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password123'; -- 建议更改为更安全的密码
-- 授权
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

真实操作顺序

第4步：初始化 Slave 并建立链路

这一步的目的是告诉 Slave 它需要从哪个 Master 位置开始接收数据。

CHANGE MASTER TO 
  MASTER_HOST='10.1.123.197',
  MASTER_USER='repl',
  MASTER_PASSWORD='password123',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=30; -- 重试间隔(秒)
START SLAVE;

完成判断

在 Slave 节点执行以下命令进行检查：

SHOW SLAVE STATUS\G

关注两个关键字段：

• Slave_IO_Running: 应为 Yes。表示从库已连接到主库并拉取日志。
• Slave_SQL_Running: 应为 Yes。表示从库正在正常回放日志。

原文有依据的排错

当 Slave 无法启动时，通常是因为 IO 状态异常或者 SQL 状态报错。

• 手动指定起始点：

  stop slave;
  change master to master_log_file='mysql-bin.000001', master_log_pos=107;
  start slave;

  提示：上述 log 文件和位置信息可通过在 Master 执行 show master status\G 获得。若未使用 GTID，这种方式最为稳妥。
• 全量快照法（业务不可停机场景）： 如果需要保证数据绝对一致性且允许短暂锁表：

  flush tables with read lock;
  -- 保持这个锁窗口期，获取当前 File/Position，然后备份数据
  unlock tables;

• 无锁导出法（适用于大型只读库）：

  mysqldump --master-data=2 --single-transaction db_name > backup.sql

  --master-data=2 会在导出的 dump 文件中自动添加注释形式的 CHANGE MASTER 语句，导入后直接 start slave 即可对接。

简要总结

以上就是 MySQL 主从同步配置与排错的详细内容，更多关于 MySQL 运维优化的资料请关注本站其它相关文章！