I need to import data from my existing database to an Amazon RDS DB instance. How can I accomplish this with a minimum of downtime?

The basic steps that must be followed when importing existing data into any RDS DB instance are:

  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, largely depending on the size of the source database to be imported but also related to the database engine used by the destination RDS DB instance.

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

  • Load and unload data in parallel using compression and multiple threads.
  • For large data loads, consider disabling automated backups by setting the backup retention for the RDS DB instance to zero; a restart of the RDS DB instance is necessary to enact this change. Disabling backups is not recommended for production instances. Disabling backups prevents point-in-time recovery functionality, deletes all previous automated snapshots for the DB instance, and prevents recovery of deleted snapshots. If the DB instance is not yet in production, disabling backups can improve load performance. This change should be immediately rolled back when the data load is complete. Also consider generating recovery points by creating manual snapshots at critical stages of the data load process.
  • Consider disabling Multi-AZ during the data load operation to reduce the overhead caused by the synchronous write operations of Multi-AZ data loads. Multi-AZ functionality is a recommended best practice for all production RDS DB instances and should be enabled as soon as the data load is complete.
  • If you are loading a large amount of data in parallel, ensure that the client machine has sufficient resources to avoid becoming a bottleneck during the data load process.

The steps and tools required to import data into an RDS DB instance depend on the database engine used, as described in the following sections.

MySQL – See Importing and Exporting Data From a MySQL DB Instance. MySQL tools that can be used for this process include mysqldump, mysql, and replication. Replication using MySQL 5.6.13 and later can replicate data to and from an external RDS instance. Replication is useful when working with larger databases because it logs all changes to the source data while the database is being exported, uploaded, and imported. Then, after all data has been imported into the target RDS DB instance, all changes to the source data are replicated to the target RDS DB instance. This allows for a much smaller 'server maintenance' window for switching to the RDS DB instance than would otherwise be possible.

MariaDB – See Importing Data Into a MariaDB DB Instance. MariaDB tools that can be used for this process include mysqldump, mysql, and standard replication. Standard replication using MariaDB 5.3 and later can replicate data to and from an external RDS instance. Replication is useful when working with larger databases because it logs all changes to the source data while the database is being exported, uploaded, and imported. Then, after all data has been imported into the target RDS DB instance, all changes to the source data are replicated to the target RDS DB instance. This allows for a much smaller 'server maintenance' window for switching to the RDS DB instance than would otherwise be possible.

Both MariaDB and MySQL – To improve data load performance for both MariaDB and MySQL, consider employing one or more of the following options:

  • Set the system variable innodb_flush_log_at_trx_commit to 0.
  • Increase the value of the system variable innodb_log_file_size to provide additional log file capacity.
  • Increase the value of the system variable max_allowed_packet to raise the upper limit of the packet message buffer size.
  • Disable auto_commit through the use of a custom parameter group for the duration of the data load.
  • Consider using the third-party utility MySQL Data Dumper to import data into your RDS DB Instance.

Note
Revert these configuration changes when the data import is complete. These modifications are not optimal for normal server operations.

PostgreSQL – See Importing Data into PostgreSQL on Amazon RDS. PostgreSQL tools that can be used for this process include pg_dump, psql, and the copy command. To improve data load performance, consider employing one or more of the following options:

  • Use pg_dump –Fc (compressed) pg_restore –j (parallel) to make use of compression and multiple parallel threads. For more information about these utilities, see pg_dump and pg_restore.
  • Increase the value of the maintenance_work_mem server configuration option to provide additional memory for maintenance operations. The default value for maintenance_work_mem is 16 (MB).
  • Increase the value of the checkpoint_segments and checkpoint_timeout run-time configuration options. For more information about these options, see Checkpoints.
  • Disable synchronous_commit parameters by using a custom parameter group for the duration of the data load.

Note
Revert these configuration changes when the data import is complete. These modifications are not optimal for normal server operations.

Oracle – See 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 defined on the RDS instance when specifying export parameters.

SQL Server – RDS SQL Server DB instances cannot be created by restoring data from a .BAK file. For details, see Importing and Exporting SQL Server Data. SQL Server tools that can be used for this process include the Generate and Publish Scripts Wizard, SQL Server Import and Export Wizard, and the Bulk Copy (bcp.exe) command-line tool described at Bulk Copy Operations in SQL Server. The following blog post provides additional information about importing data from SQL Server to an RDS DB instance running the SQL Server database engine: How to Migrate SQL Server Database to Amazon RDS Instance.

RDS, import, data migration, replication, export, copy, data pump, DB instance


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2015-11-04