MySQL 主从复制读写分离实战：个人站也能扛住的数据库架构升级

  1.  Home
  2. Text  
    3. Share to：

个人站长需要主从复制吗

先泼盆冷水：如果你的站日 PV 不到一万，单机 MySQL 加个索引、加个缓存就够了，主从复制是过度设计。但下面两种情况，主从会立刻变成刚需：

  • 备份不敢做：mysqldump 一把下去，锁表 + 磁盘 IO 飙满，前台直接卡住。有了从库，备份在从库上跑，主库毫无感知。
  • 读请求把主库拖垮：所有查询都压在一台机器上，加机器也只能加 CPU，而 MySQL 单实例的写是瓶颈、读其实可以横向扩。

本文讲清楚三件事：怎么搭一主一从、怎么让程序读到从库、以及最容易踩的坑（主从延迟导致的"发完文章看不见"）。

一、搭建一主一从（MySQL 8.0）

假设主库 10.0.0.1，从库 10.0.0.2，两机都装好 MySQL 8.0 且能互通 3306。

第一步：主库开启 binlog

编辑 /etc/mysql/mysql.conf.d/mysqld.cnf：

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800   # binlog 保留 7 天
gtid_mode = ON
enforce_gtid_consistency = ON
max_binlog_size = 256M

重点说明：binlog_format = ROW 是当前唯一推荐的格式。老教程里的 STATEMENT 遇到 NOW()、UUID()、随机函数会造成主从数据不一致，务必别用。启用 GTID 之后，主从切换、重建从库都会简单很多。

重启主库并确认：

systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'gtid_mode';"   # 应为 ON
mysql -e "SHOW VARIABLES LIKE 'log_bin';"     # 应为 ON

第二步：主库建复制账号

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '换成强密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

注意 'repl'@'10.0.0.%' 限定内网段，不要写 '%' 让复制账号裸奔。复制账号只需要 REPLICATION SLAVE 权限，不要图省事给 ALL。

第三步：主库做一次全量备份并传到从库

# 主库上（--single-transaction 保证不锁表，--master-data 记录位点）
mysqldump -uroot -p --single-transaction --master-data=2 \
  --routines --triggers --events --all-databases > /tmp/full.sql

scp /tmp/full.sql root@10.0.0.2:/tmp/

--single-transaction 对 InnoDB 有效，能在不锁表的前提下拿到一致性快照，这是生产环境备份的标准姿势。

第四步：从库配置并启动复制

先改从库 my.cnf：

[mysqld]
server-id = 2
relay-log = /var/log/mysql/relay-bin
read_only = ON
super_read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON

read_only 让从库拒绝普通写入，super_read_only 连 super 用户也拒绝。这两项能在你手滑把程序连到从库上执行 INSERT 时救你一命。

重启从库、恢复数据、启动复制：

systemctl restart mysql
mysql < /tmp/full.sql

mysql -e "CHANGE MASTER TO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='强密码',
  MASTER_AUTO_POSITION=1;
START SLAVE;"

# 检查
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"

关键指标：Slave_IO_Running 和 Slave_SQL_Running 都必须是 Yes，Seconds_Behind_Master 应该很快收敛到 0。任何一个是 No，往下看第五节排错。

二、程序侧如何"读从库、写主库"

复制搭好后，最危险的就是"以为搭完就自动读从库了"。MySQL 本身不会自动读写分离，必须由程序或中间件决定。三种主流做法：

做法一：应用层双连接（推荐个人站）

在配置里定义两个连接，读操作走从库：

// 写连接（主库）
$writeDb = new PDO('mysql:host=10.0.0.1;dbname=blog', $u, $p);

// 读连接（从库）
$readDb = new PDO('mysql:host=10.0.0.2;dbname=blog', $u, $p);

// 查列表走从库
$stmt = $readDb->query('SELECT * FROM contents LIMIT 10');

// 发文章走主库
$writeDb->prepare('INSERT INTO contents ...')->execute();

优点是完全可控、零额外组件；缺点是每个查询都要手工判断，代码容易写乱。

做法二：一主多从的中间件

ProxySQL 或 MySQL Router 能透明地做读写分离，程序只连中间件，由它按 SQL 类型分流。代价是多一个组件要运维。如果你只有一主一从，我不建议引入，收益不划算。

做法三：只把"重读"挪到从库

这是个人站最务实的一种：只在真正重的地方用从库，比如后台报表统计、全站文章导出、sitemap 生成、备份脚本。这些操作对实时性要求低，读从库毫无风险，还能顺便把主库的压力挪走。改造成本几乎为零，收益却立竿见影。

三、必须解决的核心问题：主从延迟

假设你在应用里做了读写分离，用户发了新文章（写主库），紧接着跳转到文章列表（读从库）。如果从库还没同步到这条记录，用户就会看到"我明明发了，怎么没有？"——这是读写分离 90% 的线上投诉来源。

解决方案有三个层次：

1. 识别"写后立即读"的路径

发布、编辑、删除、评论提交这些操作之后紧接着的那个读请求，强制走主库。这是最有效也是最简单的办法。用 session 打个标记即可：

// 刚写完，标记接下来 3 秒内该用户的读请求都走主库
$_SESSION['force_master_until'] = time() + 3;

function getDb() {
    if (!empty($_SESSION['force_master_until']) && time() < $_SESSION['force_master_until']) {
        return $writeDb;   // 强制主库
    }
    return $readDb;
}

2. 监控延迟，超阈值直接切主库

定时读 SHOW SLAVE STATUS 的 Seconds_Behind_Master，大于 1 秒就自动把所有读都切回主库，延迟恢复后再切回来。别小看这个，它能在从库卡住的凌晨自动兜底，比人肉发现快得多。

3. 从根上减少延迟

  • 从库硬件至少别比主库差一个数量级。主库用 NVMe、从库用机械盘，延迟是必然的。
  • 从库上不要跑重查询。备份脚本、统计 SQL 在从库上是双刃剑，尤其是 SELECT ... FOR UPDATE 或大事务，会把 SQL 线程堵住。
  • 控制大事务。一次 DELETE 删 100 万行，主库瞬间执行完，从库要单线程重放很久。务必分批删，每批几千行加个 select sleep(0.1)。
  • MySQL 8.0 支持并行复制，打开 slave_parallel_workers = 4（8.0.26 后参数改名为 replica_parallel_workers），能显著缓解延迟。

四、从库到底能不能提升"抗并发"能力

能，但有前提。读写分离提升的是读吞吐：从库可以加多台，读请求可以分摊，这是真正的横向扩展。但写永远只能写主库，写能力不会因为加了从库而提升丝毫。

所以判断要不要做主从，看你的瓶颈在哪：

  • SHOW GLOBAL STATUS LIKE 'Com_select' 的增速远大于 Com_insert/Com_update → 读瓶颈，主从有意义；
  • 如果主库 CPU 被写操作占满 → 加从库没用，该优化的是表结构、索引、或者把写拆到不同业务库。

另外提醒一句：从库的 read_only 只防手滑，不防主从数据不一致带来的错误。定期跑 pt-table-checksum（Percona Toolkit）对比主从数据，能提前发现 STATEMENT 格式、跳过错误事务等问题造成的数据漂移。个人站一年跑一两次也行。

五、故障排查清单

  • Slave_IO_Running = Connecting：网络不通，或复制账号密码错、权限不足。先用 telnet 10.0.0.1 3306 测通，再核对 CHANGE MASTER 参数。
  • Slave_SQL_Running = No：看一下 Last_SQL_Error。常见是"主键冲突"或"表不存在"，多为从库被手工写过数据。用 GTID 的话可以先 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; 跳过（GTID 模式下用 SET GTID_NEXT），但跳过前一定要搞清楚为什么错，否则数据会持续漂移。
  • 从库磁盘写满：relay log 或 binlog 堆积。检查 relay_log_purge（应为 ON），并确认磁盘配额。
  • 重建从库：数据漂移太严重时别修，直接重新 dump 全量、清空从库、RESET SLAVE ALL 再按第一步重做，通常比修更快更干净。
  • 主库 binlog 被清理导致从库接不上：说明从库断开时间超过了 binlog_expire_logs_seconds，只能重做全量。给 binlog 保留时间留足余量（7 天是底线）。

六、写在最后：先量化，再架构

主从复制是一套"一旦上了就要长期维护"的东西：监控、延迟处理、故障切换、重建流程，缺一不可。个人站长的时间和精力有限，我的建议顺序是：

  1. 先加索引、优化慢查询（成本最低，收益最大）；
  2. 再上 Redis 缓存（把重复读干掉）；
  3. 最后考虑主从复制（解决备份和高读吞吐）。

走到第三步时，你大概率已经不是"个人站"的规模了。但即便如此，一套跑通的一主一从依然是值得投的时间——它带来的最大价值其实是让你终于敢做全量备份，而这恰恰是很多个人站最脆弱的地方。

Last modification：September 20th, 2026 at 10:28 pm
© 允许规范转载

相关文章

Leave a Comment