The mysqldump utility is easy to use but requires extensive downtime. It is suitable for scenarios where the data volume is small or extensive downtime is allowed.

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 that of migrating data from one MySQL server to another.

Note The migration process that is based on mysqldump is complex. We recommend that you use Data Transmission Service (DTS) to perform the migration. For more information, see Overview of data migration scenarios.

Precautions

After the migration, table names are all in lowercase and not case-sensitive.

Prerequisites

  • On the RDS 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 RDS for MySQL.
  • An Elastic Compute Service (ECS) instance is purchased.

Procedure

Before the migration, you must create an account that is used to migrate data from the self-managed MySQL instance. In addition, you must grant the read and write permissions on specific self-managed MySQL databases to the account.

  1. Create an account on the self-managed MySQL instance.
    CREATE USER'username'@'host' IDENTIFIED BY 'password';

    Parameter description:

    • username: the username of the account.
    • host: the host from which the account is authorized to log on to the self-managed MySQL instance. If you want to allow access from a local host, set this parameter to localhost. If you want to allow access from all hosts, set this parameter to a percent sign (%) wildcard.
    • password: the password of the account.

    For example, you can execute the following statement to create an account with the username William and the password Changme123. The account is authorized to log on to the self-managed MySQL instance from all hosts.

    CREATE USER'William'@'%' IDENTIFIED BY 'Changme123';
  2. Grant permissions to the account on the self-managed MySQL instance.
    GRANT SELECT ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
    GRANT REPLICATION SLAVE ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

    Parameter description:

    • SELECT and REPLICATION SLAVE: the permissions that you want to grant to the account. If you want to grant all permissions to the account, enter ALL.
    • databasename: the name of the self-managed MySQL database. If you want to grant the permissions on all self-managed MySQL databases to the account, set this parameter to an asterisk (*) wildcard.
    • tablename: the name of the table whose data you want to migrate. If you want to grant the permissions on all tables to the account, set this parameter to an asterisk (*) wildcard.
    • username: the username of the account.
    • host: the host from which the account is authorized to log on to the self-managed MySQL instance. If you want to allow access from a local host, set this parameter to localhost. If you want to allow access from all hosts, set this parameter to a percent sign (%) wildcard.
    • WITH GRANT OPTION: specifies to authorize the account to use the GRANT statement. This parameter is optional.

    For example, you can execute the following statement to grant the permissions on all self-managed MySQL databases and tables to the William account that is authorized to log on to the self-managed MySQL instance from all hosts:

    GRANT ALL ON*. * TO 'William'@'%';
  3. Use mysqldump to export data from the self-managed MySQL database as a data file.
    Note Do not update data during the data export. In this step, only data is exported. Stored procedures, triggers, and functions are not exported.
    mysqldump -h localIp -u userName -p --opt --default-character-set=utf8 --hex-blob dbName --skip-triggers --skip-lock-tables > /tmp/dbName.sql

    Parameter description:

    • localIp: the IP address of the host where the self-managed MySQL database resides.
    • userName: the account that is used to migrate data from the self-managed MySQL database.
    • dbName: the name of the self-managed MySQL database.
    • /tmp/dbName.sql: the name of the exported data file.
  4. Use mysqldump to export stored procedures, triggers, and functions.
    Note If the self-managed MySQL database does not have stored procedures, triggers, or functions, you can skip this step. In this export process, you must remove DEFINER to ensure compatibility with ApsaraDB RDS for MySQL.
    mysqldump -h localIp -u userName -p --opt --default-character-set=utf8 --hex-blob dbName -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/triggerProcedure.sql

    Parameter description:

    • localIp: the IP address of the host where the self-managed MySQL database resides.
    • userName: the account that is used to migrate data from the self-managed MySQL database.
    • dbName: the name of the self-managed MySQL database.
    • /tmp/triggerProcedure.sql: the name of the exported stored procedure file.
  5. Upload the data file and the stored procedure file to the ECS instance.

    For this example, upload the files to the following paths:

    /tmp/dbName.sql
    /tmp/triggerProcedure.sql
  6. Log on to the ECS instance and import the files into the RDS instance.
    mysql -h intranet4example.mysql.rds.aliyuncs.com -u userName -p dbName < /tmp/dbName.sql
    mysql -h intranet4example.mysql.rds.aliyuncs.com -u userName -p dbName < /tmp/triggerProcedure.sql

    Parameter description:

    • intranet4example.mysql.rds.aliyuncs.com: the endpoint that is used to connect to the RDS instance. In this example, the internal endpoint is used.
    • userName: the username of an account with the read and write permissions on the RDS instance. The account can be the privileged account.
    • dbName: the name of the self-managed MySQL database from which you want to import data.
    • /tmp/dbName.sql: the name of the data file that you want to import.
    • /tmp/triggerProcedure.sql: the name of the stored procedure file you want to import.