1. 项目概述1.1 核心需求解析先说结论这个脚本解决的是MySQL数据库迁移中最让人头疼的三个问题——跨实例搬迁、库名不一致、所有者不匹配。日常运维里我遇到过太多次这样的场景项目要从测试环境搬到生产环境库名从project_test改成project_prod或者接手别人留下的库Owner还是之前某个已经离职的账号应用连库时报各种权限错误。每次手工处理一遍少说半小时数据量一大一两个小时就耗进去了。这个脚本的核心思路并不复杂用mysqldump把数据导出成SQL文件然后通过文本替换把库名和所有者改掉再导入目标库。听起来平平无奇但真正把它做成一个一次执行、全程无脑的脚本并在生产环境跑稳中间有不少细节值得梳理——尤其是**视图View、触发器Trigger、存储过程Procedure**这些特殊对象的处理以及DEFINER权限问题稍不注意就会在导入时报一堆莫名其妙的错误。1.2 适合谁看如果你属于下面任何一类情况这篇文章值得花五分钟读完正在做MySQL跨服务器迁移但源库和目标库的库名不一致需要把某个库从A账号名下转移到B账号名下又不想一个个表ALTER TABLE ... OWNER TO虽然MySQL和PG不一样但思路类似迁移后应用连库报Access denied或command denied排查半天找不到原因写了不少shell脚本但sed、mysqldump参数总是记不牢每次都要现查。我尽量把每一步的为什么讲清楚而不仅仅是给一份能跑通的代码。毕竟脚本只有三四十行真正值钱的是里面那些踩过坑才知道的细节。2. 方案选型与设计思路2.1 主流迁移方式对比在做MySQL迁移时常见的方案有这么几类方案优点缺点适用场景mysqldump逻辑备份简单直接跨版本兼容好数据量大时慢几GB以上明显吃力中小型库一般5GB以内mydumper并行逻辑备份速度比mysqldump快不少支持并行需要额外安装部分旧版本对视图/触发器支持不佳中大库5GB-50GBPercona XtraBackup物理备份快几乎不影响业务要求同版本、同平台基本上Linux x86同类不支持跨大版本超大库50GB以上、在线迁移主从复制/克隆插件几乎零停机数据实时同步架构复杂需要交互和回放高可用、滚动迁移这个脚本选的是mysqldump原因是绝大多数迁移场景中目标库的大小都在可接受范围内。测试环境、预生产、甚至不少中小型生产库逻辑备份的时间成本完全可控。同时SQL文件可读、可改、可重用这正是我们要修改库名和所有者的前提——物理备份没法随手改文本mydumper产出的多文件结构也增加了批量替换的复杂度。提示如果你的库超过10GB建议先把方案从mysqldump调整到mydumper或物理备份但下面讲解的改库名/改所有者思路依然适用无非是替换的对象从单个SQL文件变成多个分片文件。2.2 为什么选择导出后改SQL而非导出前改权限有些人会想既然要改所有者能不能在源库上直接把表权限转移了再导出这个思路在MySQL里实现起来很不优雅。MySQL的所有者和PostgreSQL不同它没有严格的OWNER概念对象归属主要体现在两个地方表、视图、函数的DEFINER在mysqldump导出文件里表现为DEFINER\源用户host库级和使用对象的授权存储在mysql.db、mysql.tables_priv等系统表中。如果在源库上直接改权限你面临的问题是需要构造大量GRANT、REVOKE语句且只对当前实例生效目标实例如果没法网络连通到源库这些权限数据不会跟着SQL文件走视图的DEFINER在MySQL中想要修改往往要重建视图风险不小。所以我的结论是在SQL文本层面做替换是最干净、最可逆、最不容易出错的方式。导出前不动源库任何东西导出后只改文本目标库导入后一切归位。源库保持不变还可以随时回滚重来。2.3 脚本的三大核心模块拆分整个脚本我拆成了三块导出模块负责调mysqldump合理加参数保证数据完整性和一致性文本替换模块负责改库名、改DEFINER/所有者这是整个脚本的灵魂导入模块负责以干净的目标库状态导入SQL文件并对权限、字符集做二次校准。三个模块独立成函数出错时可以直接单独执行。比如导出已经完成了只想重新生成一遍替换后的SQL就单独跑第二个函数目标库导入出问题了可以清库后只重跑第三部分。这种设计在实际运维中非常实用——脚本不怕笨怕的是每次都要从头再来。3. 核心细节与脚本实现3.1 导出时的关键参数直接给可复用的导出命令mysqldump \ -h${SRC_HOST} -P${SRC_PORT} \ -u${SRC_USER} -p${SRC_PASS} \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --hex-blob \ --default-character-setutf8mb4 \ --no-tablespaces \ --result-file${EXPORT_FILE} \ ${SRC_DB}逐个参数说明--single-transaction对InnoDB表做一致性快照导出不加锁不影响线上业务。这是InnoDB引擎必备的参数没有它导出期间源库的写操作会被阻塞。MyISAM表不受此参数保护会退化到LOCK TABLES这点要心里有数。--routines导出存储过程和函数。默认mysqldump不导这个漏了的话应用层调用存储过程直接报PROCEDURE ... does not exist。--triggers导出触发器。同样是默认行为的一部分但如果你用了--no-data之类的参数最好显式加上。触发器这块有个容易忽略的问题导入到新库后某些老版本触发器里的DEFINER不一致会导致执行时报错下面专门讲。--events导出事件调度器Event Scheduler。--set-gtid-purgedOFF如果源库开启了GTID模式默认会导出SET GLOBAL.GTID_PURGED语句在目标库上执行时容易报GTID_PURGED can only be set when GTID_EXECUTED is empty。除非你做的是基于GTID的主从初始化否则一律OFF。--hex-blob二进制字段以十六进制方式导出避免字符集转换导致乱码或数据破坏。--no-tablespaces跳过表空间信息。常规环境下一个实例就一个表空间这个参数能避免部分权限不足时报错。--default-character-setutf8mb4这个强烈建议显式指定不依赖服务端默认值。我自己遇到过源库默认latin1导致导出的中文全部乱码的情况后来就养成了永远显式写字符集参数的习惯。--result-file指定输出文件同时避免mysqldump把统计信息打到标准输出污染重定向文件。这些参数组合起来解决的是数据完整性和跨环境兼容性两个问题。少一个参数可能在几十万行之后突然报错中断或者数据落地后出现无法解释的乱码。3.2 修改库名的正确姿势库名替换是整个脚本的技术含量所在。直接用sed s/旧库名/新库名/g替换整个文件往往会出问题。比如库名是db_shop把SQL里所有的db_shop替换成db_mall看起来没问题但实际上一旦库名存在大小写差异或者表名前缀跟库名重名就很容易误伤。我的做法是精确匹配带反引号的库名sed -i s/\${SRC_DB}\/\${DST_DB}\/g ${EXPORT_FILE}注意这里用的是反引号包裹MySQL导出文件里库名、表名默认都带反引号。精确匹配\db_shop这个组合能避免误替换掉db_shop_order这种表名前缀中包含库名的场景。但这里还有一个USE语句的问题。mysqldump默认导出的SQL里包含-- MySQL dump 10.13 Distrib 8.0.x CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_shop /*!40100 DEFAULT CHARACTER SET utf8mb4 */; USE db_shop;这是一把双刃剑好处是直接导入就能用自动建库坏处是如果目标库已经存在且数据不能丢CREATE DATABASE IF NOT EXISTS不会报错但后续的CREATE TABLE会因表已存在而中断。所以我的脚本里在替换库名后会删除CREATE DATABASE和USE语句sed -i /^CREATE DATABASE/d ${EXPORT_FILE} sed -i /^USE /d ${EXPORT_FILE}这样导入前必须先手工在目标实例建好空库授权好账号再执行导入。看起来多了一步操作但换来的是极高的可控性——所有表、数据、过程都在一个明确的、空的库里落地不会有任何覆盖已有数据的意外。提示如果你是第一次跑这个脚本强烈建议删除CREATE DATABASE和USE改为手工建库的方式。这个习惯能让你在导入前就把字符集、排序规则、加密方式都统一好而不是让备份文件替你做决定。3.3 修改所有者的完整方案所有者的修改分成两部分。第一部分替换DEFINER导出文件里表、视图、触发器、函数、过程的定义中都有类似这样的行CREATE DEFINERold_user% PROCEDURE sp_example(...) CREATE ALGORITHMUNDEFINED DEFINERold_user% SQL SECURITY DEFINER VIEW v_example AS ...如果目标库里不存在old_user导入时通常不会立即报错MySQL对表结构定义里的DEFINER检查不那么严格但一旦业务执行这些对象时报权限错误排查起来特别痛苦。尤其是视图SQL SECURITY DEFINER意味着执行视图时以定义者身份做权限检查定义者不存在或者权限不足查数据就报错。所以脚本里对DEFINER做统一替换sed -i s/DEFINER\${SRC_USER}\\%\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} sed -i s/DEFINER\${SRC_USER}\\localhost\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE}注意几个细节源账号的Host可能是%也可能是localhost需要分别处理目标统一用%还是localhost取决于目标实例的账号管理方式。如果目标库和应用服务器不在同一台机器localhost会连不上建议统一成%如果业务里有账号用了user10.0.%这种网段写法脚本里再加一条对应替换即可。我的做法是先用grep找出所有DEFINER行看有哪些Host再针对性补规则。第二部分处理目标库的账号和授权这是很多人容易忽略的一步。mysqldump默认不会导出mysql.user和mysql.db等系统表所以仅仅替换DEFINER目标库里可能连对应的登录账号都不存在。导入的数据表结构没有任何问题但应用连库时直接报Access denied。因此导入前需要准备一段权限脚本CREATE USER IF NOT EXISTS ${DST_USER}% IDENTIFIED BY ${DST_PASS}; GRANT ALL PRIVILEGES ON db_mall.* TO ${DST_USER}%; FLUSH PRIVILEGES;如果目标账号在目标实例里已经存在比如复用公共账号就用ALTER USER调整密码或者直接把授权加上。我见过不少踩坑现场数据导进去了视图建好了所有表都在但应用报SELECT command denied to user——其实就是忘了授权。还有个隐藏点mysql系统库不随mysqldump导出如果你迁移时想连账号带库一起走需要单独导出系统库mysqldump -u${SRC_USER} -p${SRC_PASS} --databases mysql --routines --tables user db tables_priv columns_priv mysql_priv.sql这种方式适合很少见的目标实例重建场景一般情况下不建议动mysql系统库很容易搞出权限错乱比手工授权复杂得多。3.4 完整脚本下面给出实际使用的完整脚本。参数通过环境变量或命令行传入我在脚本里做了简单的默认值处理方便不同环境复用。#!/bin/bash # 功能MySQL库快速导出→改库名/所有者→导入 # 用法./mysql_migrate.sh 源库名 目标库名 源用户 目标用户 [目标密码] set -euo pipefail # ---------- 配置区 ---------- SRC_HOST${SRC_HOST:-127.0.0.1} SRC_PORT${SRC_PORT:-3306} SRC_USER${SRC_USER:-root} SRC_PASS${SRC_PASS:-} DST_HOST${DST_HOST:-127.0.0.1} DST_PORT${DST_PORT:-3306} DST_USER${4:-app_user} DST_PASS${5:-app_pass123} WORK_DIR${WORK_DIR:-/tmp/mysql_migrate} SRC_DB${1:?源库名不能为空} DST_DB${2:?目标库名不能为空} EXPORT_FILE${WORK_DIR}/${SRC_DB}_$(date %Y%m%d%H%M%S).sql mkdir -p ${WORK_DIR} # ---------- 1. 导出 ---------- function do_export() { echo [1/4] 从 ${SRC_HOST}:${SRC_PORT}/${SRC_DB} 导出... mysqldump \ -h${SRC_HOST} -P${SRC_PORT} \ -u${SRC_USER} -p${SRC_PASS} \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --hex-blob \ --default-character-setutf8mb4 \ --no-tablespaces \ --result-file${EXPORT_FILE} \ ${SRC_DB} echo 导出完成${EXPORT_FILE} wc -l ${EXPORT_FILE} } # ---------- 2. 文本替换 ---------- function do_replace() { echo [2/4] 替换库名和DEFINER... # 库名精确匹配带反引号 sed -i s/\${SRC_DB}\/\${DST_DB}\/g ${EXPORT_FILE} # 删除自动建库与USE语句避免覆盖目标库已有对象 sed -i /^CREATE DATABASE/d ${EXPORT_FILE} sed -i /^USE /d ${EXPORT_FILE} # DEFINER替换常见四种Host写法都处理 sed -i s/DEFINER\${SRC_USER}\\%\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} sed -i s/DEFINER\${SRC_USER}\\localhost\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} sed -i s/DEFINER\${SRC_USER}\\127.0.0.1\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} echo 替换完成改动行数抽查 grep -c DEFINER ${EXPORT_FILE} || true } # ---------- 3. 目标库建库授权 ---------- function do_dst_prepare() { echo [3/4] 初始化目标库和账号... mysql -h${DST_HOST} -P${DST_PORT} -u${DST_USER} -p${DST_PASS} SQL CREATE DATABASE IF NOT EXISTS \${DST_DB}\ DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; CREATE USER IF NOT EXISTS ${DST_USER}% IDENTIFIED BY ${DST_PASS}; GRANT ALL PRIVILEGES ON \${DST_DB}\.* TO ${DST_USER}%; FLUSH PRIVILEGES; SQL echo 目标库与账号就绪。 } # ---------- 4. 导入 ---------- function do_import() { echo [4/4] 导入数据到 ${DST_HOST}:${DST_PORT}/${DST_DB} ... mysql \ -h${DST_HOST} -P${DST_PORT} \ -u${DST_USER} -p${DST_PASS} \ --default-character-setutf8mb4 \ ${DST_DB} ${EXPORT_FILE} echo 导入完成。 } # ---------- 主流程 ---------- do_export do_replace do_dst_prepare do_import echo 完成。耗时情况 ls -lh ${EXPORT_FILE}一个值得注意的点脚本里do_dst_prepare使用了目标库的${DST_USER}去连接MySQL执行建库授权。如果这个账号在目标实例里不存在这一步是连接不上的。所以实际执行时这里应该改为用目标实例的管理员账号比如root去连接建好库和账号之后导入部分再用应用账号。否则第一次跑脚本的时候do_dst_prepare可能卡在Access denied上。我把它拆开正是因为这两个阶段在权限要求上是不同的do_dst_prepare需要管理权限负责建库建账号do_import只需要目标库的INSERT/CREATE权限用应用账号即可。实战中我会把DST_ADMIN_USER和DST_ADMIN_PASS单独提取成变量避免为了省事而把管理员密码硬编码在应用账号段里。4. 实操过程中踩过的坑4.1set -euo pipefail的副作用脚本开头我加了set -euo pipefail好处是出错立刻退出不会带着坏数据往下走。但这也带来了一个问题grep -c在没有匹配到内容时返回非零退出码会把脚本卡死在中间。比如某些库完全没有游程、函数、视图grep -c DEFINER输出0同时退出码为1脚本直接中断。所以我在脚本里给所有可能返回非零的命令都加了|| true。别嫌丑在生产环境里脚本因为一个grep的退出码中断比脚本多跑了几行麻烦得多。这个教训是在某次凌晨迁移时用真金白银换来的当时整个任务跑到一半停在替换完成之后我盯着日志看了十分钟才反应过来是grep的锅。另外如果源库里的视图数量很多DEFINER替换之后需要手工验证一下有没有漏网之鱼。最稳的办法是导入之前在目标实例上跑一遍SELECT table_schema, table_name, table_type, DEFINER FROM information_schema.views WHERE table_schema db_mall;把所有视图的DEFINER列拉出来确认清一色是目标账号。如果还有源账号的残留多半是Host写法有别的变种比如user192.168.1.%脚本里没覆盖到。这时候比改脚本更快的方法是导入后在目标库执行ALTER DEFINER dst_user% VIEW db_mall.v_example AS SELECT ...;但改视图定义有风险最好还是导入前把DEFINER替换规则补齐。4.2CREATE DATABASE里的注释号陷阱前面提到要删除CREATE DATABASE和USE语句但MySQL导出文件里的CREATE DATABASE语句实际长这样CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_shop /*!40100 DEFAULT CHARACTER SET utf8mb4 */;它前面的注释里带着IF NOT EXISTS。如果你用sed /^CREATE DATABASE/d删除只能删掉以CREATE DATABASE开头的行。但MySQL导出的文件里这一行前通常还有一行注释-- Current Database: db_shop这一行不会被删掉但也没什么影响最多是导入时的注释信息有点过时。真正要注意的是如果源库名和目标库名在多个地方出现-- Current Database: ...、-- Table structure for table ...等注释行里的库名也会被替换。这没问题。但有一个特殊情况比较坑目标库名如果包含特殊字符比如db_mallv2sed替换时符号不需要转义但如果你用#作为sed分隔符就要小心了。我的建议是sed的分隔符坚持用|或者避免和路径、特殊符号撞车。写替换规则时可以先用grep -n看一眼实际文件结构再跑sed别盲目迷信一条命令通吃所有场景。例如grep -n CREATE DATABASE ${EXPORT_FILE} grep -n ^USE ${EXPORT_FILE} grep -n DEFINER ${EXPORT_FILE} | head -20这三条命令在任何一次迁移前都值得先跑一遍。4.3 导入速度优化mysql file.sql是最朴素的导入方式但并不是最快的。如果文件超过500MB导入时间会比较明显。我实际比较过几种方式方式速度备注mysql file.sql基准单线程适合100MB级别mysql --max-allowed-packet1G file.sql略快避免单条INSERT过大导致的packet错误先关外键约束再导快30%以上导入前SET FOREIGN_KEY_CHECKS0导完恢复并行导入分片文件最快需要先把SQL按表拆开多线程并发MySQL导出文件里其实已经带了SET FOREIGN_KEY_CHECKS0和SET UNIQUE_CHECKS0所以前两步不需要关心。如果导入慢优先检查是不是目标实例的innodb_buffer_pool_size太小或者innodb_flush_log_at_trx_commit1导致每次提交都刷盘。生产导入时临时改成innodb_flush_log_at_trx_commit2、sync_binlog0导入完成后改回来速度提升明显。View这些对象在导入时是按文件顺序创建的。如果视图依赖的表还没建好导入就会失败。MySQL的工具在导出时会把表先全部创建再导数据最后创建视图、函数、过程所以顺序通常是安全的。但如果你用--no-data只导结构并且手工分割过文件就要注意顺序了。4.4 函数、触发器里的DELIMITER问题mysqldump导出包含存储过程、函数、触发器时会自动带上DELIMITER语句DELIMITER ;; CREATE DEFINERold_user% PROCEDURE sp_example() BEGIN ... END ;; DELIMITER ;如果你用文本编辑器修改过SQL文件或者在导入前做了任何SQL片段的拼接一定要保证DELIMITER ;;和DELIMITER ;是成对出现的。我的脚本直接用mysql file.sql导入不做拼接所以没这个问题。但有人习惯把自定义的存储过程手动加在文件末尾这时候如果最后的语句没有DELIMITER配对导入过程会在一个不完整的过程定义处报语法错误。处理这个问题的标准做法是除非必要不要在导出文件里手工追加SQL。所有想在导入后执行的SQL比如修正统计信息、重置自增ID单独写一个post_import.sql在导入完成后单独执行mysql -h${DST_HOST} -u${DST_USER} -p${DST_PASS} ${DST_DB} post_import.sql4.5 大小写敏感与lower_case_table_names这是Windows/Linux跨平台迁移时最容易踩的坑。MySQL在Linux上默认lower_case_table_names0表名大小写敏感Windows上默认lower_case_table_names1表名大小写不敏感。如果你从Windows迁移到Linux源库建表时用了大写表名导出文件的CREATE TABLE \Order在Linux上是可以正常创建的。但应用层如果写的SQL是全小写order在Linux上就会报Table db_mall.order doesnt exist。这种问题非常难排查因为数据都在表也在只是大小写不匹配。我的脚本里没有专门做大小写转换因为这会引入复杂度和风险。但如果你判断目标环境是Linux且应用层SQL写得不够规范建议在导入前先检查一遍表名SELECT table_name FROM information_schema.tables WHERE table_schema db_mall;对比一下源库的表名列表大小写不一致的处理原则是以应用SQL里实际使用的大小写为准。把表RENAME成应用期望的形式而不是依赖数据库的lower_case_table_names配置去兜底因为改配置需要重启实例成本更高。4.6 无法导出事件Event的特殊情况--events参数能不能导出来取决于目标实例上event_scheduler是否开启以及账号是否有EVENT权限。如果导出时看到警告Warning: ... cannot be loaded because the event_scheduler is disabled说明事件没导出来。此时需要看源实例的event_scheduler状态SHOW GLOBAL VARIABLES LIKE event_scheduler;如果为OFF要么临时开启再导出要么手动从information_schema.EVENTS把定义捞出来。个人建议如果业务确实依赖事件调度折腾事件定义不如先开启event_scheduler导完再关。毕竟事件定义里通常还藏着一堆DEFINER信息手动重建的出错率太高。5. 常见问题速查表问题现象可能原因解决方法导入时报CREATE DATABASE权限不足用了无建库权限的账号导入删除导出文件中的CREATE DATABASE语句手工建库视图查询报Unknown user old_userDEFINER未替换干净补替换规则或者改用SQL SECURITY INVOKER视图应用连库报Access denied for user目标实例未创建对应账号或未授权执行CREATE USER和GRANT ALL PRIVILEGES ON db_mall.* TO ...导入过程卡住不动CPU却很高单个大表的INSERT在排序或索引创建查询SHOW PROCESSLIST确认状态必要时等待或调大innodb_buffer_pool_size数据导入后发现中文乱码源端与目标端字符集不一致导库时统一加--default-character-setutf8mb4检查连接参数GTID_PURGED相关报错未加--set-gtid-purgedOFF重新导出或导入前手工RESET MASTER导入后触发器不执行触发器DEFINER为不存在的账号替换触发器定义里的DEFINER或者重建触发器存储过程调用时报command denied目标账号缺少EXECUTE权限GRANT EXECUTE ON db_mall.* TO user%mysqldump导出时卡住大表正在被长查询锁住使用--single-transaction仍卡住检查是否有DDL锁等待这张表里大多数问题都可以归结为两个根因导出参数不完整和替换规则覆盖不全面。脚本的设计初衷就是让这两个根因被统一收敛但如果你使用的是其他同事的迁移脚本建议先跑一遍上述检查清单再决定要不要信任它。额外心得身份切换与实际建议关于这个脚本的最终形态我回顾了一下自己从第一次写迁移脚本到现在能在生产环境放心跑的过程最值钱的一条经验是不要想着一个脚本通吃所有迁移场景而是把脚本改造成参数化流程模板。库名、用户名、Host、端口、密码全部从环境变量或命令行读取脚本本身不包含任何环境相关的硬编码。换一台服务器换一个项目改参数就能跑。这也方便纳入统一的CMDB或部署平台让开发、测试、生产环境的迁移流程保持完全一致。另外实际执行迁移前我强烈建议按这个顺序过一遍先在测试实例上完整跑一遍脚本确认替换规则在本次数据形状下无遗漏对比导出前后表数量、行数、视图数量、函数数量SELECT COUNT(*) FROM information_schema.tables WHERE table_schemadb_shop; -- 导入后在目标库执行同样查询比对数量再抽查几条关键业务表数据SELECT COUNT(*) FROM db_shop.orders; SELECT COUNT(*) FROM db_mall.orders;最后再让应用联调。数据迁移这事儿从来都不是能导出、能导入就等于成功。真正算成功是业务方说没问题那一刻才算数。脚本只是把搬到另一台机器这件事从一小时压缩到十分钟但它的意义在于省出来的时间可以全部用来做验证和核对而不是浪费在手工改SQL、补权限、修触发器上。这个小脚本本身很简单不值钱。值钱的是它背后那套先检查、后替换、再验证的操作习惯——这事儿急不来但一旦养成后面每次迁移都会稳很多。
1. 项目概述1.1 核心需求解析先说结论这个脚本解决的是MySQL数据库迁移中最让人头疼的三个问题——跨实例搬迁、库名不一致、所有者不匹配。日常运维里我遇到过太多次这样的场景项目要从测试环境搬到生产环境库名从project_test改成project_prod或者接手别人留下的库Owner还是之前某个已经离职的账号应用连库时报各种权限错误。每次手工处理一遍少说半小时数据量一大一两个小时就耗进去了。这个脚本的核心思路并不复杂用mysqldump把数据导出成SQL文件然后通过文本替换把库名和所有者改掉再导入目标库。听起来平平无奇但真正把它做成一个一次执行、全程无脑的脚本并在生产环境跑稳中间有不少细节值得梳理——尤其是**视图View、触发器Trigger、存储过程Procedure**这些特殊对象的处理以及DEFINER权限问题稍不注意就会在导入时报一堆莫名其妙的错误。1.2 适合谁看如果你属于下面任何一类情况这篇文章值得花五分钟读完正在做MySQL跨服务器迁移但源库和目标库的库名不一致需要把某个库从A账号名下转移到B账号名下又不想一个个表ALTER TABLE ... OWNER TO虽然MySQL和PG不一样但思路类似迁移后应用连库报Access denied或command denied排查半天找不到原因写了不少shell脚本但sed、mysqldump参数总是记不牢每次都要现查。我尽量把每一步的为什么讲清楚而不仅仅是给一份能跑通的代码。毕竟脚本只有三四十行真正值钱的是里面那些踩过坑才知道的细节。2. 方案选型与设计思路2.1 主流迁移方式对比在做MySQL迁移时常见的方案有这么几类方案优点缺点适用场景mysqldump逻辑备份简单直接跨版本兼容好数据量大时慢几GB以上明显吃力中小型库一般5GB以内mydumper并行逻辑备份速度比mysqldump快不少支持并行需要额外安装部分旧版本对视图/触发器支持不佳中大库5GB-50GBPercona XtraBackup物理备份快几乎不影响业务要求同版本、同平台基本上Linux x86同类不支持跨大版本超大库50GB以上、在线迁移主从复制/克隆插件几乎零停机数据实时同步架构复杂需要交互和回放高可用、滚动迁移这个脚本选的是mysqldump原因是绝大多数迁移场景中目标库的大小都在可接受范围内。测试环境、预生产、甚至不少中小型生产库逻辑备份的时间成本完全可控。同时SQL文件可读、可改、可重用这正是我们要修改库名和所有者的前提——物理备份没法随手改文本mydumper产出的多文件结构也增加了批量替换的复杂度。提示如果你的库超过10GB建议先把方案从mysqldump调整到mydumper或物理备份但下面讲解的改库名/改所有者思路依然适用无非是替换的对象从单个SQL文件变成多个分片文件。2.2 为什么选择导出后改SQL而非导出前改权限有些人会想既然要改所有者能不能在源库上直接把表权限转移了再导出这个思路在MySQL里实现起来很不优雅。MySQL的所有者和PostgreSQL不同它没有严格的OWNER概念对象归属主要体现在两个地方表、视图、函数的DEFINER在mysqldump导出文件里表现为DEFINER\源用户host库级和使用对象的授权存储在mysql.db、mysql.tables_priv等系统表中。如果在源库上直接改权限你面临的问题是需要构造大量GRANT、REVOKE语句且只对当前实例生效目标实例如果没法网络连通到源库这些权限数据不会跟着SQL文件走视图的DEFINER在MySQL中想要修改往往要重建视图风险不小。所以我的结论是在SQL文本层面做替换是最干净、最可逆、最不容易出错的方式。导出前不动源库任何东西导出后只改文本目标库导入后一切归位。源库保持不变还可以随时回滚重来。2.3 脚本的三大核心模块拆分整个脚本我拆成了三块导出模块负责调mysqldump合理加参数保证数据完整性和一致性文本替换模块负责改库名、改DEFINER/所有者这是整个脚本的灵魂导入模块负责以干净的目标库状态导入SQL文件并对权限、字符集做二次校准。三个模块独立成函数出错时可以直接单独执行。比如导出已经完成了只想重新生成一遍替换后的SQL就单独跑第二个函数目标库导入出问题了可以清库后只重跑第三部分。这种设计在实际运维中非常实用——脚本不怕笨怕的是每次都要从头再来。3. 核心细节与脚本实现3.1 导出时的关键参数直接给可复用的导出命令mysqldump \ -h${SRC_HOST} -P${SRC_PORT} \ -u${SRC_USER} -p${SRC_PASS} \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --hex-blob \ --default-character-setutf8mb4 \ --no-tablespaces \ --result-file${EXPORT_FILE} \ ${SRC_DB}逐个参数说明--single-transaction对InnoDB表做一致性快照导出不加锁不影响线上业务。这是InnoDB引擎必备的参数没有它导出期间源库的写操作会被阻塞。MyISAM表不受此参数保护会退化到LOCK TABLES这点要心里有数。--routines导出存储过程和函数。默认mysqldump不导这个漏了的话应用层调用存储过程直接报PROCEDURE ... does not exist。--triggers导出触发器。同样是默认行为的一部分但如果你用了--no-data之类的参数最好显式加上。触发器这块有个容易忽略的问题导入到新库后某些老版本触发器里的DEFINER不一致会导致执行时报错下面专门讲。--events导出事件调度器Event Scheduler。--set-gtid-purgedOFF如果源库开启了GTID模式默认会导出SET GLOBAL.GTID_PURGED语句在目标库上执行时容易报GTID_PURGED can only be set when GTID_EXECUTED is empty。除非你做的是基于GTID的主从初始化否则一律OFF。--hex-blob二进制字段以十六进制方式导出避免字符集转换导致乱码或数据破坏。--no-tablespaces跳过表空间信息。常规环境下一个实例就一个表空间这个参数能避免部分权限不足时报错。--default-character-setutf8mb4这个强烈建议显式指定不依赖服务端默认值。我自己遇到过源库默认latin1导致导出的中文全部乱码的情况后来就养成了永远显式写字符集参数的习惯。--result-file指定输出文件同时避免mysqldump把统计信息打到标准输出污染重定向文件。这些参数组合起来解决的是数据完整性和跨环境兼容性两个问题。少一个参数可能在几十万行之后突然报错中断或者数据落地后出现无法解释的乱码。3.2 修改库名的正确姿势库名替换是整个脚本的技术含量所在。直接用sed s/旧库名/新库名/g替换整个文件往往会出问题。比如库名是db_shop把SQL里所有的db_shop替换成db_mall看起来没问题但实际上一旦库名存在大小写差异或者表名前缀跟库名重名就很容易误伤。我的做法是精确匹配带反引号的库名sed -i s/\${SRC_DB}\/\${DST_DB}\/g ${EXPORT_FILE}注意这里用的是反引号包裹MySQL导出文件里库名、表名默认都带反引号。精确匹配\db_shop这个组合能避免误替换掉db_shop_order这种表名前缀中包含库名的场景。但这里还有一个USE语句的问题。mysqldump默认导出的SQL里包含-- MySQL dump 10.13 Distrib 8.0.x CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_shop /*!40100 DEFAULT CHARACTER SET utf8mb4 */; USE db_shop;这是一把双刃剑好处是直接导入就能用自动建库坏处是如果目标库已经存在且数据不能丢CREATE DATABASE IF NOT EXISTS不会报错但后续的CREATE TABLE会因表已存在而中断。所以我的脚本里在替换库名后会删除CREATE DATABASE和USE语句sed -i /^CREATE DATABASE/d ${EXPORT_FILE} sed -i /^USE /d ${EXPORT_FILE}这样导入前必须先手工在目标实例建好空库授权好账号再执行导入。看起来多了一步操作但换来的是极高的可控性——所有表、数据、过程都在一个明确的、空的库里落地不会有任何覆盖已有数据的意外。提示如果你是第一次跑这个脚本强烈建议删除CREATE DATABASE和USE改为手工建库的方式。这个习惯能让你在导入前就把字符集、排序规则、加密方式都统一好而不是让备份文件替你做决定。3.3 修改所有者的完整方案所有者的修改分成两部分。第一部分替换DEFINER导出文件里表、视图、触发器、函数、过程的定义中都有类似这样的行CREATE DEFINERold_user% PROCEDURE sp_example(...) CREATE ALGORITHMUNDEFINED DEFINERold_user% SQL SECURITY DEFINER VIEW v_example AS ...如果目标库里不存在old_user导入时通常不会立即报错MySQL对表结构定义里的DEFINER检查不那么严格但一旦业务执行这些对象时报权限错误排查起来特别痛苦。尤其是视图SQL SECURITY DEFINER意味着执行视图时以定义者身份做权限检查定义者不存在或者权限不足查数据就报错。所以脚本里对DEFINER做统一替换sed -i s/DEFINER\${SRC_USER}\\%\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} sed -i s/DEFINER\${SRC_USER}\\localhost\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE}注意几个细节源账号的Host可能是%也可能是localhost需要分别处理目标统一用%还是localhost取决于目标实例的账号管理方式。如果目标库和应用服务器不在同一台机器localhost会连不上建议统一成%如果业务里有账号用了user10.0.%这种网段写法脚本里再加一条对应替换即可。我的做法是先用grep找出所有DEFINER行看有哪些Host再针对性补规则。第二部分处理目标库的账号和授权这是很多人容易忽略的一步。mysqldump默认不会导出mysql.user和mysql.db等系统表所以仅仅替换DEFINER目标库里可能连对应的登录账号都不存在。导入的数据表结构没有任何问题但应用连库时直接报Access denied。因此导入前需要准备一段权限脚本CREATE USER IF NOT EXISTS ${DST_USER}% IDENTIFIED BY ${DST_PASS}; GRANT ALL PRIVILEGES ON db_mall.* TO ${DST_USER}%; FLUSH PRIVILEGES;如果目标账号在目标实例里已经存在比如复用公共账号就用ALTER USER调整密码或者直接把授权加上。我见过不少踩坑现场数据导进去了视图建好了所有表都在但应用报SELECT command denied to user——其实就是忘了授权。还有个隐藏点mysql系统库不随mysqldump导出如果你迁移时想连账号带库一起走需要单独导出系统库mysqldump -u${SRC_USER} -p${SRC_PASS} --databases mysql --routines --tables user db tables_priv columns_priv mysql_priv.sql这种方式适合很少见的目标实例重建场景一般情况下不建议动mysql系统库很容易搞出权限错乱比手工授权复杂得多。3.4 完整脚本下面给出实际使用的完整脚本。参数通过环境变量或命令行传入我在脚本里做了简单的默认值处理方便不同环境复用。#!/bin/bash # 功能MySQL库快速导出→改库名/所有者→导入 # 用法./mysql_migrate.sh 源库名 目标库名 源用户 目标用户 [目标密码] set -euo pipefail # ---------- 配置区 ---------- SRC_HOST${SRC_HOST:-127.0.0.1} SRC_PORT${SRC_PORT:-3306} SRC_USER${SRC_USER:-root} SRC_PASS${SRC_PASS:-} DST_HOST${DST_HOST:-127.0.0.1} DST_PORT${DST_PORT:-3306} DST_USER${4:-app_user} DST_PASS${5:-app_pass123} WORK_DIR${WORK_DIR:-/tmp/mysql_migrate} SRC_DB${1:?源库名不能为空} DST_DB${2:?目标库名不能为空} EXPORT_FILE${WORK_DIR}/${SRC_DB}_$(date %Y%m%d%H%M%S).sql mkdir -p ${WORK_DIR} # ---------- 1. 导出 ---------- function do_export() { echo [1/4] 从 ${SRC_HOST}:${SRC_PORT}/${SRC_DB} 导出... mysqldump \ -h${SRC_HOST} -P${SRC_PORT} \ -u${SRC_USER} -p${SRC_PASS} \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --hex-blob \ --default-character-setutf8mb4 \ --no-tablespaces \ --result-file${EXPORT_FILE} \ ${SRC_DB} echo 导出完成${EXPORT_FILE} wc -l ${EXPORT_FILE} } # ---------- 2. 文本替换 ---------- function do_replace() { echo [2/4] 替换库名和DEFINER... # 库名精确匹配带反引号 sed -i s/\${SRC_DB}\/\${DST_DB}\/g ${EXPORT_FILE} # 删除自动建库与USE语句避免覆盖目标库已有对象 sed -i /^CREATE DATABASE/d ${EXPORT_FILE} sed -i /^USE /d ${EXPORT_FILE} # DEFINER替换常见四种Host写法都处理 sed -i s/DEFINER\${SRC_USER}\\%\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} sed -i s/DEFINER\${SRC_USER}\\localhost\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} sed -i s/DEFINER\${SRC_USER}\\127.0.0.1\/DEFINER\${DST_USER}\\%\/g ${EXPORT_FILE} echo 替换完成改动行数抽查 grep -c DEFINER ${EXPORT_FILE} || true } # ---------- 3. 目标库建库授权 ---------- function do_dst_prepare() { echo [3/4] 初始化目标库和账号... mysql -h${DST_HOST} -P${DST_PORT} -u${DST_USER} -p${DST_PASS} SQL CREATE DATABASE IF NOT EXISTS \${DST_DB}\ DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; CREATE USER IF NOT EXISTS ${DST_USER}% IDENTIFIED BY ${DST_PASS}; GRANT ALL PRIVILEGES ON \${DST_DB}\.* TO ${DST_USER}%; FLUSH PRIVILEGES; SQL echo 目标库与账号就绪。 } # ---------- 4. 导入 ---------- function do_import() { echo [4/4] 导入数据到 ${DST_HOST}:${DST_PORT}/${DST_DB} ... mysql \ -h${DST_HOST} -P${DST_PORT} \ -u${DST_USER} -p${DST_PASS} \ --default-character-setutf8mb4 \ ${DST_DB} ${EXPORT_FILE} echo 导入完成。 } # ---------- 主流程 ---------- do_export do_replace do_dst_prepare do_import echo 完成。耗时情况 ls -lh ${EXPORT_FILE}一个值得注意的点脚本里do_dst_prepare使用了目标库的${DST_USER}去连接MySQL执行建库授权。如果这个账号在目标实例里不存在这一步是连接不上的。所以实际执行时这里应该改为用目标实例的管理员账号比如root去连接建好库和账号之后导入部分再用应用账号。否则第一次跑脚本的时候do_dst_prepare可能卡在Access denied上。我把它拆开正是因为这两个阶段在权限要求上是不同的do_dst_prepare需要管理权限负责建库建账号do_import只需要目标库的INSERT/CREATE权限用应用账号即可。实战中我会把DST_ADMIN_USER和DST_ADMIN_PASS单独提取成变量避免为了省事而把管理员密码硬编码在应用账号段里。4. 实操过程中踩过的坑4.1set -euo pipefail的副作用脚本开头我加了set -euo pipefail好处是出错立刻退出不会带着坏数据往下走。但这也带来了一个问题grep -c在没有匹配到内容时返回非零退出码会把脚本卡死在中间。比如某些库完全没有游程、函数、视图grep -c DEFINER输出0同时退出码为1脚本直接中断。所以我在脚本里给所有可能返回非零的命令都加了|| true。别嫌丑在生产环境里脚本因为一个grep的退出码中断比脚本多跑了几行麻烦得多。这个教训是在某次凌晨迁移时用真金白银换来的当时整个任务跑到一半停在替换完成之后我盯着日志看了十分钟才反应过来是grep的锅。另外如果源库里的视图数量很多DEFINER替换之后需要手工验证一下有没有漏网之鱼。最稳的办法是导入之前在目标实例上跑一遍SELECT table_schema, table_name, table_type, DEFINER FROM information_schema.views WHERE table_schema db_mall;把所有视图的DEFINER列拉出来确认清一色是目标账号。如果还有源账号的残留多半是Host写法有别的变种比如user192.168.1.%脚本里没覆盖到。这时候比改脚本更快的方法是导入后在目标库执行ALTER DEFINER dst_user% VIEW db_mall.v_example AS SELECT ...;但改视图定义有风险最好还是导入前把DEFINER替换规则补齐。4.2CREATE DATABASE里的注释号陷阱前面提到要删除CREATE DATABASE和USE语句但MySQL导出文件里的CREATE DATABASE语句实际长这样CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_shop /*!40100 DEFAULT CHARACTER SET utf8mb4 */;它前面的注释里带着IF NOT EXISTS。如果你用sed /^CREATE DATABASE/d删除只能删掉以CREATE DATABASE开头的行。但MySQL导出的文件里这一行前通常还有一行注释-- Current Database: db_shop这一行不会被删掉但也没什么影响最多是导入时的注释信息有点过时。真正要注意的是如果源库名和目标库名在多个地方出现-- Current Database: ...、-- Table structure for table ...等注释行里的库名也会被替换。这没问题。但有一个特殊情况比较坑目标库名如果包含特殊字符比如db_mallv2sed替换时符号不需要转义但如果你用#作为sed分隔符就要小心了。我的建议是sed的分隔符坚持用|或者避免和路径、特殊符号撞车。写替换规则时可以先用grep -n看一眼实际文件结构再跑sed别盲目迷信一条命令通吃所有场景。例如grep -n CREATE DATABASE ${EXPORT_FILE} grep -n ^USE ${EXPORT_FILE} grep -n DEFINER ${EXPORT_FILE} | head -20这三条命令在任何一次迁移前都值得先跑一遍。4.3 导入速度优化mysql file.sql是最朴素的导入方式但并不是最快的。如果文件超过500MB导入时间会比较明显。我实际比较过几种方式方式速度备注mysql file.sql基准单线程适合100MB级别mysql --max-allowed-packet1G file.sql略快避免单条INSERT过大导致的packet错误先关外键约束再导快30%以上导入前SET FOREIGN_KEY_CHECKS0导完恢复并行导入分片文件最快需要先把SQL按表拆开多线程并发MySQL导出文件里其实已经带了SET FOREIGN_KEY_CHECKS0和SET UNIQUE_CHECKS0所以前两步不需要关心。如果导入慢优先检查是不是目标实例的innodb_buffer_pool_size太小或者innodb_flush_log_at_trx_commit1导致每次提交都刷盘。生产导入时临时改成innodb_flush_log_at_trx_commit2、sync_binlog0导入完成后改回来速度提升明显。View这些对象在导入时是按文件顺序创建的。如果视图依赖的表还没建好导入就会失败。MySQL的工具在导出时会把表先全部创建再导数据最后创建视图、函数、过程所以顺序通常是安全的。但如果你用--no-data只导结构并且手工分割过文件就要注意顺序了。4.4 函数、触发器里的DELIMITER问题mysqldump导出包含存储过程、函数、触发器时会自动带上DELIMITER语句DELIMITER ;; CREATE DEFINERold_user% PROCEDURE sp_example() BEGIN ... END ;; DELIMITER ;如果你用文本编辑器修改过SQL文件或者在导入前做了任何SQL片段的拼接一定要保证DELIMITER ;;和DELIMITER ;是成对出现的。我的脚本直接用mysql file.sql导入不做拼接所以没这个问题。但有人习惯把自定义的存储过程手动加在文件末尾这时候如果最后的语句没有DELIMITER配对导入过程会在一个不完整的过程定义处报语法错误。处理这个问题的标准做法是除非必要不要在导出文件里手工追加SQL。所有想在导入后执行的SQL比如修正统计信息、重置自增ID单独写一个post_import.sql在导入完成后单独执行mysql -h${DST_HOST} -u${DST_USER} -p${DST_PASS} ${DST_DB} post_import.sql4.5 大小写敏感与lower_case_table_names这是Windows/Linux跨平台迁移时最容易踩的坑。MySQL在Linux上默认lower_case_table_names0表名大小写敏感Windows上默认lower_case_table_names1表名大小写不敏感。如果你从Windows迁移到Linux源库建表时用了大写表名导出文件的CREATE TABLE \Order在Linux上是可以正常创建的。但应用层如果写的SQL是全小写order在Linux上就会报Table db_mall.order doesnt exist。这种问题非常难排查因为数据都在表也在只是大小写不匹配。我的脚本里没有专门做大小写转换因为这会引入复杂度和风险。但如果你判断目标环境是Linux且应用层SQL写得不够规范建议在导入前先检查一遍表名SELECT table_name FROM information_schema.tables WHERE table_schema db_mall;对比一下源库的表名列表大小写不一致的处理原则是以应用SQL里实际使用的大小写为准。把表RENAME成应用期望的形式而不是依赖数据库的lower_case_table_names配置去兜底因为改配置需要重启实例成本更高。4.6 无法导出事件Event的特殊情况--events参数能不能导出来取决于目标实例上event_scheduler是否开启以及账号是否有EVENT权限。如果导出时看到警告Warning: ... cannot be loaded because the event_scheduler is disabled说明事件没导出来。此时需要看源实例的event_scheduler状态SHOW GLOBAL VARIABLES LIKE event_scheduler;如果为OFF要么临时开启再导出要么手动从information_schema.EVENTS把定义捞出来。个人建议如果业务确实依赖事件调度折腾事件定义不如先开启event_scheduler导完再关。毕竟事件定义里通常还藏着一堆DEFINER信息手动重建的出错率太高。5. 常见问题速查表问题现象可能原因解决方法导入时报CREATE DATABASE权限不足用了无建库权限的账号导入删除导出文件中的CREATE DATABASE语句手工建库视图查询报Unknown user old_userDEFINER未替换干净补替换规则或者改用SQL SECURITY INVOKER视图应用连库报Access denied for user目标实例未创建对应账号或未授权执行CREATE USER和GRANT ALL PRIVILEGES ON db_mall.* TO ...导入过程卡住不动CPU却很高单个大表的INSERT在排序或索引创建查询SHOW PROCESSLIST确认状态必要时等待或调大innodb_buffer_pool_size数据导入后发现中文乱码源端与目标端字符集不一致导库时统一加--default-character-setutf8mb4检查连接参数GTID_PURGED相关报错未加--set-gtid-purgedOFF重新导出或导入前手工RESET MASTER导入后触发器不执行触发器DEFINER为不存在的账号替换触发器定义里的DEFINER或者重建触发器存储过程调用时报command denied目标账号缺少EXECUTE权限GRANT EXECUTE ON db_mall.* TO user%mysqldump导出时卡住大表正在被长查询锁住使用--single-transaction仍卡住检查是否有DDL锁等待这张表里大多数问题都可以归结为两个根因导出参数不完整和替换规则覆盖不全面。脚本的设计初衷就是让这两个根因被统一收敛但如果你使用的是其他同事的迁移脚本建议先跑一遍上述检查清单再决定要不要信任它。额外心得身份切换与实际建议关于这个脚本的最终形态我回顾了一下自己从第一次写迁移脚本到现在能在生产环境放心跑的过程最值钱的一条经验是不要想着一个脚本通吃所有迁移场景而是把脚本改造成参数化流程模板。库名、用户名、Host、端口、密码全部从环境变量或命令行读取脚本本身不包含任何环境相关的硬编码。换一台服务器换一个项目改参数就能跑。这也方便纳入统一的CMDB或部署平台让开发、测试、生产环境的迁移流程保持完全一致。另外实际执行迁移前我强烈建议按这个顺序过一遍先在测试实例上完整跑一遍脚本确认替换规则在本次数据形状下无遗漏对比导出前后表数量、行数、视图数量、函数数量SELECT COUNT(*) FROM information_schema.tables WHERE table_schemadb_shop; -- 导入后在目标库执行同样查询比对数量再抽查几条关键业务表数据SELECT COUNT(*) FROM db_shop.orders; SELECT COUNT(*) FROM db_mall.orders;最后再让应用联调。数据迁移这事儿从来都不是能导出、能导入就等于成功。真正算成功是业务方说没问题那一刻才算数。脚本只是把搬到另一台机器这件事从一小时压缩到十分钟但它的意义在于省出来的时间可以全部用来做验证和核对而不是浪费在手工改SQL、补权限、修触发器上。这个小脚本本身很简单不值钱。值钱的是它背后那套先检查、后替换、再验证的操作习惯——这事儿急不来但一旦养成后面每次迁移都会稳很多。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
