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

Background information

ApsaraDB RDS for MariaDB is fully compatible with the native MariaDB database service. The process of migrating data from a self-managed MariaDB database to an RDS instance is similar to the process of migrating data between two MariaDB database servers.

In this topic, the database server that hosts the self-managed MariaDB database runs Linux 7 and MariaDB 10.2.4.

Usage notes

After the migration is complete, table names are not case-sensitive. All table names are provided in lowercase.

Prerequisites

An IP address whitelist is configured and a public endpoint is obtained for the RDS instance. For more information, see Configure an IP address whitelist for an ApsaraDB RDS for MariaDB instance and Apply for or release a public endpoint for an ApsaraDB RDS for MariaDB instance.

Procedure

1. Use a remote connection tool to log on to the RDS instance and create an empty database. This database is the destination database to which data is migrated. For example, you can create an empty database named test001. For more information, see Connect to an ApsaraDB RDS for MariaDB instance.

2. Log on to the on-premises database server. Then, use the mysqldump tool that is provided with MariaDB to export the data of the on-premises database as a file. This file is known as a data file.

   mysqldump -h localhost -u <The username of the account of the self-managed database> -p --opt --default-character-set=utf8 --hex-blob <The name of the self-managed database> --skip-triggers > /tmp/<The name of the self-managed database>.sql

   Sample commands

   Note

   The user that is used in the subsequent steps must have the required permissions. For more information, see mariadb-dump/mysqldump.

   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 process. In this step, only data is exported. Stored procedures, triggers, and functions are not exported.

3. Use the mysqldump tool to export the stored procedures, triggers, and functions as a file. This file is known as a stored procedure file.

   mysqldump -h localhost -u <The username of the account of the self-managed database> -p --opt --default-character-set=utf8 --hex-blob <The name of the self-managed database> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<The name of the self-managed database>_trigger.sql

   Sample commands

   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 the self-managed MariaDB database does not contain stored procedures, triggers, or functions, you can skip this step. In this export process, you must remove DEFINER to ensure compatibility with ApsaraDB RDS for MariaDB.

4. Run the following commands to import the data file and the stored procedure file into the RDS instance:

   mysql -h <The public endpoint of the RDS instance> -P <The public port of the RDS instance> -u <The username of the privileged account of the RDS instance> -p <The name of the destination database on the RDS instance> < /tmp/<The name of the self-managed database>.sql
   mysql -h <The public endpoint of the RDS instance> -P <The public port of the RDS instance> -u <The username of the privileged account of the RDS instance> -p <The name of the destination database on the RDS instance < /tmp/<The name of the self-managed database>trigger.sql

   Sample commands

   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

5. Refresh the remote connection tool and view the tables in the destination database of the RDS instance. If the tables contain data, the migration is successful.