This topic describes how to use mysqldump to migrate data from a self-managed MariaDB database to an ApsaraDB RDS for MariaDB TX instance.

Background information

ApsaraDB RDS for MariaDB TX is fully compatible with the native MariaDB database service. The method that is used to migrate data from a self-managed MariaDB database to an ApsaraDB RDS for MariaDB TX instance is similar to the method that is used to migrate data between two MariaDB database servers.

In this topic, the self-managed database is deployed on an on-premises server that runs Linux 7 and MariaDB 10.2.4.

Precautions

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

Prerequisites

The IP address of the on-premises server is added to an IP address whitelist of the RDS instance, and a public endpoint is obtained for the RDS instance. For more information, see Configure an IP address whitelist or security group for an ApsaraDB RDS for MariaDB TX instance and Apply for or release a public endpoint for an ApsaraDB RDS for MariaDB TX instance.

Procedure

  1. Use a remote connection tool to log on to the RDS instance and create an empty database. The empty 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 TX instance.
  2. Log on to the on-premises server. Then, use mysqldump to export the data of the self-managed database as a file. This file is known as a data file.
    mysqldump -h localhost -u <The username of the account that has permissions on 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

    Example:

    mysqldump -h localhost -u root -p --opt --default-character-set=utf8 --hex-blob testdb --skip-triggers > /tmp/testdb.sql
    Notice Do not update data during the export process. In this step, only the data is exported. The 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 that has permissions on 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

    Example:

    mysqldump -h localhost -u root -p --opt --default-character-set=utf8 --hex-blob testdb -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/testdb_trigger.sql
    Note If the self-managed 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 TX.
  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

    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
  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.