2442 字
12 分钟
主从复制入门:PostgreSQL 与 MySQL 各搭一套
2026-11-15

写在前面:复制解决的不是备份问题#

先把一个观念摆正:主从复制不是备份。你在主库执行一条没带 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

改完重启主库:

Terminal window
sudo systemctl restart postgresql

第二步:从库用 pg_basebackup 初始化#

从库上先停掉自带的空实例、清掉数据目录,然后从主库拉一份基础数据:

Terminal window
sudo systemctl stop postgresql
sudo -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)。

拉完直接启动从库:

Terminal window
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 = 1
log_bin = mysql-bin
gtid_mode = ON
enforce_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';

改完配置重启主库,然后把存量数据导一份到从库(空库对空库可以跳过这步):

Terminal window
mysqldump -u root -p --single-transaction --source-data=2 --all-databases > full.sql
# 把 full.sql 传到从库并导入

第二步:从库配置并启动复制#

从库 my.cnf:

[mysqld]
server_id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = mysql-relay-bin
read_only = ON
super_read_only = ON

server_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 复制
同步的日志WALbinlog
从库初始化pg_basebackup 一条命令拉全量先导全量数据,再启动复制线程
位点管理自动(LSN)GTID 自动定位
状态查看pg_stat_replicationSHOW REPLICA STATUS
默认模式异步(可配同步)异步(可配半同步)
从库可读可以(热备模式默认可查)可以(建议开只读锁防误写)

主从延迟与故障切换的基本思路#

延迟是异步复制的固有现象:主库写入高峰、从库机器性能差、大事务(如一次更新百万行)都会让从库落后。延迟的查看方式两家都在上文给出了。延迟几秒对读写分离的读请求意味着可能读到旧数据,所以「写完立刻读」的关键路径(如支付后查订单)应强制走主库,这是读写分离落地的第一原则。

故障切换(主库宕机时)的手工流程,两家通用:

  1. 确认主库真的不可用(别被网络抖动误判,否则会出现双主脑裂)
  2. 等待从库把已收到的日志重放完(PostgreSQL 看 pg_last_wal_replay_lsn,MySQL 看状态里的已执行 GTID 追平)
  3. 提升从库:PostgreSQL 执行 pg_ctl promote 或 SELECT pg_promote();;MySQL 执行 STOP REPLICA; SET GLOBAL read_only = OFF; SET GLOBAL super_read_only = OFF;
  4. 把应用连接指向新主库
  5. 老主库修好后,只能作为新主库的从库重新加入(数据要重新同步),绝不能直接让它复活继续当主

生产环境一般会用 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 把位点管理自动化。搭好之后记住三件事:复制不等于备份,两者要并行存在;读写分离时强一致读走主库;切换流程先在测试环境演练一遍,真出故障时才不至于手忙脚乱。下一篇是本系列收官——《数据库性能监控与常见故障排障》,讲怎么盯住这些数据库的健康状态。

主从复制入门:PostgreSQL 与 MySQL 各搭一套
https://www.anexns.cloudns.org/posts/database-replication-basics/
作者
Anexns
发布于
2026-11-15
许可协议
CC BY-NC-SA 4.0