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
Note 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

Note MySQL 8.0 and the Linux operating system are used in the example of this topic.
  1. Use mysqldump to export data, stored procedures, triggers, and functions of the self-managed MySQL database.
    Note Do not update data until the data export task is complete.
    1. In the Linux command-line interface (CLI), run the following command to export data:
      Note When 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
    2. In the Linux CLI, run the following command to export stored procedures, triggers, and functions. This is an optional step.
      Note If 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
  2. 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
    Note
    • The 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.
  3. 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.