如何使用mysqldump工具高效迁移RDS for MySQL数据？

mysqldump是一种用于将MySQL数据库的数据导出为SQL文本文件的工具，它能够生成包含创建表、插入数据等SQL语句的文件，从而方便地迁移和备份数据，以下是使用mysqldump迁移RDS for MySQL数据的详细步骤：

前提条件

1、RDS实例设置：确保RDS实例设置了白名单，申请了外网地址，并创建了数据库和账号。

2、权限设置：执行mysqldump操作的用户需要具备相应的权限。

适用场景

自建MySQL数据库迁移至RDS MySQL或RDS MySQL Serverless实例内。

注意事项

默认情况下，自建库迁移到RDS以后表名统一变为小写，可以通过设置实例参数来区分表名大小写。

lower_case_table_names参数设置为0后，务必不要再次设置为1，否则可能导致ERROR 1146错误。

RDS MySQL 8.0版本实例暂不支持修改该参数，请在创建实例时进行设置。

操作步骤

1. 导出自建数据库的数据

在Linux命令行下导出自建数据库的数据，命令如下：

mysqldump h 127.0.0.1 u user p opt defaultcharacterset=utf8 hexblob <自建数据库名> skiptriggers skiplocktables > /tmp/<自建数据库名>.sql

示例：

mysqldump h 127.0.0.1 u user p opt defaultcharacterset=utf8 hexblob testdb skiptriggers skiplocktables > /tmp/testdb.sql

2. 导出存储过程、触发器和函数（可选）

如果数据库中没有使用存储过程、触发器和函数，可跳过此步骤，在Linux命令行下导出存储过程、触发器和函数，命令如下：

mysqldump h 127.0.0.1 u user p opt defaultcharacterset=utf8 hexblob <自建数据库名> R | sed e 's/DEFINER[ ]*=[ ]*[^*]**/*/' > /tmp/<自建数据库名>Trigger.sql

示例：

mysqldump h 127.0.0.1 u user p opt defaultcharacterset=utf8 hexblob testdb R | sed e 's/DEFINER[ ]*=[ ]*[^*]**/*/' > /tmp/testdbTrigger.sql

3. 将导出的文件上传到ECS实例上

如果自建数据库原本就在ECS实例上，可跳过本步骤。

4. 导入数据到目标RDS

将导出的文件导入到目标RDS中，命令如下：

mysql h <RDS实例连接地址> P <RDS实例端口> u <RDS实例账号> p <RDS数据库名称> < /tmp/<自建数据库名>.sql
mysql h <RDS实例连接地址> P <RDS实例端口> u <RDS实例账号> p <RDS数据库名称> < /tmp/<自建数据库名>Trigger.sql

示例：

mysql h rmbpxxxxx.mysql.rds.aliyuncs.com P 3306 u testuser p testdb  < /tmp/testdb.sql
mysql h rmbpxxxxx.mysql.rds.aliyuncs.com P 3306 u testuser p testdb  < /tmp/testdbTrigger.sql

5. 验证数据迁移结果

导入成功后登录RDS实例数据库中查看数据是否正常。

FAQs

Q1: OPERATION need to be executed set by ADMIN报错怎么解决？

A1: 可能是SQL脚本里面包括视图，触发器，存储过程等对象的definer问题，或者含有set global类SQL导致，详情请参见RDS MySQL出现“OPERATION need to be executed set by ADMIN”报错。

Q2: Access denied; you need (at least one of) the SUPER privilege(s) for this operation报错怎么解决？

A2: SQL脚本里面包括SUPER权限的语句，将相关语句删除再执行。