This topic describes how to use mysqldump to migrate data from a self-managed MySQL database to PolarDB for MySQL.
Prerequisites
The following steps have been completed for the PolarDB for MySQL cluster:
Compare migration methods
You can migrate data from a self-managed MySQL database to PolarDB for MySQL by using mysqldump or Data Transmission Service (DTS). The following table describes the differences between the two migration methods.
Item | mysqldump | DTS |
The version of self-managed MySQL database | Unlimited | The version of the self-managed MySQL database is 5.1, 5.5, 5.6, 5.7, or 8.0. |
Schema migration and full data migration | Supported | Supported |
Incremental data migration | Not supported | Supported |
Migration without service interruption | Not supported | Supported |
For more information about how to use DTS, see Migrate data from a self-managed MySQL database to a PolarDB for MySQL cluster.
Considerations
After the migration is complete, the destination table names are not case-sensitive. All table names are provided in lowercase.
Procedure
MySQL 8.0 and the Linux operating system are used in the example of this topic.
Use mysqldump to export data, stored procedures, triggers, and functions of the self-managed MySQL database.
NoteDo not update data until the data export task is complete.
In the Linux command-line interface (CLI), run the following command to export data:
NoteWhen you enter the endpoint for the self-managed database, take note of the following rules:
If the self-managed MySQL database is deployed on an ECS instance, enter
127.0.0.1
.If the self-managed MySQL database is deployed on an on-premises machine, enter the public endpoint of the database.
mysqldump -h <The endpoint of your database> -u user -p --opt --default-character-set=utf8 --hex-blob <The name of your database> --skip-triggers --skip-lock-tables > /tmp/<The name of your database>.sql
Example
mysqldump -h 127.0.0.1 -u user -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 stored procedures, triggers, and functions. This is an optional step.
NoteIf the database does not have stored procedures, triggers, or functions, skip this step.
mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob <The name of your database> -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/<The name of your database>Trigger.sql
Example
mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob testdb -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/testdbTrigger.sql
Run the following commands to import the exported files into the PolarDB cluster:
mysql -h <The endpoint of the PolarDB cluster> -P <The port of the PolarDB cluster> -u <The username of the account of the PolarDB cluster> -p <The name of the PolarDB database> < /tmp/<The name of your database>.sql mysql -h <The endpoint of the PolarDB cluster> -P <The port of the PolarDB cluster> -u <The username of the account of the PolarDB cluster> -p <The name of the PolarDB database> < /tmp/<The name of your database>Trigger.sql
Example
mysql -h polardbtest.mysql.polardb.rds.aliyuncs.com -P 3306 -u testuser -p testdb < /tmp/testdb.sql mysql -h polardbtest.mysql.polardb.rds.aliyuncs.com -P 3306 -u testuser -p testdb < /tmp/testdbTrigger.sql
NoteThe PolarDB database name must be the name of the database that is created on the PolarDB cluster. For more information about how to create a database, see Create a database.
The account of the PolarDB cluster must be a privileged account or a standard account that has the read and write permissions.
After the data is imported, you can log on to the PolarDB cluster database to check the data. For more information, see Connect to a cluster.
FAQ
What do I do if the error message Access denied; you need (at least one of) the SUPER privilege(s) for this operation
is returned?
SUPER permissions are required to execute the SQL statements in the script. You can first delete the statements that require the SUPER permissions, and execute the script.