同服务器不同schema下使用mysqldump备份恢复指定表及存储程序方案咨询

此前迁移失败的原因 #

当mysqldump命令中明确指定了要导出的表名时，--routines、--events参数会自动失效，所以之前的操作仅能导出表结构和数据，无法导出存储过程、事件等对象。

具体操作步骤 #

1. 导出数据（分两部分执行，也可合并为单文件） #

导出指定表（含触发器）

执行以下命令导出你需要的生产用表，命令不会生成任何库级操作语句：

mysqldump -u 你的数据库用户名 -p staging库名 \
--no-create-db \
--skip-add-drop-table \
--triggers \
表名1 表名2 表名3 > tables_dump.sql

• 参数说明：
  • --no-create-db：不生成CREATE DATABASE语句，避免覆盖目标库配置
  • --skip-add-drop-table：不生成DROP TABLE语句，如果你需要覆盖目标库的同名表可以删除该参数，不会影响原始staging库
  • --triggers：导出对应表的所有触发器，默认开启可省略

导出全量存储过程、函数、事件

单独导出routines和events，同时删除所有可能切换库的USE语句，避免操作跳到staging库：

mysqldump -u 你的数据库用户名 -p staging库名 \
--no-create-db \
--no-data \
--no-tablespaces \
--routines \
--events \
--skip-triggers \
--skip-definer | sed -e '/^USE /d' > routines_events_dump.sql

• 参数说明：
  • --no-data：不导出任何表数据，仅导出结构对象
  • --routines：导出所有存储过程、函数
  • --events：导出所有事件调度器
  • --skip-definer：去除DEFINER权限声明，避免导入时出现权限不足问题
  • sed -e '/^USE /d'：删除所有切换库的语句，彻底避免误操作staging库

如果需要合并为单个导出文件，执行以下命令即可：

(mysqldump -u 你的数据库用户名 -p staging库名 --no-create-db --skip-add-drop-table 表名1 表名2 表名3; mysqldump -u 你的数据库用户名 -p staging库名 --no-create-db --no-data --no-tablespaces --routines --events --skip-triggers --skip-definer | sed -e '/^USE /d') > full_migration_dump.sql

2. 导入到目标Schema #

首先创建目标库（如果还未创建）：

CREATE DATABASE IF NOT EXISTS 目标库名 DEFAULT CHARSET utf8mb4;

然后直接指定目标库名导入即可，所有操作仅作用在目标库上：

# 分文件导入
mysql -u 你的数据库用户名 -p 目标库名 < tables_dump.sql
mysql -u 你的数据库用户名 -p 目标库名 < routines_events_dump.sql

# 单文件导入
# mysql -u 你的数据库用户名 -p 目标库名 < full_migration_dump.sql

方案合规性验证 #

• 满足要求A：指定表导出时自动携带对应触发器，单独导出的routines、events为staging库全量对象，可筛选表的同时导出全部所需对象
• 满足要求B：导出文件无USE、CREATE DATABASE语句，导入时指定目标库名即可直接写入不同Schema
• 满足要求C：所有导出操作为只读操作，不会修改staging库任何内容；导出文件无任何针对staging库的删改语句，导入操作全程仅作用在指定的目标库，完全没有擦除原始Schema的风险

内容的提问来源于stack exchange，提问作者Koushik Barhale