This topic describes how to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance by using the mysqldump plug-in. The mysqldump plug-in is easy to use but causes long downtime. The mysqldump plug-in is suitable for scenarios in which the data volume is small or long downtime does not have a negative impact on your business.
Prerequisites
IP address whitelists are configured, a public endpoint is obtained, and databases and accounts are created in the RDS for MySQL 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 ApsaraDB RDS for MySQL instance is similar to the process of migrating data from one MySQL server to another MySQL server.
- The mysqldump-based migration process is complex. We recommend that you use Data Transmission Service (DTS) to migrate data. For more information, see Overview of data migration methods.
- For more information about the parameters of the mysqldump plug-in, see the official MySQL documentation.
Scenario
You want to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL 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 ApsaraDB RDS for MySQL instance. You can use the following method to configure the names of tables on the ApsaraDB RDS for MySQL instance to be case-sensitive:
- 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 the ApsaraDB RDS for MySQL instance runs MySQL 8.0, you cannot reconfigure the lower_case_table_names parameter for the instance.
Procedure
- Use the mysqldump plug-in 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.
- 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
- 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.
- In the Linux command-line interface (CLI), run the following command to export the
data as a file:
- Upload the 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.
- Run the following commands to import the 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 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
- 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.