之前接手过一个内部系统改造项目涉及把老平台的数据整体迁到新架构上。业务方给的需求一句话就能说完数据要过去系统别停太久。但真正做下去才发现SQL迁移从来不是把表结构复制一份、把数据导一导这么简单。字段隐式转换、主键冲突、字符集乱码、甚至某个字段在旧库里允许为空而在新库被业务逻辑隐式依赖这些坑一个接一个冒出来。这篇就把整个过程里踩过的坑、验证过的方法和最终沉淀下来的检查清单整理出来。1. 为什么SQL迁移常常被当成复制粘贴来对待大多数项目走到SQL迁移这一步时表面上看起来确实像一个导出再导入的操作源库执行mysqldump或者pg_dump目标库把这堆SQL脚本跑一遍完事。但这么理解迁移等于默认了一个前提——新旧两个库是同构的。实际项目里这个前提几乎不成立。以我参与的这个改造为例源端是一套跑了好几年的老关系型数据库业务表上百张其中有好几张单表数据量已经过亿还有一堆历史表只有归档价值。目标端换成了另一款数据库用于承载未来几年的新业务。所谓SQL迁移表面是数据搬家实际要处理的是四件事Schema迁移表结构、索引、约束、存储过程、触发器、视图这些对象怎么在新库里重建并且保证语义一致数据迁移全量存量数据搬到新库过程中不丢数据、不错数据增量同步从迁移开始到正式切换之间源库还在正常写入这两段数据怎么追平应用侧切换SQL写法、事务行为、大小写敏感性等差异怎么让应用代码平滑适配很多人容易忽略的是SQL迁移的难点几乎都不在搬这个动作上而在搬之前和搬之后。搬之前要摸清现网数据的真实质量搬之后要验证新库上的行为不跑偏。这个项目里有个很典型的案例源库某张订单表用了自增主键数据量到了1.2亿左右。新库同样支持自增列看起来直接建表导入就行问题不大。但迁移过去后业务侧偶尔出现主键冲突的告警。排查了很久最后定位到原因新库的自增序列初始值没有手动推进到源库的当前值而是从1重新开始。正常情况确实不会撞因为源库导出的数据自带主键值序列只要跟着当前最大值走就行但当时的导出脚本里部分数据漏了主键值导致目标端重新生成刚跑了几小时就和存量数据的最大值撞了。这种问题不实际跑一轮完整迁移根本发现不了。所以SQL迁移这件事本质上是一个带状态变更的系统工程而不是一段可执行脚本。把心态从写个导出脚本切换到做一次带验证的发布后面每一步都会顺很多。2. 迁移前的摸底三张清单决定成败2.1 数据字典清单——把自己家底先数清楚动手迁移的第一个动作不是写脚本而是把库里的对象清单拉出来。这一步我用的是很朴素的方法就是跑系统表查询。以源库为例-- 列出所有用户表及其估算行数 SELECT table_name, table_rows, engine, create_time FROM information_schema.tables WHERE table_schema your_db ORDER BY table_rows DESC;核心想要的是三样东西表的总数、每张表的量级、哪些表是InnoDB哪些是归档引擎。量级直接决定迁移方案采用全量还是全量加增量引擎类型决定导出脚本时要不要特殊处理。比如源库里几张MyISAM表没有事务支持导数据时中间失败了一次已经插入的一半数据不会自动回滚。重新跑之前必须先把目标端清干净否则重复导入必然主键冲突。然后是索引和约束的梳理。有一件事我建议提前做把所有外键关系导出来看一眼不用细看每条至少知道有没有、集中在哪些表。外键在迁移时很麻烦因为它们要求导入顺序严格满足依赖关系——先导主表再导子表。自动工具通常能调度手写脚本时最容易在这里翻车。2.2 业务字典清单——搞清楚字段的真实含义这一步很容易被当成多余工作跳过其实是最值得花时间的。数据字典告诉你表里有什么业务字典告诉你这些字段可以被怎样使用。很多时候同一个字段在不同环境里的语义是不同的。举这个项目里的实例。源库里几乎所有表都有create_time和update_time两个字段类型是datetime默认值写死为CURRENT_TIMESTAMP。看似没问题新库也同样支持。但仔细翻业务代码后发现部分老业务在更新记录时会显式给update_time传入一个过去的时间用于修正录入错误。这个逻辑在旧库里能跑通因为旧库的datetime精度只到秒索引大小也无所谓但新库如果开启更高精度的时间戳或者应用侧在切换后统一走新代码这些历史行为就可能被新逻辑刷新掉导致审计记录对不上。业务字典要做的事就是把这类隐性约定挖出来。我习惯用一种比较土但有效的办法把源库的字段名、注释、代码里出现的所有SQL查询条件拉出来做一次交叉比对。不用什么高级工具几张Excel表和几个简单的正则就够用。重点标记三类字段时间字段确认精度要求秒还是毫秒还是微秒状态字段确认枚举值范围和默认值是否在新库中同样存在金额/数值字段确认小数位、正负号范围以及是否有被当作字符串处理的这三类最容易在新旧库之间出现行为不一致。2.3 边界条件清单——停机窗口和可用性要求数据量、停机窗口、迁移后回滚的可能性这三个边界条件决定了整个迁移方案的形态而不是反过来。结合这个项目的实际情况业务方给出的停机窗口是6小时左右但在此之前只给了3天准备时间。3天里要完成摸底、方案评审、演练说实话非常紧张。好在存量数据虽然大但增量很小日增也就几万条。于是方案可以收敛为某个时间点做一次全量导出导完直接进入增量追平阶段增量脚本每5分钟拉取一次变更数据一直追到切换时刻最后在停机窗口内做一次短暂的写操作冻结追平剩余增量然后应用切换。这里有个关键判断需要提前量和业务方对齐切换时刻允许的最终数据丢失窗口是多久如果业务方接受迁移过程中产生的新数据最多丢5分钟增量同步方案就好做很多如果要求数据零丢失那就要引入更复杂的双写机制。这个边界不提前确认清楚到停机当晚和业务扯皮风险就大了。边界条件清单确定后迁移方案的选型才有依据这一点放在下一节细说。3. 方案选型离线全量、在线迁移还是双写方案选型不是选一个听起来高级的而是选一个在当前边界下最稳的。现在主流路径大致有三条我给它们做了个对比实际用哪条取决于前一步的边界清单方案适用场景优点风险点离线全量导出导入数据量小、停机窗口充裕实施简单脚本可控性最强迁移期间业务完全停写窗口拉长在线增量同步数据量大、希望缩短停机时间全量阶段业务可继续运行只需在切换前冻结写入增量同步链路本身可能故障追平时间不确定双写同步对停机时间和数据一致性都有极高要求切换瞬间即完成基本无感知双写逻辑复杂度高业务代码改造量大这个项目选择的是第二种——在线增量同步。不是因为它是最优解而是它在当前资源约束下最匹配要迁移的数据量很大全量导出就要跑三四个小时不可能让业务在白天时段停这么久而晚上的停机窗口6小时又不够全量加应用切换。所以设计成提前做全量复制切换前追平增量是最划算的。选型过程中有个容易被忽略的细节全量导出和增量开始的时间点必须严格对齐。我们最终的实现是这样的先做一次统一的FLUSH TABLES WITH READ LOCK确保拿到一致性快照位置随后立即开始mysqldump导出全量数据同时记录当前binlog文件名和位点导出完成后从记录的位点开始做增量同步切换前冻结写入把增量追到最新校验无差异后切换应用为什么必须记录binlog位点因为全量导出过程本身是耗时很长的如果增量同步从这个时间点之前的某个位置开始那增量任务把全量导出期间产生的新变更也追进来就会和全量导出的数据重复后面必然出现主键冲突或数据覆盖这个我在多套环境上反复实验无一例外。如果源库本身不支持干净的binlog位点记录比如某些托管数据库就得换一种思路——通过一个带有递增时间戳或者业务序号的分表字段来实现增量拉取。但这种方式增加了依赖业务字段的隐性风险能不用尽量不用。双写方案这个项目里没有实际落地但我在别的项目上实施过。双写的核心问题在于两套数据库的事务行为不一致。源库一条INSERT ... ON DUPLICATE KEY UPDATE的行为和新库不一定相同尤其在遇到唯一索引冲突时执行结果可能完全不同这会让双写状态在两个库之间出现偏差而且偏差往往在几周后才暴露。所以双写方案不是写个同步插件就算完它需要额外配套一套对账任务每隔一段时间做一次样本对比才能保证长期不漂移。4. 执行阶段最不起眼但最致命的几个拦路虎迁移方案排期之后真正动手导出导入的那一两天反而会遇到各种零碎但致命的问题。这些问题在方案设计阶段很难提前想到只有真跑一遍才能暴露。我在这个项目里至少踩了四类。4.1 字符集和排序规则不一致第一类字符集问题。源库的字符集是utf8mb4排序规则是utf8mb4_general_ci这个在迁移前看表结构时一切正常。但建表脚本在目标库执行时如果目标库的默认字符集被设置成了utf8mb4_unicode_ci那么所有varchar字段的索引排序规则也会跟着变。表面上查询结果是一样的但涉及ORDER BY、GROUP BY、甚至某些LIKE模糊匹配时行为会有细微差异。这类问题在数据量小的表上几乎看不出差异一旦到了千万级数据量的表上一个索引因为排序规则不同导致无法使用全表扫描的后果就是查询耗时从毫秒级涨到秒级。所以建表脚本执行前必须显式指定字符集和排序规则不要依赖默认值CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;4.2 触发器、存储过程、视图和函数比表更棘手第二类逻辑对象的迁移。表结构导过去容易视图、存储过程、函数、触发器这些对象才是麻烦制造者。源库的存储过程可能用了大量SELECT ... INTO等老语法目标库虽然兼容大部分但某些边界语法比如LIMIT和ORDER BY在子查询中的写法、递归公用表表达式的前缀关键字会有出入。我的建议是在正式迁移前做一次语法兼容性预检。把源库所有存储过程和函数定义导出来用一个最简单的CREATE OR REPLACE脚本逐批在目标库空库上创建把失败的单独摘出来手工改写。这一步听起来费时间但绝对值得做因为真实切换时不会有时间让你在停机窗口里逐个调试存储过程。4.3 数据一致性校验不能只比对行数第三类也是我最想强调的一类数据校验不能只比行数。很多迁移项目做完两边SELECT COUNT(*)一比数量一样就宣布成功。但这是一项非常不充分的校验。数据量的一致完全无法保证内容的一致尤其遇上隐式类型转换时问题会被悄悄掩盖。举个例子。源库里某个业务字段类型是varchar(10)实际存的值大部分是纯数字少部分带了字母后缀。迁移到目标库时如果建表脚本不小心把这个字段建成了int导入时数据库会尝试把字母后缀的字符串转成0。COUNT(*)不少一条甚至索引大小都差不多但你一到月底统计业务数据时数字就不对。所以我的数据校验脚本采用的是三步走先比对行数再用MD5对每个表的关键字段做聚合指纹对比最后按抽样条件拉出明细做抽查具体做法是写一个查询把每张表的字段拼出来做GROUP_CONCAT或加权求和形成指纹SELECT MD5(GROUP_CONCAT( CONCAT_WS(|, id, order_no, amount, create_time) ORDER BY id )) AS row_fingerprint FROM order_info;两边库分别跑一次同样的查询然后比对结果。两个MD5完全一致才说明这批数据在字段层面无差异。如果直接比对全表MD5在数据量大的表上太慢可以先按主键范围分段比对每段100万条左右。4.4 隐藏的表分区和自增序列第四类表分区和自增序列。源库有几张按月份做的范围分区表分区数量几十个目标库建表时如果只是简单建了普通表数据导过去能插入但后续运维脚本里那些针对分区的管理操作比如按月份丢弃旧分区会直接报错。这种问题数据校验再严格也发现不了因为数据本身确实完整但业务运维方式断了。自增序列前面提到过。这里再补充一点如果源库自增列当前值为A而目标库建表后没设置目标库从1开始但存量数据早就有更大的值了。好在实际上大部分导数据脚本都会保留源库主键的值所以序列不撞的概率很低。但为了稳我还是建议在导入完成后显式把序列推进到当前最大ID-- 以源库order_info表最大id为基准推进目标库序列 SELECT MAX(id) FROM order_info; -- 然后在新库中执行序列设置不同数据库语法差异较大这里以通用说明为主这步操作如果不做轻则主键冲突告警重则后续新插入的数据和存量数据主键相撞业务直接报错。5. 验证与回滚真正的挑战在切换后5.1 三层验证策略缺一不可迁移完成的判定标准不是迁移脚本跑完了而是业务侧验证通过。我习惯把迁移后的验证拆成三层第一层对象完整性验证——核对目标库的表、视图、函数、存储过程数量与源库是否一致索引数量、唯一约束、外键这些有没有在建表过程中被吞掉第二层数据一致性验证——上一节提到的行数、指纹、抽样三步走第三层业务流验证——挑几条核心链路走一遍真实的写读请求确认应用在新库上跑出来的行为和旧库一致第三层至关重要但往往被跳过。很多团队验证到第二层就宣布迁移成功结果应用切换上去后某条之前依赖旧库特定隐式行为的SQL突然走不过或者耗时从100毫秒涨到5秒。我的做法是在正式切换前做一轮影子验证——应用侧起一套只读测试实例接上新库然后把真实流量中的读请求放上去跑。不追求全流量至少把Top 20的慢查询重放一遍观察目标库的响应时间和执行计划是否在合理范围内。这一步能在正式切换前发现绝大多数SQL兼容性问题。5.2 回滚不是把数据倒回去而是有预案地倒回去回滚方案是每次迁移必须准备的但也是大多数项目最敷衍的部分。很多人写回滚预案就是一句话发现不对就切回老库。这句话在实践里等于没说。回滚的关键不在于能不能把库切回而在于从新库切回老库后数据是否还一致。如果迁移后业务已经在新库上写入了半小时数据直接切回老库这半小时的数据就丢了。所以回滚预案要和增量同步方案配合设计。在这个项目里我们设计的是双跑验证期全量迁移完成并切换后增量同步链路保持继续运行方向是从新库回到老库这样如果发现新库问题可以在分钟级把业务切回老库且老库上已经有了新库最近几分钟的增量数据。这个反向同步听起来复杂实际操作起来就是调用已有的同步工具把方向倒过来。但这一步必须提前验证不能等到回滚时再现场配置。5.3 验证脚本的一点经验分享一个写校验脚本时的小技巧。全表MD5在大表上跑起来很慢动辄几十分钟。我优化后的做法是按主键范围把表切成多个区间并行跑校验每个区间用CRC32计算聚合值不直接算全局MD5最后汇总比对定位差异的具体区间这种分而治之的做法不仅速度快而且一旦发现不一致可以快速缩小问题范围不用从头开始排查。6. 几条沉淀下来的实操建议经历了这次迁移我最后总结出几点对团队要求不高但收益很高的习惯分享出来供参考。第一每次迁移都写迁移专属的README文档。不要指望三个月后还能记住当时为什么这么选型、为什么那个表要特殊处理。这个文档不是为了应付检查而是给后来接手的人看的。我在文档里通常包含迁移背景、边界条件数据量、停机窗口、数据丢失容忍度、方案选型的原因、步骤清单、每步的验证命令、常见问题的处理方法。这份文档在回滚时也是第一参考资料。第二演练环境必须有而且演练次数不能少于一次。这个项目里如果直接按正式计划走大概率会在切换当晚遇到前面说的存储过程语法不兼容问题导致切换时间被拉长好几小时。因为我们在演练环境里提前跑了一遍全部流程把存储过程和触发器脚本全部修正过正式切换时才一次通过。演练的唯一目的不是证明方案可行而是把所有能暴露的问题全部暴露掉。第三关注binlog保留时长。增量同步方案对binlog的依赖性极高。很多数据库实例默认只保留2到3天的binlog如果全量导出加上增量追平的时间超过了binlog保留期增量数据就断了只能重新全量导出。所以在方案设计阶段就要和DBA确认binlog保留时长必要时提前调大保留时间。第四给自己的迁移任务留一些意外缓冲时间。无论计划排得多满最后导入、校验、切换的每个环节都要预留比预期多20%到30%的时间。数据迁移这个领域不存在万无一失只存在提前踩过坑。SQL迁移一次做下来最大的感受就是它没有标准答案每个项目的边界条件不同解法就完全不一样。但一条主线是明确的在动手写第一条mysqldump命令之前把数据情况摸清楚把边界条件对齐把方案选型的原因写下来这个流程走得越扎实后面切换当晚会越轻松。