You can access RDS instances through an EIP or from an ECS over a private network.
For details about how to create and connect to an ECS, see How Can I Create and Connect to an ECS?
Ensure that the MySQL client version is the same as the DB engine version of your RDS for MySQL instance. Both MySQL and its client include the built-in utilities mysqldump and mysql.
After data is migrated to RDS, application IP configurations often require updates. RDS simplifies this process. It allows you to change floating IP addresses of instances, reducing both migration complexity and cost. For details, see Changing a Floating IP Address.
RDS system databases mysql and sys cannot be imported from one RDS for MySQL instance to another.
Before migrating a database to RDS, its data needs to be exported.
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>
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
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 3306 -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
You can connect your client to RDS and import exported SQL files into RDS.
If the source database contains triggers, stored procedures, functions, or events, you must set log_bin_trust_function_creators to ON on the destination database before importing data.
# mysql -f -h<RDS_ADDRESS> -P<DB_PORT> -u root -p < <BACKUP_DIR>/dump-defs.sql
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 (mydb) in the command. Otherwise, the error message "No database selected" may be displayed. Example:
# mysql -f -h 172.16.66.198 -P 3306 -u root -p mydb < dump-defs.sql
Enter password:
# mysql -f -h<RDS_ADDRESS> -P<DB_PORT> -u root -p < <BACKUP_DIR>/dump-data.sql
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 (mydb) in the command. Otherwise, the error message "No database selected" may be displayed. Example:
# mysql -f -h 172.16.66.198 -P 3306 -u root -p mydb < dump-defs.sql
Enter password:
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)