MySQL 复制、高可用、读写分离、分片与恢复实战

高谷深陵大约 34 分钟约 10153 字

MySQL 高可用、扩展与恢复工程图从 GTID 事务身份、复制确认、故障仲裁和连接路由，一直走到分片迁移、备份链与恢复演练。高清原图

一次切换成功，为何仍然造成了数据事故

凌晨的主库进程已经退出，值班同学把副本提升为新主，应用连接也改到了新地址。监控很快恢复为绿色，大家以为切换结束了。十几分钟后，旧主因机器自愈重新启动；一批仍缓存旧 DNS 的应用实例继续向它写入，另一批应用已经写入新主。两边都能执行 COMMIT，订单却从此分成了两套事实。

这类事故不是“主从没有搭好”，而是团队只处理了数据库进程，没有处理完整的高可用链路：

只做“提升新主”相当于只完成了图中的一个节点。真正可用的方案必须同时回答：事务有没有到达候选节点、谁有权决定提升、旧主如何被隔离、现有连接怎样失败、重试会不会重复写、旧节点如何重新加入、备份能否覆盖复制没有解决的误删与逻辑损坏。

接下来的操作以 MySQL 8.4 LTS 为基线。实验机需要 Docker Compose、MySQL 客户端以及至少 6 GiB 可用内存；InnoDB Cluster 演练还需要同一发行线的 MySQL Shell 与 MySQL Router。所有密码都通过环境变量或交互输入传递，命令历史、Compose 文件和仓库中都不应出现真实生产凭证。

先建立拓扑事实，而不是先选产品名

高可用评审先画出五个平面，产品名放在最后：

平面	需要确认的对象	失败时的第一证据
数据平面	InnoDB 数据、redo、binlog、relay log、GTID 集	`@@GLOBAL.gtid_executed`、复制线程状态、事务校验
成员平面	source、replica、PRIMARY、SECONDARY、quorum	`SHOW REPLICA STATUS`、`performance_schema.replication_group_members`
控制平面	人工 runbook、MHA、AdminAPI、云控制台	切换审计、选举日志、控制面健康状态
路由平面	DNS、VIP、Router、ProxySQL、连接池	后端身份、连接错误、代理运行时表
恢复平面	全量备份、增量、binlog 归档、恢复实例	备份结束位点、归档连续性、恢复校验结果

异步复制解决“把事务传过去”，不自动完成故障判断和客户端切换；半同步改变提交确认窗口，仍不负责提升节点；Group Replication 管理组成员和事务认证，InnoDB Cluster 再用 MySQL Shell AdminAPI 组织控制面，Router 才提供面向客户端的路由入口。把这些层次混成一句“上 MGR 就高可用”，会把风险藏在产品名后面。

RPO 与 RTO 也不能从架构名称推断。RPO 是事故后最多允许缺失多少已提交业务事实，RTO 是从故障被发现到业务恢复所允许的时间。两者必须来自实际演练：记录故障前最后一个业务序号、候选节点最后一个 GTID、客户端恢复时刻和人工步骤，才能得到团队自己的数字。

用两个容器跑通 GTID 主从

建立可销毁的实验目录

新建空目录 mysql-ha-lab，在目录中保存下面的 compose.yaml。MYSQL_ROOT_PASSWORD 和复制密码只存在当前终端环境中：

services:
  source:
    image: mysql:8.4
    container_name: mysql-ha-source
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?set MYSQL_ROOT_PASSWORD}
    command:
      - --server-id=101
      - --log-bin=mysql-bin
      - --gtid-mode=ON
      - --enforce-gtid-consistency=ON
      - --log-replica-updates=ON
      - --binlog-expire-logs-seconds=604800
      - --skip-name-resolve=ON
    ports:
      - "13306:3306"
    volumes:
      - source-data:/var/lib/mysql
    healthcheck:
      test: ["CMD-SHELL", "mysqladmin ping -h 127.0.0.1 -uroot -p$$MYSQL_ROOT_PASSWORD --silent"]
      interval: 5s
      timeout: 3s
      retries: 30

  replica:
    image: mysql:8.4
    container_name: mysql-ha-replica
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:?set MYSQL_ROOT_PASSWORD}
    command:
      - --server-id=102
      - --log-bin=mysql-bin
      - --relay-log=relay-bin
      - --gtid-mode=ON
      - --enforce-gtid-consistency=ON
      - --log-replica-updates=ON
      - --read-only=ON
      - --super-read-only=ON
      - --skip-name-resolve=ON
    ports:
      - "13307:3306"
    volumes:
      - replica-data:/var/lib/mysql
    healthcheck:
      test: ["CMD-SHELL", "mysqladmin ping -h 127.0.0.1 -uroot -p$$MYSQL_ROOT_PASSWORD --silent"]
      interval: 5s
      timeout: 3s
      retries: 30

volumes:
  source-data:
  replica-data:

PowerShell 中启动：

$env:MYSQL_ROOT_PASSWORD = Read-Host "实验 root 密码"
$env:MYSQL_REPL_PASSWORD = Read-Host "复制账号密码"
docker compose up -d
docker compose ps

两个服务都应进入 healthy。接着确认实例身份，不能只看端口能否连接：

docker compose exec -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD source `
  mysql -uroot -Nse "SELECT VERSION(), @@server_id, @@server_uuid, @@gtid_mode, @@read_only"

docker compose exec -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD replica `
  mysql -uroot -Nse "SELECT VERSION(), @@server_id, @@server_uuid, @@gtid_mode, @@super_read_only"

预期看到不同的 server_id 和 server_uuid，两边 gtid_mode 都为 ON，副本的 super_read_only 为 1。如果 UUID 相同，通常是复制了整个数据目录；这样的节点不能继续配置复制，应销毁副本 volume 后重新初始化。

创建最小权限复制账号

复制账号只用于复制连接，不应复用应用账号或 root。容器网络中的主机名是 replica，因此可以把来源约束到容器网段或明确主机；为了让实验不依赖 Compose 自动生成的网段，这里使用 %，生产必须改成受控网段并启用 TLS：

$createRepl = @"
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY '$env:MYSQL_REPL_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
"@
$createRepl | docker compose exec -T -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD source mysql -uroot

在副本上使用 GTID 自动定位：

$changeSource = @"
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='$env:MYSQL_REPL_PASSWORD',
  SOURCE_AUTO_POSITION=1,
  GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
"@
$changeSource | docker compose exec -T -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD replica mysql -uroot

GET_SOURCE_PUBLIC_KEY=1 让未配置 TLS 的本地实验可以在默认认证插件下交换公钥。生产复制链路应使用 SOURCE_SSL=1 并校验证书身份，不能把这个本地便利项当成网络安全方案。

用状态链证明复制真的工作

先看接收线程和应用线程：

docker compose exec -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD replica `
  mysql -uroot -e "SHOW REPLICA STATUS\G"

至少核对：

Replica_IO_Running: Yes：接收线程能连接 source 并读取 binlog。Replica_SQL_Running: Yes：应用线程能执行 relay log 中的事务。Auto_Position: 1：使用 GTID，而不是人工维护文件位点。

Last_IO_Error 与 Last_SQL_Error 为空：没有连接或执行错误。

再在 source 写入一个带业务序号的事务：

$seed = @"
CREATE DATABASE IF NOT EXISTS ha_lab;
CREATE TABLE IF NOT EXISTS ha_lab.order_event (
  id BIGINT PRIMARY KEY,
  event_name VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO ha_lab.order_event(id, event_name) VALUES (1001, 'SOURCE_COMMIT');
"@
$seed | docker compose exec -T -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD source mysql -uroot

docker compose exec -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD replica `
  mysql -uroot -e "SELECT @@server_id, @@read_only; SELECT * FROM ha_lab.order_event;"

副本应返回 id=1001，同时 @@read_only=1。最后比较 GTID 集：

docker compose exec -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD source `
  mysql -uroot -Nse "SELECT @@GLOBAL.gtid_executed"

docker compose exec -e MYSQL_PWD=$env:MYSQL_ROOT_PASSWORD replica `
  mysql -uroot -Nse "SELECT @@GLOBAL.gtid_executed"

GTID 由“源实例 UUID + 单调事务序号”组成。SOURCE_AUTO_POSITION=1 的关键价值是副本按已执行 GTID 集请求缺失事务，不再由操作者猜测 binlog.000123:456。它简化拓扑切换，但不会自动消除 errant transaction：如果有人绕过 super_read_only 在副本本地写入，那个只存在于副本的 GTID 仍会阻碍安全重入。

故意配错密码，学会读第一证据

不要等生产事故才第一次看 Last_IO_Error。先停掉复制，注入错误密码：

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_PASSWORD='intentionally-wrong';
START REPLICA;

在副本执行后等待一个连接周期，再看：

SHOW REPLICA STATUS\G

预期 Replica_IO_Running 不是 Yes，Last_IO_Error 出现认证失败；Replica_SQL_Running 可能仍为 Yes，因为应用线程和接收线程是两个不同状态机。修复时不要重建数据：

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_PASSWORD='<MYSQL_REPL_PASSWORD>';
START REPLICA;

再次确认两个线程都为 Yes，并在 source 写入 id=1002 验证副本追平。若错误已经变成 Last_SQL_Error，修复方向就不同：先定位冲突对象和 GTID，禁止直接用 sql_replica_skip_counter 连续跳过错误来换绿色监控。

半同步改变的是确认窗口，不是高可用控制面

异步复制中，source 可以在任何副本收到事务前就向客户端返回成功。半同步要求至少一个具备能力的副本把事务事件写入并刷入 relay log 后确认，source 才向客户端返回；这意味着成功提交的数据至少存在于两个位置，但不表示副本已经应用，更不表示故障后一定零丢失。

在两个实例安装组件：

-- source
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET PERSIST rpl_semi_sync_source_enabled = ON;
SET PERSIST rpl_semi_sync_source_timeout = 3000;

-- replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET PERSIST rpl_semi_sync_replica_enabled = ON;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

3000 毫秒只是故障演示值。生产超时需要结合同城网络往返、提交延迟 SLO 和降级策略测量；设置太短会频繁退化为异步，设置太长会把副本或网络故障放大成写接口卡顿。

状态检查：

-- source
SHOW STATUS LIKE 'Rpl_semi_sync_source_status';
SHOW STATUS LIKE 'Rpl_semi_sync_source_clients';
SHOW STATUS LIKE 'Rpl_semi_sync_source_no_times';
SHOW STATUS LIKE 'Rpl_semi_sync_source_timefunc_failures';

-- replica
SHOW STATUS LIKE 'Rpl_semi_sync_replica_status';

正向结果是 source status 为 ON、clients 至少为 1。现在在副本执行 STOP REPLICA IO_THREAD，随后在 source 提交一个小事务。调用会等待约三秒再返回，source status 转为 OFF，no_times 增加；恢复副本接收线程并追平后，半同步可重新启用。

这个反向实验揭示三个架构事实：

半同步会把网络往返加入提交延迟，跨地域链路通常不是它的舒适区。超时后可以退化成异步，因此“启用了插件”不等于每个提交都获得确认。故障 source 可能含有未被副本确认的事务，不能未经 GTID 差异检查就重新作为 source 接入。

切换的核心是 fencing、GTID 判断和客户端语义

计划内切换

计划内切换要先停止新增写入，再比较事务集。下面的顺序适用于演练环境，生产应由受审计的控制面执行：

从入口层停止写流量，等待在途事务结束。在旧 source 设置 super_read_only=ON，确认没有普通账号继续写入。等副本接收并应用完 relay log。

比较 source 与候选副本的 gtid_executed。隔离旧 source 的业务入口，再提升候选节点。切换代理或服务发现，强制连接池建立新连接。

写入带切换批次号的探针，验证读取与审计链路。

-- 旧 source
SET GLOBAL super_read_only = ON;
SELECT @@GLOBAL.gtid_executed;

-- 候选 replica
SELECT WAIT_FOR_EXECUTED_GTID_SET('<SOURCE_GTID_SET>', 30) AS caught_up;
SELECT GTID_SUBTRACT('<SOURCE_GTID_SET>', @@GLOBAL.gtid_executed) AS missing_on_candidate;
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, '<SOURCE_GTID_SET>') AS errant_on_candidate;

caught_up=0 且两个差集都为空，才说明候选节点在这个冻结点拥有同一事务集合。随后在候选节点：

STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

RESET REPLICA ALL 会清除连接参数，属于不可随手执行的状态变更。执行前保存原拓扑、复制账号和 TLS 配置；需要回滚时，必须重新用 CHANGE REPLICATION SOURCE TO 建立复制关系。

非计划故障不能假装拥有完美信息

source 已经不可达时，无法证明它最后落盘了什么。控制面只能比较所有可达候选节点，选择 gtid_executed 最完整且数据健康的节点，并把不可达 source 先从网络、代理、VIP、服务发现和凭证层隔离。fencing 至少要有两种独立手段，例如：

从负载均衡和代理后端移除旧节点。撤销旧节点数据库安全组的应用访问。关闭旧节点实例或隔离存储。

启动后保持 super_read_only=ON，只允许恢复账号连接。

仅执行 SET GLOBAL read_only=ON 不够：拥有高权限的会话仍可能写入，进程重启后动态变量也可能丢失。fencing 的验收证据应同时包含网络拒绝、代理后端状态、节点只读状态和业务写探针失败。

旧主重入前先查 errant transaction

旧节点恢复后，第一动作不是取消只读，而是从隔离网络启动并比较 GTID：

-- 在旧节点记录
SELECT @@GLOBAL.gtid_executed AS old_node_set;

-- 在新主记录
SELECT @@GLOBAL.gtid_executed AS new_primary_set;

-- 任一安全分析实例执行
SELECT GTID_SUBTRACT('<OLD_NODE_SET>', '<NEW_PRIMARY_SET>') AS only_on_old;
SELECT GTID_SUBTRACT('<NEW_PRIMARY_SET>', '<OLD_NODE_SET>') AS missing_on_old;

only_on_old 非空代表旧节点存在新主没有的事务。不能用“让它追上新主”掩盖分叉：先通过 binlog、审计日志和业务表识别这些事务，再决定人工补偿、重建旧节点或事故恢复。没有 errant transaction 且旧节点数据未损坏时，才可把它重新配置为新主的 replica；存在不确定性时，最稳妥的做法是从受信快照或 Clone 重建。

应用侧还要处理“结果未知”。连接在 COMMIT 返回前断开，客户端无法只凭异常判断事务是否提交。重试写操作必须依靠业务幂等键、唯一约束或状态查询，不能看到连接错误就无条件重发。

用 InnoDB Cluster 看清官方高可用链路

Group Replication 是 MySQL Server 插件，负责组成员、事务认证、主节点选举和分布式恢复；InnoDB Cluster 用 MySQL Shell AdminAPI 管理这些成员；MySQL Router 根据集群元数据提供客户端入口。三者分别承担数据成员、控制和路由职责。

三节点 sandbox 从创建到故障恢复

本地 sandbox 只用于理解行为，不代表跨主机网络、磁盘和故障域。启动 mysqlsh，切换 JavaScript 模式：

\js
dba.deploySandboxInstance(3310)
dba.deploySandboxInstance(3320)
dba.deploySandboxInstance(3330)

shell.connect('root@localhost:3310')
cluster = dba.createCluster('devCluster')
cluster.addInstance('root@localhost:3320', {recoveryMethod: 'clone'})
cluster.addInstance('root@localhost:3330', {recoveryMethod: 'clone'})
cluster.status({extended: 1})

三次部署使用同一实验密码。预期 status 为 OK，一个成员角色为 PRIMARY，两个为 SECONDARY。Group Replication 要求复制表使用 InnoDB，并拥有主键或等价的非空唯一键；组成员关键配置、地址解析和网络延迟也必须一致。

Clone 在这里用于新成员置备或分布式恢复：它会覆盖 recipient 的用户数据，不复制 Server 配置与 binary log，只克隆 InnoDB 数据，而且 donor 与 recipient 必须位于同一 MySQL Server series。它不是可离线保存、可保留多版本、可做 PITR 的备份。

引导 Router：

mysqlrouter --bootstrap root@127.0.0.1:3310 --directory router-dev
./router-dev/start.sh

Windows 使用生成目录中的 start.ps1 或直接按输出启动 mysqlrouter.exe -c <CONFIG_PATH>。bootstrap 会交互读取密码并生成 metadata cache 配置；不要手工维护一份静态后端列表冒充 InnoDB Cluster Router。

默认 Classic Protocol 读写端口通常是 6446，只读端口通常是 6447，但实际端口必须以 bootstrap 输出和生成配置为准：

mysql -h 127.0.0.1 -P 6446 -uroot -p -e "SELECT @@port, @@server_uuid, @@read_only"
mysql -h 127.0.0.1 -P 6447 -uroot -p -e "SELECT @@port, @@server_uuid, @@read_only"

读写入口应落到 PRIMARY，只读入口应落到 SECONDARY。现在确认当前 PRIMARY 端口，再在 mysqlsh 执行：

dba.killSandboxInstance(3310)
cluster.status({extended: 1})

如果 3310 正是 PRIMARY，集群会选出新的 PRIMARY。通过 6446 的旧连接下一条查询首先可能收到 Lost connection；客户端重新连接后，Router 才会把新连接送到新主。Router 不迁移既有 TCP 连接和事务，连接池必须配置连接有效性检查、有限重试、退避和幂等。

恢复节点：

dba.startSandboxInstance(3310)
cluster = dba.getCluster()
cluster.rejoinInstance('root@localhost:3310')
cluster.status({extended: 1})

节点应以 SECONDARY 重入。若缺失事务仍在其他成员 binlog 中，可以增量恢复；缺口过大或日志已清理时需要 Clone。自动重入次数和节点离组后的动作可以通过 autoRejoinTries、exitStateAction 管理，但自动化不能替代节点身份、事务集和网络故障原因检查。

清理 sandbox 与 Router：

cluster.dissolve()
dba.deleteSandboxInstance(3310)
dba.deleteSandboxInstance(3320)
dba.deleteSandboxInstance(3330)

停止 Router 后删除 router-dev。cluster.dissolve() 不会删除已复制的数据，但会移除集群元数据并禁用 Group Replication，而且不能撤销；有不可达成员时不要强行 dissolve 后假设它们自动安全。

quorum、fencing 与跨地域边界

三成员集群能容忍一个成员故障，不代表任意网络分区都能继续服务。失去多数成员时，剩余少数派不能安全确认新的组视图。人工强制恢复 quorum 前必须确认其他分区已被隔离，否则会制造两个可写事实源。

cluster.fenceAllTraffic() 会停止 Group Replication，把成员置于只读与 offline mode，适合完整隔离独立 Cluster；恢复需要 dba.rebootClusterFromCompleteOutage()。cluster.fenceWrites() 与 unfenceWrites() 主要服务 InnoDB ClusterSet 的主集群写隔离。它们不是日常开关，执行前要确认 Router、连接池和业务降级会怎样响应。

单个 InnoDB Cluster 面向低延迟局域网。跨地域容灾应评估 InnoDB ClusterSet 的异步 Cluster 间复制、受控切换、紧急切换和失效集群 fencing；跨 WAN 强行把节点塞进一个同步组，会把链路时延和抖动直接带入写路径。

MHA 与 PXC 是第三方路线，不属于 MySQL 官方控制面

MHA 建立在传统复制拓扑上，由 Manager 发现 source 故障、评估 replica、尝试补齐 relay log 并调用脚本完成提升和入口切换。它的价值在于接住大量存量主从架构，代价是维护状态、MySQL 版本兼容、SSH 权限、VIP/DNS 脚本、Manager 自身监控和 fencing 都由采用团队负责。没有完成当前版本的故障演练，不应根据历史案例推断 RPO 或 RTO。

Percona XtraDB Cluster 是 Percona 维护的 Galera/wsrep 路线。它使用 quorum 约束可服务分区，通过写集认证处理并发冲突，并用 IST 或 SST 帮助节点追赶；慢节点的接收队列达到阈值时，flow control 会抑制整个集群写入。PXC 能减少传统异步复制的延迟窗口，却不能把多个节点变成线性写扩展：热点冲突、跨节点网络、大事务、DDL 和最慢节点都会进入提交成本。

选择第三方方案时必须独立检查上游支持的 MySQL series、升级路径、许可证、备份工具、代理集成、故障恢复手册和企业支持责任。MySQL Server、Shell、Router 的官方支持承诺不能替 MHA 或 PXC 背书。

方案	更适合的起点	必须接受的代价	首要演练
GTID 主从 + 自建控制面	团队已有主从运维和明确业务补偿	fencing、选主、路由和重入都需自建	source 宕机后比较 GTID、隔离旧主、回切
InnoDB Cluster + Router	希望采用 MySQL 官方高可用控制链	Group Replication 约束、Router 冗余、客户端重连	PRIMARY 故障、quorum 丢失、节点重入
MHA	存量传统复制且已有成熟脚本资产	第三方维护与脚本责任长期留在团队	Manager 故障、旧主隔离、relay log 补齐
PXC	能承受同步写集认证并需要多节点一致副本	网络、流控、冲突和 SST 成为核心成本	网络分区、慢节点、集群重启、SST

ProxySQL 读写分离要按业务语义路由

“所有 SELECT 去副本”是一个危险规则。事务内读取、写后立即读、库存余额、权限判断、SELECT ... FOR UPDATE 都可能要求主库事实。ProxySQL 官方也明确警告，泛化的 ^SELECT 规则会破坏事务、会话状态和一致性；更稳妥的步骤是默认走主库，先观察 query digest，再对白名单查询定向放行。

配置后端、监控账号和应用账号

继续使用前面的 source/replica。在两台 MySQL 上创建最小权限账号：

CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY '<MONITOR_PASSWORD>';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';

CREATE USER 'app_proxy'@'%' IDENTIFIED BY '<APP_PROXY_PASSWORD>';
GRANT SELECT, INSERT, UPDATE, DELETE ON ha_lab.* TO 'app_proxy'@'%';

将 ProxySQL 连接到与两个 MySQL 容器相同的 Docker 网络后，登录其 6032 管理端口。下例使用 hostgroup 10 表示 writer、20 表示 reader：

UPDATE global_variables
SET variable_value='<MONITOR_PASSWORD>'
WHERE variable_name='mysql-monitor_password';

UPDATE global_variables
SET variable_value='proxysql_monitor'
WHERE variable_name='mysql-monitor_username';

INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_replication_lag)
VALUES
  (10, 'source', 3306, 5),
  (20, 'replica', 3306, 5);

INSERT INTO mysql_replication_hostgroups(
  writer_hostgroup, reader_hostgroup, check_type, comment
) VALUES (10, 20, 'read_only', 'ha_lab');

INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent, active)
VALUES ('app_proxy', '<APP_PROXY_PASSWORD>', 10, 1, 1);

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

transaction_persistent=1 让事务建立后保持在同一 hostgroup，但它不能修复错误的业务规则。先只放行一个可以容忍延迟的查询形状：

INSERT INTO mysql_query_rules(
  rule_id, active, match_digest, destination_hostgroup, apply, comment
) VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 10, 1, 'locking read stays on writer'),
  (110, 1, '^SELECT id,event_name,created_at FROM ha_lab.order_event WHERE id=\\?', 20, 1, 'approved replica read');

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

规则上线后，从 ProxySQL 数据端口 6033 连接，分别执行白名单查询和锁定读。后端身份必须进入证据：

SELECT @@server_id, @@server_uuid, @@read_only;
SELECT id,event_name,created_at FROM ha_lab.order_event WHERE id=1001;
SELECT * FROM ha_lab.order_event WHERE id=1001 FOR UPDATE;

在管理端检查命中次数和运行时后端：

SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, Queries
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;
SELECT hostgroup_id, hostname, status, max_replication_lag
FROM runtime_mysql_servers
ORDER BY hostgroup_id, hostname;

如果规则 hits 不增长，先查 stats_mysql_query_digest 中 ProxySQL 实际看到的 digest，再调整正则；不要凭原始 SQL 猜规则。

制造延迟并验证降级

在副本临时设置延迟复制：

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY=15;
START REPLICA;

随后在 source 连续插入带递增 ID 的事件，观察：

SHOW REPLICA STATUS\G

以及 ProxySQL 管理端：

SELECT hostname, port, time_start_us, success_time_us, error
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC
LIMIT 10;

SELECT hostgroup_id, hostname, status, max_replication_lag
FROM runtime_mysql_servers
ORDER BY hostgroup_id, hostname;

当监控到的延迟超过 max_replication_lag=5 时，reader 后端应进入 SHUNNED_REPLICATION_LAG 一类的运行时状态并停止承接新查询。此时命中 reader hostgroup 的请求应失败，而不是悄悄读旧数据；应用再通过独立 writer 数据源重试允许回主的只读请求。不同版本和监控周期下状态变化不是瞬时的，验收要同时保留 monitor log、runtime status、客户端错误和回主后的实际 @@server_id，不能只截图代理配置。

清理延迟并追平：

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY=0;
START REPLICA;
SELECT WAIT_FOR_EXECUTED_GTID_SET('<CURRENT_SOURCE_GTID_SET>', 60);

生产策略还要定义延迟阈值由谁维护、所有 reader 不可用时是回主库、返回陈旧数据还是拒绝服务，以及回主库会不会把故障从副本放大到主库。核心读请求应由业务代码显式表达一致性需求，代理只负责执行已经审查的策略。

两库两表实验：路由成功只是分片的起点

下面用 Apache ShardingSphere-JDBC 5.5.3 建立 ds_0、ds_1 两个库，每库各有 t_order_0、t_order_1 两张表。数据库按 user_id % 2 路由，表按 order_id % 2 路由。版本必须与项目依赖锁定文件一致，升级时重新验证 YAML 格式与算法插件。

初始化四个物理表

在同一 MySQL 实例创建两个实验库：

CREATE DATABASE ds_0 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE DATABASE ds_1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE ds_0.t_order_0 (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  KEY idx_user_id(user_id)
) ENGINE=InnoDB;
CREATE TABLE ds_0.t_order_1 LIKE ds_0.t_order_0;
CREATE TABLE ds_1.t_order_0 LIKE ds_0.t_order_0;
CREATE TABLE ds_1.t_order_1 LIKE ds_0.t_order_0;

Maven 依赖：

<dependencies>
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.3</version>
  </dependency>
  <dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
  </dependency>
  <dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
  </dependency>
  <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-simple</artifactId>
    <version>2.0.17</version>
  </dependency>
</dependencies>

sharding.yaml：

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:13306/ds_0?useSSL=false&serverTimezone=UTC
    username: shard_app
    password: "<SHARD_DB_PASSWORD>"
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:13306/ds_1?useSSL=false&serverTimezone=UTC
    username: shard_app
    password: "<SHARD_DB_PASSWORD>"

rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_${0..1}.t_order_${0..1}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: database_inline
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: table_inline
    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${user_id % 2}
      table_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_${order_id % 2}

props:
  sql-show: true

配置文件中的 ${...} 是 ShardingSphere inline 表达式，不能顺手拿同一种写法猜测环境变量替换。运行前把模板复制到被版本控制忽略的本地配置，由应用配置层把 <SHARD_DB_PASSWORD> 替换为密钥系统下发值；缺失时立即启动失败，真实密码不进入仓库和构建日志。

最小 Java 程序：

import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory;

public class ShardingLab {
    public static void main(String[] args) throws Exception {
        DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(
            Paths.get("sharding.yaml").toFile());

        try (Connection connection = dataSource.getConnection()) {
            String insert = "INSERT INTO t_order(order_id,user_id,status,amount) VALUES (?,?,?,?)";
            try (PreparedStatement ps = connection.prepareStatement(insert)) {
                long[][] rows = {{1000, 6000}, {1001, 6000}, {1002, 6001}, {1003, 6001}};
                for (long[] row : rows) {
                    ps.setLong(1, row[0]);
                    ps.setLong(2, row[1]);
                    ps.setString(3, "CREATED");
                    ps.setBigDecimal(4, new java.math.BigDecimal("10.00"));
                    ps.executeUpdate();
                }
            }

            try (PreparedStatement ps = connection.prepareStatement(
                    "SELECT order_id,user_id,status FROM t_order WHERE user_id=? AND order_id=?")) {
                ps.setLong(1, 6001);
                ps.setLong(2, 1003);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.printf("order=%d user=%d status=%s%n",
                            rs.getLong(1), rs.getLong(2), rs.getString(3));
                    }
                }
            }
        }
    }
}

开启 sql-show 后，四次写入应分别出现四个实际节点；user_id=6001、order_id=1003 的查询只路由到 ds_1.t_order_1。同时直接连接物理库验证：

SELECT 'ds_0.t_order_0' AS node_name, order_id, user_id FROM ds_0.t_order_0
UNION ALL
SELECT 'ds_0.t_order_1', order_id, user_id FROM ds_0.t_order_1
UNION ALL
SELECT 'ds_1.t_order_0', order_id, user_id FROM ds_1.t_order_0
UNION ALL
SELECT 'ds_1.t_order_1', order_id, user_id FROM ds_1.t_order_1
ORDER BY order_id;

去掉分片键，观察查询如何退化

把查询改成：

SELECT order_id,user_id,status FROM t_order WHERE status='CREATED';

日志会显示请求被广播到四个物理表，再在逻辑层合并结果。继续增加 ORDER BY amount DESC LIMIT 20，每个分片都可能产生局部排序与结果，再由中间件归并。分片没有让这个查询消失，只是把单库成本改成扇出、网络和归并成本。

再做一个失败实验：只在 ds_0 创建新列，然后通过逻辑表执行带该列的查询。部分分片成功、部分分片报列不存在，说明 schema 变更必须有全分片编排、进度记录、幂等和回滚，不能让研发逐库手工执行。

清理：

DROP DATABASE ds_0;
DROP DATABASE ds_1;
DROP USER IF EXISTS 'shard_app'@'%';

扩容迁移不是改一条取模表达式

从两库扩到四库时，如果把 % 2 直接改成 % 4，约一半数据会立刻被新规则路由到空节点。线上扩容需要把“旧规则可读写”平滑迁移为“新规则可读写”：

迁移控制表至少记录 migration_id、分片范围、全量游标、增量位点、校验状态、切流比例和回滚状态。全量复制要限速，避免把源库 Buffer Pool 与 IO 打满；CDC 账号只授予必要复制权限，消息中的身份证号、手机号和订单明细不能无控制地进入日志或临时队列。

校验不能只比 COUNT(*)。同样行数可能包含不同数据，应按稳定主键区间比较行数、金额汇总、状态分布和规范化 checksum，并对核心业务执行抽样读。切流后至少保留一个可以把新侧增量反向同步到旧侧的窗口；一旦新旧两侧都被独立写入且没有双向事实治理，回滚就不再是切开关。

分片键的长期代价大于中间件安装成本。用户维度路由有利于用户订单列表，却会让商家、财务和风控查询跨片；时间维度便于归档，却会制造当前时间热点；租户维度便于隔离，却要处理超级租户。设计时要把最高频在线查询、唯一约束、事务边界、归档方式和扩容单位一起评审。

备份解决复制无法解决的故障

复制会忠实传播 DROP TABLE、错误更新和应用缺陷，也可能把逻辑损坏复制到所有节点。因此 replica 不是备份，Clone 也不是备份。恢复链应该由可独立保存的基础备份和基础备份后的连续 binlog 组成。

逻辑备份与恢复验证

小库或按 schema 迁移可以使用 mysqldump：

mysqldump \
  -h 127.0.0.1 -P 13306 \
  -u backup_user -p \
  --single-transaction \
  --routines --triggers --events \
  --set-gtid-purged=OFF \
  --databases ha_lab \
  > ha_lab-base.sql

--single-transaction 为 InnoDB 建立一致性快照，不保证非事务表的一致性，备份期间的 DDL 仍可能破坏结果。大数据集应评估 MySQL Shell dump/load 的并行、压缩与加载能力，不能默认单线程 SQL 文件满足 RTO。

备份完成后记录：

SHOW BINARY LOG STATUS;
SELECT @@GLOBAL.gtid_executed;

把备份文件、binlog 文件、位点、GTID 集、Server series、字符集、加密密钥标识和校验和写入同一份清单。密码和密钥本体不进入清单。

新建隔离恢复实例，而不是在原库试恢复：

mysql -h 127.0.0.1 -P 13308 -u restore_user -p < ha_lab-base.sql
mysql -h 127.0.0.1 -P 13308 -u verify_user -p \
  -e "SELECT COUNT(*), MIN(id), MAX(id) FROM ha_lab.order_event"

命令退出码为零只证明客户端没有报告致命错误。验收还要比较表结构、行数、关键聚合、抽样业务对象、账号与 DEFINER、事件调度状态，并让应用冒烟测试连接隔离实例。

物理备份的真正成本在恢复路径

大库通常需要物理备份。MySQL 官方的 MySQL Enterprise Backup 属于商业许可产品；Percona XtraBackup 是 Percona 维护的第三方工具。两者的命令、支持的 Server series、加密、增量链与 prepare 语义必须按采用版本分别验证。

物理恢复一般经历“备份文件校验、prepare、停止目标实例、清空或替换 datadir、copy-back、修复属主权限、启动、崩溃恢复、业务校验”。容量预算至少包含压缩备份、解压目录、prepare 临时空间和目标 datadir，不能只按源库数据量准备一份磁盘。

恢复耗时还包含对象存储下载、解密、解压、文件复制、InnoDB recovery、binlog 回放和应用验证。只测备份吞吐、不测完整恢复，会系统性低估 RTO。

用 binlog 完成 PITR

制造一个可控误删场景：基础备份后写入两条合法事件，再记录误删前位置，然后删除一条：

INSERT INTO ha_lab.order_event(id,event_name) VALUES (9001,'AFTER_BASE_1');
INSERT INTO ha_lab.order_event(id,event_name) VALUES (9002,'AFTER_BASE_2');
SHOW BINARY LOG STATUS;
DELETE FROM ha_lab.order_event WHERE id=9001;

先把相关 binlog 拷贝到只读证据目录，用 verbose 输出定位 DELETE 事件前的安全位点：

mysqlbinlog --base64-output=DECODE-ROWS --verbose \
  binlog.000010 binlog.000011 > inspected-binlog.sql

在隔离实例恢复基础备份后，按文件顺序、同一个连接回放到安全位点：

mysqlbinlog \
  --start-position=<BASE_BACKUP_END_POSITION> \
  --stop-position=<LAST_SAFE_POSITION> \
  binlog.000010 binlog.000011 \
  | mysql --binary-mode -h 127.0.0.1 -P 13308 -u restore_user -p

--start-position 作用于第一个文件，--stop-position 作用于最后一个文件；多文件必须按顺序交给同一 MySQL 连接。时间参数适合先缩小检索窗口，最终回放优先使用事件位置，避免时区和相邻事务边界误判。

验证：

SELECT * FROM ha_lab.order_event WHERE id IN (9001,9002) ORDER BY id;
SELECT COUNT(*), MIN(id), MAX(id) FROM ha_lab.order_event;
CHECKSUM TABLE ha_lab.order_event;

预期 9001 与 9002 都存在，误删事件没有被执行。然后选择最小影响修复方式：从隔离实例导回缺失行、替换分区、整库回切或由业务补偿。未经审查不要把整个恢复库覆盖生产库。

binlog 归档本身也是一条生产流水线。mysqlbinlog --read-from-remote-server --raw --stop-never 的持续连接中断后不会自动替团队补齐缺口；必须监控最后归档文件、最后事件位置、归档延迟、文件校验和、对象存储写入和保留策略。

用演练把 RPO 和 RTO 变成团队事实

一次有效演练要选择明确故障，记录 T0 前后状态，而不是只证明脚本能执行：

演练	注入方式	数据证据	服务证据	恢复后检查
source 进程退出	非优雅停止 PRIMARY	最后业务序号、候选 GTID 差集	连接错误、Router/ProxySQL 后端变化	旧主 fencing、重入、幂等重试
副本持续延迟	delayed replication 或阻塞 applier	relay log、worker 状态、延迟趋势	读请求是否降级到主库	追平时间、主库是否被冲垮
quorum 丢失	隔离多数 Group Replication 成员	组视图、成员状态、GTID	写入口是否停止	恢复 quorum 的授权与审计
误删数据	删除实验表或行	基础备份位点、binlog 安全位点	恢复实例可用时间	行级校验、业务冒烟、修复方式
代理不可用	停止一个 Router/ProxySQL	数据库成员仍健康	客户端入口是否有冗余	连接池恢复、旁路权限回收

RPO 用“故障前最后确认的业务序号减去恢复后最大连续序号”计算；RTO 从监控首次确认故障开始，到业务探针连续成功且数据校验通过结束。演示阈值不能直接带入生产，目标必须绑定业务 SLO、容量和人力值守能力。

演练账号、脚本和日志也属于敏感资产。故障注入必须限制环境和时间窗，命令打印目标集群、账号、变更单与回滚入口；恢复日志要脱敏连接串、密码、证书路径和业务数据，避免事故处理再次造成泄露。

云托管省掉机器运维，不会替团队做架构判断

云数据库通常把主备、备份、监控、补丁和 endpoint 包装成服务，但不同厂商对参数、SUPER 级权限、插件、binlog 保留、跨区容灾、备份导出和故障切换的实现不同。不能把自建命令原样套进托管实例，也不能把产品页上的 SLA 当成业务 RTO。

迁入前应实际验证：

私网、DNS、TLS 与证书轮换怎样接入，安全组和白名单由谁审批。应用、迁移、只读、备份和审计账号能获得哪些权限，哪些高权限永远不可用。参数组修改是否重启，回滚是否保留旧值，版本升级能否灰度。

只读节点的延迟如何暴露，写后读是否支持一致性入口。自动备份能否恢复到隔离实例，PITR 粒度、保留期和跨账号复制是否满足目标。主备切换时 endpoint、旧连接、事务和连接池如何表现。

能否导出逻辑与物理数据，退出云服务时需要多久、多少网络费用和停机窗口。

成本模型至少包含实例计算、存储、IO、备份超额、跨区复制、跨地域流量、只读节点、代理、监控日志和长期归档。Serverless 的弹性不能消除连接风暴、冷启动、最大容量和费用上限；预留实例的折扣也不能掩盖闲置副本。团队应按业务单元追踪“每万次事务成本”或“每租户数据库成本”，而不是只看总账单。

生产治理要同时约束数据、控制面和人

权限与凭证分层

身份	权限方向	禁止事项
应用写账号	指定 schema 的最小 DML	不授予 DDL、复制、管理权限
应用只读账号	指定表或视图的 SELECT	不与写账号共用密码
复制账号	复制连接与必要 TLS	不作为应用或运维登录账号
代理监控账号	健康、只读状态、复制延迟所需最小权限	不持有业务写权限
备份账号	备份工具所需权限	不允许从个人电脑任意下载备份
切换控制面	只读切换、复制重配、成员管理	必须短时授权、审批并审计

凭证进入密钥管理系统，通过工作负载身份或短期令牌下发；Compose 和示例中的占位符不能被真实值替换后提交。轮换复制或 Router 账号前，要验证新旧凭证并存窗口、连接重建和失败回滚，避免“安全轮换”变成全量中断。

容量与长期维护

复制和 HA 会放大资源需求。每个副本都要承受 binlog 接收、relay log、应用线程、备份与查询负载；Router 和 ProxySQL 还增加连接、监控与日志。容量评审至少保留：

source 峰值 TPS、事务大小与 binlog 生成速率。replica 应用速率、最大可追赶窗口与慢查询负载。binlog、relay log、备份、prepare 和恢复实例的磁盘预算。

代理连接上限、应用连接池总和、后端连接复用和突发重连。分片数据倾斜、最大租户、跨片查询和迁移带宽。

版本升级不能逐节点“随便滚”。先核对 Server、Shell、Router、Connector、ProxySQL、备份工具与中间件兼容矩阵，再在副本或新节点完成备份恢复、复制、切换、回滚和性能基线验证。MySQL Shell、Router 与 Connector 的版本号不能简单等同于 MySQL Server 的 LTS/Innovation 发布轨道。

把异常现象连接到第一判断

现象	第一证据	常见原因	安全动作
`Replica_IO_Running: No`	`Last_IO_Error`、网络与 TLS	密码轮换、证书、DNS、账号来源限制	修连接，不跳 GTID
`Replica_SQL_Running: No`	`Last_SQL_Error`、worker 状态	schema 漂移、主键冲突、数据分叉	识别冲突事务，不连续跳错
延迟持续增长	relay log、worker queue、source TPS	大事务、慢副本、报表、网络	隔离报表、拆事务、扩应用并行度
Router 切换后仍报错	客户端连接与重连日志	旧连接已断、连接池未重建	有限重连并用幂等键查结果
ProxySQL 读到旧数据	rule hits、后端身份、lag log	泛化规则、阈值过大、监控失败	核心读回主库，收窄白名单
节点无法重入 Cluster	GTID 差集、recovery 日志	日志已清理、errant GTID、配置不一致	Clone/重建或人工处理分叉
PITR 缺数据	备份结束位点、binlog 连续性	归档缺口、起止位置错误	停止覆盖证据，重新构造恢复链

上线前最后走一遍证据链

每个实例都有唯一 server_id、server_uuid、明确发行线和受控配置。GTID 主从的正向写入、错误密码、延迟和追平实验均有状态证据。半同步的启用、超时退化和恢复行为已经测量，不把它称为强一致。

切换 runbook 明确写流冻结、候选判断、旧主 fencing、入口切换、客户端重连和旧主重入。InnoDB Cluster 已演练 PRIMARY 故障、Router 断连重连、节点 rejoin 和 quorum 边界。MHA、PXC 的第三方维护、兼容、许可、支持和升级责任已经单独评估。

ProxySQL 默认走主库，只对白名单查询读副本，并验证延迟超限后的降级。两库两表实验验证了精准路由、广播查询、schema 漂移和清理回滚。扩容方案包含全量、增量、校验、灰度切流、反向同步和终止条件。

逻辑或物理备份已经恢复到隔离实例，binlog 归档连续且 PITR 成功避开误操作。RPO、RTO 来自故障演练和业务序号，不来自架构名称或产品宣传。应用、复制、代理、备份和控制面账号分离，凭证可轮换、下载可审计。

云托管的权限、网络、切换、备份导出、退出路径与完整成本已经验证。容量预算覆盖副本追赶、binlog、备份 prepare、代理重连和分片迁移峰值。

高可用不是“数据库永远不报错”，而是在错误发生时只有一个可写事实源，客户端知道如何面对结果未知，团队能用 GTID 和业务不变量解释数据去向，并能在受控时间内从可验证的备份链恢复。做到这一点，复制、代理、分片和云服务才真正成为效率工具，而不是把事故推迟到更复杂的拓扑中。