• Relational Database Service

rds
  1. Help Center
  2. Relational Database Service
  3. User Guide
  4. Working with RDS for MySQL
  5. Migrating MySQL Data Using mysqldump
  6. Exporting Data

Exporting Data

Before migrating an existing MySQL database to RDS, you need to export the MySQL database.

  • 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.

Procedure

  1. Log in to the ECS or device that can access RDS prepared in 1 in section Preparing for Data Migration.
  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 the database password as prompted.

    NOTE:

    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 3306 -r dump-data.sql

    NOTE:

    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