ApsaraDB RDS:Migrate data between ApsaraDB RDS for MariaDB instances

Prerequisites

• CentOS 7 and MySQL 5.7 are installed on your computer or an Elastic Compute Service (ECS) instance.

• The public IP address of your computer or the ECS instance is added to the IP address whitelists of two RDS instances.

• You have applied for public IP addresses for two RDS instances.

Procedure

1. Use a client to log on to the destination RDS instance and create a database.

2. Use mysqldump to export the database that you want to migrate of the source RDS instance as a data file.

   mysqldump -h <Public IP address of the source RDS instance> -P <Port of the source RDS instance> -u <Privileged account of the source RDS instance> -p<Password of the privileged account> --opt --default-character-set=utf8 --hex-blob <Name of the database to be migrated> --skip-triggers > /tmp/<Name of the database to be migrated>.sql

   Example

   mysqldump -h rm-xxx.mariadb.rds.aliyuncs.com -P 3306 -u test -pTestxxx --opt --default-character-set=utf8 --hex-blob testdb --skip-triggers > /tmp/testdb.sql

   Important

   Do not update data during the export process. This step only exports the data. It does not export stored procedures, triggers, or functions.

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

   mysqldump -h <Public IP address of the source RDS instance> -P <Port of the source RDS instance> -u <Privileged account of the source RDS instance> -p<Password of the privileged account> --opt --default-character-set=utf8 --hex-blob <Name of the database to be migrated> -R > /tmp/<Name of the database to be migrated>trigger.sql

   Example

   mysqldump -h rm-xxx.mariadb.rds.aliyuncs.com -P 3306 -u test -pTestxxx --opt --default-character-set=utf8 --hex-blob testdb -R > /tmp/testdbtrigger.sql

   Note

   If the database does not have stored procedures, triggers, or functions, skip this step.

4. Execute the following statements to import the data file, stored procedures, triggers, and functions to the destination RDS instance.

   mysql -h <Public IP address of the destination RDS instance> -P <Port of the destination RDS instance> -u <Privileged account of the destination RDS instance> -p<Password of the privileged account> <Database name of the destination RDS instance> < /tmp/<Name of the database to be migrated>.sql
   mysql -h <Public IP address of the destination RDS instance> -P <Port of the destination RDS instance> -u <Privileged account of the destination RDS instance> -p<Password of the privileged account> <Database name of the destination RDS instance> < /tmp/<Name of the database to be migrated>trigger.sql

   Example

   mysql -h rm-xxx.mariadb.rds.aliyuncs.com -P 3306 -u test2 -pTest2xxx test001 < /tmp/testdb.sql
   mysql -h rm-xxx.mariadb.rds.aliyuncs.com -P 3306 -u test2 -pTest2xxx test001 < /tmp/testdbtrigger.sql