How do I import data into my Amazon RDS DB instance?

Short description

To import data from an existing database to an RDS DB instance:

1. Export data from the source database.
2. Upload the exported data.
3. Import the uploaded data into an RDS DB instance.

The data import process requires varying amounts of server downtime depending on the size of the source database that is imported. The required downtime is also affected by the database engine type used by the destination DB instance. See the following DB-specific resolutions for more information.

Resolution

General data import performance guidelines

The following performance guidelines apply to all Amazon RDS data import/export operations:

• Load and unload data in parallel using compression and multiple threads. If you're loading a large amount of data in parallel, be sure that the client machine has sufficient resources during the data load process.
• Consider disabling automated backups for large data loads by setting the backup retention for the RDS DB instance to zero. You must reboot the RDS DB instance to apply this change. Important: Disabling backups is not recommended for production instances, because this prevents point-in-time recovery functionality, deletes all previous automated snapshots for the DB instance, and prevents recovery of deleted snapshots. However, if the DB instance is not yet in production, disabling backups can improve load performance. After the data load is complete, be sure to enable automated backups. Also consider creating DB snapshots at critical stages of the data load process, so that you can restore your instance from a snapshot, if necessary.
• Consider disabling Multi-AZ during the data load operation to reduce the overhead caused by the synchronous write operations of Multi-AZ data loads. Important: Multi-AZ functionality is a best practice for all production RDS DB instances, and Multi-AZ should be enabled as soon as the data load is complete.

Database engine-specific data import guidelines

Importing Data to an Amazon RDS MySQL or MariaDB – You can use MySQL tools, such as mysqldump, mysql, and replication, to import data to Amazon RDS. Replication using MySQL 5.6.13 and later can replicate data to and from an external RDS DB instance. When importing data into a MariaDB DB instance, you can use MariaDB tools such as mysqldump, mysql, and standard replication to import data to Amazon RDS.

Importing Data into PostgreSQL on Amazon RDS – You can use PostgreSQL tools such as pg_dump, psql, and the copy command to import data to Amazon RDS.

Importing Data into Oracle on Amazon RDS – Small databases can use the Database Copy feature available with Oracle SQL Developer. Larger databases require a data pump to export and import using a database link and file transfer to the directory that's defined on the RDS DB instance when specifying export parameters.

Importing and Exporting SQL Server Databases – You can use native backup and restore for Microsoft SQL Server databases by using .bak files. You can also create a full backup of your on-premises database, store the backup on Amazon Simple Storage Service (Amazon S3), and then restore the backup file to Amazon RDS.

Importing Data into Aurora MySQL – Similarly to importing into Amazon RDS, you can use to native tools such as mysqldump and mydumper to migrate to Amazon Aurora for MySQL. You can use Aurora MySQL compatible binary log replication to reduce downtime. You can also migrate to Aurora MySQL by using Percona Xtrabakup stored on Amazon S3, by using a snapshot of an Amazon RDS MySQL DB instance, or by creating an Aurora replica of an existing RDS MySQL DB instance. For all migration options, be sure that all source tables are converted to InnoDB Storage engine with dynamic row format. This helps accelerate the speed of your migration, and helps achieve a successful migration to Aurora. For more information, see Best Practices for Migrating MySQL Databases to Amazon Aurora.

Using AWS Database Migration Service (AWS DMS) - You can use AWS DMS to import data from on-premise environments to AWS. DMS offers both homogeneous and heterogeneous migrations with minimal downtime. DMS offers the following migration types:

• Migrate existing data (full load) – Move existing data from the source to the target database instance. This is a one-time load, and it's similar to the import and export options listed previously. This migration type is best for small and medium databases that can afford considerable downtime
• Migrate existing data and replicate ongoing changes (full load + change data capture (CDC)) – To migrate data with minimal downtime, AWS DMS can migrate the existing data and replicate the data changes from the source to the target until the cutover. This migration type is best for small and medium databases that require minimal downtime, which only lasts for the duration of the cutover.
• Replicate data changes only (CDC) – Use the efficiency of the native import and export tools to migrate the existing data and set up a CDC-only task that captures ongoing changes from the source to the target instance. The CDC custom start time parameter specifies the point in time to start the data sync. This migration type is best for medium and large databases that require minimal downtime lasting only for the duration of the cutover.

Related information

Exporting Data from a MySQL DB Instance by Using Replication

Best Practices for Amazon RDS

Migrating Databases to Amazon Web Services (AWS)