Migrating Data from a MySQL DB Instance to an Amazon Aurora DB Cluster by Using a DB Snapshot
You can migrate (copy) data to an Amazon Aurora DB cluster from an Amazon RDS MySQL DB snapshot, as described following.
Because Amazon Aurora is compatible with MySQL, you can migrate data from your MySQL database by setting up replication between your MySQL database, and an Amazon Aurora DB cluster. We recommend that your MySQL database run MySQL version 5.5 or later. For more information, see Amazon Aurora Replication.
Migrating an RDS MySQL Snapshot to Aurora
You can migrate a DB snapshot of an Amazon RDS MySQL DB instance to create an Aurora DB cluster. The new DB cluster is populated with the data from the original Amazon RDS MySQL DB instance. The DB snapshot must have been made from an Amazon RDS DB instance running MySQL 5.6.
You can migrate either a manual or automated DB snapshot. After the DB cluster is created, you can then create optional Aurora Replicas.
The general steps you must take are as follows:
Determine the amount of space to provision for your Amazon Aurora DB cluster. For more information, see How Much Space Do I Need?
Use the console to create the snapshot in the region where the Amazon RDS MySQL 5.6 instance is located. For information about creating a DB snapshot, see Creating a DB Snapshot.
If the DB snapshot is not in the region as your DB cluster, use the Amazon RDS console to copy the DB snapshot to that region. For information about copying a DB snapshot, see Copying a DB Snapshot.
Use the console to migrate the DB snapshot and create an Amazon Aurora DB cluster with the same databases as the original DB instance of MySQL 5.6.
Amazon RDS limits each AWS account to one snapshot copy into each region at a time.
How Much Space Do I Need?
When you migrate a snapshot of a MySQL DB instance into an Aurora DB cluster, Aurora uses an Amazon Elastic Block Store (Amazon EBS) volume to format the data from the snapshot before migrating it. In some cases, additional space is needed to format the data for migration. When migrating data into your DB cluster, observe the following guidelines and limitations:
Although Amazon Aurora supports storage up to 64 TB in size, the process of migrating a snapshot into an Aurora DB cluster is limited by the size of the EBS volume of the snapshot. Thus, the maximum size for a snapshot that you can migrate is 6 TB.
Tables that are not MyISAM tables and are not compressed can be up to 6 TB in size. If you have MyISAM tables, then Aurora must use additional space in the volume to convert the tables to be compatible with Aurora. If you have compressed tables, then Aurora must use additional space in the volume to expand these tables before storing them on the Aurora cluster volume. Because of this additional space requirement, you should ensure that none of the MyISAM and compressed tables being migrated from your MySQL DB instance exceeds 3 TB in size.
Reducing the Amount of Space Required to Migrate Data into Amazon Aurora
You might want to modify your database schema prior to migrating it into Amazon Aurora. Such modification can be helpful in the following cases:
You want to speed up the migration process.
You are unsure of how much space you need to provision.
You have attempted to migrate your data and the migration has failed due to a lack of provisioned space.
You can make the following changes to improve the process of migrating a database into Amazon Aurora.
Be sure to perform these updates on a new DB instance restored from a snapshot of a production database, rather than on a production instance. You can then migrate the data from the snapshot of your new DB instance into your Amazon Aurora DB cluster to avoid any service interruptions on your production database.
|Table Type||Limitation or Guideline|
Amazon Aurora supports InnoDB tables only. If you have MyISAM tables in your database, then those tables must be converted before being migrated into Amazon Aurora. The conversion process requires additional space for the MyISAM to InnoDB conversion during the migration procedure.
To reduce your chances of running out of space or to speed up the migration process, convert all of your MyISAM tables to InnoDB tables before migrating them. The size of the resulting InnoDB table is equivalent to the size required by Amazon Aurora for that table. To convert a MyISAM table to InnoDB, run the following command:
Amazon Aurora does not support compressed tables
(that is, tables created with
To reduce your chances of running out of space or
to speed up the migration process, expand your compressed
tables by setting
You can use the following SQL script on your existing MySQL DB instance to list the tables in your database that are MyISAM tables or compressed tables.
-- This script examines a MySQL database for conditions that will block -- migrating the database into Amazon's Aurora DB. -- It needs to be run from an account that has read permission for the -- INFORMATION_SCHEMA database. -- Verify that this is a supported version of MySQL. select msg as `==> Checking current version of MySQL.` from ( select 'This script should be run on MySQL version 5.6. ' + 'Earlier versions are not supported.' as msg, cast(substring_index(version(), '.', 1) as unsigned) * 100 + cast(substring_index(substring_index(version(), '.', 2), '.', -1) as unsigned) as major_minor ) as T where major_minor <> 506; -- List MyISAM and compressed tables. Include the table size. select concat(TABLE_SCHEMA, '.', TABLE_NAME) as `==> MyISAM or Compressed Tables`, round(((data_length + index_length) / 1024 / 1024), 2) "Approx size (MB)" from INFORMATION_SCHEMA.TABLES where ENGINE <> 'InnoDB' and ( -- User tables TABLE_SCHEMA not in ('mysql', 'performance_schema', 'information_schema') or -- Non-standard system tables ( TABLE_SCHEMA = 'mysql' and TABLE_NAME not in ( 'columns_priv', 'db', 'event', 'func', 'general_log', 'help_category', 'help_keyword', 'help_relation', 'help_topic', 'host', 'ndb_binlog_index', 'plugin', 'proc', 'procs_priv', 'proxies_priv', 'servers', 'slow_log', 'tables_priv', 'time_zone', 'time_zone_leap_second', 'time_zone_name', 'time_zone_transition', 'time_zone_transition_type', 'user' ) ) ) or ( -- Compressed tables ROW_FORMAT = 'Compressed' );
The script produces output similar to the output in the following example. The example shows two tables that must be converted from MyISAM to InnoDB. The output also includes the approximate size of each table in megabytes (MB).
+---------------------------------+------------------+ | ==> MyISAM or Compressed Tables | Approx size (MB) | +---------------------------------+------------------+ | test.name_table | 2102.25 | | test.my_table | 65.25 | +---------------------------------+------------------+ 2 rows in set (0.01 sec)
Migrating a DB Snapshot by Using the Console
You can migrate a DB snapshot of an Amazon RDS MySQL DB instance to create an Aurora DB cluster. The new DB cluster will be populated with the data from the original Amazon RDS MySQL DB instance. The DB snapshot must have been made from an Amazon RDS DB instance running MySQL 5.6 and must not be encrypted. For information about creating a DB snapshot, see Creating a DB Snapshot.
If the DB snapshot is not in the AWS Region where you want to locate your data, use the Amazon RDS console to copy the DB snapshot to that region. For information about copying a DB snapshot, see Copying a DB Snapshot.
When you migrate the DB snapshot by using the console, the console takes the actions necessary to create both the DB cluster and the primary instance.
You can also choose for your new Aurora DB cluster to be encrypted "at rest" using an AWS Key Management Service (AWS KMS) encryption key. This option is available only for unencrypted DB snapshots.
To migrate a MySQL 5.6 DB snapshot by using the console
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
On the Snapshots page, choose the snapshot that you want to migrate into an Aurora DB cluster.
Choose Migrate Database.
Set the following values on the Migrate Database page:
DB Instance Class: Select a DB instance class that has the required storage and capacity for your database, for example
db.r3.large. Aurora cluster volumes automatically grow as the amount of data in your database increases, up to a maximum size of 64 terabytes (TB). So you only need to select a DB instance class that meets your current storage requirements. For more information, see Amazon Aurora Storage.
DB Instance Identifier: Type a name for the DB cluster that is unique for your account in the region you selected. This identifier is used in the endpoint addresses for the instances in your DB cluster. You might choose to add some intelligence to the name, such as including the region and DB engine you selected, for example
The DB instance identifier has the following constraints:
It must contain from 1 to 63 alphanumeric characters or hyphens.
Its first character must be a letter.
It cannot end with a hyphen or contain two consecutive hyphens.
It must be unique for all DB instances per AWS account, per AWS Region.
VPC: If you have an existing VPC, then you can use that VPC with your Amazon Aurora DB cluster by selecting your VPC identifier, for example
vpc-a464d1c1. For information on using an existing VPC, see How to Create a VPC for Use with Amazon Aurora.
Otherwise, you can choose to have Amazon RDS create a VPC for you by selecting Create a new VPC.
Subnet Group: If you have an existing subnet group, then you can use that subnet group with your Amazon Aurora DB cluster by selecting your subnet group identifier, for example
Otherwise, you can choose to have Amazon RDS create a subnet group for you by selecting Create a new subnet group.
Publicly Accessible: Select No to specify that instances in your DB cluster can only be accessed by resources inside of your VPC. Select Yes to specify that instances in your DB cluster can be accessed by resources on the public network. The default is Yes.
Your production DB cluster might not need to be in a public subnet, because only your application servers will require access to your DB cluster. If your DB cluster doesn't need to be in a public subnet, set Publicly Accessible to No.
Availability Zone: Select the Availability Zone to host the primary instance for your Aurora DB cluster. To have Amazon RDS select an Availability Zone for you, select No Preference.
Database Port: Type the default port to be used when connecting to instances in the DB cluster. The default is
You might be behind a corporate firewall that doesn't allow access to default ports such as the MySQL default port, 3306. In this case, provide a port value that your corporate firewall allows. Remember that port value later when you connect to the Aurora DB cluster.
Enable Encryption: Choose Yes for your new Aurora DB cluster to be encrypted "at rest." If you choose Yes, you will be required to choose an AWS KMS encryption key as the Master Key value.
Auto Minor Version Upgrade: Select Yes if you want to enable your Aurora DB cluster to receive minor MySQL DB engine version upgrades automatically when they become available.
The Auto Minor Version Upgrade option only applies to upgrades to MySQL minor engine versions for your Amazon Aurora DB cluster. It doesn't apply to regular patches applied to maintain system stability.
Choose Migrate to migrate your DB snapshot.
Choose Instances, and then choose the arrow icon to show the DB cluster details and monitor the progress of the migration. On the details page, you will find the cluster endpoint used to connect to the primary instance of the DB cluster. For more information on connecting to an Amazon Aurora DB cluster, see Connecting to an Amazon Aurora DB Cluster.