MySQL Router 实现读写分离

1. 读写分离不该由 DNS 来做

用 DNS 做读写分离是个流传很广的方案：健康检查脚本判断主从状态，正常就让读写两个域名解析到对应 IP，异常就摘掉。听上去自洽，但它有两个绕不过去的硬伤。

第一，DNS 有缓存，摘不干净。 应用侧的 JVM、libc、连接池各有各的缓存策略。主库挂掉、域名记录已经撤掉之后，客户端仍可能拿着旧解析结果继续连过去，直到 TTL 过期。TTL 设得再短也只是缩短窗口，不能消除。

第二，DNS 只能决定「连到哪」，不能决定「已经连上的怎么办」。 已建立的连接不会因为域名记录变化而断开。主从切换后，那些握着旧连接的会话还在往老主库写。

MySQL Router 是官方给出的答案：它是一个位于应用与数据库之间的轻量代理，直接读取集群元数据来感知拓扑，切换时主动断开失效连接，不依赖任何 DNS 缓存行为。

2. Router 解决了什么

Router 的定位是无状态的路由层，通常和应用部署在同一台机器（或同一个 Pod），应用连本地的 Router，Router 负责把请求送到正确的实例。

它比 DNS 方案强的地方在于：

• 拓扑来自元数据，不是猜的。接入 InnoDB Cluster / ReplicaSet 后，Router 从集群元数据里读到谁是 PRIMARY、谁是 SECONDARY，主从切换后自动跟随，不需要你写健康检查脚本。
• 故障时主动断连。目标实例不可用时 Router 会将其隔离（quarantine），并断开指向它的连接，让应用的连接池立刻重连到新的正确实例，而不是卡在 TCP 超时上。
• 无状态、可水平铺开。Router 本身不存数据，挂了重启即可；每台应用机器跑一个实例就不存在单点。

代价也要说清楚：多了一跳网络转发。Router 与应用同机部署时这一跳走 loopback，开销很小；如果你把 Router 集中部署成一个「中间层集群」，就等于自己造了一个新的单点和新的延迟来源，不建议。

3. 部署与配置

3.1 两种读写分离模式

这是选型时最需要先搞清楚的一点，两种模式的适用场景完全不同：

模式	版本要求	工作方式	应用改造
双端口（连接级）	8.0 起	6446 写、6447 读，两个端口分别指向 PRIMARY 和 SECONDARY	应用需配置两个数据源，自己决定走哪个
单端口自动分流（语句级）	8.2 起	单个端口，Router 解析语句判断读写并自动分流	应用只连一个端口，无需区分

双端口模式是长期以来的标准做法，稳定、行为可预测，但读写分流的判断责任在应用侧——你得在代码或框架里配好两个数据源。

单端口自动分流是 MySQL Router 8.2 引入的能力，Router 自己判断语句是读还是写。它省掉了应用改造，但引入了新的一致性问题（见 4.3）。如果你在 8.0 LTS 上，只有双端口这一个选择。

先确认版本：

mysqlrouter --version

预期输出：

MySQL Router  Ver 8.4.0 for Linux on x86_64 (MySQL Community - GPL)

3.2 前置条件

Router 的自动路由依赖集群元数据，所以后端必须是下列之一：

• InnoDB Cluster（基于 MGR，支持自动选主）——见innodb cluster安装
• InnoDB ReplicaSet（基于异步复制，手动切换）——见MySQL ReplicaSet 安装

如果你的后端是裸的异步主从、没有 MySQL Shell 纳管，Router 也能用，但只能配静态路由（见 3.6），会失去自动跟随切换的能力。

3.3 bootstrap 自动生成配置

不要手写配置文件，用 --bootstrap 让 Router 连上集群、读取元数据并生成一份完整配置：

mysqlrouter --bootstrap icadmin@mysql-node1:3306 \
            --directory /opt/myrouter \
            --conf-use-sockets \
            --account router_app \
            --user mysqlrouter

参数说明：

• --bootstrap <user>@<host>:<port>：任意一个集群成员即可，Router 会自己发现其余节点
• --directory：生成独立的自包含目录，便于一机多实例
• --account：Router 运行时使用的专用账号（与 bootstrap 账号分开，权限更小）
• --user：Router 进程运行的系统用户

预期输出（节选）：

# MySQL Router configured for the InnoDB Cluster 'myCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /opt/myrouter/mysqlrouter.conf

InnoDB Cluster 'myCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

记住这四个端口，它们是默认约定：6446 读写、6447 只读（Classic 协议），6448/6449 是对应的 X 协议端口。

3.4 生成的配置长什么样

/opt/myrouter/mysqlrouter.conf 的核心是两个 [routing] 段：

[metadata_cache:myCluster]
cluster_type=gr
router_id=1
user=router_app
metadata_cluster=myCluster
ttl=0.5

[routing:myCluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://myCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:myCluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://myCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

几个关键字段：

• destinations=metadata-cache://...?role=PRIMARY|SECONDARY —— 目标不是写死的 IP，而是角色，这正是它能跟随切换的原因
• routing_strategy —— first-available（按顺序取第一个可用，写端口用）、round-robin（轮询）、round-robin-with-fallback（轮询从库，全挂时回退到主库）
• ttl —— 元数据刷新间隔，默认 0.5 秒，决定了感知拓扑变化的最大延迟

生产环境建议把 bind_address 收紧为 127.0.0.1（Router 与应用同机时），避免把路由端口暴露到网络上。

3.5 启动与验证

/opt/myrouter/start.sh          # bootstrap 生成的启动脚本
# 或注册为服务后
systemctl start mysqlrouter

验证读写端口是否真的指向了不同角色——分别连两个端口查 @@hostname：

mysql -h 127.0.0.1 -P 6446 -u app_user -p -e "SELECT @@hostname, @@read_only;"
mysql -h 127.0.0.1 -P 6447 -u app_user -p -e "SELECT @@hostname, @@read_only;"

预期输出（6446 落在主库，read_only=0）：

+-------------+-------------+
| @@hostname  | @@read_only |
+-------------+-------------+
| mysql-node1 |           0 |
+-------------+-------------+

+-------------+-------------+
| @@hostname  | @@read_only |
+-------------+-------------+
| mysql-node2 |           1 |
+-------------+-------------+

多连几次 6447，@@hostname 应该在各个从库之间轮换，说明 round-robin 生效。

查看 Router 自己的运行状态：

-- 连到任意集群节点
SELECT * FROM performance_schema.replication_group_members;

3.6 没有集群元数据时的静态路由

后端是裸异步主从、没有 InnoDB Cluster 元数据时，只能写死地址：

[routing:static_rw]
bind_address=127.0.0.1
bind_port=6446
destinations=192.0.2.11:3306
routing_strategy=first-available

[routing:static_ro]
bind_address=127.0.0.1
bind_port=6447
destinations=192.0.2.12:3306,192.0.2.13:3306
routing_strategy=round-robin

必须清楚这种模式的局限：Router 只做端口存活探测，不判断主从角色。主库挂了它不会把 6446 指向从库，主从切换后需要人工改配置并 reload。它能替你做的只有「从库轮询 + 摘掉连不上的节点」。真要自动切换，还是得上 InnoDB Cluster 或 ReplicaSet。

3.7 单端口自动分流（8.2+）

如果版本满足且不想改应用，可以配一个 access_mode=auto 的路由段：

[routing:myCluster_rw_split]
bind_address=127.0.0.1
bind_port=6450
destinations=metadata-cache://myCluster/?role=PRIMARY_AND_SECONDARY
routing_strategy=round-robin
access_mode=auto
protocol=classic

Router 会把写语句和显式事务送往 PRIMARY，把只读语句送往 SECONDARY。验证时注意要在同一个连接里观察：

SELECT @@hostname;                     -- 从库
BEGIN; SELECT @@hostname; COMMIT;      -- 主库（显式事务一律走主）

4. 四个高频坑

4.1 bootstrap 用的账号权限不足

症状：--bootstrap 报错 Access denied 或提示无法读取 metadata schema。

原因：bootstrap 需要读取并写入 mysql_innodb_cluster_metadata，权限要求高于普通应用账号；而 --account 指定的运行账号权限则要小得多。两者混用会在某一侧失败。

解药：bootstrap 用集群管理账号（如 InnoDB Cluster 的 icadmin），运行账号用 --account 单独指定，让 Router 自己去创建并授权。不要把运行账号也给成管理员。

4.2 应用连了 6447 却在写，报只读错误

症状：偶发 ERROR 1290 (HY000): The MySQL server is running with the --read-only option。

原因：双端口模式下，Router 不检查你从只读端口发来的是不是写语句，它只负责把连接送到 SECONDARY。从库的 super_read_only 挡下了写操作。

解药：这是应用侧的数据源配置问题，把写路径指回 6446。排查时先确认报错连接的目标端口，别一上来怀疑 Router。要想让 Router 替你判断，得用 3.7 的单端口模式。

4.3 自动分流后读到旧数据

症状：启用 access_mode=auto 后，写完立刻查，偶尔查不到刚写的行。

原因：写走 PRIMARY、后续的只读查询被分流到 SECONDARY，而复制存在延迟。这不是 Router 的 bug，是读写分离的固有语义。

解药：需要「写后读一致」的逻辑放进显式事务（BEGIN...COMMIT），Router 会把整个事务留在 PRIMARY。这也是为什么把分流交给 Router 之前，必须先确认应用的事务边界是清晰的——边界模糊的代码换到自动分流上一定会出问题。

4.4 切换后应用长时间卡住

症状：主库故障、集群已完成选主，但应用要等很久才恢复。

原因：多半不在 Router，而在应用的连接池——池里缓存的旧连接没有被及时判定为失效，仍在被反复借出。

解药：连接池开启存活检测（如 HikariCP 的 keepaliveTime、连接最大存活时间），并把 socket 层超时配置好。同时确认 metadata_cache 的 ttl 没被调得过大（默认 0.5 秒已经够快）。

5. 可复用要点

1. 读写分离要放在连接层，不要放在 DNS。DNS 有缓存、且管不了已建立的连接，这两个问题无法通过调小 TTL 解决。
2. Router 与应用同机部署。它是无状态的，跟着应用走才不会引入新的单点；集中式部署等于自己造了个中间层单点。
3. 路由目标写角色，不写 IP。role=PRIMARY 这类元数据路由才是自动跟随切换的前提；静态路由只能做存活探测，切换仍需人工介入。
4. 先看版本再决定分流方式。8.0 LTS 只有双端口（分流责任在应用），单端口自动分流要 8.2 以上。
5. 自动分流的前提是事务边界清晰。依赖「写后立即读」的逻辑必须包在显式事务里，否则一定会读到复制延迟中的旧数据。

6. 延伸阅读

• innodb cluster安装——Router 自动路由所依赖的集群与元数据
• MySQL ReplicaSet 安装——异步复制版的纳管方案
• MySQL Replicaset 自动切换主从——切换行为与 Router 的配合
• MySQL8 配置文件 my.cnf 重要参数解读——super_read_only、server_id 等相关参数

{
  "topic": "使用 MySQL Router 实现读写分离",
  "component": "MySQL Router",
  "why_not_dns": [
    "客户端与 libc/JVM 层 DNS 缓存导致摘除不干净，TTL 只能缩短窗口",
    "DNS 无法影响已建立的连接，切换后旧连接仍写向老主库"
  ],
  "backend_requirements": {
    "auto_routing": ["InnoDB Cluster (MGR)", "InnoDB ReplicaSet"],
    "static_routing": "裸异步主从亦可，但仅做存活探测，不感知角色"
  },
  "modes": {
    "dual_port": {
      "since": "8.0",
      "rw_port": 6446,
      "ro_port": 6447,
      "x_protocol_ports": [6448, 6449],
      "split_responsibility": "应用侧配置两个数据源"
    },
    "single_port_auto": {
      "since": "8.2",
      "config_key": "access_mode=auto",
      "default_port": 6450,
      "behavior": "写语句与显式事务走 PRIMARY，只读语句走 SECONDARY"
    }
  },
  "bootstrap": {
    "command": "mysqlrouter --bootstrap <admin>@<host>:3306 --directory /opt/myrouter --account <router_user> --user mysqlrouter",
    "note": "bootstrap 账号需集群管理权限；--account 指定的运行账号权限更小，二者分开"
  },
  "key_config": {
    "destinations": "metadata-cache://<cluster>/?role=PRIMARY|SECONDARY|PRIMARY_AND_SECONDARY",
    "routing_strategy": ["first-available", "round-robin", "round-robin-with-fallback"],
    "metadata_cache_ttl": {"default": 0.5, "unit": "second"},
    "bind_address": {"advice": "127.0.0.1，Router 与应用同机部署"}
  },
  "verify": [
    "mysql -h 127.0.0.1 -P 6446 -e 'SELECT @@hostname, @@read_only;'  -- 期望 read_only=0",
    "mysql -h 127.0.0.1 -P 6447 -e 'SELECT @@hostname, @@read_only;'  -- 期望 read_only=1 且多次连接轮换 hostname"
  ],
  "pitfalls": [
    {"symptom": "bootstrap 报 Access denied", "cause": "bootstrap 账号缺少元数据读写权限", "fix": "bootstrap 用集群管理账号，运行账号用 --account 单独指定"},
    {"symptom": "ERROR 1290 read-only", "cause": "双端口模式下 Router 不校验语句读写，写请求发到了 6447", "fix": "应用侧把写路径指向 6446，或改用 access_mode=auto"},
    {"symptom": "自动分流后写完读不到", "cause": "只读语句被分流到有复制延迟的 SECONDARY", "fix": "把写后读逻辑包进显式事务，整个事务留在 PRIMARY"},
    {"symptom": "切换后应用长时间卡住", "cause": "应用连接池缓存了失效连接", "fix": "连接池开启存活检测与最大存活时间，并配置 socket 超时"}
  ],
  "tradeoff": "多一跳网络转发；与应用同机走 loopback 开销可忽略，集中部署会引入新的单点与延迟"
}