开发者日记

外观

GTID 一主两从实战：从断点修复到秒级延迟监控 ​

为什么生产环境更适合选择 GTID 主从复制 ​

在传统基于 binlog 文件和 position 的主从复制中，DBA 需要手工记录主库位点，并在主从切换或故障恢复时小心对齐。实例越多、切换越频繁，越容易出现位点错配、重复执行或漏执行事务。GTID（Global Transaction Identifier）为每个已提交事务分配全局唯一编号，MySQL 可以自动判断从库是否已经执行过某事务，从而显著降低一主两从架构的搭建和修复成本。

典型拓扑为一个写入主库、两个只读从库：从库 A 承载在线查询，从库 B 用于备份和报表。业务高峰期主库每秒可能产生数百个事务，一旦某个从库遇到重复主键、不存在的行或人工误写入，就会导致 SQL 线程中断。此时如果仍然使用传统位点修复，不仅要解析 binlog，还可能影响另一个健康从库；使用 GTID 后，可以按事务粒度定位、跳过或补偿，处理路径更清晰。

GTID 复制与传统位点复制怎么选 ​

GTID 并不是银弹，但在 MySQL 5.7 之后的生产环境中，它通常是主从复制的首选。传统位点复制适合极老版本升级过渡、跨异构复制或特殊第三方工具链；GTID 更适合自动故障切换、多实例标准化运维、容器化重建和高频备份恢复场景。

对比项传统 binlog 位点GTID 复制
事务标识文件名 + position全局唯一 GTID
新从库接入手工对齐位点自动协商已执行事务
主从切换容易位点混乱更适合自动切换
断点修复需解析 binlog 定位可按 GTID 精确处理
限制兼容性较宽不支持部分非事务写法混用

下面的流程图展示了 GTID 在一主两从中的提交与回放路径：主库事务提交后生成 source_uuid:transaction_id，两个从库分别记录已接收和已执行集合。

主库写入事务生成 GTID从库 A：在线查询IO/SQL 线程回放从库 B：备份报表记录已执行集合binlog 传输 + GTID 自动去重

一主两从 GTID 复制搭建步骤 ​

以下示例假设三个实例地址分别为 10.0.0.11:3306、10.0.0.21:3306、10.0.0.22:3306，MySQL 版本为 8.0。三个节点都需要启用 GTID，并使用 ROW 格式 binlog。

第一步：统一主从实例参数 ​

主库和两个从库建议配置相同的 server_id，注意不是相同值，而是各自唯一。修改配置后重启实例，或在支持的版本中先在线开启再持久化。

ini
# /etc/my.cnf
[mysqld]
server_id=11                    # 从库分别改为 21、22
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
log_slave_updates=ON            # 从库未来可能提升为主库时必须开启
relay_log=relay-bin
binlog_expire_logs_seconds=604800

确认 GTID 状态：

sql
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SHOW MASTER STATUS;

第二步：在主库准备复制账号和业务表 ​

复制账号只授予必要权限，并限制来源网段。为了让后续验证更直观，先建一个订单明细表，并在用户 ID 与状态字段上建组合索引。

sql
-- 在主库执行
CREATE USER IF NOT EXISTS 'repl'@'10.0.0.%'
  IDENTIFIED BY 'Repl@StrongPass#2026';

GRANT REPLICATION SLAVE, REPLICATION CLIENT
  ON *.* TO 'repl'@'10.0.0.%';

CREATE DATABASE IF NOT EXISTS shop
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS shop.order_gtid_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  order_status TINYINT NOT NULL DEFAULT 0,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                 ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_user_status (user_id, order_status),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB;

INSERT INTO shop.order_gtid_demo(user_id, order_status, amount)
VALUES (1001, 1, 99.50), (1002, 0, 210.00);

第三步：让两个从库基于 GTID 自动追踪主库 ​

如果从库是全新实例，且能直接读取主库全量历史 binlog，可以直接执行下面语句。生产环境更推荐先用 XtraBackup 或 mysqldump 带 --source-data 与 --set-gtid-purged=ON 初始化数据，再启动复制。

sql
-- 在每个从库执行，MySQL 8.0 语法
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.11',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='Repl@StrongPass#2026',
  SOURCE_AUTO_POSITION=1,
  GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

SHOW REPLICA STATUS\G

重点检查以下字段：

sql
SELECT
  SERVICE_STATE,
  RECEIVED_TRANSACTION_SET,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  SECONDS_BEHIND_MASTER
FROM performance_schema.replication_connection_status AS rcs
JOIN performance_schema.replication_applier_status_by_worker AS rasw;

正常情况下，Replica_IO_Running 和 Replica_SQL_Running 都应为 Yes，Retrieved_Gtid_Set 与 Executed_Gtid_Set 应持续推进。

复制中断如何快速定位 ​

一主两从最常见的问题不是两个从库同时中断，而是单个从库被人工误写后出现数据差异。例如有人直接在从库 B 插入相同主键：

sql
-- 错误示范：直接在从库写入业务数据
SET SESSION sql_log_bin=OFF;
INSERT INTO shop.order_gtid_demo(id, user_id, order_status, amount)
VALUES (3, 1003, 1, 88.00);

主库随后提交同一主键事务时，从库 B 回放失败，报错通常类似 Duplicate entry '3' for key 'order_gtid_demo.PRIMARY'。此时不要立刻重启复制，也不要盲目全库重建，应先拿到失败事务号。

sql
SHOW REPLICA STATUS\G

SELECT
  CHANNEL_NAME,
  LAST_ERROR_NUMBER,
  LAST_ERROR_MESSAGE,
  LAST_APPLIED_TRANSACTION,
  LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
  LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP,
  LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker
WHERE SERVICE_STATE = 'OFF';

如果错误明确是幂等冲突，例如重复插入但数据内容一致，可以使用空事务跳过该 GTID。假设失败事务为 3E11F1D8-1111-2222-3333-444444444444:108：

sql
STOP REPLICA;

SET GTID_NEXT='3E11F1D8-1111-2222-3333-444444444444:108';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

START REPLICA;

但如果冲突数据内容不同，跳过事务会造成主从长期不一致。更安全的方式是删除或修正从库冲突行，再重新回放；核心表则应通过备份恢复或重新搭建从库处理。

GTID 延迟监控与只读保护 ​

SECONDS_BEHIND_MASTER 只能反映 SQL 回放时间差，遇到大事务、并行回放瓶颈或网络抖动时并不充分。建议同时监控已接收但未执行的 GTID 数量、主库执行集合和从库执行集合差异。

sql
-- 在从库查看复制线程状态
SELECT
  CHANNEL_NAME,
  SERVICE_STATE,
  LAST_QUEUED_TRANSACTION,
  LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP AS queued_at,
  LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP AS io_start
FROM performance_schema.replication_connection_status;

SELECT
  CHANNEL_NAME,
  WORKER_ID,
  SERVICE_STATE,
  LAST_APPLIED_TRANSACTION,
  LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP AS commit_at,
  LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP AS apply_end
FROM performance_schema.replication_applier_status_by_worker;

还可以把延迟监控结果落到一张运维表，便于 Prometheus 以外的轻量场景巡检：

sql
CREATE DATABASE IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS ops.replica_lag_snapshot (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  server_uuid CHAR(36) NOT NULL,
  channel_name VARCHAR(64) NOT NULL,
  io_state VARCHAR(16) NOT NULL,
  sql_state VARCHAR(16) NOT NULL,
  seconds_behind_master BIGINT NULL,
  last_error_message TEXT,
  checked_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_checked_at (checked_at)
) ENGINE=InnoDB;

INSERT INTO ops.replica_lag_snapshot(
  server_uuid, channel_name, io_state, sql_state,
  seconds_behind_master, last_error_message
)
SELECT
  @@server_uuid,
  rcs.CHANNEL_NAME,
  rcs.SERVICE_STATE,
  ras.SERVICE_STATE,
  rs.SECONDS_BEHIND_MASTER,
  rs.LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status rcs
JOIN performance_schema.replication_applier_status ras
  ON rcs.CHANNEL_NAME = ras.CHANNEL_NAME
JOIN information_schema.replica_status rs
  ON rcs.CHANNEL_NAME = rs.CHANNEL_NAME;

从库必须设置只读，避免业务账号或人工误写：

sql
SET PERSIST read_only=ON;
SET PERSIST super_read_only=ON;

注意 read_only 不会阻止拥有 SUPER 或 CONNECTION_ADMIN 权限的账号写入，因此生产从库应同时开启 super_read_only，并收回业务账号的超级权限。

避坑总结：复制中断修复的实战注意事项 ​

第一，不要在开启 GTID 后混用不支持 GTID 一致性的语句。常见现象是执行 CREATE TABLE ... SELECT 后报 Statement violates GTID consistency。MySQL 将该语句拆成建表 DDL 和插入 DML，在事务语义上无法保证原子复制。修正方式是拆成两条语句：

sql
CREATE TABLE shop.order_archive_2026 LIKE shop.order_gtid_demo;

INSERT INTO shop.order_archive_2026
SELECT *
FROM shop.order_gtid_demo
WHERE created_at < '2026-01-01';

第二，不要把跳过事务当成常规修复手段。很多人看到复制中断就连续 STOP REPLICA、设置空事务、START REPLICA，结果错误号从 1062 变成 1032，后续查询出现主从数据不一致。正确做法是先记录失败 GTID、主键值和行内容，确认幂等后才跳过；涉及金额、订单、权限等核心表，应优先补偿数据或重建从库。

第三，不要在从库关闭 binlog 后手工修数。SET sql_log_bin=0 会让本地修改不进入 binlog，GTID 集合也无法反映这次变更，短期看似绕过权限，长期会制造隐蔽差异。需要修数时应在主库执行，让变更通过标准复制链路下发；必须在从库紧急修复时，也要同步修正其他节点并留下审计记录。

第四，不要只看 SECONDS_BEHIND_MASTER=0。大事务未开始回放时该值可能仍显示为 0，但主从已经存在数据延迟。应结合 GTID 集合差异、主库提交时间与从库应用结束时间、业务只读探针共同判断。对一主两从而言，还应分别设置两个从库的告警，避免备份从库延迟却被在线查询从库的健康状态掩盖。

第五，新从库重建时不要忘记 SOURCE_AUTO_POSITION=1。如果备份文件已经携带 GTID_PURGED，又手工指定旧的 binlog 文件和 position，可能重复执行事务或报致命位点错误。使用 mysqldump 时确认 --set-gtid-purged=ON，使用物理备份时确认 xtrabackup_binlog_info 中记录了 GTID 信息。

通过以上步骤，团队可以把 GTID 主从复制从“能搭起来”推进到“出问题能定位、有延迟能发现、坏节点能重建”的运维状态。对于 MySQL 一主两从架构，真正的稳定性不只来自复制参数本身，还来自只读保护、事务规范、监控采集和修复流程的共同约束。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。