Amazon Relational Database Service
User Guide (API Version 2013-09-09)
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.Did this page help you?  Yes | No |  Tell us about it...

Importing Data From a MySQL Instance Running External to Amazon RDS

To import data from a MySQL database that is running external to Amazon RDS, you can configure replication from that database to a MySQL DB instance. The external MySQL database you are importing from can be running either on-premises in your data center, or in an Amazon EC2 instance. The MySQL DB instance must be running either MySQL 5.5 version 5.5.33 or later, or MySQL 5.6 version 5.6.13 or later. Using replication to import the data reduces downtime.

Replication from a MySQL instance running external to Amazon RDS is only supported during the time it takes to import a database to a MySQL DB instance. The replication should be terminated when the data has been transferred and all applications and services have been cut over to use the Amazon RDS instance.

The following list shows the steps to take. Each step is discussed in more detail in later sections.

  1. Prepare a MySQL DB instance that is either MySQL 5.5 version 5.5.33 or later, or MySQL 5.6 version 5.6.13 or later; configure it to be a read replica of the MySQL instance running external to Amazon RDS.

  2. Configure the MySQL instance running external to Amazon RDS to be the replication source.

  3. Use mysqldump to import the database from the MySQL instance running external to Amazon RDS into the Amazon RDS instance.

  4. Start replication to the MySQL DB instance to capture updates made since the mysqldump files were created. Because the source MySQL DB instance is not in Amazon RDS, you do not use the standard Amazon RDS process to create a replica, you instead use stored procedures to establish replication.

  5. After the import completes, point the applications to the MySQL DB instance. Stop replication by running stored procedures that terminate the replication process. After the stored procedures are run, the replica runs as a standard MySQL DB instance without any need to promote the instance.

Prepare a MySQL DB Instance

Determine which DB instance class is required to support both the import and production workloads. For more information see DB Instance Class.

Determine what configuration options are required to support the workloads. If no existing Amazon RDS MySQL parameter group has that configuration, create a new parameter group. For more information see Working with DB Parameter Groups.

Determine if Amazon RDS Provisioned IOPS is required to support the workloads. For more information, see Provisioned IOPS Storage.

Create a MySQL DB instance by specifying the correct DB instance class, parameter group, security group, Provisioned IOPS settings, and a single Availability Zone. Select either MySQL 5.5 version 5.5.33 or later, or MySQL 5.6 version 5.6.13 or later. For more information, see Creating a DB Instance Running the MySQL Database Engine. Do not configure multiple Availability Zones until after the import has completed. We recommend that you also do not create read replicas until the import has completed.

Connect to the instance as the master user, and create the users required to support the administrators, applications, and services that will need to access the instance.

By default, a MySQL DB instance has the egress rules required for it to connect as a read replica to an instance of MySQL running external to Amazon RDS. If you have changed the security group for the MySQL DB instance, you may need to configure an egress rule for the Amazon RDS instance to operate as the read replica during the import. Specify an egress rule that allows TCP connections to the port and IP address of the external MySQL source instance. If the read replica is running as a DB instance in an Amazon VPC, specify the egress rule in a VPC security group. If the read replica is running as an Amazon RDS DB instance that is not in a VPC, specify the egress rule in a database security group.

If you configure an egress rule in a VPC security group, you must also configure these VPC ACL rules. For more information about Amazon VPC network ACLs, go to Network ACLs.

  • ACL ingress rule allowing TCP traffic to ports 1024-65535 from the IP address of the MySQL instance.

  • ACL egress rule: allowing outbound TCP traffic to the port and IP address of the MySQL instance.

Prepare the Replication Source

Follow the directions in the MySQL documentation to prepare the instance of MySQL running external to Amazon RDS as a replication master.

Set the configuration by following the directions in Setting the Replication Master Configuration.

Create a replication account by following the directions in Creating a User For Replication.

Use the MySQL SHOW MASTER STATUS statement to record the location of the MySQL binary log. For more information, go to Obtaining the Replication Master Log Binary Coordinates in the MySQL documentation.

Configure ingress rules on the system running the external MySQL source instance that will allow the Amazon RDS read replica to connect during replication. Specify an ingress rule that allows TCP connections to the port used by the MySQL instance from the IP address of the Amazon RDS read replica.

To find the IP address of the MySQL DB instance, use ping and specify the DNS name of the DB instance. The DNS name is that part of the endpoint before the colon (:) and port number. For example, if the endpoint of a MySQL DB instance is myinstance.d7c9lmnxfgb0.us-west-2.rds.amazonaws.com:3306, use the following to get the IP address (on Windows, use -n 5 instead of -c 5):

ping myinstance.d7c9lmnxfgb0.us-west-2.rds.amazonaws.com -c 5

If the external MySQL instance is running in an Amazon EC2 instance in a VPC, specify the ingress rules in a VPC security group. If the MySQL instance is running in an Amazon EC2 instance that is not in a VPC, specify the ingress rules in a security group associated with the Amazon EC2 instance. If the MySQL instance is running on-premises, specify the ingress rules in a firewall.

If the external MySQL instance is running on Amazon EC2 in a VPC, configure VPC ACL rules in addition to the security group ingress rules. For more information about Amazon VPC network ACLs, go to Network ACLs.

  • ACL ingress rule: allow TCP connections to the port used by the external MySQL instance from the CIDR range of the Amazon RDS read replica subnet.

  • ACL egress rule: allow TCP connections from ports 1024-65535 to the Amazon RDS read replica subnet.

Take a snapshot of the databases to be replicated by running the mysqldump utility. Do not include the MySQL system database in the snapshot. Follow the directions in Creating a Dump Snapshot Using mysqldump, except do not use --all-databases as shown in the example, instead use the --databases parameter to specify the databases to dump. The source instance will be in read-only mode during this operation. Do not purge these binary logs until they have been applied to the read replica.

mysqldump supports multiple ways to dump the data from a database. Since the source system is in read-only mode while mysqldump extracts the data, choose a method that will minimize the amount of time that mysqldump runs. For more information about choosing a mysqldump format and then loading the data, go to Using mysqldump For Backups.

Start Replication

Load the database dumps from the external MySQL instance into the MySQL DB instance by using the appropriate method for the dump format you chose when using mysqldump.

Run the mysql.rds_set_external_master procedure on the MySQL DB instance to configure it as a read replica of the MySQL instance running external to Amazon RDS. Specify the connection information for connecting to the MySQL instance running external to Amazon RDS, the replication user credentials, and the binary log location. For more information, see mysql.rds_set_external_master.

Run the mysql.rds_start_replication procedure on the Amazon RDS instance to start the replication process. For more information, see mysql.rds_start_replication.

Run the MySQL SHOW SLAVE STATUS statement on the Amazon RDS instance to verify that it is operating as a read replica. For more information about interpreting the results, go to SHOW SLAVE STATUS Syntax. If replication errors are reported, you can use mysql.rds_skip_repl_error to skip the errors. For more information, see mysql_rds_skip_repl_error.

The replication process may stop if some event changes the IP address of one of the instances, such as a Multi-AZ instance failing over to the secondary instance. If this happens, you may need to reconfigure your ingress or egress rules to reflect the new IP address. If the instance is in a VPC subnet, you can minimize the need to change the rule by specifying the CIDR range of the subnet.

Stop Replication

After the databases have been replicated and you have verified the databases are synchronized, change all applications and services to connect to the MySQL DB instance.

Run the mysql.rds_stop_replication procedure to stop the replication process. For more information, see mysql.rds_stop_replication.

Verify that the applications and services are running correctly.

Run the mysql.rds_reset_external_master procedure to remove the replication configuration. For more information, see mysql.rds_reset_external_master.

Take a snapshot of the Amazon RDS instance.

Modify the security group to remove the entry for the instance of MySQL that was not running on Amazon RDS.

Advise your network administrators they can remove the firewall rule that authorized the replication connection.

Configure the Amazon RDS instance to run in the configuration environment, such as by enabling multiple Availability Zones or creating read replicas from the instance.

Monitor the performance of the instance after it starts running the production load. If necessary, you can resize and reconfigure the instance if it is not supporting the production load. For more information, see Monitoring .