ApsaraDB RDS:Use the mysqldump extension to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance

Prerequisites

IP address whitelists are configured, a public endpoint is obtained, and databases and accounts are created for the RDS instance. 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 RDS instance is similar to the process of migrating data from one MySQL server to another MySQL server.

Scenario

You want to migrate data of a self-managed MySQL instance to an RDS instance.

Precautions

After the migration is complete, the names of all tables that are migrated from the self-managed MySQL instance are in lowercase on the RDS instance. You can use the following methods to enable case-sensitivity for the RDS instance:

• When you created an RDS instance, set the Table Name Case Sensitivity parameter to Case-sensitive. For more information, see Create an ApsaraDB RDS for MySQL instance.
• If you want to enable case-sensitivity for an existing RDS instance, set the lower_case_table_names parameter to 0 for the RDS instance in the ApsaraDB RDS console. The value 0 indicates that the names of tables on the RDS instance are case-sensitive. For more information, see Modify the parameters of an ApsaraDB RDS for MySQL instance.
  Note

  • After you set the lower_case_table_names parameter to 0, do not change the value of this parameter to 1. If you change the value of this parameter to 1, the "ERROR 1146 (42S02): Table doesn't exist" error occurs. This error has a serious impact on your business.
  • If your RDS instance runs MySQL 8.0, you cannot reconfigure the lower_case_table_names parameter for your RDS instance.

Procedure

1. Use the mysqldump extension 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 the data. Wait until the export task is completed.
   1. In the Linux 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 database> --skip-triggers --skip-lock-tables > /tmp/<The name of the self-managed MySQL database>.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 contain stored procedures, triggers, or functions, you can skip this step.
2. Upload the exported files to a specified directory on an Elastic Compute Service (ECS) instance. In this example, the directory is /tmp.
   Note If the self-managed MySQL instance resides on an ECS instance, you can skip this step.
   
3. Run the following commands to import the exported files into the RDS instance:

   mysql -h <The endpoint that is used to connect to the RDS instance> -P <The port number that is used to connect to the RDS instance> -u <The username of the account that is used to log on to the RDS instance> -p <The name of the destination database on the RDS 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 RDS instance> -P <The port number that is used to connect to the RDS instance> -u <The username of the account that is used to log on to the RDS instance> -p <The name of the destination database on the RDS instance> < /tmp/<The name of the source database on the self-managed MySQL instance>Trigger.sql

   Note

   • The destination database on the RDS instance must be an existing database that you 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 RDS instance must be a privileged account or a standard account that has the read and write permissions.

   Example:

   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 RDS instance and check whether the data is normal. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.