Download MySql Backup/Snapshot from Amazon RDS

Question

I have hosted my MySql Database on Amazon RDS on my Production Instance. I have another RDS instance for Development purpose. Now I am looking for best/fastest way to restore database backup/snapshot from Production Instance to Development Instance, because My database is very huge and Backup downloading process is very time taking job as well as it's block my all tables for read/write transaction. Or Is there any option for download backup without blocking insert/update/select transaction?

Thanks.

EDIT :

Here one mistake I have done, In above question I mean to say account not an Instance. I have two different accounts, one for production and another is for development/testing. I would like to restore snapshot from production account's instance to the development account's instance, and both instances are located in same region (e.g. singapore).

Paez

Related: #18193109 – Aparri

Answer 1

Given that you have clarified that you need to take the backup and put it back to a different RDS instance which in a different AWS Account; the only way you can do it to take the sql-dump or database backup and re-restore it; as you can't take and share the snapshot between accounts.

The one way you can kind of side line the I/O blocking and transaction bottlenecks is making use of Read-Replica. This way the entire backup process would be done in the Read-Replica and your main instance would not get affected / effected.

• Create a read-replica
• Allow the replica to get populated as much as possible
• Then apply your SQL Backup tool to connect to that Replica Instance & create the backup
• Put the backup in S3
• Go to the new instance and restore it back

PS - Considerations :

1. As we are using a read-replica, there may be a stale ( replication - lag ) between the main-instance and read replica. You need consider that before starting the process. The replication happens asynchronously.

2. You should try performing the backup operations from an EC2 instance to take advantage of the latency, networking bandwidth pipe.

Unreadable

thanks @Naveen, If I am taking a backup from read-replica, then very much possibilities of replication lag on my heavy write intensive database which you have mentioned in your first point. How Do I take backup (I mean to say sqldump, I don't know another option yet!) with data integrity? – Paez