This topic describes how to migrate data from an on-premises MariaDB database to an ApsaraDB RDS for MariaDB TX instance by using mysqldump.

Background information

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

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

Precautions

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

Prerequisites

An IP address whitelist that contains the IP address of the on-premises database server is configured, and a public endpoint is obtained for the RDS instance. For more information, see Configure a whitelist 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. 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 that has permissions on the on-premises database> -p --opt --default-character-set=utf8 --hex-blob <The name of the on-premises database> --skip-triggers > /tmp/<The name of the on-premises 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 this 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 on-premises database> -p --opt --default-character-set=utf8 --hex-blob <The name of the on-premises database> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<The name of the on-premises 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 on-premises database does not have 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 for the RDS instance> -p <The name of the destination database on the RDS instance> < /tmp/<The name of the on-premises 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 for the RDS instance> -p <The name of the destination database on the RDS instance> < /tmp/<The name of the on-premises database>trigger.sql

    Examples:

    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.