ApsaraDB RDS:Migrate data between ApsaraDB RDS for MariaDB instances

Prerequisites

• CentOS 7 and MySQL 5.7 are installed on a local host or an ECS instance.

• The whitelists of both ApsaraDB RDS for MariaDB instances are configured to allow the public IP address of the local host or ECS instance.

• Both ApsaraDB RDS for MariaDB instances must have a public IP address that has been applied for.

Procedure

1. Use a client to connect to the destination ApsaraDB RDS for MariaDB instance and create an empty database. In a database management tool such as MySQL-Front, execute the SQL statement create database test001; to create the destination database test001. After the database is created, the test001 node appears in the database object tree on the left.

2. On the CentOS 7 host, use the built-in mysqldump tool to export the database from the source instance to a data file.

   mysqldump -h <source_instance_endpoint> -P <source_instance_port> -u <privileged_account> -p<password> --opt --default-character-set=utf8 --hex-blob <database_name> --skip-triggers > /tmp/<database_name>.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. This step exports only data, not stored procedures, triggers, or functions.

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

   mysqldump -h <source_instance_endpoint> -P <source_instance_port> -u <privileged_account> -p<password> --opt --default-character-set=utf8 --hex-blob <database_name> -R > /tmp/<database_name>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

   Skip this step if the database contains no stored procedures, triggers, or functions.

4. Run the following commands to import the data file, stored procedures, triggers, and functions into the destination ApsaraDB RDS for MariaDB instance.

   mysql -h <destination_instance_endpoint> -P <destination_instance_port> -u <privileged_account> -p<password> <destination_database_name> < /tmp/<database_name>.sql
   mysql -h <destination_instance_endpoint> -P <destination_instance_port> -u <privileged_account> -p<password> <destination_database_name> < /tmp/<database_name>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