--stream=tar \
/mydata/s3-restore/backup | split -d --bytes=512000 \
- /mydata/s3-restore/backup3/backup.tar
```
For example, 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=512000 \
- /mydata/s3-restore/backup/backup.xbstream
```
Amazon S3 limits the size of a file uploaded to a 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 Aurora MySQL does not 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.
For more information, see [The innobackupex Script](https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html).
Amazon Aurora MySQL 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, `0xbstream` for files stored using the Percona xbstream format.
Amazon Aurora MySQL consumes your backup files in alphabetical order as well as 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.
### Step 2: Copying Files to an Amazon S3 Bucket
Once you have backed up your MySQL database using the Percona Xtrabackup utility, then you can copy your backup files to an Amazon S3 bucket.
For information about creating and uploading a file to an Amazon S3 bucket, see [Getting Started with Amazon Simple Storage Service](https://docs.aws.amazon.com/AmazonS3/latest/gsg/GetStartedWithS3.html) in the *Amazon S3 Getting Started Guide*.
### Step 3: Restoring an Aurora MySQL DB Cluster from an Amazon S3 Bucket
You can restore your backup files from your Amazon S3 bucket to a create new Amazon Aurora MySQL DB cluster by using the Amazon RDS console.
1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
1. In the RDS Dashboard, choose **Restore Aurora MySQL DB Cluster from S3**.
1. In the **Create database by restoring from S3** page, specify the following settings in the following sections:
1. In the **S3 Destination** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **Engine Options** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **IAM role** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **Settings** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **DB Instance Class** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **Availability & durability** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **Connectivity** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **Database authentication** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. In the **Additional configuration** section, specify the following:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-mysql2aurora.html)
1. Choose **Launch DB Instance** to launch your Aurora MySQL 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. For more information, see [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Viewing.html).
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 MySQL to Amazon Aurora MySQL by Using mysqldump
You can create a dump of your data using the `mysqldump` utility, and then import that data into an existing Amazon Aurora MySQL DB cluster.
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 Amazon Aurora MySQL DB cluster.
# Migrating Data from an Amazon RDS MySQL DB Instance to an Amazon Aurora MySQL DB Cluster
You can migrate (copy) data to an Amazon Aurora MySQL DB cluster from an Amazon RDS snapshot, as described following.
**Note**
Because Amazon Aurora MySQL is compatible with MySQL, you can migrate data from your MySQL database by setting up replication between your MySQL database, and an Amazon Aurora MySQL DB cluster. We recommend that your MySQL database run MySQL version 5.5 or later.
## Migrating an Amazon RDS for MySQL Snapshot to Aurora MySQL
You can migrate a DB snapshot of an Amazon RDS MySQL DB instance to create an Aurora MySQL 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 MySQL Replicas.
The general steps you must take are as follows:
1. Determine the amount of space to provision for your Amazon Aurora MySQL DB cluster. For more information, see [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html).
1. Use the console to create the snapshot in the region where the Amazon RDS MySQL 5.6 instance is located
1. 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 the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CopySnapshot.html).
1. Use the console to migrate the DB snapshot and create an Amazon Aurora MySQL DB cluster with the same databases as the original DB instance of MySQL 5.6.
**Warning**
Amazon RDS limits each user 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 MySQL DB cluster, Aurora MySQL 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 MySQL supports storage up to 64 TB in size, the process of migrating a snapshot into an Aurora MySQL 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 MySQL must use additional space in the volume to convert the tables to be compatible with Aurora MySQL. If you have compressed tables, then Aurora MySQL must use additional space in the volume to expand these tables before storing them on the Aurora MySQL 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 MySQL
You might want to modify your database schema prior to migrating it into Amazon Aurora MySQL. 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 MySQL.
**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 MySQL DB cluster to avoid any service interruptions on your production database.
| Table Type | Limitation or Guideline |
| --- | --- |
| MyISAM tables | Amazon Aurora MySQL supports InnoDB tables only. If you have MyISAM tables in your database, then those tables must be converted before being migrated into Amazon Aurora MySQL. 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 MySQL for that table. To convert a MyISAM table to InnoDB, run the following command: alter table . engine=innodb, algorithm=copy;
|
| Compressed tables | Amazon Aurora MySQL 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 an Amazon Aurora MySQL 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',
'general_log_backup','slow_log_backup'
)
)
)
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 for MySQL DB instance to create an Aurora MySQL DB cluster. The new DB cluster will be populated with the data from the original Amazon RDS for 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 the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html).
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 the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CopySnapshot.html).
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 MySQL 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.
1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
1. Choose **Snapshots**.
1. On the **Snapshots** page, choose the snapshot that you want to migrate into an Aurora MySQL DB cluster.
1. Choose **Migrate Database**.
![\[Migrate a snapshot into Amazon Aurora MySQL\]](http://docs.aws.amazon.com/dms/latest/sbs/images/AuroraMigrate02.png)
1. 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 MySQL 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.
+ **DB Instance Identifier**: Enter 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 `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 user, for each AWS Region.
+ ** **VPC**:** If you have an existing VPC, then you can use that VPC with your Amazon Aurora MySQL DB cluster by selecting your VPC identifier, for example `vpc-a464d1c1`. For information about using an existing VPC, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.CreateVPC.html).
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 MySQL 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 MySQL DB cluster. To have Amazon RDS select an Availability Zone for you, select **No Preference**.
+ ** **Database Port**:** Enter the default port to be used when connecting to instances in the DB cluster. The default is `3306`.
**Note**
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 MySQL DB cluster.
+ ** **Enable Encryption**:** Choose **Yes** for your new Aurora MySQL DB cluster to be encrypted "at rest." If you choose **Yes**, you will be required to choose an AWS KMS encryption key as the KMS key value.
+ ** **Auto Minor Version Upgrade**:** Select **Yes** if you want to enable your Aurora MySQL 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 MySQL DB cluster. It doesn’t apply to regular patches applied to maintain system stability.
![\[Migrate a snapshot into Amazon Aurora MySQL\]](http://docs.aws.amazon.com/dms/latest/sbs/images/AuroraMigrate03.png)
1. Choose **Migrate** to migrate your DB snapshot.
1. 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 about connecting to an Amazon Aurora MySQL DB cluster, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Connect.html).
![\[DB Cluster Details\]](http://docs.aws.amazon.com/dms/latest/sbs/images/AuroraMigrate04.png)