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.
- 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:
- Bind an EIP to the DB instance. For details, see Binding an EIP.
- Ensure that the local device can access the EIP.
- Install a MySQL client on the prepared ECS or device.
The MySQL client version must be the same as the DB engine version of your RDS for MySQL instance. A MySQL database or client will provide mysqldump and mysql.
After data is migrated to RDS, you may need to change the IP address. For details, see Viewing and Changing a Floating IP Address.
RDS system databases mysql and sys cannot be imported from one RDS for MySQL instance to another.
Exporting Data
Before migrating a database to RDS, its data needs to be exported.
![](https://support.huaweicloud.com/eu/usermanual-rds/public_sys-resources/notice_3.0-en-us.png)
- The export tool must match the DB engine version.
- Database migration is performed offline. Before the migration, you have to stop all applications using the source database.
- Log in to the source database.
- Use the mysqldump tool to export the table structure to an SQL file.
The mysql database is required for RDS management. When exporting the table structure, do not specify --all-database. Otherwise, a database fault will occur.
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 when 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
- Use the mysqldump tool to export data to an SQL file.
The mysql database is required for RDS management. When exporting data, do not specify --all-database. Otherwise, a database fault will occur.
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 when 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.
![](https://support.huaweicloud.com/eu/usermanual-rds/public_sys-resources/notice_3.0-en-us.png)
If the source database calls triggers, stored procedures, functions, or events, you must set log_bin_trust_function_creators to ON on the destination database before importing data.
- Log in to the ECS or the device that can access the RDS DB instance.
- Connect to the RDS DB instance through a client.
- Import the table structure into RDS.
# mysql -f -h<RDS_ADDRESS>-P<DB_PORT>-uroot-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:
If you intend to import SQL statements of a table to RDS, specify a database in the command. Otherwise, the error message "No database selected" may be displayed. For example, if you intend to import SQL statements of a table to database mydb, run the following command:
# mysql -f -h 172.16.66.198 -P 3306 -u root -p mydb < dump-defs.sql
Enter password:
- Import data into RDS.
# mysql -f -h<RDS_ADDRESS>-P<DB_PORT>-uroot-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:
If you intend to import SQL statements of a table to RDS, specify a database in the command. Otherwise, the error message "No database selected" may be displayed. For example, if you intend to import SQL statements of a table to database mydb, run the following command:
# mysql -f -h 172.16.66.198 -P 3306 -u root -p mydb < dump-defs.sql
Enter password:
- View the import result.
mysql> show databases;
The following result indicates that database rdsdb has been imported.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | rdsdb | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.