MySQL 主从复制配置实战：读写分离与故障切换 - 财富云计算

14 10 月 2026

单台数据库有三个天花板：读性能（查询全压一台）、可用性（挂了全站停）、备份影响（大库备份拖垮业务）。主从复制一次解决这三个问题。

这篇讲 MySQL 主从的完整配置：一主一从起步、数据同步原理、故障切换、以及最常见的坑。

需要云服务器可以看看财富云计算：NAT 型无独立公网 IP，价格比独立 IP 机型省一半以上，一键开服面板，主流游戏快速部署。

一、架构与原理

写入 → 主库(Master) ──binlog──→ 从库(Slave) ──→ 读查询分流
         ↑                          ↑
      所有写操作               只读(报表/备份/读分担)

同步三步骤：

主库把数据变更写进 binlog（二进制日志）
从库的 I/O 线程拉取主库 binlog，存到本地 relay log
从库的 SQL 线程重放 relay log，应用到自己的数据

从库是异步的——主库提交成功不代表从库已同步。延迟通常毫秒级，大事务时可能到秒级。

二、环境准备

两台机器（或同机不同实例测试）：

角色	IP	数据目录
主库	192.168.1.11	/var/lib/mysql
从库	192.168.1.12	/var/lib/mysql

# 两台都装 MySQL 8.0
apt install -y mysql-server

# 版本必须一致(或从库 >= 主库版本)
mysql --version

三、主库配置

# /etc/mysql/my.cnf(或 /etc/mysql/mysql.conf.d/mysqld.cnf)
[mysqld]
server-id = 1                       # 唯一,不能重复
log-bin = /var/log/mysql/mysql-bin  # 开 binlog(复制的前提)
binlog_format = ROW                 # 行模式,数据最安全(推荐)
binlog_expire_logs_seconds = 604800 # binlog 保留 7 天
sync_binlog = 1                     # 每次提交都刷盘(最安全,稍慢)
# 性能优先可设 0,但崩溃可能丢数据

# 跳过 DNS 反查
skip-name-resolve

systemctl restart mysql

创建复制账号

-- 在主库执行
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY '复制密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 只授给从库网段,别用 '%'

记录同步起点

-- 锁表(短时间阻塞写入,确保数据一致)
FLUSH TABLES WITH READ LOCK;

-- 记下这两个值(从库要用)
SHOW MASTER STATUS;
-- 输出: File: mysql-bin.000003, Position: 154, ...

-- 另开终端: 导出初始数据(全量)
mysqldump --all-databases --single-transaction --master-data=2 \
  --routines --triggers > /tmp/master-data.sql
-- --master-data=2 会把 CHANGE MASTER TO 注释掉写进文件,里面有确切的坐标

-- 回到 SQL 终端,解锁
UNLOCK TABLES;

如果主库已有数据：必须先做这个全量导出，从库导入后才能开始增量同步。空库可以直接跳过。

四、从库配置

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 2                       # 和主库不同
log-bin = /var/log/mysql/mysql-bin  # 从库也开 binlog(级联复制/切换用)
relay-log = /var/log/mysql/relay-bin
read_only = 1                       # 从库只读(防误写导致数据分歧)
super_read_only = 1                 # 连 root 都不能写(MySQL 5.7+)

# 跳过 DNS
skip-name-resolve

systemctl restart mysql

导入初始数据（已有数据的主库）

# 从库: 先停掉可能写库的服务,导入
mysql < /tmp/master-data.sql

配置复制链路

-- 从库执行,坐标用主库 SHOW MASTER STATUS 的值
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.11',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = '复制密码',
  MASTER_LOG_FILE = 'mysql-bin.000003',   -- 主库的 File
  MASTER_LOG_POS = 154;                   -- 主库的 Position

-- 启动复制
START SLAVE;

-- 查看状态
SHOW SLAVE STATUS\G

必须确认的两行：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两个都是 Yes，复制正常工作。任何一个 No，看下面的 Last_IO_Error / Last_SQL_Error。

验证同步

-- 主库: 建库建表插数据
CREATE DATABASE test_repl;
CREATE TABLE test_repl.t (id INT PRIMARY KEY, v VARCHAR(50));
INSERT INTO test_repl.t VALUES (1, 'hello');

-- 从库: 查一下(几秒内应该出现)
SELECT * FROM test_repl.t;

从库能查到，复制链路通了。

五、常用运维操作

看同步状态

-- 从库
SHOW SLAVE STATUS\G

关键字段：

字段	含义	关注点
`Slave_IO_Running`	拉取线程	必须 Yes
`Slave_SQL_Running`	重放线程	必须 Yes
`Seconds_Behind_Master`	同步延迟（秒）	持续增长 = 有问题
`Last_Error`	最近的错误	非空要看
`Master_Log_File / Read_Master_Log_Pos`	已拉取的位置	—

处理同步中断

-- 常见: 主库执行了从库不该有的操作、或大事务超时
SHOW SLAVE STATUS\G    -- 看 Last_SQL_Error

-- 跳过出错的单条语句(确认可跳过时!)
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 数据冲突(从库被误写过): 按错误修数据,再 START

主从切换（主库挂了）

手动切换流程：

# 1. 从库确保同步追平(Seconds_Behind_Master = 0)
# 2. 从库停止复制、提升为主
STOP SLAVE;
RESET SLAVE ALL;          -- 清复制配置
SET GLOBAL read_only = 0; -- 开写

# 3. 应用改指向新主库
# 4. (可选)旧主库恢复后作为新从库挂上来

生产建议：手动切换慢且易错。要么用 MHA/Orchestrator 自动故障转移，要么云数据库（RDS）自带高可用。本站内容规模，手动切换 + 监控告警够用。

备份从从库拿

# 从库备份不影响主库业务!
mysqldump -h 192.168.1.12 -u backup -p --single-transaction \
  --all-databases | gzip > /backup/mysql-$(date +%F).sql.gz

这是主从最实在的收益之一：备份不再拖垮主库。

六、读写分离：让查询走从库

应用层配置（示例）：

// PHP: 写走主,读走从
$write = new PDO('mysql:host=192.168.1.11;dbname=app', 'app', 'pass');
$read  = new PDO('mysql:host=192.168.1.12;dbname=app', 'app', 'pass');

// INSERT/UPDATE/DELETE 用 $write
// SELECT 用 $read

注意：写入后立刻读（如注册后查用户信息），必须走主库——从库有延迟，读不到刚写的。刚需场景强制读主，普通查询读从。

七、常见问题

Q：Seconds_Behind_Master 一直是 NULL？
复制没正常启动（I/O 线程挂了），或者时钟不同步。先确认两个 Running 都是 Yes，再同步两台机器时间（NTP）。

Q：延迟越来越大？
大事务（一次更新百万行）、从库硬件差、从库还在被拿去跑重查询。定位：看主库 binlog 里的大事务，从库 SHOW PROCESSLIST 看有没有慢查询堵着重放。

Q：从库数据被误改了？
从库开了 super_read_only 就不会有这问题（root 也写不了）。已经改了导致同步中断：停应用，修数据或重建从库（重新全量 + 挂复制）。

Q：主库 binlog 被清了，从库拉不到？
MASTER_LOG_FILE 指向的 binlog 不存在了。重建从库：重新全量导出 + 配置新坐标。

Q：一主两从怎么配？
多个从库各自 CHANGE MASTER TO 指向同一主库即可。server-id 各自唯一。

Q：NAT 型服务器之间能做主从吗？
可以，但延迟和稳定性看服务商网络。NAT 机型没有内网互通的话，复制流量要走公网——加密（SSL 复制）+ 专网或 VPN 更好。同机房同服务商的内网产品最适合做主从。

八、配置清单（速查）

# 主库 my.cnf 必备
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

# 从库 my.cnf 必备
server-id = 2
relay-log = relay-bin
read_only = 1
super_read_only = 1

# 主库 SQL
CREATE USER 'repl'@'从库网段' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库网段';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;      -- 记下 File 和 Position
-- (导出全量数据)
UNLOCK TABLES;

# 从库 SQL
-- (导入全量数据)
CHANGE MASTER TO MASTER_HOST='主库IP', MASTER_USER='repl',
  MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G      -- 确认两个 Running: Yes

小结

主从复制是数据库架构升级的第一步：读分担、备份不影响业务、故障切换有基础。配置本身半小时能跑通，难点在运维：延迟监控、同步中断处理、切换演练。建议：从库必开 super_read_only、备份走从库、每季度做一次切换演练——真出事时，演练过的和没演练的，恢复速度差十倍。

更多数据库内容见财富云计算知识库：MySQL 优化、备份恢复、Redis 集群、数据库安全全系列。需要云服务器可以看看财富云计算：一键开服面板，NAT 型无独立公网 IP，价格比独立 IP 机型省一半以上。

取消回复

Redis 哨兵与集群模式：高可用部署实战 - 财富云计算 - 维基百科

[...]主库挂了 → 哨兵投票 → 从库提升为主 → 应用切换1. 部署主从（复用上篇）先按MySQL 主从的思路……不对，Redis 的从库配置很简单：# 三台机器(或同机不同端口测试)[...]

2026-10-14 回复
网站数据库迁移与换机实战：mysqldump 五步零丢数据 - 财富云计算 - 维基百科

[...]SHOW SLAVE STATUS\G # 看 Seconds_Behind_Master 追平追平后：旧站停写 → 等从库同步到 0 → 新库提主 → 应用切连接。停机秒级。主从完整搭建见MySQL 主从复制配置实战。四、程序实操：WordPress 与 TypechoWordPress# 数据库迁移后,还要改 wp_options 里的站点地址(换域名时)[...]

2026-10-15 回复
Steam游戏开服端口速查表：AppID、端口协议、配置需求一表查完（含Windows/Linux双系统索引） - 财富云计算 - 维基百科

[...]netsh advfirewall firewall add rule name="GameRange" dir=in action=allow protocol=UDP localport=26901-26903完整的 Windows 环境准备（远程桌面连接、运行库安装、SteamCMD 用法、开机自启、8 个常见坑）见：《Windows 云服务器开服通用前置教程》→ htt[...]

2026-10-18 回复