文章

分库分表

发表于
作者 月牙坠 17 分钟阅读

分库分表数据迁移手册（老库 → 2库×4表）

目标：把 catfun_trade（单库）的存量数据迁到 catfun_trade_0 / catfun_trade_1（2 物理库 × 每库 4 分表），并把业务读写从老库切到分片库，全程尽量在线、可回退。

分片规则（应用端、迁移脚本、canal 消费端三方必须一致）：

  • 库 = MOD(CRC32(user_id), 2) → catfun_trade_0 / catfun_trade_1
  • 表 = MOD(FLOOR(CRC32(user_id) / 2), 4) → *_0 ~ *_3
  • 哈希用 MySQL 的 CRC32，与 ShardingSphereConfig 对齐，避免”应用路由到 A、数据落 B”

0. 拓扑

1
2
3
4
5
6
7
8
9

老库 catfun_trade (192.168.139.172:3306)   ← 迁移前唯一真相，业务还在写
        │
        ├─ 全量: catfun_sharding_migrate.sh 按 dump 拆行路由导入
        │
        ├─ 增量: canal 正向实例(订阅老库) ──TCP 11111──> 消费端(按分片规则落库)
        │
分片库 catfun_trade_0 / catfun_trade_1  ← 迁移目标，切写后成为唯一真相
        │
        └─ 回退保底: canal 反向实例(订阅分片库) ──> 写回老库（可选）
  • 分片表（7 张，有 user_id）：cf_order cf_order_item cf_cart cf_coupon_user cf_payment cf_refund cf_subscription
  • 广播表（1 张，无 user_id，每库全量一份）：cf_coupon（不进分片路由）

1. 步骤总览（执行顺序即依赖顺序）

1
2
3
4
5
6
7
8
9
10
11

0  前置检查与授权        binlog 设置、canal 账号（老库+分片库双侧授权）
1  建分片库表            catfun_sharding_create.sh
2  部署 canal            deploy/infra/canal 已有资产（admin 纳管 + server）
3  全量 dump（拿位点）    mysqldump --master-data=2：快照 + 同源位点一次拿到，位点写在 dump 头注释
4  钉正向 canal 位点      位点 = 第 3 步 dump 头注释坐标，启动实例攒增量（此时先不开消费端）
5  全量导入（迁移）       把 dump 数据拆行路由导入分片库（catfun_sharding_migrate.sh，与位点同源、零重叠）
6  配置反向 canal 位点    全量导入后分片库有了数据，启动反向 Canal 实例（订阅分片库，提前在位，此时不开反向 Client）
7  增量追平              正向 Client 拉正向 canal，补位点之后的流水
8  对账                  count 全表 + 抽样校验，确认 老库 = 分片库
9  切写                  Nacos 开关热切，正向 Client 继续跑 → 停正向 Client → 启动反向 Client
10 验证与收尾            业务验证、观察窗口、关停/保留

顺序红线：位点必须与 dump 快照同源 —— 先 dump（--master-data=2），位点取 dump 头注释里的坐标，再配 canal、再导入。 反过来（手工钉一个与 dump 不同步的位点）会踩两个坑：

  • 位点早于 dump 快照 → canal 从旧位点重放，dump 快照已含那些写入 → 与全量重叠；
  • 位点晚于 dump 快照 → [dump, 位点] 这段既不在 dump 里、canal 也不拉 → 丢增量。 mysqldump 没有”dump 到某位点为止”的选项，它只导执行那一刻的一致快照，所以只能让位点跟着 dump 走，而非 dump 跟着位点走。

2. 详细步骤

第 0 步 前置检查与授权

检查 binlog 设置（老库 + 分片库两侧都查）

1

SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','binlog_row_image');

期望：log_bin=ON、binlog_format=ROW、binlog_row_image=FULL。

  • 5.7 默认 binlog_format=STATEMENT，没显式配过就是它，必须改（[mysqld] 加两行后重启）。
  • 改动后历史 binlog 里非 ROW 段解析不了：改完从新位点开订阅，别追历史。

canal 账号授权（老库 + 每个分片库都要）

1
2
3
4
5
6
7

CREATE USER IF NOT EXISTS 'canal'@'%' IDENTIFIED BY 'CatFun@0614';
GRANT SELECT ON catfun_trade.*    TO 'canal'@'%';
GRANT SELECT ON catfun_trade_0.*  TO 'canal'@'%';
GRANT SELECT ON catfun_trade_1.*  TO 'canal'@'%';
-- 复制权限是全局的，只能 ON *.*，按库授不生效
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
FLUSH PRIVILEGES;

验证（用 canal 账号连老库执行，MySQL 8.4+ 用 SHOW BINARY LOG STATUS，8.0- 用 SHOW MASTER STATUS）：

1
2
3
4

# MYSQL_PWD 环境变量传密码，避免命令行明文告警
export MYSQL_PWD='CatFun@0614'
mysql -h192.168.139.172 -ucanal -e "SHOW BINARY LOG STATUS;"        # 验证 REPLICATION（能查到位点即 OK）
mysql -h192.168.139.172 -ucanal -e "SELECT 1 FROM catfun_trade_1.cf_order_0 LIMIT 1;"  # 验证分片库 SELECT

检查待分片表是否都包含分片键 user_id（缺列表先补全，再进第 1 步）

分片路由依赖 user_id，凡缺该列的表，全量 dump、增量 binlog 都无法路由。老库侧逐表核查：

1
2
3
4
5
6
7
8
9

SELECT t.TABLE_NAME
FROM (
  SELECT 'cf_order' AS TABLE_NAME UNION ALL SELECT 'cf_order_item' UNION ALL
  SELECT 'cf_cart' UNION ALL SELECT 'cf_coupon_user' UNION ALL SELECT 'cf_payment' UNION ALL
  SELECT 'cf_refund' UNION ALL SELECT 'cf_subscription'
) t
LEFT JOIN information_schema.COLUMNS c
       ON c.TABLE_SCHEMA = 'catfun_trade' AND c.TABLE_NAME = t.TABLE_NAME AND c.COLUMN_NAME = 'user_id'
WHERE c.COLUMN_NAME IS NULL;

结果为空 → 7 张分片表全部自带分片键，直接进入第 1 步。

第 1 步 建分片库表

1

bash catfun-server/scripts/catfun_sharding_create.sh

产出：catfun_trade_0/catfun_trade_1 各 29 张表（28 分片 + 1 广播 cf_coupon）。

第 2 步 部署 canal

已有资产：catfun-server/deploy/infra/canal/（admin 纳管 + server 自愈）。

1

cd catfun-server/deploy/infra && bash deploy.sh canal

第 3 步 全量 dump（快照 + 同源位点）

先跑 dump：数据与位点在同一瞬间取得，dump 头注释里的坐标就是下一步 canal 的起点。

注：MySQL 8.4/8.4+ 用 –source-data=2 SOURCE_LOG，之前的版本用 –master-data=2 MASTER_LOG

1
2
3
4
5
6
7
8
9
10
11
12
13

# 用 root 连老库执行
docker run --rm \
  -e MYSQL_PWD='CatFun@0614' \
  --entrypoint mysqldump \
  mysql:8.4 \
  -h192.168.139.172 -uroot \
  --single-transaction --complete-insert --source-data=2 --set-gtid-purged=OFF \
  --databases catfun_trade \
  > catfun-server/scripts/full_dump.sql

# 位点写在 dump 文件头部注释里，读出来填给 canal：
grep 'SOURCE_LOG' catfun-server/scripts/full_dump.sql
# → -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000039', SOURCE_LOG_POS=651314294;
  • --complete-insert：带显式列名的 INSERT，必须加。
  • --source-data=2 保证快照与位点同源。
  • --single-transaction 期间别跑 DDL。

第 4 步 配置正向 canal 位点并启动

把第 3 步 grep 到的坐标填进正向实例。不要沿用之前手工钉的旧坐标（如 binlog.000038:80725739）：旧位点早于 dump 快照，canal 会重放 dump 已含的写入 → 与全量重叠。

canal_forward.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#################################################
## 1. 连接信息（源 = 老库）
#################################################
# 模拟的 MySQL slaveId，全局唯一
canal.instance.mysql.slaveId = 20
canal.instance.master.address = 192.168.139.172:3306
canal.instance.dbUsername = canal
canal.instance.dbPassword = CatFun@0614
canal.instance.connectionCharset = UTF-8
canal.instance.defaultDatabaseName = catfun_trade

#################################################
## 2. 位点（值 = 第 3 步 dump 头注释的坐标）
#################################################
canal.instance.master.journal.name = binlog.000039
canal.instance.master.position = 651314294
canal.instance.master.timestamp =

#################################################
## 3. 订阅过滤
#################################################
canal.instance.filter.regex = catfun_trade\\.(.*)
canal.instance.filter.black.regex =

创建启动正向实例。要点：

  • 实例从该坐标开始拉 binlog 攒增量，内部位置自行推进，与消费端开没开无关。
  • 此时先不开消费端：全新 client 首连无历史消费位点，会从缓冲积压的最早事件开始消费。
  • 实例内存缓冲积压上限约 1.6 万条事件，全量导入期间积压超限时 parser 会阻塞等消费——只是延迟，不丢。
  • 若该实例之前跑过（存在持久化断点 meta），改 journal.name/position 重启， 不会生效：直接重建实例，让它把新坐标当起点。

第 5 步 全量导入（把 dump 数据落进分片库）

dump 拆行路由导入（唯一方式）：把第 3 步的 catfun-server/scripts/full_dump.sql 按行解析， 用 MOD(CRC32(user_id),2) / MOD(FLOOR(CRC32(user_id)/2),4) 路由到 catfun_trade_0/1 的 _0~_3。 内容 = 第 4 步位点那一瞬间的快照，与 canal 起点完全同源：全量 = 位点前，canal = 位点后，不重叠、无需幂等兜底。

1
2
3
4

bash catfun-server/scripts/catfun_sharding_migrate.sh
#   ├─ 导入前强制清空 56 张分片目标表（保证重跑幂等）
#   ├─ 只想先解析+路由统计、不连库：--dry-run
#   └─ 执行经 docker mysql:8.4（需已拉取镜像），连接参数见 catfun_sharding_dump_load.py

实现要点（catfun_sharding_dump_load.py）：

  • 7 张分片表均自带 user_id（第 0 步前置检查保证），从 INSERT 声明列中取出该列直接路由。
  • 值组按 INSERT 声明的列序解析并与 CREATE 校验一致，字段数逐组校验防错位。
  • 分片键哈希与应用端一致（CRC32 按 UTF-8 字节，与 Java/MySQL 对齐），库、表两级规则与 canal 消费端三方一致。
  • 落库攒批（约 4000 行/条 INSERT），脚本末尾自动对账：dump 行数 vs 各分片表合计，应全部 OK。

第 6 步 配置反向 canal 位点并启动

此时不启动反向 Client（不消费、不回写老库）。

反向实例运行期间，正向 Client 写入分片库的 binlog 会被反向实例拉到缓冲里积压。切写后启动反向 Client 时，它会先消费这些积压（幂等回灌，数据本就在老库），再追到切写后的业务写入。

canal_reverse.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#################################################
## 1. 连接信息（源 = 分片库）
#################################################
canal.instance.mysql.slaveId = 21
canal.instance.master.address = 192.168.139.172:3306
canal.instance.dbUsername = canal
canal.instance.dbPassword = CatFun@0614
canal.instance.connectionCharset = UTF-8
canal.instance.defaultDatabaseName = catfun_trade_0

#################################################
## 2. 位点（从分片库当前最新位点开始）
#################################################
# 留空 = 启动时自动取分片库当前最新位点
canal.instance.master.journal.name =
canal.instance.master.position =
canal.instance.master.timestamp =

#################################################
## 3. 订阅过滤（分片库全库）
#################################################
canal.instance.filter.regex = catfun_trade_0\\.(.*),catfun_trade_1\\.(.*)
canal.instance.filter.black.regex =

要点：

  • 反向实例 slaveId 必须与正向（20）和 MySQL server-id 区分，用 21。
  • 位点留空，启动时自动取分片库当前最新位点。

第 7 步 增量追平

第 4 步的实例已从 dump 位点持续攒增量，跑正向 Client 把它落进分片库。实现为 catfun-server/catfun-canal-replay（纯 main 的可执行 jar）：

1
2
3

cd catfun-server/catfun-canal-replay/target/
mvn -DskipTests package
java -Dcom.google.protobuf.use_unsafe_pre22_gencode -jar catfun-canal-replay-1.0.0-SNAPSHOT.jar

运行逻辑（重放第 4 步位点之后的增量，与第 5 步 dump 同源无重叠）：connect → subscribe（沿用实例过滤 catfun_trade\.(.*)）→ rollback 从积压最老开始消费 → getWithoutAck 拉批逐条处理，批成功才 ack、异常整批 rollback 等重发：

  • 7 张分片表按行内 user_id 路由（CRC32 分库分表，与全量/应用三方一致），落 catfun_trade_0/1 的 _0~_3；
  • cf_coupon 广播：同一条 DML 写两个分片库各一份；
  • INSERT/UPDATE → UPSERT 全列覆盖、DELETE → 全列等值删除：幂等，重放任意次结果一致；
  • 老库其余表跳过，DDL 不重放只告警。

追平判据（见 CanalReplayForwardMain）：每批处理完后算 lag = 当前时间 − 批内最大 executeTime。lag < 5s 或空批连续 3 轮 → 打出 “已追平”，消费端继续跑不退出。

日志中看到 “已追平” 即可进入第 8 步对账。

第 8 步 对账

1
2
3
4
5

bash catfun-server/scripts/catfun_sharding_verify.sh
#   ├─ 分片表对账：老库每表总数 vs 分片 2 库 × 4 表合计
#   ├─ 广播表对账：老库 vs 分片两库各自相等
#   └─ 抽样行校验：随机取 user_id 比对老库整行 MD5 vs 分片表整行 MD5（防行数对但数据错位）
# 用 -n 指定每表抽样数（默认 10），如：catfun_sharding_verify.sh -n 20
  • 总量相等只是必要不充分，抽样行校验能发现”行数对但数据错位”的问题。
  • 对账通过且追平后，才允许进入切写。

第 9 步 切写

Nacos 开关热切，无需重启。Nacos 控制台 dataId catfun-trade-dev.yml 发布：

1
2
3
4

catfun:
  trade:
    datasource:
      active: sharding

回退改回 legacy。Nacos 多实例下发有秒级窗口，安排在低峰切。

正向 Client 与反向 Client 不能同时运行：正向订阅老库写分片库，反向订阅分片库写老库，同时运行会循环复制且存在版本覆盖风险。但 Canal 实例可以提前启动在位，Client（TCP 消费端）才是真正写入的一方。

完整启停顺序：正向 Client 追平 → 对账 → Nacos 切写（正向 Client 继续跑）→ 等 Nacos 下发完成、确认老库无新写入 → 停正向 Client → 启动反向 Client → 观察期结束全停。

回退顺序：反向 Client 追平 → 对账 → Nacos 切回 legacy（反向 Client 继续跑）→ 等 Nacos 下发完成、确认分片库无新写入 → 停反向 Client。

第 10 步 验证与收尾

  • 业务走查：下单/查单等核心链路各跑一遍，确认读写命中分片（看 ShardingSphere SQL_SHOW 路由日志 ds0/ds1）。
  • 观察窗口（建议 24~48h）：无报错、对账仍平、无回退诉求 → 停反向 Client + 停反向 Canal 实例 + 停正向 Canal 实例。
微服务
分库分表
本文由作者按照 CC BY 4.0 进行授权