数据库单点太危险，手把手教你配MySQL主从

公司数据库是单点，老板说要搞高可用。研究了一番MySQL主从复制，踩了不少坑，记录一下。

为什么要主从复制

• 高可用：主库挂了，从库顶上
• 读写分离：读请求分到从库，减轻主库压力
• 数据备份：从库备份不影响主库性能

主从复制原理

主库                              从库|                                 || 1. 写入binlog                   ||                                 || 2. dump线程发送binlog  -------> ||                                 | 3. I/O线程接收,写入relay log|                                 ||                                 | 4. SQL线程执行relay log|                                 |

三个关键组件：

• 主库binlog：记录所有数据变更
• 从库I/O线程：拉取主库binlog
• 从库SQL线程：重放binlog

GTID vs 传统复制

传统方式：基于binlog文件名+位置

CHANGE MASTER TOMASTER_HOST='主库IP',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

问题：切换主库时，需要手动计算新的binlog位置，容易出错。

GTID方式：全局事务ID（推荐）

CHANGE MASTER TOMASTER_HOST='主库IP',MASTER_AUTO_POSITION=1;

每个事务有唯一ID，从库自动找位置，主从切换更简单。

实战：配置GTID主从复制

环境

• 主库：192.168.1.10
• 从库：192.168.1.11
• MySQL版本：8.0.35（5.7也适用）

Step 1：主库配置

编辑 /etc/my.cnf：

[mysqld]
# 服务器ID，集群内唯一
server-id=10# 开启binlog
log-bin=mysql-bin
binlog_format=ROW# GTID配置
gtid_mode=ON
enforce_gtid_consistency=ON# binlog保留天数
expire_logs_days=7# 同步配置（可选，提高数据安全性）
sync_binlog=1
innodb_flush_log_at_trx_commit=1

重启MySQL：

systemctl restart mysqld

Step 2：主库创建复制账号

-- 创建复制专用账号
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;-- 查看主库状态
SHOW MASTER STATUS;
-- 记录File和Position，传统模式需要用（GTID模式不需要）

Step 3：从库配置

编辑 /etc/my.cnf：

[mysqld]
# 服务器ID，和主库不同
server-id=11# 开启binlog（建议开启，方便后续做级联复制）
log-bin=mysql-bin
binlog_format=ROW# GTID配置
gtid_mode=ON
enforce_gtid_consistency=ON# relay log配置
relay-log=relay-bin
relay_log_purge=ON# 从库只读（应用层也要做好控制）
read_only=ON
super_read_only=ON

重启MySQL：

systemctl restart mysqld

Step 4：主库数据导入从库

如果主库已有数据，需要先同步：

# 主库导出（带GTID信息）
mysqldump -uroot -p --all-databases --single-transaction \--routines --triggers --set-gtid-purged=ON > full_backup.sql# 传输到从库
scp full_backup.sql 192.168.1.11:/tmp/# 从库导入
mysql -uroot -p < /tmp/full_backup.sql

Step 5：从库配置主从关系

-- 配置主库连接信息
CHANGE MASTER TOMASTER_HOST='192.168.1.10',MASTER_USER='repl',MASTER_PASSWORD='Repl@123456',MASTER_AUTO_POSITION=1;-- 启动复制
START SLAVE;-- 查看复制状态
SHOW SLAVE STATUS\G

Step 6：验证复制状态

SHOW SLAVE STATUS\G

关键指标：

Slave_IO_Running: Yes       # I/O线程正常
Slave_SQL_Running: Yes      # SQL线程正常
Seconds_Behind_Master: 0    # 延迟秒数

两个Running都是Yes，延迟为0，配置成功。

常见故障排查

1. Slave_IO_Running: No

原因：I/O线程无法连接主库

排查：

-- 查看具体错误
SHOW SLAVE STATUS\G
-- 看 Last_IO_Error 字段-- 常见原因：
-- 1. 网络不通
ping 主库IP-- 2. 账号密码错误
mysql -h主库IP -urepl -p-- 3. 防火墙拦截
telnet 主库IP 3306

2. Slave_SQL_Running: No

原因：SQL线程执行失败，通常是数据冲突

排查：

SHOW SLAVE STATUS\G
-- 看 Last_SQL_Error 字段-- 常见错误：主键冲突、表不存在等

处理方式一：跳过错误（谨慎使用）

-- 传统模式：跳过1个事务
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;-- GTID模式：跳过指定事务
STOP SLAVE;
SET GTID_NEXT='主库UUID:事务号';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

处理方式二：重新同步（更安全）

数据冲突多时，建议重新全量同步。

3. 主从延迟大

排查：

-- 查看延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 延迟秒数-- 查看当前执行的SQL
SHOW PROCESSLIST;-- 查看从库配置
SHOW VARIABLES LIKE '%slave%';

优化方案：

# 从库并行复制（MySQL 5.7+）
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8# MySQL 8.0
replica_parallel_type=LOGICAL_CLOCK
replica_parallel_workers=8

4. GTID事务被跳过警告

-- 查看主从GTID差异
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';-- 主库执行
SELECT * FROM mysql.gtid_executed;

主从切换流程

主库故障后，提升从库为主库：

Step 1：确认从库数据已同步

-- 从库执行
SHOW SLAVE STATUS\G
-- 确认 Seconds_Behind_Master: 0

Step 2：停止从库复制

STOP SLAVE;
RESET SLAVE ALL;

Step 3：关闭只读

SET GLOBAL read_only=OFF;
SET GLOBAL super_read_only=OFF;

Step 4：应用切换连接

修改应用配置，连接新主库。

监控脚本

#!/bin/bash
# mysql_repl_check.shMYSQL_USER="monitor"
MYSQL_PASS="xxx"result=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null)io_running=$(echo "$result" | grep "Slave_IO_Running:" | awk '{print $2}')
sql_running=$(echo "$result" | grep "Slave_SQL_Running:" | awk '{print $2}')
delay=$(echo "$result" | grep "Seconds_Behind_Master:" | awk '{print $2}')echo "IO线程: $io_running"
echo "SQL线程: $sql_running"
echo "延迟: ${delay}秒"if [ "$io_running" != "Yes" ] || [ "$sql_running" != "Yes" ]; thenecho "警告：复制异常！"# 发送告警...
fiif [ "$delay" -gt 60 ]; thenecho "警告：延迟超过60秒！"
fi

异地备份方案

如果主从在同一机房，机房出问题数据就全没了。建议搞异地备份。

我的做法是用星空组网把异地的备份机组到一起，通过虚拟局域网直接做主从复制，不用开公网端口，延迟也可控。比搞专线便宜多了。

总结

阶段	关键点
配置	server-id唯一、GTID开启、binlog格式ROW
同步	mysqldump带--set-gtid-purged=ON
验证	两个Running都是Yes
监控	关注Seconds_Behind_Master
切换	STOP SLAVE → RESET SLAVE ALL → 关只读

推荐配置优先级：

1. 先跑通基础主从
2. 开启并行复制减少延迟
3. 配置监控告警
4. 测试切换流程