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

7 minute read

I want to import data from my existing database into an Amazon Relational Database Service (Amazon RDS) instance with minimal downtime.

Short description

To import data from an existing database to an Amazon RDS DB instance, complete the following steps:

Export data from the source database
Upload the exported data
Import the uploaded data into an Amazon RDS DB instance

Note: The data import process requires varying lengths of server downtime. The length of downtime depends on the size of the source database that's being imported. The length of downtime also depends on the database engine type used by the DB instance.

Resolution

To import data into your Amazon RDS DB instance, complete the following steps:

Export data from the source database

To create a copy of your existing database, complete the following steps based on the source engine of your database:

Amazon RDS for MySQL

To create a copy of an existing MySQL database, use MySQL tools such as mysqldump and mysql. For more information, see mysqldump — A Database Backup Program and mysql — The MySQL Command-Line Client on the MySQL website.

Another method is to use replication to import data from a source MySQL database to Amazon RDS. For more information, see Replication on the MySQL website.

Amazon RDS for PostgreSQL

To create a copy of an existing PostgreSQL database, use PostgreSQL tools such as pg_dump, psql, and the copy command. For more information, see pg_dump, psql, and copy on the PostgreSQL website.

Amazon RDS for Oracle

To export data from an existing Oracle database to Amazon RDS, use Oracle tools such as Oracle SQL Developer and Oracle Datapump.

For small databases, see Database copy using Oracle SQL Developer on the Oracle website.

For large databases, use a database link to perform a data pump export and import to the directory that's defined on the instance. If you want to specify export parameters, then database links are required. For more information, see Database Links, Data Pump Export, Data Pump Import, and Specifying Export Parameters on the Oracle website.

Amazon RDS for SQL Server

To export SQL Server Databases, use the native backup and restore for Microsoft SQL Server databases. For this method, use .bak files.

Another method is to back up your database, store the backup on Amazon Simple Storage Service (Amazon S3), and then restore the backup to Amazon RDS.

Amazon Aurora MySQL

To export data from an external MySQL database to an Amazon Aurora MySQL DB cluster, use native tools such as mysqldump and mydumper. Also, use Aurora MySQL compatible binary log replication to reduce downtime.

Another method to migrate MySQL data to an Aurora MySQL DB cluster is by using either a snapshot of your DB instance or an Aurora read replica.

Note: For all migration options, make sure that all source tables are converted to an InnoDB storage engine with dynamic row format. This accelerates the speed of your migration. For more information, see Migrating your databases to Amazon Aurora.

Upload the exported data

Amazon RDS for MySQL

To upload exported data, upload to either Amazon Elastic Compute Cloud (Amazon EC2) or Amazon S3:

For Amazon EC2, see Create an Amazon EC2 instance and copy the compressed database.
For Amazon S3, see Restoring a backup into a MySQL DB instance.

Amazon RDS for PostgreSQL

To upload exported data, upload to either Amazon EC2 or Amazon S3:

For Amazon EC2, see Importing a PostgreSQL database from an Amazon EC2 instance.
For Amazon S3, see Importing data from Amazon S3 into an RDS for PostgreSQL DB instance.

Amazon RDS for Oracle

To upload exported data to your Amazon RDS for Oracle DB instance, use Oracle Data Pump with either Amazon S3 or a database link:

To use Oracle Data Pump with Amazon S3, see Importing data with Oracle Data Pump and an Amazon S3 bucket.
To use Oracle Data Pump with a database link, see Importing data with Oracle Data Pump and a database link.

For more information, see Overview of Oracle Data Pump.

Amazon RDS for SQL Server

To upload exported data from a SQL Server database to Amazon S3, see Setting up for native backup and restore.

Import the uploaded data into an Amazon RDS DB instance

Amazon RDS for MySQL

To import uploaded data from a MySQL database, use tools such as mysqldump, mysql, and replication on the MySQL website. If you use MySQL version 5.7.9 and later, then data can be replicated to and from an external Amazon RDS DB instance. For multi-threaded migrations, see Multi-threaded migration using mydumper and myloader.

For more information, see Importing data to an Amazon RDS MariaDB or MySQL database with reduced downtime.

Amazon RDS for MariaDB

To import uploaded data from a MariaDB database, use tools such as mysqldump, mysql, and replication on the MariaDB website. For large database migrations, use mydumper and myloader to perform multi-threaded dumps and imports. For multi-threaded migration, see Multi-threaded migration using mydumper and myloader.

For more information, see Importing data to an Amazon RDS MariaDB or MySQL database with reduced downtime.

Amazon RDS for PostgreSQL

To import uploaded data from a PostgreSQL database, use psql to create the database on the DB instance and load data. For more information, see Use psql to create the database on the DB instance and load data.

To import uploaded data from your Amazon S3 bucket, use the table_import_from_s3 function of the aws_s3 extension. For more information, see Importing data from Amazon S3 to your RDS for PostgreSQL DB instance.

Amazon RDS for Oracle

To import uploaded data from an Oracle database, use Oracle Data Pump and the impdp command-line client. For more information about Oracle Data Pump, see Overview of Oracle Data Pump. For more information about the impdp command, see Starting Oracle Data Pump Import on the Oracle website.

Amazon RDS for SQL Server

To import uploaded data from a SQL Server database from Amazon S3, use the rds_restore_database procedure. For more information, see Restoring a database.

Data import guidelines

General data import guidelines

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

Use compression and multiple threads to load and unload data in parallel. If you're loading a large amount of data in parallel, then make sure that the client machine has sufficient resources during the data load process.
Set the backup retention for your Amazon RDS DB instance to zero to turn off automated backups to improve performance for large data loads. Make sure that you reboot your Amazon RDS DB instance to apply this change. When the data load is complete, turn on automated backups. It's a best practice to create a DB snapshot at critical stages of the data load. This allows you to restore your instance from a snapshot, if required.

Note: It's a best practice to not turn off backups for production instances. Turning off backups prevents point-in-time recovery functionality, deletes previously automated snapshots of the DB instance, and prevents recovery of deleted snapshots.

To reduce the overhead that's caused by the write operations of Multi-Availability Zone (AZ) data loads, turn off Multi-AZ.

Note: It's a best practice to use Multi-AZ for all production Amazon RDS DB instances. Make sure that Multi-AZ is turned on as soon as the data load is complete.

Source database data import guidelines

Use AWS Database Migration Service (AWS DMS) to import data from on-premise environments to AWS. AWS DMS offers homogenous and heterogeneous migrations with minimal downtime. AWS DMS has the following migration types: