- What's New
- Function Overview
- Product Bulletin
- Service Overview
- Billing
-
Getting Started
- Getting Started with RDS for MySQL
- Getting Started with RDS for MariaDB
- Getting Started with RDS for PostgreSQL
- Change History
- Huawei Cloud MySQL Kernel
-
User Guide
-
Working with RDS for MySQL
- Suggestions on Using RDS for MySQL
- Database Migration
- Performance Tuning
- Instance Lifecycle
-
Instance Modifications
- Upgrading a Minor Version
- Changing a DB Instance Name
- Changing a DB Instance Description
- Changing the Replication Mode
- Changing the Failover Priority
- Changing a DB Instance Class
- Scaling Up Storage Space
- Configuring Storage Autoscaling
- Changing the Maintenance Window
- Changing a DB Instance Type from Single to Primary/Standby
- Promoting a Read Replica to Primary
- Manually Switching Between Primary and Standby DB Instances
- Changing the AZ of a Standby DB Instance
- Read Replicas
- Data Backups
- Data Restorations
-
Parameter Templates
- Creating a Parameter Template
- Modifying Parameters of an RDS for MySQL Instance
- Exporting a Parameter Template
- Comparing Parameter Templates
- Viewing Parameter Change History
- Replicating a Parameter Template
- Resetting a Parameter Template
- Applying a Parameter Template
- Viewing Application Records of a Parameter Template
- Modifying a Parameter Template Description
- Deleting a Parameter Template
- Connection Management
-
Database Proxy (Read/Write Splitting)
- Introducing Read/Write Splitting
- Suggestions on Using Database Proxy
- Kernel Versions
- Best Practices for Database Proxy
- Enabling Read/Write Splitting
- Configuring Transaction Splitting
- Configuring Connection Pools
- Configuring Multi-Statement Processing Modes
- Changing a Proxy from Pay-per-Use to Yearly/Monthly
- Configuring the Delay Threshold and Routing Policy
- Changing the Read/Write Splitting Address
- Changing the Read/Write Splitting Port
- Changing the Instance Class of a DB Proxy Instance
- Changing the Number of Proxy Nodes
- Upgrading the Kernel Version of Database Proxy
- Enabling or Disabling Access Control
- Restarting a Database Proxy
- Modifying Read/Write Splitting Parameters
- Disabling Read/Write Splitting
- Testing Read/Write Splitting Performance
- Database Management
- Account Management (Non-Administrator)
- Account and Network Security
- Metrics
- Interconnection with CTS
- Log Management
- Task Center
- Billing Management
- Managing Tags
-
Working with RDS for MariaDB
- Suggestions on Using RDS for MariaDB
- Instance Connection
- Parameter Tuning
- Instance Lifecycle
- Instance Modifications
- Read Replicas
- Data Backups
- Data Restorations
-
Parameter Templates
- Creating a Parameter Template
- Modifying RDS for MariaDB Instance Parameters
- Exporting a Parameter Template
- Importing a Parameter Template
- Comparing Parameter Templates
- Viewing Parameter Change History
- Replicating a Parameter Template
- Resetting a Parameter Template
- Applying a Parameter Template
- Viewing Application Records of a Parameter Template
- Modifying a Parameter Template Description
- Deleting a Parameter Template
- Connection Management
- Database Management
- Account Management (Non-Administrator)
- Account and Network Security
- Metrics
- Interconnection with CTS
- Log Management
- Task Center
- Managing Tags
-
Working with RDS for PostgreSQL
- Suggestions on Using RDS for PostgreSQL
- Database Migration
- Performance Tuning
- Instance Lifecycle
-
Instance Modifications
- Upgrading a Minor Version
- Changing a DB Instance Name
- Changing a DB Instance Description
- Changing the Replication Mode
- Changing the Failover Priority
- Changing a DB Instance Class
- Scaling Storage Space
- Changing the Maintenance Window
- Changing a DB Instance Type from Single to Primary/Standby
- Manually Switching Between Primary and Standby DB Instances
- Changing the AZ of a Standby DB Instance
- Read Replicas
- Data Backups
- Data Restorations
-
Parameter Templates
- Creating a Parameter Template
- Modifying Parameters of an RDS for PostgreSQL Instance
- Exporting a Parameter Template
- Comparing Parameter Templates
- Viewing Parameter Change History
- Replicating a Parameter Template
- Resetting a Parameter Template
- Applying a Parameter Template
- Viewing Application Records of a Parameter Template
- Modifying a Parameter Template Description
- Deleting a Parameter Template
- Connection Management
- Extension Management
- Tablespace Management
- Account and Network Security
- Metrics
- Interconnection with CTS
- Log Management
- Task Center
- Billing Management
- Major Version Upgrade
- Managing Tags
-
Working with RDS for MySQL
- Best Practices
- Performance White Paper
-
API Reference
- Before You Start
- API Overview
- Calling APIs
- API v3.1 (Recommended)
-
API v3 (Recommended)
- Querying Version Information About APIs
- Querying Version Information About a DB Engine
- Querying Database Specifications
- Querying the Storage Type of a Database
-
DB Instance Management
- Creating a DB Instance
- Stopping an Instance
- Changing a DB Instance Name
- Changing the Description of a DB Instance
- Changing DB Instance Specifications
- Scaling Up Storage Space of a DB Instance
- Changing a Single DB Instance to Primary/Standby DB Instances
- Rebooting a DB Instance
- Deleting a DB Instance
- Querying DB Instances
- Binding and Unbinding an EIP
- Changing the Failover Priority
- Manually Switching Primary/Standby DB Instances
- Changing the Data Replication Mode of Primary/Standby DB Instances
- Configuring the Maintenance Window
- Database Security
- Backup and Restoration
- Log Information Queries
-
Database and Account Management (MySQL)
- Creating a Database
- Querying Details About a Database
- Querying Databases
- Querying Authorized Databases of a Specified User
- Deleting a Database
- Creating a Database Account
- Querying Database Users
- Querying Authorized Users of a Specified Database
- Deleting a Database Account
- Configuring a Password for a Database Account
- Authorizing a Database Account
- Revoking Permissions of a Database Account
- Resetting the Password for User root
- Database and Account Management (PostgreSQL)
-
Parameter Management
- Obtaining a Parameter Template List
- Creating a Parameter Template
- Modifying a Parameter Template
- Applying a Parameter Template
- Modifying Parameters of a Specified DB Instance
- Obtaining the Parameter Template of a Specified DB Instance
- Obtaining Parameters in a Specified Parameter Template
- Deleting a Parameter Template
- Recycling a DB Instance
- Tag Management
- Obtaining Task Information
- Appendix
- Change History
- SDK Reference
-
FAQs
- Product Consulting
- Resource and Disk Management
-
Database Connection
- What Should I Do If I Can't Connect to My RDS DB Instance?
- Can an External Server Access the RDS Database?
- What Do I Do If the Number of RDS Database Connections Reaches the Upper Limit?
- What Is the Maximum Number of Connections to an RDS DB Instance?
- How Can I Create and Connect to an ECS?
- What Should I Do If an ECS Cannot Connect to an RDS DB Instance Through a Private Network?
- What Should I Do If a Database Client Problem Causes a Connection Failure?
- What Should I Do If an RDS Database Problem Causes a Connection Failure?
- How Do My Applications Access an RDS DB Instance in a VPC?
- Do Applications Need to Support Reconnecting to the RDS DB Instance Automatically?
- Why Cannot I Ping My EIP After It Is Bound to a DB Instance?
- How Can I Obtain the IP Address of an Application?
- Can I Access an RDS DB Instance Over an Intranet Connection Across Regions?
- Is an SSL Connection to a DB Instance Interrupted After a Primary/Standby Switchover or Failover?
- Database Migration
- Database Permission
- Database Storage
- Client Installation
- Backup and Restoration
- Database Monitoring
- Capacity Expansion and Specification Change
- Database Parameter Modification
- Network Security
- Change History
- Troubleshooting
- Videos
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.
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 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.
- 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 -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.
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:
NOTE:
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:
NOTE:
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.