Use the mysqldump extension to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

If you need to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance and the data volume is small or a longer downtime is acceptable, the mysqldump extension is a suitable method. It exports both the schema and data of a self-managed database to a text file, which includes SQL statements necessary for creating tables and inserting data, ensuring data integrity and consistency.

Prerequisites

For more information, see Getting Started for operations such as setting the whitelist for the RDS instance, requesting an Internet address, and creating databases and accounts.

Background information

Because RDS is fully compatible with native database services, migrating an existing database to an RDS instance is similar to migrating a database from one MySQL server to another.

Note

The DTS tool is more convenient for smooth migration. It is recommended to use DTS to migrate data.
For additional mysqldump parameters, refer to the official MySQL documentation.

Scenarios

Migrate a self-managed MySQL database to an RDS MySQL instance.

Usage notes

By default, when a self-managed database is migrated to RDS, table names are converted to lowercase. To configure the RDS MySQL instance to be case-sensitive for table names, you can use one of the following two methods.

When creating an RDS MySQL instance or creating an RDS MySQL Serverless instance, set Table Name Case Sensitivity to Case Sensitive.
For an existing instance, you can set the instance parameter lower_case_table_names to 0 to enable case sensitivity for table names.
Warning
- After setting the lower_case_table_names parameter to 0, do not revert it to 1, as it may cause a ERROR 1146 (42S02): Table doesn't exist fault, potentially impacting your business.
- If the RDS MySQL instance runs MySQL 8.0, you cannot modify this parameter after creation.

Procedure

This example uses a Linux OS, but you can also run the mysqldump command in Terminal on macOS or in Command Prompt on Windows.

Use the mysqldump extension to export the data, stored procedures, triggers, and functions of the self-managed MySQL instance.
Important
- Avoid updating data during the export task; wait until the task is complete.
- The user mentioned in the following steps must have the necessary permissions. For detailed operations on permission settings, refer to Permission Settings.
1. In the Linux CLI, execute the following command to export the data of the self-managed MySQL database.
```
mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob <self-managed database name> --skip-triggers --skip-lock-tables > /tmp/<self-managed database name>.sql
```
  Note
  To export data from an RDS MySQL database using mysqldump, replace the connection address, account, password, and database name in the command with the details of the RDS MySQL instance.
  
  Example
```
mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob testdb --skip-triggers --skip-lock-tables > /tmp/testdb.sql
```
2. In the Linux CLI, execute the following command to export the stored procedures, triggers, and functions to a file.
```
mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob <self-managed database name> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<self-managed database name>Trigger.sql
```
  Example
```
mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob testdb -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/testdbTrigger.sql
```
  Note
  Skip this step if the self-managed MySQL instance does not contain stored procedures, triggers, or functions.
Upload the two exported files to the ECS instance. In this example, the path is /tmp.

Note
If the self-managed MySQL instance is on an ECS instance, you can skip this step.

Execute the following commands to import the exported files into the ApsaraDB RDS for MySQL instance.

mysql -h <RDS instance connection address> -P <RDS instance port> -u <RDS instance account> -p <RDS database name> < /tmp/<self-managed database name>.sql
mysql -h <RDS instance connection address> -P <RDS instance port> -u <RDS instance account> -p <RDS database name> < /tmp/<self-managed database name>Trigger.sql

Note

The destination database on the ApsaraDB RDS for MySQL instance must be an existing database you created. For more information about creating databases, see Manage Databases.
The account used to log on to the ApsaraDB RDS for MySQL instance must be a privileged account or a standard account with read and write permissions.

Example

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

After a successful import, log on to the RDS database through DMS to verify the data integrity.