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

Migrating Data from an External MySQL Database to an Amazon Aurora MySQL DB Cluster

If your database supports the InnoDB or MyISAM tablespaces, you have these options for migrating your data to an Amazon Aurora MySQL DB cluster:

Migrating Data from MySQL by Using an Amazon S3 Bucket

You can copy the full and incremental backup files from your source MySQL version 5.5 or 5.6 database to an Amazon S3 bucket, and then restore an Amazon Aurora MySQL DB cluster from those files.

This option can be considerably faster than migrating data using mysqldump, because using mysqldump replays all of the commands to recreate the schema and data from your source database in your new Aurora MySQL DB cluster. By copying your source MySQL data files, Aurora MySQL can immediately use those files as the data for an Aurora MySQL DB cluster.

Note

The Amazon S3 bucket and the Amazon Aurora MySQL DB cluster must be in the same region.

Aurora MySQL doesn't restore everything from your database. You should save the database schema and values for the following items from your source MySQL database and add them to your restored Aurora MySQL DB cluster after it has been created.

  • User accounts

  • Functions

  • Stored procedures

  • Time zone information. Time zone information is loaded from the local operating system of your Amazon Aurora MySQL DB cluster. For more information, see Local Time Zone for Amazon Aurora DB Clusters.

Before You Begin

Before you can copy your data to an Amazon S3 bucket and restore a DB cluster from those files, you must do the following:

  • Install Percona XtraBackup on your local server.

  • Permit Aurora MySQL to access your Amazon S3 bucket on your behalf.

Installing Percona XtraBackup

Amazon Aurora can restore a DB cluster from files that were created using Percona XtraBackup. You can install Percona XtraBackup from the Percona website.

Note

You must use Percona XtraBackup version 2.3 or later. Aurora MySQL is not compatible with earlier versions of Percona XtraBackup.

Required Permissions

To migrate your MySQL data to an Amazon Aurora MySQL DB cluster, several permissions are required:

  • The user that is requesting that Amazon RDS create a new cluster from an Amazon S3 bucket must have permission to list the buckets for your AWS account. You grant the user this permission using an AWS Identity and Access Management (IAM) policy.

  • Amazon RDS requires permission to act on your behalf to access the Amazon S3 bucket where you store the files used to create your Amazon Aurora MySQL DB cluster. You grant Amazon RDS the required permissions using an IAM service role.

  • The user making the request must also have permission to list the IAM roles for your AWS account.

  • If the user making the request will create the IAM service role, or will request that Amazon RDS create the IAM service role (by using the console), then the user must have permission to create an IAM role for your AWS account.

For example, the following IAM policy grants a user the minimum required permissions to use the console to list IAM roles, create an IAM role, and list the Amazon S3 buckets for your account.

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "iam:ListRoles", "iam:CreateRole", "iam:CreatePolicy", "iam:AttachRolePolicy", "s3:ListBucket", "s3:ListObjects" ], "Resource": "*" } ] }

Additionally, for a user to associate an IAM role with an Amazon S3 bucket, the IAM user must have the iam:PassRole permission for that IAM role. This permission allows an administrator to restrict which IAM roles a user can associate with Amazon S3 buckets.

For example, the following IAM policy allows a user to associate the role named S3Access with an Amazon S3 bucket.

{ "Version":"2012-10-17", "Statement":[ { "Sid":"AllowS3AccessRole", "Effect":"Allow", "Action":"iam:PassRole", "Resource":"arn:aws:iam::123456789012:role/S3Access" } ] }

For more information on IAM user permissions, see Using Identity-Based Policies (IAM Policies) for Amazon RDS.

Creating the IAM Service Role

You can have the AWS Management Console create a role for you by choosing the Create a New Role option (shown later in this topic). If you select this option and specify a name for the new role, then Amazon RDS creates the IAM service role required for Amazon RDS to access your Amazon S3 bucket with the name that you supply.

As an alternative, you can manually create the role using the following procedure.

To create an IAM role for Amazon RDS to access Amazon S3

  1. Complete the steps in Creating an IAM Policy to Access Amazon S3 Resources.

  2. Complete the steps in Creating an IAM Role to Allow Amazon Aurora to Access AWS Services.

  3. Complete the steps in Associating an IAM Role with an Amazon Aurora MySQL DB Cluster.

Backing Up Files to be Restored as an Amazon Aurora MySQL DB Cluster

You can create a full backup of your MySQL database files using Percona XtraBackup and upload the backup files to an Amazon S3 bucket. Alternatively, if you already use Percona XtraBackup to back up your MySQL database files, you can upload your existing full and incremental backup directories and files to an Amazon S3 bucket.

Creating a Full Backup With Percona XtraBackup

To create a full backup of your MySQL database files that can be restored from Amazon S3 to create an Amazon Aurora MySQL DB cluster, use the Percona XtraBackup utility (innobackupex) to back up your database.

For example, the following command creates a backup of a MySQL database and stores the files in the /s3-restore/backup folder.

innobackupex --user=myuser --password=<password> --no-timestamp /s3-restore/backup

If you want to compress your backup into a single file (which can be split, if needed), you can use the --stream option to save your backup in one of the following formats:

  • Gzip (.gz)

  • tar (.tar)

  • Percona xbstream (.xbstream)

The following command creates a backup of your MySQL database split into multiple Gzip files.

innobackupex --user=myuser --password=<password> --stream=tar \ /mydata/s3-restore/backup | gzip - | split -d --bytes=500MB \ - /mydata/s3-restore/backup/backup.tar.gz

The following command creates a backup of your MySQL database split into multiple tar files.

innobackupex --user=myuser --password=<password> --stream=tar \ /mydata/s3-restore/backup | split -d --bytes=500MB \ - /mydata/s3-restore/backup/backup.tar

The following command creates a backup of your MySQL database split into multiple xbstream files.

innobackupex --stream=xbstream \ /mydata/s3-restore/backup | split -d --bytes=500MB \ - /mydata/s3-restore/backup/backup.xbstream

Once you have backed up your MySQL database using the Percona XtraBackup utility, then you can copy your backup directories and files to an Amazon S3 bucket.

For information on creating and uploading a file to an Amazon S3 bucket, see Getting Started with Amazon Simple Storage Service in the Amazon S3 Getting Started Guide.

Using Incremental Backups With Percona XtraBackup

Amazon Aurora MySQL supports both full and incremental backups created using Percona XtraBackup. If you already use Percona XtraBackup to perform full and incremental backups of your MySQL database files, you don't need to create a full backup and upload the backup files to Amazon S3. Instead, you can save a significant amount of time by copying your existing backup directories and files for your full and incremental backups to an Amazon S3 bucket. For more information about creating incremental backups using Percona XtraBackup, see Incremental Backups with innobackupex

When copying your existing full and incremental backup files to an Amazon S3 bucket, you must recursively copy the contents of the base directory. Those contents include the full backup and also all incremental backup directories and files. This copy must preserve the directory structure in the Amazon S3 bucket. Aurora iterates through all files and directories. Aurora uses the xtrabackup-checkpoints file included with each incremental backup to identify the base directory and to order incremental backups by log sequence number (LSN) range.

For information on creating and uploading a file to an Amazon S3 bucket, see Getting Started with Amazon Simple Storage Service in the Amazon S3 Getting Started Guide.

Backup Considerations

Amazon S3 limits the size of a file uploaded to an Amazon S3 bucket to 5 terabytes (TB). If the backup data for your database exceeds 5 TB, then you must use the split command to split the backup files into multiple files that are each less than 5 TB.

Amazon RDS limits the number of source files uploaded to an Amazon S3 bucket to 1 million files. If the backup data for your database, including all full and incremental backups, include a large number of files, use a tarball (.tar.gz) file to store full and incremental backup files in the Amazon S3 bucket.

Aurora consumes your backup files based on the file name. Be sure to name your backup files with the appropriate file extension based on the file format—for example, .xbstream for files stored using the Percona xbstream format.

Aurora consumes your backup files in alphabetical order and also in natural number order. Always use the split option when you issue the innobackupex command to ensure that your backup files are written and named in the proper order.

Aurora doesn't support partial backups created using Percona XtraBackup. You cannot use the --include, --tables-file, or --databases options to create a partial backup when you backup the source files for your database.

Aurora supports incremental backups created using Percona XtraBackup, with or without the --no-timestamp option. We recommend that you use the --no-timestamp option, to reduce the depth of the directory structure for your incremental backup.

For more information, see The innobackupex Script.

Restoring an Amazon Aurora MySQL DB Cluster from an Amazon S3 Bucket

You can restore your backup files from your Amazon S3 bucket to create a new Amazon Aurora MySQL DB cluster by using the Amazon RDS console.

To restore an Amazon Aurora MySQL DB cluster from files on an Amazon S3 bucket

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

  2. In the RDS dashboard, choose Restore from S3 under Create instance.

  3. On the Select engine page, choose Amazon Aurora and choose the MySQL-compatible edition, and choose Next.

  4. In the Specify source backup details, specify the following:

    For This Option Do This

    Source engine

    Aurora MySQL currently supports only restoring from backup files for the mysql database engine.

    Source engine version

    Specify the version of the MySQL database that the backup files were created from. MySQL version 5.5 and 5.6 are supported.

    S3 bucket

    Select the Amazon S3 bucket where your backup files are stored.

    S3 folder path prefix (optional)

    Specify a file path prefix for the files stored in your Amazon S3 bucket. The S3 Bucket Prefix is optional. If you don't specify a prefix, then Aurora MySQL creates the DB cluster using all of the files and folders in the root folder of the Amazon S3 bucket. If you specify a prefix, then Aurora MySQL creates the DB cluster using the files and folders in the Amazon S3 bucket where the full path for the file begins with the specified prefix.

    Aurora doesn't traverse other subfolders in your Amazon S3 bucket looking for backup files. Only the files from the folder identified by the S3 Bucket Prefix are used. If you store your backup files in a subfolder in your Amazon S3 bucket, then you must specify a prefix that identifies the full path to the folder where the files are stored.

    For example, suppose that you store your backup files in a subfolder of your Amazon S3 bucket named backups, and you have multiple sets of backup files, each in its own directory (gzip_backup1, gzip_backup2, and so on.) In this case, you specify a prefix of backups/gzip_backup1 to restore from the files in the gzip_backup1 folder.

    Create a new role

    Choose Yes to create a new IAM role, or No to select an existing IAM role, to authorize Aurora to access Amazon S3 on your behalf. For more information, see Required Permissions.

    IAM role name

    This option is available only if Create a new role is set to Yes. Specify the name of the new IAM role to be created. The new role is used to authorize Amazon Aurora to access Amazon S3 on your behalf. For more information, see Required Permissions.

    IAM Role

    This option is available only if Create a new role is set to No. Select the IAM role that you created to authorize Aurora to access Amazon S3 on your behalf. If you have not created an IAM role, you can instead set Create a New Role to Yes to create one. For more information, see Required Permissions.

    A typical Specify source backup details page looks like the following.

    
                                Amazon Aurora Migrate from an Amazon S3 bucket
  5. Choose Next.

  6. On the Specify DB details page, specify your DB cluster information. The following table shows settings for a DB instance.

    For This Option... Do this

    DB instance class

    Select a DB instance class that defines the processing and memory requirements for each instance in the DB cluster. For more information about DB instance classes, see DB Instance Class.

    Multi-AZ deployment

    Determine if you want to create Aurora Replicas in other Availability Zones for failover support. If you select Create Replica in Different Zone, then Amazon RDS will create an Aurora Replica for you in your DB cluster in a different Availability Zone than the primary instance for your DB cluster. For more information about multiple Availability Zones, see Regions and Availability Zones.

    DB instance identifier

    Type a name for the primary instance in your DB cluster. This identifier will be used in the endpoint address for the primary instance of your DB cluster.

    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.

    Master username

    Type a name using alphanumeric characters that you will use as the master user name to log on to your DB cluster.

    Master password

    Type a password that contains from 8 to 41 printable ASCII characters (excluding /,", and @) for your master user password.

    A typical Specify DB details page looks like the following.

    
                                Amazon Aurora Launch DB Instance Wizard DB Instance
                                    Details
  7. Confirm your master password, and then choose Next.

  8. On the Configure advanced settings page, you can customize additional settings for your Aurora MySQL DB cluster. The following table shows the advanced settings for a DB cluster.

    For This Option... Do This

    Virtual Private Cloud (VPC)

    Select the VPC that will host the DB cluster. Select Create a New VPC to have Amazon RDS create a VPC for you. For more information, see DB Cluster Prerequisites earlier in this topic.

    Subnet group

    Select the DB subnet group to use for the DB cluster. For more information, see DB Cluster Prerequisites earlier in this topic.

    Public accessibility

    Select Yes to give the DB cluster a public IP address; otherwise, select No. The instances in your DB cluster can be a mix of both public and private DB instances. For more information about hiding instances from public access, see Hiding a DB Instance in a VPC from the Internet.

    Availability zone

    Determine if you want to specify a particular Availability Zone. For more information about Availability Zones, see Regions and Availability Zones.

    VPC security groups

    Select Create new VPC security group to have Amazon RDS create a VPC security group for you. Or, select Select existing VPC security groups and specify one or more VPC security groups to secure network access to the DB cluster. For more information, see DB Cluster Prerequisites earlier in this topic.

    DB Cluster Identifier

    Type a name for your DB cluster that is unique for your account in the region you selected. This identifier will be used in the cluster endpoint address for your DB cluster. For information on the cluster endpoint, see Aurora Endpoints.

    The DB cluster 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 clusters per AWS account, per region.

    Database name

    Type a name for your default database of up to 64 alpha-numeric characters. If you don't provide a name, Amazon RDS will not create a database on the DB cluster you are creating.

    To create additional databases, connect to the DB cluster and use the SQL command CREATE DATABASE. For more information about connecting to the DB cluster, see Connecting to an Amazon Aurora DB Cluster.

    Database port

    Specify the port that applications and utilities will use to access the database. Aurora MySQL DB clusters default to the default MySQL port, 3306, and Aurora PostgreSQL DB clusters default to the default PostgreSQL port, 5432. The firewalls at some companies block connections to these default ports. If your company firewall blocks the default port, choose another port for the new DB cluster.

    DB parameter group

    Select a parameter group. Aurora has a default parameter group you can use, or you can create your own parameter group. For more information about parameter groups, see Working with DB Parameter Groups.

    DB cluster parameter group

    Select a DB cluster parameter group. Aurora has a default DB cluster parameter group you can use, or you can create your own DB cluster parameter group. For more information about DB cluster parameter groups, see Working with DB Parameter Groups.

    Option group

    Select an option group. Aurora has a default option group you can use, or you can create your own option group. For more information about option groups, see Working with Option Groups.

    Copy tags to snapshots

    Applies only to Aurora PostgreSQL. Select to specify that tags defined for this DB instance are copied to DB snapshots created from this DB instance. For more information, see Tagging Amazon RDS Resources.

    IAM DB authentication

    Applies only to Aurora MySQL. Select Enable IAM DB authentication to enable IAM database authentication. For more information, see IAM Database Authentication for MySQL and Amazon Aurora.

    Encryption

    Select Enable encryption to enable encryption at rest for this DB cluster. For more information, see Encrypting Amazon RDS Resources.

    Master key

    Only available if Encryption is set to Enable encryption. Select the master key to use for encrypting this DB cluster. For more information, see Encrypting Amazon RDS Resources.

    Priority

    Choose a failover priority for the instance. If you don't select a value, the default is tier-1. This priority determines the order in which Aurora Replicas are promoted when recovering from a primary instance failure. For more information, see Fault Tolerance for an Aurora DB Cluster.

    Backup retention period

    Select the length of time, from 1 to 35 days, that Aurora will retain backup copies of the database. Backup copies can be used for point-in-time restores (PITR) of your database down to the second.

    Enhanced monitoring

    Choose Enable enhanced monitoring to enable gathering metrics in real time for the operating system that your DB cluster runs on. For more information, see Enhanced Monitoring.

    Monitoring Role

    Only available if Enhanced Monitoring is set to Enable enhanced monitoring. Choose the IAM role that you created to permit Amazon RDS to communicate with Amazon CloudWatch Logs for you, or choose Default to have RDS create a role for you named rds-monitoring-role. For more information, see Enhanced Monitoring.

    Granularity

    Only available if Enhanced Monitoring is set to Enable enhanced monitoring. Set the interval, in seconds, between when metrics are collected for your DB cluster.

    Auto minor version upgrade

    Select Enable auto minor version upgrade 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.

    Maintenance window

    Select Select window and specify the weekly time range during which system maintenance can occur. Or, select No preference for Amazon RDS to assign a period randomly.

  9. Choose Launch DB instance to launch your Aurora DB instance, and then choose Close to close the wizard.

On the Amazon RDS console, the new DB instance appears in the list of DB instances. The DB instance has a status of creating until the DB instance is created and ready for use. When the state changes to available, you can connect to the primary instance for your DB cluster. Depending on the DB instance class and store allocated, it can take several minutes for the new instance to be available.

To view the newly created cluster, choose the Clusters view in the Amazon RDS console and choose the cluster. For more information, see Viewing an Amazon Aurora DB Cluster.


                        Amazon Aurora DB Instances List

Note the port and the endpoint of the cluster. Use the endpoint and port of the cluster in your JDBC and ODBC connection strings for any application that performs write or read operations.

Migrating from MySQL to Amazon Aurora by Using mysqldump

Because Amazon Aurora MySQL is a MySQL-compatible database, you can use the mysqldump utility to copy data from your MySQL or MariaDB database to an existing Aurora MySQL DB cluster. For a discussion of how to do so with MySQL databases that are very large, see Importing Data to an Amazon RDS MySQL or MariaDB DB Instance with Reduced Downtime. For MySQL databases that have smaller amounts of data, see Importing Data from a MySQL or MariaDB DB to an Amazon RDS MySQL or MariaDB DB Instance.

Related Topics