Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Migrating Data to an Amazon Aurora DB Cluster

You can migrate (copy) data to an Amazon Aurora DB cluster either from an Amazon RDS snapshot or from a MySQL instance running externally to Amazon RDS, as described following.

Note

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

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:

  1. Determine the amount of space to provision for your Amazon Aurora DB cluster. For more information, see How Much Space Do I Need?

  2. 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.

  3. 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.

  4. 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.

Caution

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 up to 64 TB of storage, 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 compressed 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. This 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.

Important

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 TypeLimitation/Guideline

MyISAM tables

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:

alter table <schema>.<table_name> engine=innodb, algorithm=copy;

Compressed tables

Amazon Aurora does not support compressed tables (that is, tables created with ROW_FORMAT=COMPRESSED).

To reduce your chances of running out of space or to speed up the migration process, expand your compressed tables by setting ROW_FORMAT to DEFAULT, COMPACT, DYNAMIC, or REDUNDANT. For more information, see https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html.

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 as shown 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 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 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 System (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

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Choose Snapshots.

  3. On the Snapshots page, choose the snapshot that you want to migrate into an Aurora DB cluster.

  4. Choose Migrate Database.

    Migrate a snapshot into Amazon Aurora
  5. 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 will be 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 aurora-cluster1.

      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 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, gs-subnet-group1.

      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.

      Note

      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 that will be used when connecting to instances in the DB cluster. The default is 3306.

      Note

      You might be behind a corporate firewall that does not 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 a 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 does not apply to regular patches applied to maintain system stability.

    Migrate a snapshot into Amazon Aurora
  6. Choose Migrate to migrate your DB snapshot.

  7. Select Instances and click 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.

    DB Cluster Details

Migrating from MySQL to Amazon Aurora with Reduced Downtime

When importing data from a MySQL database that supports a live application to an Amazon Aurora DB cluster, you can use the procedure documented in Importing Data to an Amazon RDS MySQL or MariaDB DB Instance with Reduced Downtime to reduce the amount of time that service to your data is interrupted in order to migrate your data to Aurora. The procedure can especially help if you are working with a very large database, because you can reduce the cost of the import by minimizing the amount of data that is passed across the network to AWS.

The procedure lists steps to transfer a copy of your database data to an Amazon EC2 instance and import the data into a new Amazon RDS MySQL DB instance. Because Amazon Aurora is compatible with MySQL, you can instead use an Amazon Aurora DB cluster for the target Amazon RDS MySQL DB instance.