MySQL跨环境迁移实战：库名替换与权限调整脚本指南

做数据库迁移这事我相信凡是亲自动手搞过 MySQL 的人都经历过那种“导出两小时、导入两小时、半夜提心吊胆看进度条”的日子。尤其当目标环境要求换库名、换授权用户时如果直接拿着 mysqldump 出来的文件往新库灌往往会遇到“表导进来了但是权限全丢”“存储过程和触发器不见了”“库名还是旧的”等一系列问题。最近我刚完成一次 MySQL 跨环境迁移源库是线上业务库目标环境要求改成全新的库名同时把访问账号一并调整。整个过程我整理成了一套脚本包含导出、库名替换、导入、授权四个环节实测跑下来比手工操作省心很多。这篇文章把这套脚本的完整思路、关键参数、踩坑记录都放出来希望对正在做类似迁移的同学有帮助。1. 迁移场景与方案选型1.1 这个脚本解决的是什么问题说白了这个脚本回答的问题是怎样把 MySQL 里的一个数据库快速、完整、不丢东西地搬到另一个环境并且搬过去之后库名和访问权限都符合新环境的要求。听起来不复杂但实际需求往往是这样出现的测试环境要复刻生产数据但测试库名和生产不一样例如生产叫order_center测试库要求叫order_test。公司内部数据隔离同一个 MySQL 实例上要新建一个库给新的业务线使用旧库的数据要整体搬过去。数据库要从旧服务器迁移到新服务器IP、端口变了访问账号也要从旧的root换成专用的业务账号。在这些场景里如果只是mysqldump 导出 mysql 导入那库名和所有者实际是访问授权用户不会自动变。你需要在中间加一层“修改”的操作这正是脚本存在的原因。1.2 为什么选择 mysqldump 而不是物理备份做 MySQL 迁移方案有好几种我先说结论对绝大多数中小体量数据库mysqldump 仍然是最通用、最可控的方式。物理备份方式比如直接拷贝整个 datadir速度确实快但要求源库和目标库的主版本、系统环境、配置参数高度一致而且要把表空间文件、日志文件、配置文件整套搬过去灵活性很差。一旦遇到“库名要改”这种需求物理拷贝这种方案基本没法满足。Mydumper/myloader 这类社区工具多线程并行确实很香但需要额外安装还要考虑和目标 MySQL 版本的兼容性团队里如果没有现成环境临时部署反而增加风险。mysqldump 是 MySQL 自带的逻辑备份工具本身就是为“产生一份可移植的 SQL 文件”设计的。导出的文件是纯文本可以做很多预处理——改库名、改字符集、过滤表、替换 definer这些都是物理备份做不到的。虽然大数据量大表场景下速度相对慢但通过并行导出、压缩管道等技巧可以把差距拉小。对 10GB 以内的库mysqldump 完全够用。1.3 整体迁移思路这套脚本分成四个环节导出源库使用 mysqldump带上必要的参数把结构、数据、存储过程、触发器、事件全部导出来。替换对导出的 SQL 文件做文本处理把旧库名替换成新库名。这个环节是整个方案的精华也是最容易踩坑的地方。导入目标端执行 SQL 文件重建数据库并写入数据。授权在目标库上创建或更新访问用户将新库的权限授给对应账号。后面我会把每个环节的参数选择和坑点分别展开。2. 核心脚本设计与实现2.1 导出阶段的参数解析导出脚本我写成了变量配置的形式方便在不同环境复用。核心导出命令如下mysqldump \ -h$SRC_HOST -P$SRC_PORT -u$SRC_USER -p$SRC_PASS \ --default-character-setutf8mb4 \ --single-transaction \ --routines --triggers --events \ --set-gtid-purgedOFF \ --hex-blob \ --databases $SRC_DB \ $BACKUP_FILE挑几个关键参数展开说明--single-transaction是 InnoDB 表导出的保命参数。它基于一致性快照实现不加锁备份导出过程中线上业务可以继续读和写不会因为备份把业务卡死。如果没加这个参数mysqldump 默认会对 MyISAM 表加全局读锁对在线业务影响很大。当然前提是表都是 InnoDB如果存在 MyISAM 表导出时依然会有锁行为。--routines --triggers --events这三个参数很多人会漏掉。默认情况下 mysqldump 不导出存储过程、存储函数、触发器和事件调度器里的定时任务。如果是业务库存储过程几乎肯定有漏了导过去再跑一遍业务就会报错。这三个参数分别对应存储例程、触发器和事件建议统一带上。--set-gtid-purgedOFF是迁移场景下经常需要处理的一环。如果源库开了 GTID 模式导出文件里会包含SET GLOBAL.GTID_PURGED...语句导入到未开启 GTID 的目标库时会报错或者在目标库也是 GTID 模式时导致 GTID 冲突。迁移场景下建议显式关闭让目标环境按自己的 GTID 策略重新分配。--hex-blob处理二进制字段。如果表里有 BLOB、BINARY 等二进制类型不加上这个参数导出的文件在导入时可能因为编码问题产生异常。加上之后二进制内容会被转成十六进制字面量兼容性更好。2.2 库名替换的常见做法与风险这一节是整个脚本的核心也是我踩坑最多的地方。先看 mysqldump 导出的文件里库名出现在哪些位置CREATE DATABASE /*!32312 IF NOT EXISTS*/ order_center /*!40100 DEFAULT CHARACTER SET utf8mb4 */; USE order_center;以及表定义和数据之外某些视图、存储过程的定义里也可能引用order_center.table_name这种全限定名。最粗暴的替换方式是直接全局替换字符串sed -i s/order_center/order_test/g dump.sql这种写法在大多数情况下能跑通但隐患很大。比如库名是db那文件中所有包含db的单词都会被替换表名db_log会变成order_test_log列名db_status也可能被误改这种错误通常导入时才会暴露排错非常痛苦。我的做法是收紧替换范围只替换两种明确结构CREATE DATABASE 语句和 USE 语句。sed -i s/CREATE DATABASE \/\*!32312 IF NOT EXISTS\*\/ \$SRC_DB\/CREATE DATABASE \/\*!32312 IF NOT EXISTS\*\/ \$DST_DB\/g $BACKUP_FILE sed -i s/USE \$SRC_DB\/USE \$DST_DB\/g $BACKUP_FILE这样替换的是带反引号的库名引用精确度高很多。但还有一个细节需要补上如果原库视图或存储过程里用了库名.表名这种全限定写法光替换上面两条还不够必须处理。稳妥的做法是继续替换反引号包裹的库名前缀sed -i s/\$SRC_DB\\./\$DST_DB\./g $BACKUP_FILE把三条替换规则都加上才能覆盖大多数场景。有一点必须提醒库名如果本身很短比如a、b这种即使加了反引号也可能有边界问题。这种极端情况建议改成用正则带词边界处理或者在导出时就不要用极短库名生产。实操中我见过因为库名是members而被误替换成members_log这种案例就是因为没用反引号限定。注意替换文件之前务必先确认$SRC_DB和$DST_DB的字符串没有包含特殊字符最好只使用字母、数字、下划线。带横线或带点的库名在 SQL 和脚本中都需要额外转义非常容易出错。2.3 所有者修改的本质授权用户迁移MySQL 里的“所有者”和 PostgreSQL 不太一样它没有真正意义上的数据库 owner 概念你看到的“某个库归哪个用户管”本质上是该用户对库的权限记录存在mysql.db表中。所以“修改所有者”落地到操作上其实是两步在目标实例创建或更新用户账号。把新库的权限授予这个账号。我一般在导入完成后单独执行授权脚本mysql -h$DST_HOST -P$DST_PORT -u$DST_ROOT_USER -p$DST_ROOT_PASS EOF CREATE USER IF NOT EXISTS $APP_USER$APP_HOST IDENTIFIED BY $APP_PASS; ALTER USER $APP_USER$APP_HOST IDENTIFIED BY $APP_PASS; GRANT ALL PRIVILEGES ON $DST_DB.* TO $APP_USER$APP_HOST; FLUSH PRIVILEGES; EOF这里有一个容易忽略的点$APP_HOST是允许应用连接的主机范围。线上环境通常指定应用服务器 IP开发环境常用%通配。如果直接照抄%会带来不必要的安全暴露范围建议根据实际情况缩小。如果原环境里除了默认的 root还有其他只读账号、报表账号也需要一并迁移。这时可以先在源库执行SHOW GRANTS FOR userhost;查看授权情况再在目标库手动重建。脚本里我预留了EXTRA_GRANTS的 SQL 片段位置方便补充。3. 完整实操流程3.1 前置检查与配置变量拿到迁移任务后不要急着执行脚本先花几分钟做环境确认。我一般按下面这个清单过一遍源库和目标库的 MySQL 版本差异建议大版本一致小版本差异问题不大。跨大版本比如 5.7 到 8.0需要额外关注。磁盘空间是否足够存放导出文件和导入后的数据建议至少预留 1.5 倍数据库体积。字符集是否一致不一致时导入会有乱码风险。目标实例是否已有同名的库如果存在必须先清理或选择不同库名。网络是否互通源和目标的口令、账号是否提前确认。脚本顶部是变量区所有环境相关参数都集中放在这里#!/bin/bash # MySQL 快速迁移脚本导出、替换库名、导入、授权 # 源库连接信息 SRC_HOST127.0.0.1 SRC_PORT3306 SRC_USERroot SRC_PASS源库密码 SRC_DBorder_center # 目标库连接信息 DST_HOST127.0.0.1 DST_PORT3307 DST_USERroot DST_PASS目标库密码 DST_DBorder_test # 目标库访问账号 APP_USERorder_app APP_PASS应用账号密码 APP_HOST% # 备份文件 BACKUP_FILE./migration_${SRC_DB}_to_${DST_DB}.sql密码直接写脚本里有安全隐患生产环境建议改用--login-path或环境变量方式。这里为了演示直观先用最直白的写法部署时请自行加强。3.2 导出实操与格式验证导出命令执行完先看一下文件大小和行数确认导出不是 0 字节ls -lh $BACKUP_FILE wc -l $BACKUP_FILE接着抽查文件头部head -n 100 $BACKUP_FILE重点确认三件事文件是否以-- MySQL dump开头。CREATE DATABASE语句是否存在库名是否还是旧库名。是否包含CREATE PROCEDURE、CREATE TRIGGER等结构。如果导出的文件里没有存储过程定义说明--routines参数没生效需要检查执行账号是否具有SELECT权限和SHOW VIEW权限mysqldump 导出存储过程要求账号有相应权限否则即使加了参数也不会报错只是静默跳过。3.3 执行替换与精准验证执行前面的三条 sed 命令替换库名。替换完以后用 grep 再检查一遍确认旧库名已经不再出现grep -n \$SRC_DB\ $BACKUP_FILE | head -n 20如果输出为空说明替换成功。如果还有残留看具体位置再决定是继续替换还是手动修正。这一步宁可多花时间检查也不要跳过。我之前遇到过一次替换不彻底因为库名从order改成order_test后原来的order_test这个库名本身又出现在文件里的某条注释中导致后续导入异常。替换完成后建议再检查一下文件里的表语句是否还正常。一个比较快的办法是统计CREATE TABLE的数量grep -c CREATE TABLE $BACKUP_FILE这个数量应该和源库表数量一致。同时统计CREATE PROCEDURE和CREATE TRIGGER数量分别看是否都保留下来了。3.4 导入执行与耗时观察导入这一步没那么神秘本质就是把 SQL 文件交给 mysql 客户端执行mysql -h$DST_HOST -P$DST_PORT -u$DST_USER -p$DST_PASS $BACKUP_FILE如果文件很大几个 GB建议用time命令记录耗时同时用nohup在后台执行nohup mysql -h$DST_HOST -P$DST_PORT -u$DST_USER -p$DST_PASS --default-character-setutf8mb4 $BACKUP_FILE ./import.log 21 导入过程中观察日志是否出现ERROR关键字。常见报错我会在下一节集中整理。导入完成后用下面的 SQL 验证表数量是否完整SELECT COUNT(*) FROM information_schema.tables WHERE table_schema order_test;再抽查几个关键表的数据量SELECT COUNT(*) FROM order_test.orders;如果表数量一致、关键表行数对得上导入基本成功。3.5 授权执行与效果验证导入成功后执行授权脚本然后使用新账号连接验证mysql -h$DST_HOST -P$DST_PORT -u$APP_USER -p$APP_PASS -e USE $DST_DB; SHOW TABLES LIMIT 5;这一步能同时验证账号密码正确、网络可达、新库权限生效三件事。如果这个账号还能在旧库上访问注意检查授权语句是否误用了GRANT ALL ON *.*这种全库授权在迁移场景属于权限过大应当避免。4. 常见问题与排查实录4.1 导入时报错ERROR 1449definer 不存在这是迁移中最常见的报错之一报错信息长这样ERROR 1449 (HY000): The user specified as a definer (old_user%) does not exist原因是视图、存储过程、触发器的定义里带着DEFINER信息目标是新环境旧用户可能根本不存在。解决方式是在导入之前先把文件里的 definer 替换成新环境已有的账号sed -i s/DEFINER\old_user\\%\/DEFINER\$APP_USER\\$APP_HOST\/g $BACKUP_FILE注意这个替换必须在替换库名之前或之后都可以不受先后影响。但要注意old_user%中的在 sed 里不需要转义反引号因为不是正则特殊字符也不需要转义替换的时候注意双引号包裹时的转义规则。还有一种情况是源库有多个不同 definer比如adminlocalhost和app_user%针对这种情况我建议先用 grep 找出所有 definer 出现的位置列出来统一替换或者只保留一个权威账号。4.2 导入时报错ERROR 1273字符集排序规则不认识从 MySQL 5.7 导出导入 8.0 时经常出现ERROR 1273 (HY000): Unknown collation: utf8mb4_0900_ai_ci原因很好理解8.0 的默认排序规则是utf8mb4_0900_ai_ci而 5.7 不认识这个排序规则。反过来 5.7 的老排序规则到 8.0 倒是兼容。如果跨版本迁移建议在导出命令中加入--default-character-setutf8mb4并在导入时同样指定--default-character-setutf8mb4把两端字符集统一起来。字符集规则的不一致归根结底要在迁移规划阶段就确认清楚临时改文件里的COLLATE也能救急但容易漏改导致后续告警。4.3 大文件导入时max_allowed_packet不够报错信息ERROR 2006 (HY000): MySQL server has gone away或者ERROR 1153 (HY000): Got a packet bigger than max_allowed_packet bytes根本原因是目标实例的max_allowed_packet参数值太小而导入文件中某条 INSERT 语句通常是包含大字段的表超过这个上限连接直接被断开。解决办法分两步先把目标实例的max_allowed_packet调大比如 256M 或 1G然后导入命令也可以显式指定mysql --max-allowed-packet1G $BACKUP_FILE这个参数对应的是 mysql 客户端读取数据包的缓冲大小同样调大后可以避免客户端侧截断。调整参数后重新导入前不需要重导数据文件但要注意如果已经导入了一部分表重新执行会导致这些表被重新创建并重新写数据通常没有问题只是浪费时间。4.4 视图、触发器“静默丢失”这种问题最坑因为报错不会像前面几种那么明显业务上线后才发现某个功能异常。我遇到过一次导出的文件里明明有CREATE TRIGGER语句导入后触发器的确也创建了但实际执行时不生效。排查后才发现触发器定义里的DEFINER指向了源库的一个账号这个账号在目标库里虽然存在但没有SUPER权限导致触发器在调用时报权限错误。存储过程和函数也有类似情况。解决办法很直接导入完成后直接用下面这两条 SQL 检查所有存储过程和触发器确认 definer 都是预期账号然后对不属于预期账号的对象单独执行ALTER修改 definer。SELECT routine_name, definer FROM information_schema.routines WHERE routine_schema order_test; SELECT trigger_name, definer FROM information_schema.triggers WHERE trigger_schema order_test;如果发现 definer 不对可以用ALTER DEFINER\新账号新host PROCEDURE 过程名 SQL SECURITY DEFINER;批量修正。要注意存储过程有SQL SECURITY DEFINER和SQL SECURITY INVOKER 两种模式只有前者才需要管 definer。4.5 替换后库名出现“二次污染”这个问题容易发生在连续执行脚本时。假设某次脚本配置写错DST_DB 恰好等于上一次迁移的旧库名那么替换后文件中的库名可能叠加替换出现order_test_test这种奇怪命名。避免这个问题的方法就是在替换前明确检查$SRC_DB和$DST_DB不能相等增加一个判断if [ $SRC_DB $DST_DB ]; then echo 源库名和目标库名不能相同 exit 1 fi同时脚本最后可以输出一个简短报告列出替换前后的库名和文件路径方便在日志中核对。5. 提速技巧与扩展5.1 大库导出慢时的并行拆分如果你的库超过 20GB单体 mysqldump 导出时间可能非常长。这里有一个并行导出的思路先把库里的表列出来然后按表分成多组同时跑多个 mysqldump 进程每个进程只导出一部分表最后把多个文件拼接起来。获取表名的 SQLSELECT table_name FROM information_schema.tables WHERE table_schema order_center ORDER BY table_name;然后写一个循环脚本按表的数量均分成 N 组mysqldump -h$SRC_HOST ... --databases $SRC_DB --tables 表1 表2 ... part1.sql mysqldump -h$SRC_HOST ... --databases $SRC_DB --tables 表3 表4 ... part2.sql wait cat part1.sql part2.sql ... all.sql使用--tables参数时要注意库名还是要写上但--databases必须放在--tables前面。另外分片导出的文件会各自包含表级锁语句和 DROP TABLE IF EXISTS 语句拼接后执行顺序没问题。由于表间往往有外键关系导入时如果按顺序执行需要先关掉外键检查mysql -e SET FOREIGN_KEY_CHECKS0; mysql all.sql mysql -e SET FOREIGN_KEY_CHECKS1;注意如果分开执行三条命令连接会断开SET 状态不会保持要在同一连接中完成。实际做法是在 SQL 文件头部追加SET FOREIGN_KEY_CHECKS0;尾部追加SET FOREIGN_KEY_CHECKS1;。当然如果有条件直接使用 mydumper 做并行导出会更省事但需要额外安装和验证选型时可以看团队情况权衡。5.2 压缩管道与断点续传另一个提速方式是压缩传输。当网络带宽比磁盘 IO 更紧张时先落盘再传输容易在带宽上浪费大量时间。可以改成管道方式mysqldump ... $SRC_DB | gzip $BACKUP_FILE.gz导入时直接解压导入gunzip -c $BACKUP_FILE.gz | mysql ...这样既省磁盘空间又减少 IO 时间。实测在 1Gbps 内网环境里10GB 数据库开启 gzip 压缩后传输时间能缩短到原来的三分之一左右。断点续传在大文件场景下很重要。如果迁移一半网络断了整个文件导不进去既浪费带宽又增加排错成本。稳妥做法是使用split将压缩包按固定大小拆分传输完成后在目标端cat合并split -b 2G $BACKUP_FILE.gz chunk_ cat chunk_* $BACKUP_FILE.gz这样即使某个分片传输失败只需要重传该分片不用全部重来。虽然这个方案略“笨”但在跨机房迁移时确实救过我的命。5.3 迁移完成后的检查清单导入授权完成后不要着急交付。我每次迁移都会按下面的清单过一遍全部通过才宣布迁移完成表数量一致源库和目标库的information_schema.tables表数量一致。关键表数据量一致抽样 5~10 张表做COUNT(*)对比。字符集一致SHOW CREATE TABLE抽查表定义中的 DEFAULT CHARSET。自增列正确处理检查AUTO_INCREMENT的值是否保留避免迁移后主键冲突。存储过程、函数、触发器、事件数量一致。权限验证用业务账号连接执行正常的增删改查语句确认无权限报错。监控指标连接数、慢查询日志、错误日志里没有新的异常。数据一致性校验这部分如果数据量很大可以对比CHECKSUM TABLE的值CHECKSUM TABLE order_test.orders;源库和目标库分别执行同一个 CHECKSUM 语句结果一致说明表数据完全一致。这个方式对于 InnoDB 表可能比较耗时但对关键表做一次校验是值得的。5.4 脚本后续扩展方向现在这套脚本是把“导出、替换、导入、授权”写在一个 shell 脚本里适合手动执行。如果迁移比较频繁可以继续扩展成下面几个方向参数化把连接信息、库名通过命令行参数传入避免反复改脚本。日志与告警导入完成或失败时发送通知。数据脱敏在导出和导入之间加一层脱敏 SQL 替换比如把手机号、身份证字段做模糊化处理。灰度切换导入完成后在应用层做流量切换把只读流量先切到新库观察稳定后再切写流量。我个人在实际操作中的体会是脚本本身并不复杂复杂度全在对异常情况的预见上。库名替换、definer、字符集、GTID 这四个点只要有一个没处理好线上就可能在半夜给你惊喜。如果你把这篇文章里的脚本和排查表拿去对照执行至少能绕开大部分常见的坑。最后再分享一个小技巧执行前把源库的表结构先单独导一份预览文件人工扫一眼建表语句确认没有特殊字符或奇怪的排序规则再开始全量导出。这多花的几分钟往往能省下后面几个小时的排错时间。