This topic describes how to migrate data to an RDS for MySQL instance by using mysqldump. mysqldump is easy to use but requires your database to go through a long service downtime. Therefore, we recommend that you use mysqldump when the data volume is small or a long service downtime is allowed.

Background information

ApsaraDB for RDS is fully compatible with native MySQL. Therefore, the process of migrating data from your database to an RDS for MySQL instance is similar to the process of migrating data from one MySQL server to another MySQL server.

Precautions

The data in the tables after migration is case-insensitive and centrally changed to lowercase letters.

Prerequisites

  • You have configured a whitelist, obtained a public endpoint, and created databases and accounts for the target RDS instance. For more information, see Quick start .
  • You have purchased an ECS instance.

Procedure

  1. Create an account used to migrate data in your on-premises database.
    CREATE USER'username'@'host' IDENTIFIED BY 'password';

    Parameter description:

    • username: the username of the account.
    • host: the name of the host from which you log on to your on-premises database by using the account. As a local user, you can set this parameter to localhost. To log on from any host, you can set this parameter to the wildcard %.
    • password: the password of the account.

    For example, if you want to create an account whose username and password are William and Changme123, respectively, and enable the account to log on to your on-premises database from any host, then run the following command:

     CREATE USER'William'@'%' IDENTIFIED BY 'Changme123';
  2. Grant permissions to the account in your on-premises database.
    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:

    • privileges: the permissions of the account, such as SELECT, INSERT, and UPDATE. To grant all permissions to the account, set this parameter to ALL.
    • databasename: the name of your on-premises database. To grant the permissions for all databases to the account, set this parameter to the wildcard *.
    • tablename: the name of a table. To grant the permissions for all tables to the account, set this parameter to the wildcard *.
    • username: the username of the account.
    • host: the name of the host from which you log on to your on-premises database by using the account. As a local user, you can set this parameter to localhost. To log on from any host, you can set this parameter to the wildcard %.
    • WITH GRANT OPTION: an optional parameter, which is used to grant the account the permission to run the GRANT command.

    For example, If you want to grant all permissions for all databases and tables to the account William and allow the account to log on from any host, then run the following command:

    GRANT ALL ON*. * TO 'William'@'%';
  3. Use mysqldump to export data from your on-premises database as a data file.
    Note Do not update the data during the data export. This step exports data only, excluding stored procedures, triggers, and functions.
    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 server where your on-premises database is located.
    • userName: the username of the account used to migrate data from your on-premises database.
    • dbName: the name of your on-premises database.
    • /tmp/dbName.sql: the name of the generated backup file.
  4. Use mysqldump to export stored procedures, triggers, and functions.
    Note If no stored procedures, triggers, or functions are used in the database, you can skip this step. When exporting stored procedures, triggers, and functions, you must remove the DEFINER parameter to guarantee compatibility with RDS.
    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 server where your on-premises database is located.
    • userName: the username of the account used to migrate data from your on-premises database.
    • dbName: the name of your on-premises database you want to migrate.
    • /tmp/triggerProcedure.sql: the name of the generated backup file.
  5. Upload the data file and stored procedure file to your ECS instance.

    In this example, assume that you want to upload the files to the following directories on your ECS instance:

    /tmp/dbName.sql
    /tmp/triggerProcedure.sql
  6. Log on to your ECS instance and import the data file and stored procedure file to the target 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 of the target RDS instance. A private endpoint is used as an example.
    • userName: the username of the account used to migrate data to the RDS instance.
    • dbName: the name of your on-premises database you want to import.
    • /tmp/dbName.sql: the name of the data file you want to import.
    • /tmp/triggerProcedure.sql: the name of the stored procedure file you want to import.