写在前面:复制解决的不是备份问题
先把一个观念摆正:主从复制不是备份。你在主库执行一条没带 WHERE 的 DELETE,这条语句会毫秒级同步到从库,两边一起删干净——这正是上一篇 《数据库备份与恢复全攻略》 里强调过的事。
那复制解决什么?两件事:
- 高可用:主库宕机时,可以把从库提升为新主库继续提供服务,停机时间从「恢复备份的几小时」缩短到「切换的几分钟」
- 读写分离:写请求走主库,读请求分一部分到从库,给主库减负
原理上两家是同一个模型:主库把数据变更记录成日志(PostgreSQL 叫 WAL,MySQL 叫 binlog),从库把日志拉过来重放一遍,于是数据追上主库。本文带你把 PostgreSQL 流复制和 MySQL GTID 复制各搭一套出来,都用两台机器(或两台 VPS、两个 Docker 容器)演示。假设主库 IP 是 192.168.1.10,从库 IP 是 192.168.1.11,请按你的实际环境替换。单机安装与初始配置分别见 《PostgreSQL 安装与初始配置》 和 《MySQL 安装与初始配置》。
PostgreSQL 流复制
原理一句话
主库持续产生 WAL 日志,从库通过一条长连接实时把 WAL 拉过来重放,这就是流复制(Streaming Replication),默认异步:主库不等从库确认就返回写入成功,极端情况下主库宕机可能丢最后一点未同步的数据。
第一步:主库准备
在主库创建专用复制用户:
CREATE USER repuser WITH REPLICATION ENCRYPTED PASSWORD '强密码';修改 postgresql.conf(关键项):
listen_addresses = '*' # 允许从库连进来(配合防火墙收紧来源)wal_level = replica # 默认就是 replica,确认一下即可max_wal_senders = 5 # 最多允许几个从库同时拉日志在 pg_hba.conf 里放行从库的复制连接:
host replication repuser 192.168.1.11/32 scram-sha-256改完重启主库:
sudo systemctl restart postgresql第二步:从库用 pg_basebackup 初始化
从库上先停掉自带的空实例、清掉数据目录,然后从主库拉一份基础数据:
sudo systemctl stop postgresqlsudo -u postgres rm -rf /var/lib/postgresql/17/main/*
sudo -u postgres pg_basebackup \ -h 192.168.1.10 -U repuser \ -D /var/lib/postgresql/17/main \ -Fp -Xs -P -R几个关键参数:-Xs 表示备份的同时以流方式把期间的 WAL 一起带过来,避免数据不一致;-R 是灵魂参数,它会自动在数据目录里写好 postgresql.auto.conf 的连接信息并创建一个 standby.signal 标记文件——有 standby.signal,这个实例启动后就知道自己是备库。执行时会提示输入 repuser 的密码,想免交互可以把密码写进 ~/.pgpass 文件(权限必须 600)。
拉完直接启动从库:
sudo systemctl start postgresql第三步:验证复制状态
主库上查从库连接情况:
SELECT client_addr, state, sync_state FROM pg_stat_replication;-- 出现一行 state = streaming、sync_state = async 就是正常工作从库上确认自己是备库状态:
SELECT pg_is_in_recovery(); -- 返回 t(true)说明处于恢复/备库模式再做个冒烟测试:主库建表插一行数据,从库立刻能查到,复制就通了。
-- 主库CREATE TABLE repl_test (id int, note text);INSERT INTO repl_test VALUES (1, 'hello');-- 从库SELECT * FROM repl_test; -- 能查到 hello 即成功MySQL 基于 GTID 的复制
原理一句话
MySQL 复制经历过「文件+位置」和「GTID」两个时代。GTID(全局事务标识)给每个事务发一个全网唯一的编号,从库只要告诉主库「我执行到哪个 GTID 了」,主库就知道该补发哪些事务,不用再人工对 binlog 文件名和偏移量。新部署一律用 GTID 模式,老式位点模式只在维护存量系统时才会碰到。
第一步:主库配置
编辑主库 my.cnf 的 [mysqld] 段:
[mysqld]server_id = 1log_bin = mysql-bingtid_mode = ONenforce_gtid_consistency = ON创建复制专用账号并授权(MySQL 8.4 语法):
CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY '强密码';GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';改完配置重启主库,然后把存量数据导一份到从库(空库对空库可以跳过这步):
mysqldump -u root -p --single-transaction --source-data=2 --all-databases > full.sql# 把 full.sql 传到从库并导入第二步:从库配置并启动复制
从库 my.cnf:
[mysqld]server_id = 2gtid_mode = ONenforce_gtid_consistency = ONrelay_log = mysql-relay-binread_only = ONsuper_read_only = ONserver_id 全网唯一,主从不能相同——这是新手最常犯的错误之一。read_only+super_read_only 把从库锁成只读,防止有人误连从库写入造成数据分叉。
重启从库后,配置复制源并启动(MySQL 8.4 推荐用 SOURCE 而不是旧的 MASTER 术语):
CHANGE REPLICATION SOURCE TO SOURCE_HOST = '192.168.1.10', SOURCE_USER = 'repl', SOURCE_PASSWORD = '强密码', SOURCE_AUTO_POSITION = 1;
START REPLICA;SOURCE_AUTO_POSITION = 1 就是开启 GTID 自动定位,从库自己算缺哪些事务,不用填日志文件名和位置。
第三步:验证状态
SHOW REPLICA STATUS\G重点看三行:
Replica_IO_Running: Yes -- 拉日志的线程正常Replica_SQL_Running: Yes -- 重放日志的线程正常Seconds_Behind_Source: 0 -- 延迟秒数,0 表示已追平两个线程都是 Yes 且延迟为 0,再在主库写一行、从库查一遍,MySQL 这套就搭好了。
WARNING搭复制时最容易被忽略的是网络安全:复制连接传输的是数据库的全部变更数据,不要把复制端口直接暴露在公网。主从之间优先走内网或 VPN 通道,防火墙只放行从库 IP 访问主库的 5432/3306 端口,复制账号也要用强密码并限定来源 IP,和前面配置里写的一样,不要为了图省事授权给
%或0.0.0.0/0。
两家复制方式对比
| 对比项 | PostgreSQL 流复制 | MySQL GTID 复制 |
|---|---|---|
| 同步的日志 | WAL | binlog |
| 从库初始化 | pg_basebackup 一条命令拉全量 | 先导全量数据,再启动复制线程 |
| 位点管理 | 自动(LSN) | GTID 自动定位 |
| 状态查看 | pg_stat_replication | SHOW REPLICA STATUS |
| 默认模式 | 异步(可配同步) | 异步(可配半同步) |
| 从库可读 | 可以(热备模式默认可查) | 可以(建议开只读锁防误写) |
主从延迟与故障切换的基本思路
延迟是异步复制的固有现象:主库写入高峰、从库机器性能差、大事务(如一次更新百万行)都会让从库落后。延迟的查看方式两家都在上文给出了。延迟几秒对读写分离的读请求意味着可能读到旧数据,所以「写完立刻读」的关键路径(如支付后查订单)应强制走主库,这是读写分离落地的第一原则。
故障切换(主库宕机时)的手工流程,两家通用:
- 确认主库真的不可用(别被网络抖动误判,否则会出现双主脑裂)
- 等待从库把已收到的日志重放完(PostgreSQL 看
pg_last_wal_replay_lsn,MySQL 看状态里的已执行 GTID 追平) - 提升从库:PostgreSQL 执行
pg_ctl promote或SELECT pg_promote();;MySQL 执行STOP REPLICA; SET GLOBAL read_only = OFF; SET GLOBAL super_read_only = OFF; - 把应用连接指向新主库
- 老主库修好后,只能作为新主库的从库重新加入(数据要重新同步),绝不能直接让它复活继续当主
生产环境一般会用 Patroni(PostgreSQL)或 Orchestrator/MHA(MySQL)做自动故障转移,但手工流程必须先懂,自动化工具出问题时才知道它在干什么。
常见问题
Q:MySQL SHOW REPLICA STATUS 报 Last_IO_Error: 1236 或连接失败?
1236 通常是主库 binlog 已被清理、从库要的日志不存在,只能重新导全量再搭。连接失败则依次查:网络是否通、复制账号的授权 IP 是否写对、主库防火墙是否放行 3306、密码是否正确。
Q:PostgreSQL 从库启动失败,日志说找不到 WAL 文件?
多半是主库的 WAL 已经被回收了(从库停机太久)。解决办法是重新跑一次 pg_basebackup 初始化;长期对策是给主库设置复制槽(replication slot),主库会为主库未消费的 WAL 保留到从库追上为止,但要注意槽会让 WAL 无限堆积撑爆磁盘,需要监控。
Q:从库延迟越来越大怎么办? 先定位是拉日志慢(网络/主库压力)还是重放慢(从库 CPU、磁盘或大事务)。MySQL 8.4 默认开启多线程重放,一般够用;大事务是延迟杀手,业务上把批量更新拆小。从库硬件不要比主库差太多,这是最常见的根因。
Q:一主多从可以吗? 可以,两家都支持一个主库挂多个从库。PostgreSQL 还支持级联复制(从库再带从库)。从库数量上去后注意主库的连接与带宽压力。
Q:Docker 环境能搭主从吗? 能,两个容器加一个自定义网络即可,流程和物理机一模一样,初始化数据的那篇 《用 Docker 快速搭建三大数据库》 可以作为单机起点。注意容器重建后 IP 可能变化,连接配置里尽量用容器名。
小结
主从复制的骨架两家是一样的:主库记日志、从库拉日志重放。PostgreSQL 靠 pg_basebackup -R 一条命令拉起备库,MySQL 靠 GTID 把位点管理自动化。搭好之后记住三件事:复制不等于备份,两者要并行存在;读写分离时强一致读走主库;切换流程先在测试环境演练一遍,真出故障时才不至于手忙脚乱。下一篇是本系列收官——《数据库性能监控与常见故障排障》,讲怎么盯住这些数据库的健康状态。