All Products
Search
Document Center

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

Last Updated:Sep 21, 2023

This topic describes how 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.