Облачная платформаAdvanced

Migrating Data to RDS for MySQL Using mysqldump

Эта статья полезна?

Язык статьи: Английский

Перевести

Preparing for Data Migration

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

Prepare an ECS for accessing an RDS instance or prepare a device for accessing a DB instance through an EIP.
- To connect to a DB instance through an ECS, you need to create an ECS first.
  For details about how to create and connect to an ECS, see How Can I Create and Connect to an ECS?
- To connect to a DB instance through an EIP, you must:
  1. Bind an EIP to the DB instance. For details, see Binding an EIP.
  2. Ensure that the local device can access the EIP.
Install a MySQL client on the prepared ECS or a device that can access the RDS. For details, see 6.
Note
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 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.

Notice

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.
Notice
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:

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
```
Use the mysqldump tool to export data to an SQL file.
Notice
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

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
```

Importing Data

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

Notice

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.

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> -u root -p < <BACKUP_DIR>/dump-defs.sql
- RDS_ADDRESS indicates the IP address of the RDS instance.
- DB_PORT indicates the 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:

Note
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:
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 instance.
- DB_PORT indicates the 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:

Note
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:

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)

Parent topic: Database Migration

Поддержка Юридические документы