Help Center/ Relational Database Service/ User Guide (ME-Abu Dhabi Region) / Working with RDS for MySQL/ Data Migration/ Migrating Data to RDS for MySQL Using mysqldump
Updated on 2023-07-06 GMT+08:00
View PDF
Share

Migrating Data to RDS for MySQL Using mysqldump

Preparing for Data Migration

You can access RDS DB instances through an EIP or through an ECS.

  1. Prepare an ECS for accessing DB instances in the same VPC or prepare a device for accessing RDS through an EIP.
    • To connect to a DB instance through an ECS, you need to create an ECS first.
    • To connect to a DB instance through an EIP, you must:
      1. Bind an EIP to a DB instance. For details, see Binding an EIP.
      2. Ensure that the local device can access the EIP.
  2. Install the MySQL client on the prepared ECS or device.

    The MySQL client version must be the same as the version of RDS for MySQL. The MySQL database or client will provide mysqldump and mysql.

Exporting Data

Before migrating data to RDS, you need to export data first.

  • The export tool must match the DB engine version.
  • Database migration is performed offline. Before the migration, you must stop any applications using the source database.
  1. Log in to the ECS or the device that can access RDS.
  2. Use the mysqldump tool to export metadata into an SQL file.

    The MySQL database is required for RDS management. When exporting metadata, do not specify --all-database. Otherwise, the MySQL database will be unavailable.

    mysqldump --databases <DB_NAME> --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u <DB_USER> -p -h <DB_ADDRESS> -P <DB_PORT> |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > <BACKUP_FILE>

    • DB_NAME indicates the name of the database to be migrated.
    • DB_USER indicates the database username.
    • DB_ADDRESS indicates the database address.
    • DB_PORT indicates the database port.
    • BACKUP_FILE indicates the name of the file to which the data will be exported.

    Enter the database password as prompted.

    Example:

    mysqldump --databases rdsdb --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u root -p -h 192.168.151.18 -P 3306 |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > dump-defs.sql

    Enter password:

    If you use mysqldump with a version earlier than 5.6, remove --set-gtid-purged=OFF before running this command.

    After this command is executed, a dump-defs.sql file will be generated as follows:

    [rds@localhost ~]$ ll dump-defs.sql
-rw-r-----. 1 rds rds 2714 Sep 21 08:23 dump-defs.sql

  3. Use the mysqldump tool to export data into an SQL file.

    The MySQL database is required for RDS management. When exporting metadata, do not specify --all-database. Otherwise, the MySQL database will be unavailable.

    mysqldump --databases <DB_NAME> --single-transaction --hex-blob --set-gtid-purged=OFF --no-create-info --skip-triggers -u <DB_USER> -p -h <DB_ADDRESS> -P <DB_PORT> -r <BACKUP_FILE>

    For details on the parameters in the preceding command, see 2.

    Enter the database password as prompted.

    Example:

    mysqldump --databases rdsdb --single-transaction --hex-blob --set-gtid-purged=OFF --no-create-info --skip-triggers -u root -p -h 192.168.151.18 -P -r dump-data.sql

    If you use mysqldump with a version earlier than 5.6, remove --set-gtid-purged=OFF before running this command.

    After this command is executed, a dump-data.sql file will be generated as follows:

    [rds@localhost ~]$ ll dump-data.sql
-rw-r-----. 1 rds rds 2714 Sep 21 08:23 dump-data.sql

Importing Data

You can connect your client to RDS and import exported SQL files into RDS.

If the source database contains triggers, storage processes, functions, or event invocation, you must set log_bin_trust_function_creators to ON for the destination database before importing data.

  1. Import metadata into RDS.

    Use the mysql tool to connect to the RDS DB instance, enter the password, and run the following command to import metadata:

    # mysql -f -h <RDS_ADDRESS> -P <DB_PORT> -u root -p < <BACKUP_DIR>/dump-defs.sql

    • RDS_ADDRESS: indicates the IP address of the RDS DB instance.
    • DB_PORT indicates the RDS DB instance port.
    • BACKUP_DIR indicates the directory where dump-defs.sql is stored.

    Example:

    # mysql -f -h 172.16.66.198 -P 3306 -u root -p < dump-defs.sql

    Enter password:

  2. Import data into RDS.

    # mysql -f -h <RDS_ADDRESS> -P <DB_PORT> -u root -p < <BACKUP_DIR>/dump-data.sql

    • RDS_ADDRESS: indicates the IP address of the RDS DB instance.
    • DB_PORT indicates the RDS DB instance port.
    • BACKUP_DIR indicates the directory where dump-data.sql is stored.

    Example:

    # mysql -f -h 172.16.66.198 -P 3306 -u root -p < dump-data.sql

    Enter password:

  3. View the import result.

    mysql> show databases;

    In this example, the database named my_db has been imported.

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Parent topic: Data Migration