Fastest way to restore sql dump to RDS

Question

I am trying to restore large *.sql dump (~4 GB) to one of my DB on RDS. Last time tried to restore it using Workbench and it took about 24+ hours until the whole process is complete.

I wonder if there is a quicker way to do this. Please help and share your thoughts

EDIT: i have my sql dump on my local computer by the way.

At the moment i have 2 options in mind:

1. Follow this link http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html (with low confidence)
2. dump the DB and compress it, and then upload the compressed dump to one of my EC2 instance, and then SSH to my EC2 instance and do mysql> source backup.sql;

I prefer the second approach (simply because i have more confidence in that), as well as it would fastened the upload time since the entire dump is first uploaded, un-compressed and finally restored.

Answer 1

Your intuition about using an EC2 intermediary is correct, but I really think the biggest thing that will benefit you is actually being inside AWS. I occasionally perform a similar procedure to move the contents of a 6GB database from one RDS DB instance to another.

My configuration:

• Source DB instance and Target DB instance are in the same region and availability zones. Both are db.m3.large.
• EC2 instance is in the same region and availability zone as the DB instances.
• The EC2 instance is compute optimized. (I use c3.xlarge but would recommend the c4 family if I were to start again from scratch).

From here, I just use the EC2 instance to perform a very simple mysqldump from the source instance, and then a very simple mysqlrestore to the target instance.

Being in the same region and availability zones really make a big difference, because it reduces network latency during the procedure. The data transfer is also free or near-free in this situation. The instance class you choose for both your EC2 and RDS instances is also important -- if you want this to be a fast operation, you want to be able to maximize I/O.

External factors like network latency and CPU can (and in my experience, have) bottleneck the operation if you don't provide enough resources. In my example with the db.m3.large instance, the MySQLDump takes less than 5 minutes and the MySQLRestore takes about 15 minutes. I've attempted to restore to a db.m3.medium RDS instance before and the restore time took a little over an hour because the CPU bottlenecked -- it wasn't able to keep up with the EC2 instance. Back when I was restoring from my local machine, being outside of their network caused whole process to take over 4 hours.

This EC2 intermediary shouldn't need to be available 24/7. Turn it off or terminate it when you're done with it. I only have to pay for an hour of c3.xlarge time. Also remember that you can scale your RDS instance class up/down temporarily to increase resources available during your dump. Try to get your local machine out of the equation, if you can.

If you're interested in this strategy, AWS themselves has provided some documentation on the matter.

Answer 2

My suggestion is to take table-wise backup of large tables and restore them by disabling indexes. Which inserts records quickly (at more than double speed) and simply enable the indexes after restore completes.

Before restore command:

ALTER TABLE `table_name` DISABLE KEYS;

After restore completes:

ALTER TABLE `table_name` ENABLE KEYS;

Also add these extra commands at the top of the file to avoid a great deal of disk access:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

And add these at the end:

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

I hope this will work, thank you.

Answer 3

Command is always fast than Workbench. Try to use this command to restore your database.

To Restore :

mysql -u root -p YOUR_DB_NAME < D:\your\file\location\dump.sql

To Take Dump :

mysqldump -u root -p YOUR_DB_NAME > D:\your\file\location\dump.sql