ApsaraDB RDS:Migrate data between ApsaraDB RDS for MariaDB instances

Use DTS

For more information about how to use DTS to migrate data between RDS instances, see Migrate data between ApsaraDB RDS for MariaDB instances.

Use mysqldump

In this topic, the RDS instances between which you migrate data run MariaDB 10.3.

Prerequisites

• CentOS7 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. For more information, see Configure an IP address whitelist for an ApsaraDB RDS for MariaDB instance.

• You have applied for public IP addresses for two RDS instances. For more information, see Apply for or release a public endpoint.

Procedure

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

2. Use the mysqldump to export the data of 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

   Note

   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 the 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/testdbtriggertrigger.sql