数据库单点太危险，手把手教你配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 TO
MASTER_HOST='主库IP',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
问题：切换主库时，需要手动计算新的binlog位置，容易出错。
GTID方式：全局事务ID（推荐）
CHANGE MASTER TO
MASTER_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 TO
MASTER_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.sh
MYSQL_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" ]; then
echo "警告：复制异常！"
# 发送告警...
fi
if [ "$delay" -gt 60 ]; then
echo "警告：延迟超过60秒！"
fi
异地备份方案
如果主从在同一机房，机房出问题数据就全没了。建议搞异地备份。
我的做法是用星空组网把异地的备份机组到一起，通过虚拟局域网直接做主从复制，不用开公网端口，延迟也可控。比搞专线便宜多了。
总结
| 阶段 | 关键点 |
|---|---|
| 配置 | server-id唯一、GTID开启、binlog格式ROW |
| 同步 | mysqldump带--set-gtid-purged=ON |
| 验证 | 两个Running都是Yes |
| 监控 | 关注Seconds_Behind_Master |
| 切换 | STOP SLAVE → RESET SLAVE ALL → 关只读 |
推荐配置优先级：
- 先跑通基础主从
- 开启并行复制减少延迟
- 配置监控告警
- 测试切换流程
浙公网安备 33010602011771号