mysql在多数据库迁移中的数据同步与分布式架构

MySQL多库迁移主键冲突需设AUTO_INCREMENT偏移、停写源库或用ON DUPLICATE KEY UPDATE；GTID同步须开启一致性校验；分库分表依赖中间件路由；ProxySQL实现灰度流量调度。

MySQL 多库迁移时主键冲突怎么破

跨数据库迁移最常踩的坑不是数据丢，而是 INSERT 报 Duplicate entry 'X' for key 'PRIMARY'。根源往往是源库和目标库都用自增主键，且没做偏移或范围隔离。

实操建议：

• 迁移前统一停写源库，或用 mysqldump --skip-triggers --skip-extended-insert 导出，避免触发器干扰
• 目标库建表时改用 AUTO_INCREMENT = 1000000（设一个远大于源库最大 ID 的起始值）
• 若需双写过渡，推荐在应用层用 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE，但要注意业务语义是否允许覆盖
• 别依赖 SELECT MAX(id) 动态算偏移——并发下不准，且无法应对已删除 ID 的空洞

binlog + GTID 实现跨实例同步的硬约束

用 CHANGE REPLICATION SOURCE TO 搭建主从是基础，但多数据库迁移中真正决定同步可靠性的，是 GTID 模式下的三件事：开启、校验、跳过。

关键配置与验证点：

• 源库必须启用 gtid_mode = ON 和 enforce_gtid_consistency = ON，否则无法保证事务全局唯一
• 迁移后首次启动从库前，执行 SELECT * FROM performance_schema.replication_applier_status_by_coordinator; 确认 APPLIER_STATUS 是 ACTIVE
• 遇到不兼容 DDL（如 CREATE TEMPORARY TABLE），不能直接 SET GLOBAL sql_slave_skip_counter = 1 —— GTID 下必须用 STOP REPLICA; SET GTID_NEXT = 'xxx'; BEGIN; COMMIT; SET GTID_NEXT = 'AUTOMATIC'; START REPLICA;
• GTID 不支持跨引擎事务（比如 InnoDB + MyISAM 混用），迁移前务必检查 SHOW CREATE TABLE 中的 ENGINE 一致性

分库分表场景下，如何让读写不绕晕

真分布式架构里，MySQL 本身不负责路由，靠中间件（如 ShardingSphere、MyCat）或客户端分片逻辑。但迁移过程中最容易出问题的是「写入新库、读取旧库」这类脏读。

落地要点：

• 禁止在应用代码里硬编码数据库名，所有 USE db_name 必须通过配置中心动态注入
• 分片键（sharding key）字段必须有索引，否则 WHERE user_id = ? 查询会扫全库；迁移前用 EXPLAIN FORMAT=TREE 验证执行计划
• 跨分片 JOIN 或 UNION 操作，中间件通常不支持或性能极差——要么改查为多次单库查询+内存聚合，要么提前物化宽表到单独汇总库
• 时间

  

  类分片（按月建表）注意 DATE 字段类型和分区表达式匹配，例如 PARTITION BY RANGE (TO_DAYS(create_time)) 要求 create_time 是 DATE 或 DATETIME，不能是 TIMESTAMP

为什么 ProxySQL 比直连更适配迁移期流量调度

单纯靠 DNS 切换或 VIP 漂移，无法实现灰度、读写分离、故障自动摘除。ProxySQL 的 mysql_servers 和 mysql_query_rules 表才是迁移期的流量阀门。

典型配置逻辑：

• 把旧库和新库都加进 mysql_servers，用不同 hostgroup_id（如 10=旧主库，20=新主库）
• 写请求规则优先匹配 destination_hostgroup = 20，但初期只设 active = 0；灰度时用 weight 控制流量比例
• 读请求可基于 username 或 SQL 注释（如 /* slave */ SELECT ...）路由到从库组，避免主库压力突增
• 监控 stats_mysql_connection_pool 表里的 status 和 ConnUsed，发现某节点连接数暴涨或状态为 SHUNNED，立刻查日志定位网络或认证失败原因

UPDATE mysql_servers SET weight=50 WHERE hostgroup_id=20 AND hostname='new-db-01';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

迁移不是一次性动作，而是状态切换过程。最常被忽略的是应用连接池的 maxActive 和 minIdle 参数——切库后若未重启服务，旧连接可能长期滞留，导致新库负载虚高、旧库连接泄漏。