单表数据同步方案选型：为什么不该用 mysqldump 做「实时同步」

1. 一条看起来很聪明的同步命令

需要把一张表的数据持续同步到另一个库时，很容易想到这个办法：给表加个 updated_at 自动更新字段，然后定时把最近变更的行导出来灌进目标库。

mysqldump -h <源库> -u <user> -p<password> <db> <table> \
  --where="updated_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)" \
  --replace --no-create-info \
| mysql -h <目标库> -u <user> -p<password> <db>

一条命令搞定，配个 cron 就成了「实时同步」。这个方案在小数据量、低要求的场景下确实能跑，但它有四个结构性缺陷，而且都不是调参能解决的。

它同步不了删除。 updated_at 只能标记「这行被改过」，行被 DELETE 之后就不存在了，查询条件永远选不中它。源库删掉的数据会永久留在目标库，两边越跑越不一致。

它没有一致性快照。 导出过程中源库仍在写入。--where 扫描是逐行进行的，扫到第 100 行时第 10 行可能又被改了，导出的结果不对应任何一个时间点。加 --single-transaction 能解决这个问题，但上面这条命令并没有加。

时间窗口两头都不安全。 窗口开小了，任务失败一次就永久丢数据（下次跑已经不在窗口内）；开大了则每次重复搬运海量数据。NOW() 用的是源库时间，源库和执行机器有时钟偏差时，边界还会漂移。

--replace 会静默覆盖。 它按主键整行替换，目标库上任何本地修改都会被无声抹掉；如果目标表有额外的字段或触发器，行为更难预测。

结论很直接：它是「定时批量补数」，不是同步。把它当同步用，数据一定会漂。

2. 先分清你要的到底是什么

「同步」这个词涵盖了几种诉求完全不同的场景，选错方案的根源往往是没先想清楚这一步：

诉求	延迟要求	要不要删除	合适的方案
灾备 / 只读副本	秒级	要	MySQL 原生复制
跨库、跨实例的表级同步	秒级	要	CDC（解析 binlog）
周期性把数据搬进数仓	小时/天级	通常不要	批量抽取（可用 mysqldump）
一次性迁移	不适用	不适用	mysqldump / mydumper 全量导出

只有最后两行才是 mysqldump 的正确用武之地。前两行必须走 binlog，因为只有 binlog 才完整记录了删除操作和变更顺序。

3. 三种正确做法

3.1 整库/表级持续同步：用原生复制

如果目标是「目标库跟着源库走」，原生复制永远是第一选择——它基于 binlog，天然包含删除，延迟通常在秒级以内，且不需要额外组件。

源库开启 binlog 与 GTID：

[mysqld]
server_id                = 1
log_bin                  = mysql-bin
binlog_format            = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON

只想同步某几张表时，在从库上做过滤（写在从库配置里，不要在主库上用 binlog-do-db，那会让 binlog 本身不完整、影响其他用途）：

[mysqld]
server_id                = 2
replicate-do-table       = mydb.orders
replicate-wild-do-table  = mydb.log_%

建立复制：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.0.2.10',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='<password>',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

验证：

SHOW REPLICA STATUS\G

预期输出（关注这三行）：

        Replica_IO_Running: Yes
       Replica_SQL_Running: Yes
     Seconds_Behind_Source: 0

参数细节见MySQL8 配置文件 my.cnf 重要参数解读，纳管与自动切换见MySQL ReplicaSet 安装。

3.2 异构目标或表级投递：用 CDC

当目标端不是 MySQL（Kafka、ES、数仓），或者只要一张表、且源库不方便挂从库时，用 CDC 工具解析 binlog。常见选择是 Debezium、Canal、Maxwell，原理都是伪装成一个从库去订阅 binlog，然后把变更事件投递出去。

前提条件和复制一样：

-- 确认 binlog 已开启且为 ROW 格式
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

预期输出：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

CDC 账号的最小权限：

CREATE USER 'cdc'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT
  ON *.* TO 'cdc'@'%';

binlog_format 必须是 ROW——STATEMENT 格式记录的是 SQL 语句而非行变更，CDC 无法从中还原出准确的行级前后镜像。

务必确认 binlog 保留时间足够。CDC 消费者停机时间一旦超过 binlog_expire_logs_seconds，所需的 binlog 已被清理，就只能全量重做：

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

3.3 周期性批量抽取：mysqldump 的正确用法

如果确实只需要「每天把数据搬一次到分析库」，mysqldump 是合适的，但要把上一节的四个缺陷逐个补上：

mysqldump -h <源库> -u <user> -p \
  --single-transaction \
  --set-gtid-purged=OFF \
  --no-create-info \
  --skip-add-locks \
  --where="updated_at >= '2026-07-28 00:00:00' AND updated_at < '2026-07-29 00:00:00'" \
  <db> <table> > /tmp/chunk.sql

关键差异：

• --single-transaction：在一个可重复读事务里导出，拿到一致性快照（仅对 InnoDB 有效）
• --where 用左闭右开的固定区间，不用 NOW()。区间由调度器生成并记录，任务失败可以精确重跑同一区间，不会因为时间流逝而丢数据
• --set-gtid-purged=OFF：避免导出文件里带上 GTID 信息，否则导入目标库时会干扰其自身的复制状态

删除仍然同步不了。批量抽取的通行做法是让业务侧改成软删除（打 is_deleted 标记而不是物理删除），这样删除也表现为一次 updated_at 更新，才能被增量条件捕获。如果业务是物理删除，那就只能定期全量比对——这也正说明该场景本就应该用 CDC。

全量迁移场景下，单线程的 mysqldump 在大表上会很慢，可以换 mydumper（多线程导出）或 MySQL Shell 的 util.dumpTables()。逻辑备份的完整用法见MySQLdump逻辑备份。

4. 四个高频坑

4.1 目标库出现源库已删除的数据

症状：跑了几个月后发现目标库行数多于源库，且多出来的都是源库删掉的。

原因：基于 updated_at 的增量条件无法感知 DELETE。

解药：改用复制或 CDC。若必须留在批量方案上，业务侧改软删除；否则只能周期性全量比对修复。

4.2 任务失败一次，那段数据永久丢失

症状：某天的 cron 失败，之后数据一直缺一段。

原因：--where 用了 NOW() 这样的相对时间，窗口随时间滑走，重跑时已经选不到当时的数据。

解药：改用固定的绝对区间，并把区间的处理状态持久化，失败时按区间重跑（幂等）。

4.3 导出的数据自相矛盾

症状：导入后出现外键对不上、父子记录状态不一致。

原因：没加 --single-transaction，导出期间源库仍在写，不同表/不同行取自不同时间点。

解约：加 --single-transaction，且相关联的表要在同一次 mysqldump 命令里一起导出，分成多次导出仍然无法保证互相一致。

4.4 CDC 消费者恢复后报找不到 binlog

症状：Could not find first log file name in binary log index file。

原因：消费者停机时间超过了 binlog 保留期，所需文件已被清理。

解药：把 binlog_expire_logs_seconds 调到大于最坏情况的停机时长，并对消费延迟做监控告警。已经发生时只能全量重新初始化。

5. 可复用要点

1. 同步必须基于 binlog。任何靠时间戳列做的增量方案都同步不了删除，这是原理性缺陷，不是调参问题。
2. 先定义诉求再选方案：秒级且要删除 → 复制或 CDC；小时级且可接受软删除 → 批量抽取；一次性 → 全量导出。
3. 批量抽取要用固定的左闭右开区间，不要用 NOW() 相对窗口，才能保证失败可幂等重跑。
4. mysqldump 做增量必须加 --single-transaction，否则拿不到一致性快照。
5. 表级过滤放在从库，不要用 binlog-do-db 在主库上过滤，那会让 binlog 对其他消费者不完整。

6. 延伸阅读

• MySQLdump逻辑备份——mysqldump 在备份场景下的完整参数
• MySQL ReplicaSet 安装——原生复制的纳管方式
• MySQL8 配置文件 my.cnf 重要参数解读——binlog 与复制相关参数
• MySQL主从跳过异常GITD——复制中断的处理

{
  "topic": "单表数据同步方案选型：mysqldump 增量为何不能当同步用",
  "anti_pattern": {
    "command": "mysqldump --where=\"updated_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)\" --replace --no-create-info | mysql <target>",
    "defects": [
      "无法同步 DELETE：时间戳列选不中已删除的行，目标库数据永久残留",
      "无一致性快照：缺少 --single-transaction，导出期间源库仍在写",
      "相对时间窗口：窗口小则失败即丢数据，大则重复搬运；NOW() 取源库时间会随时钟偏差漂移",
      "--replace 按主键整行覆盖，静默抹掉目标端的本地修改"
    ]
  },
  "decision_matrix": [
    {"need": "灾备/只读副本", "latency": "秒级", "needs_delete": true, "solution": "MySQL 原生复制"},
    {"need": "跨库跨实例表级同步", "latency": "秒级", "needs_delete": true, "solution": "CDC 解析 binlog"},
    {"need": "周期性入仓", "latency": "小时/天级", "needs_delete": false, "solution": "批量抽取，可用 mysqldump"},
    {"need": "一次性迁移", "latency": "n/a", "needs_delete": false, "solution": "mysqldump / mydumper / MySQL Shell 全量导出"}
  ],
  "native_replication": {
    "source_config": {"server_id": 1, "log_bin": "mysql-bin", "binlog_format": "ROW", "gtid_mode": "ON", "enforce_gtid_consistency": "ON"},
    "table_filter": {"where": "从库", "options": ["replicate-do-table", "replicate-wild-do-table"], "avoid": "主库上的 binlog-do-db 会使 binlog 对其他消费者不完整"},
    "setup": "CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA;",
    "verify": ["Replica_IO_Running=Yes", "Replica_SQL_Running=Yes", "Seconds_Behind_Source"]
  },
  "cdc": {
    "tools": ["Debezium", "Canal", "Maxwell"],
    "mechanism": "伪装为从库订阅 binlog",
    "requirements": {"log_bin": "ON", "binlog_format": "ROW"},
    "grants": "SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.*",
    "risk": "消费者停机超过 binlog_expire_logs_seconds 则需全量重做"
  },
  "correct_batch_extract": {
    "flags": ["--single-transaction", "--set-gtid-purged=OFF", "--no-create-info", "--skip-add-locks"],
    "where": "固定左闭右开绝对区间，禁用 NOW()",
    "delete_handling": "业务侧改软删除（is_deleted 标记），否则删除无法被增量捕获"
  },
  "pitfalls": [
    {"symptom": "目标库残留源库已删除的数据", "cause": "时间戳增量无法感知 DELETE", "fix": "改用复制/CDC，或业务改软删除"},
    {"symptom": "任务失败一次后永久缺一段数据", "cause": "NOW() 相对窗口随时间滑走", "fix": "固定绝对区间并持久化处理状态，支持幂等重跑"},
    {"symptom": "导入后数据自相矛盾", "cause": "缺少 --single-transaction", "fix": "加该参数，且关联表在同一次命令内导出"},
    {"symptom": "Could not find first log file name in binary log index file", "cause": "停机超过 binlog 保留期", "fix": "调大 binlog_expire_logs_seconds 并监控消费延迟"}
  ]
}