# Importing data into an Amazon RDS for MySQL DB instance
You can use several different techniques to import data into an RDS for MySQL DB instance. The best approach depends on a number of factors:
+ Source of the data
+ Amount of data
+ One-time import or ongoing
+ Amount of downtime
If you are also migrating an application with the data, the amount of downtime is important to consider.
The following table lists techniques to importing data into an RDS for MySQL DB instance:
| Source | Amount of data | One time or ongoing | Application downtime | Technique | More information |
| --- | --- | --- | --- | --- | --- |
| Existing MySQL database on premises or on Amazon EC2 | Any | One time | Some | Create a backup of your on-premises database, store it on Amazon S3, and then restore the backup file to a new Amazon RDS DB instance running MySQL. | [Restoring a backup into an Amazon RDS for MySQL DB instance](MySQL.Procedural.Importing.md) |
| Existing MySQL database on premises or on Amazon EC2 | Any | Ongoing | Minimal | Configure replication with an existing MySQL database as the replication source. | [Configuring binary log file position replication with an external source instance](MySQL.Procedural.Importing.External.Repl.md) [Importing data to an Amazon RDS for MySQL database with reduced downtime](mysql-importing-data-reduced-downtime.md) |
| Any existing database | Any | One time or ongoing | Minimal | Use AWS Database Migration Service to migrate the database with minimal downtime and, for many database DB engines, continue ongoing replication. | [What is AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) and [Using a MySQL-compatible database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.MySQL.html) in the *AWS Database Migration Service User Guide* |
| Existing MySQL DB instance | Any | One time or ongoing | Minimal | Create a read replica for ongoing replication. Promote the read replica for one-time creation of a new DB instance. | [Working with DB instance read replicas](USER_ReadRepl.md) |
| Existing MySQL database | Small | One time | Some | Copy the data directly to your MySQL DB instance using a command-line utility. | [Importing data from an external MySQL database to an Amazon RDS for MySQL DB instance](mysql-importing-data-external-database.md) |
| Data not stored in an existing database | Medium | One time | Some | Create flat files and import them using MySQL LOAD DATA LOCAL INFILE statements. | [Importing data from any source to an Amazon RDS for MySQL DB instance](mysql-importing-data-any-source.md) |
**Note**
The `mysql` system database contains authentication and authorization information required to log in to your DB instance and access your data. Dropping, altering, renaming, or truncating tables, data, or other contents of the `mysql` database in your DB instance can result in error and might render the DB instance and your data inaccessible. If this occurs, you can restore the DB instance from a snapshot using the AWS CLI [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html) command. You can recover the DB instance using the AWS CLI [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html) command.
# Importing data considerations for MySQL
The following content contains technical information related to loading data into MySQL. This content is aimed at users who are familiar with the MySQL server architecture.
## Binary logging
Enabling binary logging reduces data load performance and requires up to four times additional disk space compared to disabled logging. The transaction size used to load the data directly affects system performance and disk space needs—larger transactions require more resources.
## Transaction size
Transaction size influences the following aspects of MySQL data loads:
+ Resource consumption
+ Disk space utilization
+ Resume process
+ Time to recover
+ Input format (flat files or SQL)
This section describes how transaction size affects binary logging and makes the case for disabling binary logging during large data loads. You can enable and disable binary logging by setting the Amazon RDS automated backup retention period. Non-zero values enable binary logging, and zero disables it. For more information, see [Backup retention period](USER_WorkingWithAutomatedBackups.BackupRetention.md).
This section also describes the impact of large transactions on InnoDB and why it's important to keep transaction sizes small.
### Small transactions
For small transactions, binary logging doubles the number of disk writes required to load the data. This effect can severely degrade performance for other database sessions and increase the time required to load the data. The degradation experienced depends in part on the following factors:
+ Upload rate
+ Other database activity taking place during the load
+ Capacity of your Amazon RDS DB instance
The binary logs also consume disk space roughly equal to the amount of data loaded until the logs are backed up and removed. Amazon RDS minimizes this by frequently backing up and removing binary logs.
### Large transactions
For large transactions, binary logging triples IOPS and disk usage for the following reasons:
+ The binary log cache stores transaction data temporarily on disk.
+ This cache grows with the transaction size, which consumes disk space.
+ When the transaction (commit or rollback) completes, the system copies the cache to the binary log.
This process creates three copies of the data:
+ The original data
+ The cache on disk
+ The final binary log entry
Each write operation incurs additional IO, further impacting performance.
Because of this, binary logging requires triple the disk space compared to disabled logging. For example, loading 10 GiB of data as a single transaction creates three copies:
+ 10 GiB for the table data
+ 10 GiB for the binary log cache
+ 10 GiB for the binary log file
The total temporary disk space required is 30 GiB.
Important disk space considerations:
+ The cache file persists until either the session ends or a new transaction creates another cache.
+ The binary log remains until it's backed up, potentially holding 20 GiB (cache and log) for an extended period.
If you use `LOAD DATA LOCAL INFILE` to load the data, data recovery creates a fourth copy in case the database has to be recovered from a backup made before the load. During recovery, MySQL extracts the data from the binary log into a flat file. MySQL then runs `LOAD DATA LOCAL INFILE`. Building on the preceding example, this recovery requires a total temporary disk space of 40 GiB, or 10 GiB each for table, cache, log, and local file. Without at least 40 GiB of free disk space, recovery fails.
### Optimizing large data loads
For large data loads, disable binary logging to reduce overhead and disk space requirements. You can disable binary logging by setting the backup retention period to 0. After loading completes, restore the backup retention period to the appropriate non-zero value. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md) and [Backup retention period](USER_ModifyInstance.Settings.md) in the settings table.
**Note**
If the DB instance is a source DB instance for read replicas, then you can't set the backup retention period to 0.
Before loading the data, we recommend that you create a DB snapshot. For more information, see [Managing manual backups](USER_ManagingManualBackups.md).
## InnoDB
The following information about undo logging and recovery options supports keeping InnoDB transactions small to optimize database performance.
### Understanding InnoDB undo logging
Undo is a logging mechanism that enables transaction rollback and supports multi-version concurrency control (MVCC).
For MySQL 5.7 and lower versions, undo logs are stored in the InnoDB system tablespace (usually ibdata1) and are retained until the purge thread removes them. As a result, large data load transactions can cause the system tablespace to become quite large and consume disk space that you can't reclaim unless you recreate the database.
For all MySQL versions, the purge thread must wait to remove any undo logs until the oldest active transaction either commits or rolls back. If the database is processing other transactions during the load, their undo logs also accumulate and can't be removed, even if the transactions commit and no other transaction needs the undo logs for MVCC. In this situation, all transactions—including read-only transactions—slow down. This slowdown occurs because all transactions access all rows that any transaction—not just the load transaction—changes. In effect, transactions must scan through undo logs that long-running load transactions prevented from being purged during an undo log cleanup. This affects performance for any operation accessing modified rows.
### InnoDB transaction recovery options
Although InnoDB optimizes commit operations, large transaction rollbacks are slow. For faster recovery, perform a point-in-time recovery or restore a DB snapshot. For more information, see [Point-in-time recovery](USER_PIT.md) and [Restoring to a DB instance](USER_RestoreFromSnapshot.md).
## Data import formats
MySQL supports two data import formats: flat files and SQL. Review the information about each format to determine the best option for your needs.
### Flat files
For small transactions, load flat files with `LOAD DATA LOCAL INFILE`. This data import format can provide the following benefits over using SQL:
+ Less network traffic
+ Lower data transmission costs
+ Decreased database processing overhead
+ Faster processing
`LOAD DATA LOCAL INFILE` loads the entire flat file as one transaction. Keep the size of the individual files small for the following advantages:
+ **Resume capability** – You can keep track of which files have been loaded. If a problem arises during the load, you can pick up where you left off. You might need to retransmit some data to Amazon RDS, but with small files, the amount retransmitted is minimal.
+ **Parallel data loading** – If you have sufficient IOPS and network bandwidth for a single file load, loading in parallel could save time.
+ **Load rate control **– If your data load has a negative impact on other processes, you can control the load rate by increasing the interval between files.
Large transactions reduce the benefits of using `LOAD DATA LOCAL INFILE` to import data. When you can't break a large amount of data into smaller files, consider using SQL.
### SQL
SQL has one main advantage over flat files: you can easily keep transaction sizes small. However, SQL can take significantly longer to load than flat files. Also, after a failure, it can be difficult to determine where to resume—you can't restart mysqldump files. If a failure occurs while loading a mysqldump file, you must modify or replace the file before the load can resume. Or, alternatively, after you correct the cause of the failure, you can restore to the point in time before the load and resend the file. For more information, see [Point-in-time recovery](USER_PIT.md).
## Using Amazon RDS DB snapshots for database checkpoints
If you load data over long durations—such as hours or days—without binary logging, use DB snapshots to provide periodic checkpoints for data safety. Each DB snapshot creates a consistent copy of your database instance that serves as a recovery point during system failures or data corruption events. Because DB snapshots are fast, frequent checkpointing has minimal impact on load performance. You can delete previous DB snapshots without impacting database durability or recovery capabilities. For more information about DB snapshots, see [Managing manual backups](USER_ManagingManualBackups.md).
## Reducing database load times
The following items are additional tips to reduce load times:
+ Create all secondary indexes before loading data into MySQL databases. Unlike other database systems, MySQL rebuilds the entire table when adding or modifying secondary indexes. This process creates a new table with index changes, copies all data, and drops the original table.
+ Load data in primary key order. For InnoDB tables, this can reduce load times by 75%–80% and reduce data file size by 50%.
+ Disable foreign key constraints by setting `foreign_key_checks` to `0`. This is often required for flat files loaded with `LOAD DATA LOCAL INFILE`. For any load, disabling foreign key checks accelerates data loading. After loading completes, re-enable constraints by setting `foreign_key_checks` to `1` and verify the data.
+ Load data in parallel unless approaching a resource limit. To enable concurrent loading across multiple table segments, use partitioned tables when appropriate.
+ To reduce SQL execution overhead, combine multiple `INSERT` statements into single multi-value `INSERT` operations. `mysqldump` implements this optimization automatically.
+ Reduce InnoDB log IO operations by setting `innodb_flush_log_at_trx_commit` to `0`. After loading completes, restore `innodb_flush_log_at_trx_commit` to `1`.
**Warning**
Setting `innodb_flush_log_at_trx_commit` to `0` causes InnoDB to flush its logs every second instead of at each commit. This setting increases performance but can risk transaction loss during system failures.
+ If you are loading data into a DB instance that doesn't have read replicas, set `sync_binlog` to `0`. After loading completes, restore `sync_binlog parameter`to `1`.
+ Load data into a Single-AZ DB instance before converting the DB instance to a Multi-AZ deployment. If the DB instance already uses a Multi-AZ deployment, we don't recommend switching to a Single-AZ deployment for data loading. Doing so only provides marginal improvements.
# Restoring a backup into an Amazon RDS for MySQL DB instance
Amazon RDS supports importing MySQL databases with backup files. You can create a backup of your database, store the backup file on Amazon S3, and then restore the backup file to a new Amazon RDS DB instance running MySQL. Amazon RDS supports importing backup files from Amazon S3 in all AWS Regions.
The scenario described in this section restores a backup of an on-premises database. As long as the database is accessible, you can use this technique for databases in other locations, such as Amazon EC2 or other cloud services.
The following diagram shows the supported scenario.
![\[MySQL importing backup files from S3.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MySQL-bak-file.png)
If your on-premises database can be offline while you create, copy, and restore backup files, then we recommend that you use backup files to import your database to Amazon RDS. If your database can't be offline, then you can use one of the following methods:
+ **Binary logs** – First, import backup files from Amazon S3 and to Amazon RDS, as explained in this topic. Then use binary log (binlog) replication to update your database. For more information, see [Configuring binary log file position replication with an external source instance](MySQL.Procedural.Importing.External.Repl.md).
+ **AWS Database Migration Service** – Use AWS Database Migration Service to migrate your database to Amazon RDS. For more information, see [What is AWS Database Migration Service?](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html)
## Overview of setup to import backup files from Amazon S3 to Amazon RDS
To import backup files from Amazon S3 to Amazon RDS, you need the following components:
+ An Amazon S3 bucket to store your backup files.
If you already have an Amazon S3 bucket, you can use that bucket. If you don't have an Amazon S3 bucket, create a new one. For more information, see [Creating a bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/CreatingaBucket.html).
+ A backup of your on-premises database created by Percona XtraBackup.
For more information, see [Creating your database backup](#MySQL.Procedural.Importing.Backup).
+ An AWS Identity and Access Management (IAM) role to allow Amazon RDS to access the S3 bucket.
If you already have an IAM role, you can use that role and attach trust and permissions policies to it. For more information, see [Creating an IAM role manually](#MySQL.Procedural.Importing.Enabling.IAM).
If you don't have an IAM role, you have two options:
+ You can manually create a new IAM role. For more information, see [Creating an IAM role manually](#MySQL.Procedural.Importing.Enabling.IAM).
+ You can choose for Amazon RDS to create a new IAM role for you. If you want Amazon RDS to create a new IAM role for you, follow the procedure that uses the AWS Management Console in [Importing data from Amazon S3 to a new MySQL DB instance](#MySQL.Procedural.Importing.PerformingImport) section.
## Creating your database backup
Use the Percona XtraBackup software to create your backup. We recommend that you use the latest version of Percona XtraBackup. You can install Percona XtraBackup from [Software Downloads](https://www.percona.com/downloads/) on the Percona website.
**Warning**
When creating a database backup, XtraBackup might save credentials in the xtrabackup\$1info file. Make sure to confirm that the `tool_command` setting in the xtrabackup\$1info file doesn't contain any sensitive information.
The Percona XtraBackup version that you use depends on the MySQL version that you are backing up.
+ **MySQL 8.4** – Use Percona XtraBackup version 8.4.
+ **MySQL 8.0** – Use Percona XtraBackup version 8.0.
**Note**
Percona XtraBackup 8.0.12 and higher versions support migration of all versions of MySQL 8.0. If you are migrating to RDS for MySQL 8.0.32 or higher, you must use Percona XtraBackup 8.0.12 or higher.
+ **MySQL 5.7** – Use Percona XtraBackup version 2.4.
You can use Percona XtraBackup to create a full backup of your MySQL database files. 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.
For more information about backing up your database with Percona XtraBackup, see [Percona XtraBackup - Documentation](https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html) on the Percona website.
### Creating a full backup with Percona XtraBackup
To create a full backup of your MySQL database files that Amazon RDS can restore from Amazon S3, use the Percona XtraBackup utility (`xtrabackup`).
For example, the following command creates a backup of a MySQL database and stores the files in the folder `/on-premises/s3-restore/backup` folder.
```
xtrabackup --backup --user=myuser --password=password --target-dir=/on-premises/s3-restore/backup
```
If you want to compress your backup into a single file—which you can split into multiple files later, if needed—you can save your backup in one of the following formats based on your MySQL version:
+ **Gzip (.gz)** – For MySQL 5.7 and lower versions
+ **tar (.tar)** – For MySQL 5.7 and lower versions
+ **Percona xbstream (.xbstream) **– For all MySQL versions
**Note**
Percona XtraBackup 8.0 and higher only supports Percona xbstream for compression.
**MySQL 5.7 and lower versions**
The following command creates a backup of your MySQL database split into multiple Gzip files. Replace values with your own information.
```
xtrabackup --backup --user=my_user --password=password --stream=tar \
--target-dir=/on-premises/s3-restore/backup | gzip - | split -d --bytes=500MB \
- /on-premises/s3-restore/backup/backup.tar.gz
```
**MySQL 5.7 and lower versions**
The following command creates a backup of your MySQL database split into multiple tar files. Replace values with your own information.
```
xtrabackup --backup --user=my_user --password=password --stream=tar \
--target-dir=/on-premises/s3-restore/backup | split -d --bytes=500MB \
- /on-premises/s3-restore/backup/backup.tar
```
**All MySQL versions**
The following command creates a backup of your MySQL database split into multiple xbstream files. Replace values with your own information.
```
xtrabackup --backup --user=myuser --password=password --stream=xbstream \
--target-dir=/on-premises/s3-restore/backup | split -d --bytes=500MB \
- /on-premises/s3-restore/backup/backup.xbstream
```
**Note**
If you see the following error, it might be because you mixed file formats in your command:
```
ERROR:/bin/tar: This does not look like a tar archive
```
### Using incremental backups with 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, to save time, copy your existing backup directories and files to your Amazon S3 bucket. For more information about creating incremental backups using Percona XtraBackup, see [Create an incremental backup](https://docs.percona.com/percona-xtrabackup/LATEST/create-incremental-backup.html) on the Percona website.
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 both the full backup and all incremental backup directories and files. This copy must preserve the directory structure in the Amazon S3 bucket. Amazon RDS iterates through all files and directories. Amazon RDS uses the `xtrabackup-checkpoints` file that is included with each incremental backup to identify the base directory and to order incremental backups by log sequence number (LSN) range.
### Backup considerations for Percona XtraBackup
Amazon RDS consumes your backup files based on the file name. Name your backup files with the appropriate file extension based on the file format. For example, use `.xbstream` for files stored using the Percona xbstream format.
Amazon RDS consumes your backup files in alphabetical order and also in natural number order. To ensure that your backup files are written and named in the proper order, use the `split` option when you issue the `xtrabackup` command.
Amazon RDS doesn't support partial backups created using Percona XtraBackup. You can't use the following options to create a partial backup when you back up the source files for your database:
+ `--tables`
+ `--tables-exclude`
+ `--tables-file`
+ `--databases`
+ `--databases-exclude`
+ `--databases-file`
## Creating an IAM role manually
If you don't have an IAM role, you can create a new one manually. However, if you restore the database by using the AWS Management Console, we recommend that you choose to have Amazon RDS create this new IAM role for you. For Amazon RDS to create this role for you, follow the procedure in the [Importing data from Amazon S3 to a new MySQL DB instance](#MySQL.Procedural.Importing.PerformingImport) section.
To create a new IAM role manually for importing your database from Amazon S3, create a role to delegate permissions from Amazon RDS to your Amazon S3 bucket. When you create an IAM role, you attach trust and permissions policies. To import your backup files from Amazon S3, use trust and permissions policies similar to the following examples. For more information about creating the role, see [Creating a role to delegate permissions to an AWS service](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-service.html).
The trust and permissions policies require that you provide an Amazon Resource Name (ARN). For more information about ARN formatting, see [Amazon Resource Names (ARNs) and AWS service namespaces](https://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html).
**Example trust policy for importing from Amazon S3**
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Sid": "AssumeRoleForBackup",
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
```
**Example permissions policy for importing from Amazon S3 — IAM user permissions**
In the following example, replace *iam\$1user\$1id* with your own value.
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Sid": "AllowS3AccessRole",
"Effect": "Allow",
"Action": "iam:PassRole",
"Resource": "arn:aws:iam::111122223333:role/S3Access"
}
]
}
```
**Example permissions policy for importing from Amazon S3 — role permissions**
In the following example, replace *amzn-s3-demo-bucket* and *prefix* with your own values.
****
```
{
"Version":"2012-10-17",
"Statement":
[
{
"Effect": "Allow",
"Action":
[
"s3:ListBucket",
"s3:GetBucketLocation"
],
"Resource": "arn:aws:s3:::amzn-s3-demo-bucket"
},
{
"Effect": "Allow",
"Action":
[
"s3:GetObject"
],
"Resource": "arn:aws:s3:::amzn-s3-demo-bucket/prefix*"
},
{
"Effect": "Allow",
"Action":
[
"kms:Decrypt"
],
"Resource": [
"arn:aws:kms:us-east-1:111122223333:key/key_id*"
]
}
]
}
```
If you include a file name prefix, include the asterisk (\$1) after the prefix. If you don't want to specify a prefix, specify only an asterisk.
## Importing data from Amazon S3 to a new MySQL DB instance
You can import data from Amazon S3 to a new MySQL DB instance using the AWS Management Console, AWS CLI, or RDS API.
### Console
**To import data from Amazon S3 to a new MySQL DB instance**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the upper-right corner of the Amazon RDS console, choose the AWS Region where you want to create your DB instance. Choose the same AWS Region as the Amazon S3 bucket that contains your database backup.
1. In the navigation pane, choose **Databases**.
1. Choose **Restore from S3**.
The **Create database by restoring from S3** page appears.
![\[The Create database by restoring from S3 page where you specify the details for restoring a DB instance from S3.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/mys-s3-ingestion.png)
1. Under **S3 source**:
1. Choose the **S3 bucket** that contains the backup.
1. (Optional) For **S3 prefix**, enter the file path prefix for the files stored in your Amazon S3 bucket.
If you don't specify a prefix, then Amazon RDS creates your DB instance using all of the files and folders in the root folder of the S3 bucket. If you do specify a prefix, then Amazon RDS creates your DB instance using the files and folders in the S3 bucket where the path for the file begins with the specified prefix.
For example, you store your backup files on S3 in a subfolder named backups, and you have multiple sets of backup files, each in its own directory (gzip\$1backup1, gzip\$1backup2, and so on). In this case, to restore from the files in the gzip\$1backup1 folder, you specify the prefix backups/gzip\$1backup1.
1. Under **Engine options**:
1. For **Engine type**, choose **MySQL**.
1. For **Source engine version**, choose the MySQL major version of your source database.
1. For **Engine Version**, choose the default minor version of your MySQL major version in your AWS Region.
In the AWS Management Console, only the default minor version is available. After you complete the import, you can upgrade your DB instance.
1. For **IAM role**, create or choose IAM role with the required trust policy and permissions policy that allows Amazon RDS to access your Amazon S3 bucket. Perform one of the following actions:
+ (Recommended) Choose **Create a new role**, and enter the **IAM role name**. With this option, Amazon RDS automatically creates the role with the trust policy and permissions policy for you.
+ Choose an existing IAM role. Make sure that this role meets all of the criteria in [Creating an IAM role manually](#MySQL.Procedural.Importing.Enabling.IAM).
1. Specify your DB instance information. For information about each setting, see [Settings for DB instances](USER_CreateDBInstance.Settings.md).
**Note**
Be sure to allocate enough storage for your new DB instance so that the restore operation can succeed.
To allow for future growth automatically, under **Additional storage configuration**, choose **Enable storage autoscaling**.
1. Choose additional settings as needed.
1. Choose **Create database**.
### AWS CLI
To import data from Amazon S3 to a new MySQL DB instance by using the AWS CLI, run the [restore-db-instance-from-s3](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html) command with the following options. For information about each setting, see [Settings for DB instances](USER_CreateDBInstance.Settings.md).
**Note**
Be sure to allocate enough storage for your new DB instance so that the restore operation can succeed.
To enable storage autoscaling and allow for future growth automatically, use the `--max-allocated-storage` option.
+ `--allocated-storage`
+ `--db-instance-identifier`
+ `--db-instance-class`
+ `--engine`
+ `--master-username`
+ `--manage-master-user-password`
+ `--s3-bucket-name`
+ `--s3-ingestion-role-arn`
+ `--s3-prefix`
+ `--source-engine`
+ `--source-engine-version`
**Example**
For Linux, macOS, or Unix:
```
1. aws rds restore-db-instance-from-s3 \
2. --allocated-storage 250 \
3. --db-instance-identifier my_identifier \
4. --db-instance-class db.m5.large \
5. --engine mysql \
6. --master-username admin \
7. --manage-master-user-password \
8. --s3-bucket-name amzn-s3-demo-bucket \
9. --s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename \
10. --s3-prefix bucket_prefix \
11. --source-engine mysql \
12. --source-engine-version 8.0.32 \
13. --max-allocated-storage 1000
```
For Windows:
```
1. aws rds restore-db-instance-from-s3 ^
2. --allocated-storage 250 ^
3. --db-instance-identifier my_identifier ^
4. --db-instance-class db.m5.large ^
5. --engine mysql ^
6. --master-username admin ^
7. --manage-master-user-password ^
8. --s3-bucket-name amzn-s3-demo-bucket ^
9. --s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename ^
10. --s3-prefix bucket_prefix ^
11. --source-engine mysql ^
12. --source-engine-version 8.0.32 ^
13. --max-allocated-storage 1000
```
### RDS API
To import data from Amazon S3 to a new MySQL DB instance by using the Amazon RDS API, call the [RestoreDBInstanceFromS3](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html) operation.
## Limitations and considerations for importing backup files from Amazon S3 to Amazon RDS
The following limitations and considerations apply to importing backup files from Amazon S3 to an RDS for MySQL DB instance:
+ You can only migrate your data to a new DB instance, not to an existing DB instance.
+ You must use Percona XtraBackup to back up your data to Amazon S3. For more information, see [Creating your database backup](#MySQL.Procedural.Importing.Backup).
+ The Amazon S3 bucket and the RDS for MySQL DB instance must be in the same AWS Region.
+ You can't restore from the following sources:
+ A DB instance snapshot export to Amazon S3. You also can't migrate data from a DB instance snapshot export to your Amazon S3 bucket.
+ An encrypted source database. However, you can encrypt the data being migrated. You can also leave the data unencrypted during the migration process.
+ A MySQL 5.5 or 5.6 database.
+ RDS for MySQL doesn't support Percona Server for MySQL as a source database because it can contain `compression_dictionary*` tables in the `mysql schema`.
+ RDS for MySQL doesn't support backward migration for either major versions or minor versions. For example, you can't migrate from MySQL version 8.0 to RDS for MySQL 5.7, and you can't migrate from MySQL version 8.0.32 to RDS for MySQL version 8.0.26.
+ Amazon RDS doesn't support importing on the db.t2.micro DB instance class from Amazon S3. However, you can restore to a different DB instance class, and then change the DB instance class later. For more information about instance classes, see [Hardware specifications for DB instance classes](Concepts.DBInstanceClass.Summary.md).
+ Amazon S3 limits the size of a file uploaded to an Amazon S3 bucket to 5 TB. If a backup file exceeds 5 TB, then you must split the backup file into smaller files.
+ Amazon RDS limits the number of files uploaded to an Amazon S3 bucket to 1 million. If the backup data for your database, including all full and incremental backups, exceeds 1 million files, use a Gzip (.gz), tar (.tar.gz), or Percona xbstream (.xbstream) file to store full and incremental backup files in the Amazon S3 bucket. Percona XtraBackup 8.0 only supports Percona xbstream for compression.
+ To provide management services for each DB instance, Amazon RDS creates the `rdsadmin` user when it creates the DB instance. Because `rdsamin` is a reserved user in Amazon RDS, the following limitations apply:
+ Amazon RDS doesn't import functions, procedures, views, events, and triggers with the `'rdsadmin'@'localhost'` definer. For more information, see [Stored objects with 'rdsamin'@'localhost' as the definer](#MySQL.Procedural.Importing.StoredObjects) and [Master user account privileges](UsingWithRDS.MasterAccounts.md).
+ When creating the DB instance, Amazon RDS creates a master user with the maximum supported privileges. When restoring from backup, Amazon RDS automatically removes any unsupported privileges assigned to users being imported.
To identify users that might be affected by this, see [User accounts with unsupported privileges](#MySQL.Migrating.ExtMySQL.Prechecks.Users). For more information on supported privileges in RDS for MySQL, see [Role-based privilege model for RDS for MySQL](Appendix.MySQL.CommonDBATasks.privilege-model.md).
+ Amazon RDS doesn't migrate user-created tables in the `mysql` schema.
+ You must configure the `innodb_data_file_path` parameter with only one data file that uses the default data file name `ibdata1:12M:autoextend`. You can migrate databases with two data files, or with a data file with a different name, using this method.
The following examples are file names that Amazon RDS doesn't allow:
+ `innodb_data_file_path=ibdata1:50M`
+ `ibdata2:50M:autoextend`
+ `innodb_data_file_path=ibdata01:50M:autoextend`
+ You can't migrate from a source database that has tables defined outside of the default MySQL data directory.
+ The maximum supported size for uncompressed backups using this method is limited to 64 TiB. For compressed backups, this limit is lower to account for uncompression space requirements. In such cases, the maximum supported backup size is `64 TiB - compressed backup size`.
For information about the maximum supported database size that RDS for MySQL supports, see [General Purpose SSD storage](CHAP_Storage.md#Concepts.Storage.GeneralSSD) and [Provisioned IOPS SSD storage](CHAP_Storage.md#USER_PIOPS).
+ Amazon RDS doesn't support the importing of MySQL and other external components and plugins.
+ Amazon RDS doesn't restore everything from your database. We recommend that you save the database schema and values for the following items from your source MySQL system database, and then add them to your restored RDS for MySQL DB instance 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 RDS for MySQL DB instance. For more information, see [Local time zone for MySQL DB instances](MySQL.Concepts.LocalTimeZone.md).
### Stored objects with 'rdsamin'@'localhost' as the definer
Amazon RDS doesn't import functions, procedures, views, events, and triggers with `'rdsadmin'@'localhost'` as the definer.
You can use the following SQL script on your source MySQL database to list the stored objects that have the unsupported definer.
```
-- This SQL query lists routines with `rdsadmin`@`localhost` as the definer.
SELECT
ROUTINE_SCHEMA,
ROUTINE_NAME
FROM
information_schema.routines
WHERE
definer = 'rdsadmin@localhost';
-- This SQL query lists triggers with `rdsadmin`@`localhost` as the definer.
SELECT
TRIGGER_SCHEMA,
TRIGGER_NAME,
DEFINER
FROM
information_schema.triggers
WHERE
DEFINER = 'rdsadmin@localhost';
-- This SQL query lists events with `rdsadmin`@`localhost` as the definer.
SELECT
EVENT_SCHEMA,
EVENT_NAME
FROM
information_schema.events
WHERE
DEFINER = 'rdsadmin@localhost';
-- This SQL query lists views with `rdsadmin`@`localhost` as the definer.
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.views
WHERE
DEFINER = 'rdsadmin@localhost';
```
### User accounts with unsupported privileges
User accounts with privileges that RDS for MySQL doesn't supported are imported without the unsupported privileges. For the list of supported privileges, see [Role-based privilege model for RDS for MySQL](Appendix.MySQL.CommonDBATasks.privilege-model.md).
You can run the following SQL query on your source database to list the user accounts that have unsupported privileges.
```
SELECT
user,
host
FROM
mysql.user
WHERE
Shutdown_priv = 'y'
OR File_priv = 'y'
OR Super_priv = 'y'
OR Create_tablespace_priv = 'y';
```
# Importing data from an external MySQL database to an Amazon RDS for MySQL DB instance
You can import data from an existing MySQL database to an RDS for MySQL DB instance. You do so by copying the database with [mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) and piping it directly into the RDS for MySQL DB instance. The `mysqldump` command line utility is commonly used to make backups and transfer data from one MySQL server to another. It's included with MySQL client software.
**Note**
If you are importing or exporting large amounts of data with a MySQL DB instance, it's more reliable and faster to move data in and out of Amazon RDS by using `xtrabackup` backup files and Amazon S3. For more information, see [Restoring a backup into an Amazon RDS for MySQL DB instance](MySQL.Procedural.Importing.md).
A typical `mysqldump` command to move data from an external database to an Amazon RDS DB instance looks similar to the following example. Replace values with your own information.
```
mysqldump -u local_user \
--databases database_name \
--single-transaction \
--compress \
--order-by-primary \
--routines=0 \
--triggers=0 \
--events=0 \
-plocal_password | mysql -u RDS_user \
--port=port_number \
--host=host_name \
-pRDS_password
```
**Important**
Make sure not to leave a space between the `-p` option and the entered password.
As a security best practice, specify credentials other than the prompts shown in this example.
Make sure that you're aware of the following recommendations and considerations:
+ Exclude the following schemas from the dump file:
+ `sys`
+ `performance_schema`
+ `information_schema`
The `mysqldump` utility excludes these schemas by default.
+ If you need to migrate users and privileges, consider using a tool that generates the data control language (DCL) for recreating them, such as the [pt-show-grants](https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html) utility.
+ To perform the import, make sure the user doing so has access to the DB instance. For more information, see [Controlling access with security groups](Overview.RDSSecurityGroups.md).
The parameters used are as follows:
+ `-u local_user` – Use to specify a user name. In the first usage of this parameter, specify the name of a user account on the local MySQL database that you identify with the `--databases` parameter.
+ `--databases database_name` – Use to specify the name of the database on the local MySQL instance that you want to import into Amazon RDS.
+ `--single-transaction` – Use to ensure that all of the data loaded from the local database is consistent with a single point in time. If there are other processes changing the data while `mysqldump` is reading it, using this parameter helps maintain data integrity.
+ `--compress` – Use to reduce network bandwidth consumption by compressing the data from the local database before sending it to Amazon RDS.
+ `--order-by-primary` – Use to reduce load time by sorting each table's data by its primary key.
+ `--routines` – Use if routines such as stored procedures or functions exist in the database that you are copying. Set the parameter to `0`, which excludes the routines during the import process. Then later manually recreate the routines in the Amazon RDS database.
+ `--triggers` – Use if triggers exist in the database that you are copying. Set the parameter to `0`, which excludes the triggers during the import process. Then later manually recreate the triggers in the Amazon RDS database.
+ `--events` – Use if events exist in the database that you are copying. Set the parameter to `0`, which excludes the events during the import process. Then later manually recreate the events in the Amazon RDS database.
+ `-plocal_password` – Use to specify a password. In the first usage of this parameter, specify the password for the user account that you identify with the first `-u` parameter.
+ `-u RDS_user` – Use to specify a user name. In the second usage of this parameter, specify the name of a user account on the default database for the MySQL DB instance that you identify with the `--host` parameter.
+ `--port port_number` – Use to specify the port for your MySQL DB instance. By default, this is 3306 unless you changed the value when creating the DB instance.
+ `--host host_name` – Use to specify the Domain Name System (DNS) name from the Amazon RDS DB instance endpoint, for example, `myinstance.123456789012.us-east-1.rds.amazonaws.com`. You can find the endpoint value in the DB instance details in the Amazon RDS console.
+ `-pRDS_password` – Use to specify a password. In the second usage of this parameter, you specify the password for the user account identified by the second `-u` parameter.
Make sure to create any stored procedures, triggers, functions, or events manually in your Amazon RDS database. If you have any of these objects in the database that you are copying, then exclude them when you run `mysqldump`. To do so, include the following parameters with your `mysqldump` command:
+ `--routines=0`
+ `--triggers=0`
+ `--events=0`
**Example**
The following example copies the `world` sample database on the local host to an RDS for MySQL DB instance. Replace values with your own information.
For Linux, macOS, or Unix:
```
sudo mysqldump -u local_user \
--databases world \
--single-transaction \
--compress \
--order-by-primary \
--routines=0 \
--triggers=0 \
--events=0 \
-plocal_password | mysql -u rds_user \
--port=3306 \
--host=my_instance.123456789012.us-east-1.rds.amazonaws.com \
-pRDS_password
```
For Windows:
Run the following command in a command prompt that has been opened by right-clicking **Command Prompt** on the Windows programs menu and choosing **Run as administrator**. Replace values with your own information.
```
mysqldump -u local_user ^
--databases world ^
--single-transaction ^
--compress ^
--order-by-primary ^
--routines=0 ^
--triggers=0 ^
--events=0 ^
-plocal_password | mysql -u RDS_user ^
--port=3306 ^
--host=my_instance.123456789012.us-east-1.rds.amazonaws.com ^
-pRDS_password
```
**Note**
As a security best practice, specify credentials other than the prompts shown in the example.
# Importing data to an Amazon RDS for MySQL database with reduced downtime
In some cases, you might need to import data from an external MySQL database that supports a live application to an RDS for MySQL DB instance or an RDS for MySQL Multi-AZ DB cluster. Use the following procedure to minimize the impact on availability of applications. This procedure can also help if you are working with a very large database. Using this procedure, you can reduce the cost of the import by reducing the amount of data that is passed across the network to AWS.
In this procedure, you transfer a copy of your database data to an Amazon EC2 instance and import the data into a new Amazon RDS database. You then use replication to bring the Amazon RDS database up-to-date with your live external instance, before redirecting your application to the Amazon RDS database. Configure replication based on binary log coordinates.
**Note**
If you want to import data into an RDS for MySQL DB instance and your scenario supports it, we recommend moving data in and out of Amazon RDS by using backup files and Amazon S3. For more information, see [Restoring a backup into an Amazon RDS for MySQL DB instance](MySQL.Procedural.Importing.md).
The following diagram shows importing an external MySQL database into a MySQL database on Amazon RDS.
![\[Workflow that shows importing an external MySQL database into a MySQL database on Amazon RDS.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMySQLToRDS_1.png)
## Task 1: Create a copy of your existing database
The first step in the process of migrating a large amount of data to an RDS for MySQL database with minimal downtime is to create a copy of the source data.
The following diagram shows creating a backup of the MySQL database.
![\[Workflow that shows creating a backup of the MySQL database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMySQLToRDS_2.png)
You can use the `mysqldump` utility to create a database backup in either SQL or delimited-text format. We recommend that you do a test run with each format in a non-production environment to see which method minimizes the amount of time that `mysqldump` runs.
We also recommend that you weigh `mysqldump` performance against the benefit offered by using the delimited-text format for loading. A backup using delimited-text format creates a tab-separated text file for each table being dumped. To reduce the amount of time required to import your database, you can load these files in parallel using the `LOAD DATA LOCAL INFILE` command. For more information, see [Step 5: Load the data](mysql-importing-data-any-source.md#mysql-importing-data-any-source-load-data) in the Importing data from any source procedure.
Before you start the backup operation, make sure to set the replication options on the MySQL database that you are copying to Amazon RDS. The replication options include turning on binary logging and setting a unique server ID. Setting these options causes your server to start logging database transactions and prepares it to be a source replication instance later in this process.
Make sure that you're aware of the following recommendations and considerations:
+ Use the `--single-transaction` option with `mysqldump` because it dumps a consistent state of the database. To ensure a valid dump file, don't run data definition language (DDL) statements while `mysqldump` is running. You can schedule a maintenance window for these operations.
+ Exclude the following schemas from the dump file:
+ `sys`
+ `performance_schema`
+ `information_schema`
The `mysqldump` utility excludes these schemas by default.
+ If you need to migrate users and privileges, consider using a tool that generates the data control language (DCL) for recreating them, such as the [pt-show-grants](https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html) utility.
### To set replication options
1. Edit the `my.cnf` file. This file is usually located under `/etc`.
```
sudo vi /etc/my.cnf
```
Add the `log_bin` and `server_id` options to the `[mysqld]` section. The `log_bin` option provides a file name identifier for binary log files. The `server_id` option provides a unique identifier for the server in source-replica relationships.
The following example shows the updated `[mysqld]` section of a `my.cnf` file:
```
[mysqld]
log-bin=mysql-bin
server-id=1
```
For more information, see [Setting the Replication Source Configuration](https://dev.mysql.com/doc/refman/8.4/en/replication-howto-masterbaseconfig.html) in the MySQL documentation.
1. For replication with a Multi-AZ DB cluster set the `ENFORCE_GTID_CONSISTENCY` and the `GTID_MODE` parameter to `ON`.
```
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
```
```
mysql> SET @@GLOBAL.GTID_MODE = ON;
```
These settings aren't required for replication with a DB instance.
1. Restart the `mysql` service.
```
sudo service mysqld restart
```
### To create a backup copy of your existing database
1. Create a backup of your data using the `mysqldump` utility, specifying either SQL or delimited-text format.
For MySQL 8.0.25 and lower versions, specify `--master-data=2` to create a backup file that can be used to start replication between servers. For MySQL 8.0.26 and higher versions, specify `--source-data=2` to create a backup file that can be used to start replication between servers. For more information, see the [mysqldump — A Database Backup Program](https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html) in the MySQL documentation.
To improve performance and ensure data integrity, use the `--order-by-primary` and `--single-transaction` options for `mysqldump`.
To avoid including the MySQL system database in the backup, don't use the `--all-databases` option with `mysqldump`. For more information, see [Creating a Data Snapshot Using mysqldump](https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-howto-mysqldump.html) in the MySQL documentation.
Use `chmod`, if necessary, to make sure that the directory where the backup file is being created is writeable.
**Important**
On Windows, run the command window as an administrator.
+ To produce SQL output, use the following command:
For Linux, macOS, or Unix:
```
sudo mysqldump \
--databases database_name \
--master-data=2 \
--single-transaction \
--order-by-primary \
-r backup.sql \
-u local_user \
-ppassword
```
**Note**
As a security best practice, specify credentials other than the prompts shown in the example.
For Windows:
```
mysqldump ^
--databases database_name ^
--master-data=2 ^
--single-transaction ^
--order-by-primary ^
-r backup.sql ^
-u local_user ^
-ppassword
```
**Note**
As a security best practice, specify credentials other than the prompts shown in the example.
+ To produce delimited-text output, use the following command:
For Linux, macOS, or Unix:
```
sudo mysqldump \
--tab=target_directory \
--fields-terminated-by ',' \
--fields-enclosed-by '"' \
--lines-terminated-by 0x0d0a \
database_name \
--master-data=2 \
--single-transaction \
--order-by-primary \
-ppassword
```
For Windows:
```
mysqldump ^
--tab=target_directory ^
--fields-terminated-by "," ^
--fields-enclosed-by """ ^
--lines-terminated-by 0x0d0a ^
database_name ^
--master-data=2 ^
--single-transaction ^
--order-by-primary ^
-ppassword
```
**Note**
As a security best practice, specify credentials other than the prompts shown in the example.
Make sure to create any stored procedures, triggers, functions, or events manually in your Amazon RDS database. If you have any of these objects in the database that you are copying, then exclude them when you run `mysqldump`. To do so, include the following arguments with your `mysqldump` command:
`--routines=0`
`--triggers=0`
`--events=0`
For MySQL 8.0.22 and lower versions, when you run `mysqldump` and specify the delimited-text format, a `CHANGE MASTER TO` comment is returned. This comment contains the master log file name and position. For MySQL 8.0.23 and higher versions, when you run `mysqldump` using the delimited-text format, a `CHANGE REPLICATION SOURCE TO` comment is returned. This comment contains the source log file name and position. If the external instance is MySQL 8.0.23 and higher versions, note the values for `MASTER_LOG_FILE` and `MASTER_LOG_POS`. You need these values when setting up replication.
The following output is returned for MySQL 8.0.22 and lower versions:
```
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.000031', MASTER_LOG_POS=107;
```
The following output is returned for MySQL 8.0.23 and higher versions:
```
-- Position to start replication or point-in-time recovery from
--
-- CHANGE SOURCE TO SOURCE_LOG_FILE='mysql-bin-changelog.000031', SOURCE_LOG_POS=107;
```
For MySQL 8.0.22 and lower versions, if you are using SQL format, you can get the master log file name and position in the `CHANGE MASTER TO` comment in the backup file. For MySQL 8.0.23 and higher versions, if you are using SQL format, you can get the source log file name and position in the `CHANGE REPLICATION SOURCE TO` comment in the backup file.
1. Compress the copied data to reduce the amount of network resources needed to copy your data to the Amazon RDS database. Note the size of the backup file. You need this information when determining how large an Amazon EC2 instance to create. When you are done, compress the backup file using GZIP or your preferred compression utility.
+ To compress SQL output, use the following command:
```
gzip backup.sql
```
+ To compress delimited-text output, use the following command:
```
tar -zcvf backup.tar.gz target_directory
```
## Task 2: Create an Amazon EC2 instance and copy the compressed database
Copying your compressed database backup file to an Amazon EC2 instance takes fewer network resources than doing a direct copy of uncompressed data between database instances. After your data is in Amazon EC2, you can copy it from there directly to your MySQL database. For you to save on the cost of network resources, your Amazon EC2 instance must be in the same AWS Region as your Amazon RDS DB instance. Having the Amazon EC2 instance in the same AWS Region as your Amazon RDS database also reduces network latency during the import.
The following diagram shows copying the database backup to an Amazon EC2 instance.
![\[Workflow that shows copying the database backup to an Amazon EC2 instance.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMySQLToRDS_3.png)
### To create an Amazon EC2 instance and copy your data
1. In the AWS Region where you plan to create the Amazon RDS database, create a virtual private cloud (VPC), a VPC security group, and a VPC subnet. Ensure that the inbound rules for your VPC security group allow the IP addresses required for your application to connect to AWS. You can specify a range of IP addresses—for example, `203.0.113.0/24`—or another VPC security group. You can use the [Amazon VPC console](https://console.aws.amazon.com/vpc) to create and manage VPCs, subnets, and security groups. For more information, see [Getting started with Amazon VPC](https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html#getting-started) in the *Amazon Virtual Private Cloud User Guide*.
1. Open the [Amazon EC2 console](https://console.aws.amazon.com/ec2) and choose the AWS Region to contain both your Amazon EC2 instance and your Amazon RDS database. Launch an Amazon EC2 instance using the VPC, subnet, and security group that you created in Step 1. Ensure that you select an instance type with enough storage for your database backup file when it is uncompressed. For details on Amazon EC2 instances, see [Getting started with Amazon EC2](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html) in the *Amazon Elastic Compute Cloud User Guide*.
1. To connect to your Amazon RDS database from your Amazon EC2 instance, edit your VPC security group. Add an inbound rule specifying the private IP address of your EC2 instance. You can find the private IP address on the **Details** tab of the **Instance** pane in the EC2 console window. To edit the VPC security group and add an inbound rule, choose **Security Groups** in the EC2 console navigation pane, choose your security group, and then add an inbound rule for MySQL or Aurora specifying the private IP address of your EC2 instance. To learn how to add an inbound rule to a VPC security group, see [Security group rules](https://docs.aws.amazon.com/vpc/latest/userguide/security-group-rules.html) in the *Amazon Virtual Private Cloud User Guide*.
1. Copy your compressed database backup file from your local system to your Amazon EC2 instance. Use `chmod`, if necessary, to make sure that you have write permission for the target directory of the Amazon EC2 instance. You can use `scp` or a Secure Shell (SSH) client to copy the file. The following command is an example `scp` command:
```
scp -r -i key pair.pem backup.sql.gz ec2-user@EC2 DNS:/target_directory/backup.sql.gz
```
**Important**
When copying sensitive data, be sure to use a secure network transfer protocol.
1. Connect to your Amazon EC2 instance and install the latest updates and the MySQL client tools using the following commands:
```
sudo yum update -y
sudo yum install mysql -y
```
For more information, see [Connect to your instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html#ec2-connect-to-instance-linux) for Linux instances in the *Amazon Elastic Compute Cloud User Guide*.
**Important**
This example installs the MySQL client on an Amazon Machine Image (AMI) for an Amazon Linux distribution. This example doesn't install the MySQL client on a different distribution, such as Ubuntu or Red Hat Enterprise Linux. For information about installing MySQL, see [Installing MySQL](https://dev.mysql.com/doc/refman/8.4/en/installing.html) in the MySQL documentation.
1. While connected to your Amazon EC2 instance, decompress your database backup file. The following commands are examples.
+ To decompress SQL output, use the following command:
```
gzip backup.sql.gz -d
```
+ To decompress delimited-text output, use the following command:
```
tar xzvf backup.tar.gz
```
## Task 3: Create a MySQL database and import data from your Amazon EC2 instance
By creating an RDS for MySQL DB instance or an RDS for MySQL Multi-AZ DB cluster in the same AWS Region as your Amazon EC2 instance, you can import the database backup file from Amazon EC2 faster than over the internet.
The following diagram shows importing the backup from an Amazon EC2 instance into a MySQL database.
![\[Workflow that shows importing the backup from the EC2 instance into the MySQL database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMySQLToRDS_4.png)
### To create a MySQL database and import your data
1. Determine which DB instance class and what amount of storage space is required to support the expected workload for this Amazon RDS database. As part of this process, decide what is sufficient space and processing capacity for your data load procedures. Also, decide what is required to handle the production workload. You can estimate this based on the size and resources of the source MySQL database. For more information, see [DB instance classes](Concepts.DBInstanceClass.md).
1. Create a DB instance or Multi-AZ DB cluster in the AWS Region that contains your Amazon EC2 instance.
To create an RDS for MySQL Multi-AZ DB cluster, follow the instructions in [Creating a Multi-AZ DB cluster for Amazon RDS](create-multi-az-db-cluster.md).
To create an RDS for MySQL DB instance, follow the instructions in [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md) and use the following guidelines:
+ Specify a DB engine version that is compatible with your source DB instance.
+ Specify the same virtual private cloud (VPC) and VPC security group as for your Amazon EC2 instance. This approach ensures that your Amazon EC2 instance and your Amazon RDS instance are visible to each other over the network. Make sure your DB instance is publicly accessible. To set up replication with your source database as described in a following section, your DB instance must be publicly accessible.
+ Don't configure multiple Availability Zones, backup retention, or read replicas until after you have imported the database backup. When that import is completed, you can configure Multi-AZ and backup retention for the production instance.
1. Review the default configuration options for the Amazon RDS database. If the default parameter group for the database doesn't have the configuration options that you want, find a different one that does or create a new parameter group. For more information about creating a parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. Connect to the new Amazon RDS database as the master user. Create the users required to support the administrators, applications, and services that need to access the DB instance. The hostname for the Amazon RDS database is the **Endpoint** value for this DB instance without the port number, for example, `mysampledb.123456789012.us-west-2.rds.amazonaws.com`. You can find the endpoint value in the database details in the Amazon RDS console.
1. Connect to your Amazon EC2 instance. For more information, see [Connect to your instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html#ec2-connect-to-instance-linux) for Linux instances in the *Amazon Elastic Compute Cloud User Guide*.
1. Connect to your Amazon RDS database as a remote host from your Amazon EC2 instance using the `mysql` command. The following command is an example:
```
mysql -h host_name -P 3306 -u db_master_user -p
```
The *host\$1name* is the Amazon RDS database endpoint.
1. At the `mysql` prompt, run the `source` command and pass it the name of your database dump file. This command loads the data into the Amazon RDS DB instance.
+ For SQL format, use the following command:
```
mysql> source backup.sql;
```
+ For delimited-text format, first create the database, if it isn't the default database that you created when setting up the Amazon RDS database.
```
mysql> create database database_name;
mysql> use database_name;
```
Then create the tables.
```
mysql> source table1.sql
mysql> source table2.sql
etc...
```
Then import the data.
```
mysql> LOAD DATA LOCAL INFILE 'table1.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '0x0d0a';
mysql> LOAD DATA LOCAL INFILE 'table2.txt' INTO TABLE table2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '0x0d0a';
etc...
```
To improve performance, you can perform these operations in parallel from multiple connections so that all of your tables are created and then loaded at the same time.
**Note**
If you used any data-formatting options with `mysqldump` when you initially dumped the table, make sure to use the same options with `LOAD DATA LOCAL INFILE` to ensure proper interpretation of the data file contents.
1. Run a simple `SELECT` query against one or two of the tables in the imported database to verify that the import was successful.
If you no longer need the Amazon EC2 instance used in this procedure, terminate the EC2 instance to reduce your AWS resource usage. To terminate an EC2 instance, see [Terminate an instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/terminating-instances.html#terminating-instances-console) in the *Amazon Elastic Compute Cloud User Guide*.
## Task 4: Replicate data from your external database to your new Amazon RDS database
Your source database was likely updated during the time that it took to copy and transfer the data to the MySQL database. Thus, you can use replication to bring the copied database up-to-date with the source database.
![\[Workflow that shows replicating data from the external MySQL database to the database on Amazon RDS.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMySQLToRDS_5.png)
The permissions required to start replication on an Amazon RDS database are restricted and aren't available to your Amazon RDS master user. Because of this, use the appropriate Amazon RDS stored procedure for your major engine version:
+ [mysql\$1rds\$1set\$1external\$1master (RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master)
+ [mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source)
+ [mysql.rds\$1set\$1external\$1master\$1gtid](mysql_rds_set_external_master_gtid.md) to configure replication and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) to start replication
### To start replication
In Task 1, [when you set replication options](#mysql-importing-data-reduced-downtime-set-replication-options), you turned on binary logging and set a unique server ID for your source database. Now, you can set up your Amazon RDS database as a replica with your live database as the source replication instance.
1. In the Amazon RDS console, add the IP address of the server that hosts the source database to the VPC security group for the Amazon RDS database. For more information on configuring a VPC security group, see [Configure security group rules ](https://docs.aws.amazon.com/vpc/latest/userguide/working-with-security-group-rules.html) in the *Amazon Virtual Private Cloud User Guide*.
You might also need to configure your local network to permit connections from the IP address of your Amazon RDS database so that it can communicate with your source instance. To find the IP address of the Amazon RDS database, use the `host` command:
```
host host_name
```
The *host\$1name* is the DNS name from the Amazon RDS database endpoint, for example `myinstance.123456789012.us-east-1.rds.amazonaws.com`. You can find the endpoint value in the DB instance details in the Amazon RDS console.
1. Using the client of your choice, connect to the source instance and create a user to be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following command is an example:
```
CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'password';
```
**Note**
Specify credentials other than the prompts shown here as a security best practice.
1. For the source instance, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. For example, to grant the `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the '`repl_user`' user for your domain, issue the following command:
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com';
```
1. Make the Amazon RDS database the replica. Connect to the Amazon RDS database as the master user and identify the source database as the source replication instance by using the appropriate Amazon RDS stored procedure:
+ [mysql\$1rds\$1set\$1external\$1master (RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master)
+ [mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source)
If you have a SQL format backup file, use the master log file name and master log position that you determined in Step 4. If you used delimited-text format, use the name and position that you determined when creating the backup files. The following commands are examples:
**MySQL 8.4 and higher versions**
```
CALL mysql.rds_set_external_source ('myserver.mydomain.com', 3306,
'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 1);
```
**MySQL 8.0 and lower versions**
```
CALL mysql.rds_set_external_master ('myserver.mydomain.com', 3306,
'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 1);
```
**Note**
Specify credentials other than the prompts shown here as a security best practice.
1. On the Amazon RDS database, to start replication, run the following command that uses the [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) stored procedure:
```
CALL mysql.rds_start_replication;
```
1. On the Amazon RDS database, to determine when the replica is up to date with the source replication instance, run the [SHOW REPLICA STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) command. The results of the `SHOW REPLICA STATUS` command include the `Seconds_Behind_Master` field. When the `Seconds_Behind_Master` field returns 0, then the replica is up to date with the source replication instance.
**Note**
Previous versions of MySQL used `SHOW SLAVE STATUS` instead of `SHOW REPLICA STATUS`. If you are using a MySQL version before 8.0.23, then use `SHOW SLAVE STATUS`.
1. After the Amazon RDS database is up to date, turn on automated backups so you can restore that database if needed. You can turn on or modify automated backups for your Amazon RDS database by using the [Amazon RDS console](https://console.aws.amazon.com/rds/). For more information, see [Introduction to backups](USER_WorkingWithAutomatedBackups.md).
## Task 5: Redirect your live application to your Amazon RDS instance
After the MySQL database is up to date with the source replication instance, you can now update your live application to use the Amazon RDS instance.
![\[Workflow that shows stopping replication and directing the live application to the database on Amazon RDS.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMySQLToRDS_6.png)
### To redirect your live application to your MySQL database and stop replication
1. To add the VPC security group for the Amazon RDS database, add the IP address of the server that hosts the application. For more information on modifying a VPC security group, see [Configure security group rules](https://docs.aws.amazon.com/vpc/latest/userguide/working-with-security-group-rules.html) in the *Amazon Virtual Private Cloud User Guide*.
1. Verify that the `Seconds_Behind_Master` field in the [SHOW REPLICA STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) command results is 0, which indicates that the replica is up to date with the source replication instance.
```
SHOW REPLICA STATUS;
```
**Note**
Previous versions of MySQL used `SHOW SLAVE STATUS` instead of `SHOW REPLICA STATUS`. If you are using a MySQL version before 8.0.23, then use `SHOW SLAVE STATUS`.
1. Close all connections to the source when their transactions complete.
1. Update your application to use the Amazon RDS database. This update typically involves changing the connection settings to identify the hostname and port of the Amazon RDS database, the user account and password to connect with, and the database to use.
1. Connect to the DB instance.
For a Multi-AZ DB cluster, connect to the writer DB instance.
1. Stop replication for the Amazon RDS instance by running the following command that uses the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) stored procedure:
```
CALL mysql.rds_stop_replication;
```
1. Reset the replication configuration so this instance is no longer identified as a replica by using the appropriate Amazon RDS stored procedure on your Amazon RDS database:
+ [mysql\$1rds\$1reset\$1external\$1master (RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_master)
+ [mysql.rds\$1reset\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_source)
**MySQL 8.4 and higher versions**
```
CALL mysql.rds_reset_external_source;
```
**MySQL 8.0 and lower versions**
```
CALL mysql.rds_reset_external_master;
```
1. Turn on additional Amazon RDS features such as Multi-AZ support and read replicas. For more information, see [Configuring and managing a Multi-AZ deployment for Amazon RDS](Concepts.MultiAZ.md) and [Working with DB instance read replicas](USER_ReadRepl.md).
# Importing data from any source to an Amazon RDS for MySQL DB instance
With Amazon RDS, you can migrate existing MySQL data from any source to an RDS for MySQL DB instance. You can transfer data from on-premises databases, other cloud providers, or existing RDS for MySQL DB instances to your target RDS for MySQL DB instance. With this functionality, you can consolidate databases, implement disaster recovery solutions, or transition from self-managed databases. Common scenarios include moving from self-hosted MySQL servers to fully managed Amazon RDS DB instances, consolidating multiple MySQL databases into a single DB instance, or creating test environments with production data. The following sections provide step-by-step instructions for importing your MySQL data using methods such as `mysqldump`, backup files, or replication.
## Step 1: Create flat files containing the data to be loaded
Use a common format, such as comma-separated values (CSV), to store the data to be loaded. Each table must have its own file—you can't combine data for multiple tables in the same file. Give each file the same name as the table it corresponds to. The file extension can be anything you like. For example, if the table name is `sales`, the file name could be `sales.csv` or `sales.txt`.
If possible, order the data by the primary key of the table being loaded. Doing this drastically improves load times and minimizes disk storage requirements.
The speed and efficiency of this procedure depends on keeping the size of the files small. If the uncompressed size of any individual file is larger than 1 GiB, split it into multiple files and load each one separately.
On Unix-like systems (including Linux), use the `split` command. For example, the following command splits the `sales.csv` file into multiple files of less than 1 GiB, splitting only at line breaks (-C 1024m). The names of the new files include ascending numerical suffixes. The following command produces files with names such as `sales.part_00` and `sales.part_01`.
```
split -C 1024m -d sales.csv sales.part_
```
Similar utilities are available for other operating systems.
You can store the flat files anywhere. However, when you load the data in [Step 5](#mysql-importing-data-any-source-load-data), you must invoke the `mysql` shell from the same location where the files exist, or use the absolute path for the files when you run `LOAD DATA LOCAL INFILE`.
## Step 2: Stop any applications from accessing the target DB instance
Before starting a large load, stop all application activity from accessing the target DB instance that you plan to load to. We recommend this particularly if other sessions will be modifying the tables being loaded or tables that they reference. Doing this reduces the risk of constraint violations occurring during the load and improves load performance. It also makes it possible to restore the DB instance to the point just before the load without losing changes made by processes not involved in the load.
Of course, this might not be possible or practical. If you can't stop applications from accessing the DB instance before the load, take steps to ensure the availability and integrity of your data. The specific steps required vary greatly depending upon specific use cases and site requirements.
## Step 3: Create a DB snapshot
If you plan to load data into a new DB instance that contains no data, you can skip this step. Otherwise, we recommend that you create DB snapshots of the target Amazon RDS DB instance both before and after the data load. Amazon RDS DB snapshots are complete backups of your DB instance that you can use to restore your DB instance to a known state. When you initiate a DB snapshot, I/O operations to your DB instance are momentarily suspended while your database is backed up.
Creating a DB snapshot immediately before the load makes it possible for you to restore the database to its state before the load, if you need to. A DB snapshot taken immediately after the load protects you from having to load the data again in case of a mishap. You can also use DB snapshots after the load to import data into new database instances.
The following example runs the AWS CLI [create-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-snapshot.html) command to create a DB snapshot of the `AcmeRDS` instance and give the DB snapshot the identifier `"preload"`.
For Linux, macOS, or Unix:
```
aws rds create-db-snapshot \
--db-instance-identifier AcmeRDS \
--db-snapshot-identifier preload
```
For Windows:
```
aws rds create-db-snapshot ^
--db-instance-identifier AcmeRDS ^
--db-snapshot-identifier preload
```
You can also use the restore from DB snapshot functionality to create test DB instances for dry runs or to undo changes made during the load.
Keep in mind that restoring a database from a DB snapshot creates a new DB instance that, like all DB instances, has a unique identifier and endpoint. To restore the DB instance without changing the endpoint, first delete the DB instance so that you can reuse the endpoint.
For example, to create a DB instance for dry runs or other testing, you give the DB instance its own identifier. In the example, `AcmeRDS-2`" is the identifier. The example connects to the DB instance using the endpoint associated with `AcmeRDS-2`. For more information, see [restore-db-instance-from-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html).
For Linux, macOS, or Unix:
```
aws rds restore-db-instance-from-db-snapshot \
--db-instance-identifier AcmeRDS-2 \
--db-snapshot-identifier preload
```
For Windows:
```
aws rds restore-db-instance-from-db-snapshot ^
--db-instance-identifier AcmeRDS-2 ^
--db-snapshot-identifier preload
```
To reuse the existing endpoint, first delete the DB instance and then give the restored database the same identifier. For more information, see [delete-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/delete-db-instance.html).
The following example also takes a final DB snapshot of the DB instance before deleting it. This is optional but recommended.
For Linux, macOS, or Unix:
```
aws rds delete-db-instance \
--db-instance-identifier AcmeRDS \
--final-db-snapshot-identifier AcmeRDS-Final
aws rds restore-db-instance-from-db-snapshot \
--db-instance-identifier AcmeRDS \
--db-snapshot-identifier preload
```
For Windows:
```
aws rds delete-db-instance ^
--db-instance-identifier AcmeRDS ^
--final-db-snapshot-identifier AcmeRDS-Final
aws rds restore-db-instance-from-db-snapshot ^
--db-instance-identifier AcmeRDS ^
--db-snapshot-identifier preload
```
## Step 4 (Optional): Turn off Amazon RDS automated backups
**Warning**
Don't turn off automated backups if you need to perform point-in-time recovery.
Turning off automated backups is a performance optimization and isn't required for data loads. Turning off automated backups erases all existing backups. As a result, after you turn off automated backups, point-in-time recovery isn't possible. Manual DB snapshots aren't affected by turning off automated backups. All existing manual DB snapshots are still available for restore.
Turning off automated backups reduces load time by about 25 percent and reduces the amount of storage space required during the load. If you plan to load data into a new DB instance that contains no data, turning off backups is an easy way to speed up the load and avoid using the additional storage needed for backups. However, in some cases you might plan to load into a DB instance that already contains data. If so, weigh the benefits of turning off backups against the impact of losing the ability to perform point-in-time-recovery.
DB instances have automated backups turned on by default (with a one day retention period). To turn off automated backups, set the backup retention period to zero. After the load, you can turn backups back on by setting the backup retention period to a nonzero value. To turn on or turn off backups, Amazon RDS shuts the DB instance down and then restarts it to turn MySQL logging on or off.
Run the AWS CLI `modify-db-instance` command to set the backup retention to zero and apply the change immediately. Setting the retention period to zero requires a DB instance restart, so wait until the restart has completed before proceeding. For more information, see [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html).
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier AcmeRDS \
--apply-immediately \
--backup-retention-period 0
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier AcmeRDS ^
--apply-immediately ^
--backup-retention-period 0
```
You can check the status of your DB instance with the AWS CLI [describe-db-instances](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-instances.html) command. The following example displays the DB instance status of the `AcmeRDS` DB instance:
```
aws rds describe-db-instances --db-instance-identifier AcmeRDS --query "*[].{DBInstanceStatus:DBInstanceStatus}"
```
When the DB instance status is `available`, you're ready to proceed to the next step.
## Step 5: Load the data
To read rows from your flat files into the database tables, use the MySQL `LOAD DATA LOCAL INFILE` statement.
**Note**
You must invoke the `mysql` shell from the same location where your flat files exist, or use the absolute path for the files when you run `LOAD DATA LOCAL INFILE`.
The following example shows how to load data from a file named `sales.txt` into a table named `Sales` in the database:
```
mysql> LOAD DATA LOCAL INFILE 'sales.txt' INTO TABLE Sales FIELDS TERMINATED BY ' ' ENCLOSED BY '' ESCAPED BY '\\';
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
```
For more information about the `LOAD DATA` statement, see [LOAD DATA Statement](https://dev.mysql.com/doc/refman/8.4/en/load-data.html) in the MySQL documentation.
## Step 6: Turn back on Amazon RDS automated backups
If you turned off Amazon RDS automated backups in [Step 4](#mysql-importing-data-any-source-turn-off-automated-backups), after the load is finished, turn automated backups on by setting the backup retention period back to its preload value. As noted in Step 4, Amazon RDS restarts the DB instance, so be prepared for a brief outage.
The following example runs the AWS CLI [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command to turn on automated backups for the `AcmeRDS` DB instance and set the retention period to one day:
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier AcmeRDS \
--backup-retention-period 1 \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier AcmeRDS ^
--backup-retention-period 1 ^
--apply-immediately
```