How do I migrate MySQL data to an Aurora MySQL DB cluster using Amazon S3?

Resolution

Amazon Aurora MySQL is compatible with MySQL 5.6 and MySQL 5.7 versions. To restore a MySQL innobackupex backup, first go to the Percona website and install Percona Xtrabackup (version 2.3 or later) to your Amazon Elastic Compute Cloud (Amazon EC2) instance:

sudo yum install percona-xtrabackup-file_name_and_extension

Note: Replace file_name_and_extension with the appropriate file name and extension based on your Percona Xtrabackup package. See the following example:

sudo yum install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

After installing Percona Xtrabackup, back up the data that you want to migrate to Aurora MySQL. Then, upload the backup to Amazon S3 to perform the restoration. For more information see the Percona documentation for The Backup Cycle.

Connect to an EC2 instance and back up your MySQL database

1.    Connect to the instance where the MySQL database is running by using SSH.

2.    Install Percona Xtrabackup:

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
sudo yum install perl-DBD-MySQL -y
sudo yum install percona-xtrabackup -y

3.    Back up the database:

xtrabackup --backup --user=<myuser> --password --stream=xbstream \
--target-dir=</on-premises/s3-restore/backup> | split -d --bytes=500MB \
- </on-premises/s3-restore/backup/backup>.xbstream

This command creates a backup of your MySQL database that is split into multiple xbstream files.

Note: Aurora doesn't restore everything from your source. After your database is restored successfully, you can re-create the following:

• User accounts
• Functions
• Stored procedures
• Time zone information

Upload your backup to an S3 bucket

1.    Create an S3 bucket.
Note: Your bucket must be in the same Region as your EC2 instance and your new Aurora DB cluster.

2.    Choose the bucket that you created, and then choose Create Folder.

3.    Choose the folder, and then choose Upload.

4.    Upload the files, and then set the permissions.

5.    Set the properties, and then choose Upload.

Note: When you upload a file to an Amazon S3 bucket, you can use server-side encryption to encrypt the data.

Import your database from Amazon S3 to Aurora

1.    Open the Amazon Relational Database Service (Amazon RDS) console, and then choose Dashboard in the navigation pane.

2.    Choose Restore Aurora DB Cluster from S3.

3.    Enter the Source Engine Version that you noted earlier.

4.    From the S3 Backup Location dropdown menu, select the S3 bucket that you created. Enter your S3 Bucket Prefix.
Note: Don't use leading or trailing slashes ("/") when entering the bucket name in the S3 Bucket Prefix field.

5.    Create an AWS Identity and Access Management (IAM) role to give Amazon RDS permission to access the S3 bucket, and then choose Next Step.

6.    Specify your DB details and choose Next Step.

7.    Configure your Advanced Settings and Database Options. Enter the IAM role that you created for the DB Cluster Identifier.

8.    Choose Launch DB Instance.

9.    After the cluster is available, choose View Your DB Instances to confirm that the Aurora DB instance was created successfully.

Related information

Migrating data from MySQL by using an Amazon S3 bucket

Migrating data from an external MySQL database to an Amazon Aurora MySQL DB cluster