This topic describes how to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance by using the mysqldump utility. The mysqldump utility is easy to use but causes long downtime. It is suitable when the data size is small or long downtime is allowed.

Prerequisites

On the ApsaraDB RDS for MySQL instance, IP address whitelists are configured, a public endpoint is obtained, and databases and accounts are created. For more information, see General workflow to use ApsaraDB RDS for MySQL.

Background information

ApsaraDB RDS for MySQL is fully compatible with open source MySQL. The process of migrating data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance is similar to the process of migrating data from one MySQL server to another.

Note

Scenario

You need to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance.

Precautions

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

Procedure

  1. Use mysqldump to export the data, stored procedures, triggers, and functions of the self-managed MySQL instance.
    Note When the export task is in progress, do not update data. Wait until the export task is complete.
    1. In the Linux command-line interface (CLI), run the following command to export the data as a file:
      mysqldump -h 127.0.0.1 -u root -p --opt --default-character-set=utf8 --hex-blob <The name of the self-managed MySQL instance> --skip-triggers --skip-lock-tables > /tmp/<The name of the self-managed MySQL instance>.sql

      Example:

      mysqldump -h 127.0.0.1 -u root -p --opt --default-character-set=utf8 --hex-blob testdb --skip-triggers --skip-lock-tables > /tmp/testdb.sql
    2. In the Linux CLI, run the following command to export the stored procedures, triggers, and functions as a file:
      mysqldump -h 127.0.0.1 -u root -p --opt --default-character-set=utf8 --hex-blob <The name of the self-managed MySQL instance> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<The name of the self-managed MySQL instance>Trigger.sql

      Example:

      mysqldump -h 127.0.0.1 -u root -p --opt --default-character-set=utf8 --hex-blob testdb -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/testdbTrigger.sql
      Note If the self-managed MySQL instance does not have stored procedures, triggers, or functions, you can skip this step.
  2. Upload the two exported files to a specified path on an Elastic Compute Service (ECS) instance. In this example, the path is /tmp.
    Note If the self-managed MySQL instance resides on an ECS instance, you can skip this step.
    Path on an ECS instance
  3. Run the following commands to import the two exported files into the ApsaraDB RDS for MySQL instance:
    mysql -h <The endpoint that is used to connect to the ApsaraDB RDS for MySQL instance> -P <The port number that is used to connect to the ApsaraDB RDS for MySQL instance> -u <The username of the account that is used to log on to the ApsaraDB RDS for MySQL instance> -p <The name of the destination database on the ApsaraDB RDS for MySQL instance> < /tmp/<The name of the source database on the self-managed MySQL instance>.sql
    mysql -h <The endpoint that is used to connect to the ApsaraDB RDS for MySQL instance> -P <The port number that is used to connect to the ApsaraDB RDS for MySQL instance> -u <The username of the account that is used to log on to the ApsaraDB RDS for MySQL instance> -p <The name of the destination database on the ApsaraDB RDS for MySQL instance> < /tmp/<The name of the source database on the self-managed MySQL instance>Trigger.sql
    Note
    • The destination database on the ApsaraDB RDS for MySQL instance must be an existing database that you have created. For more information about how to create a database, see Create a database on an ApsaraDB RDS for MySQL instance.
    • The account that is used to log on to the ApsaraDB RDS for MySQL instance must be a privileged account or a standard account that has the read and write permissions.

    Examples:

    mysql -h rm-bpxxxxx.mysql.rds.aliyuncs.com -P 3306 -u testuser -p testdb  < /tmp/testdb.sql
    mysql -h rm-bpxxxxx.mysql.rds.aliyuncs.com -P 3306 -u testuser -p testdb  < /tmp/testdbTrigger.sql
  4. After the import is complete, log on to the ApsaraDB RDS for MySQL instance and check whether the data is normal. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.