How to migrate a self-managed MariaDB database to ApsaraDB RDS for MariaDB - ApsaraDB RDS

Use the mysqldump tool to migrate data from a self-managed MariaDB database to an ApsaraDB RDS for MariaDB instance.

Background

ApsaraDB RDS for MariaDB is fully compatible with native MariaDB. Therefore, migrating your database to an RDS instance is similar to migrating data between two MariaDB servers.

This topic uses a self-managed database running on Linux 7 and MariaDB 10.2.4 as an example.

Usage notes

After migration, table names are converted to lowercase and become case-insensitive.

Prerequisites

You have configured an IP address whitelist and applied for a public endpoint for your ApsaraDB RDS for MariaDB instance.

Procedure

Use a client tool to connect to your ApsaraDB RDS for MariaDB instance and create an empty database. For this example, the database is named test001. In the MySQL-Front client, connect to the MariaDB instance and run the create database test001; command in the SQL Editor to create the target database. After the command runs, the test001 database appears in the database list on the left.
Log on to your local Linux server and use the mysqldump tool to export the database data to a data file.
```
mysqldump -h localhost -u <source_username> -p --opt --default-character-set=utf8 --hex-blob <source_database_name> --skip-triggers > /tmp/<source_database_name>.sql
```
Example:

Note
The user account in the following example must have specific permissions. For instructions, see Permissions settings.
```
mysqldump -h localhost -u user -p --opt --default-character-set=utf8 --hex-blob testdb --skip-triggers > /tmp/testdb.sql
```
Important
Do not update data during the export. This step exports only the database data, not the stored procedures, triggers, or functions.

Use mysqldump to export stored procedures, triggers, and functions.

mysqldump -h localhost -u <source_username> -p --opt --default-character-set=utf8 --hex-blob <source_database_name> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<source_database_name>_trigger.sql

Example:

mysqldump -h localhost -u user -p --opt --default-character-set=utf8 --hex-blob testdb -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/testdb_trigger.sql

Note

If your database uses no stored procedures, triggers, or functions, skip this step. When exporting these objects, remove the DEFINER clause for compatibility with ApsaraDB RDS for MariaDB.

Run the following commands to import the exported files into the target ApsaraDB RDS for MariaDB instance.

mysql -h <rds_public_endpoint> -P <rds_port> -u <privileged_account> -p <target_database_name> < /tmp/<source_database_name>.sql
mysql -h <rds_public_endpoint> -P <rds_port> -u <privileged_account> -p <target_database_name> < /tmp/<source_database_name>_trigger.sql

Example:

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

Refresh your client tool and check the tables in the target database to verify that the data was migrated successfully.