Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have an Amazon RDS Large instance with a database of about 20GB that needs to be migrated without having to take our website down or minimize downtime to less than a couple of hours.

And I am looking for strategies in which this could work effectively? Our RDS has both innodb and myisam tables. How possible is this to do while live without corrupting data and what kind of time do you think it would take to migrate a 21 Gb db? Would it be wise to migrate first to a ec2 server so we would have more capabilities? Can you create a read-only replication of the main RDS and clone that to the EC2 for more protection? Thoughts?

share|improve this question
    
The answer depends in part on the specific version of MySQL that your RDS instance is running. What version is that? Also, do you have full control over the new host (i.e. will you have a MySQL account with the SUPER privilege on the new host?) Also, please explain what you are thinking as far as a benefit to doing an interim migration to EC2? –  Michael - sqlbot Sep 25 '13 at 16:22
    
Hi thanks for the response. The RDS is mysql(5.5.27). We have full control over the new host. I don't really have any reason for thinking a move to an EC2 first would be good except I can have root access. –  John Hamman Sep 25 '13 at 18:33
    
I also have all InnoDB tables in the database. –  John Hamman Sep 25 '13 at 18:40
    
Have you timed how long it takes to make a full backup from the current server, and the time to restore a full backup on the new server? Those two bits of info will be useful for my answer. Also, would running your site in a "read only" mode for a time be a meaningful concept? (Depends on what kind of site it is.) –  Michael - sqlbot Sep 26 '13 at 12:29
    
Amazon RDS now support replication to non-Amazon RDS slaves,you can setup slave outside Amazon and when replication goes fine you can then change it to be master and configure your site to use it. You can find more info in amazon RDS documentation –  usef_ksa Sep 27 '13 at 9:15

1 Answer 1

Dump RDS and import it into mysql that holds all data in RAM disk:

  • Buy strongest possible EC2 instance that might hold your database in memory twice (you might prepare everything on a small instance and then ramp it up)
  • Create RAM disk, install and configure mysql 5.6 to hold both data and redolog on the RAM disk
  • Stop your db-using services, dump the RDS directly into the RAM-backed mysql
  • stop mysql, rsync the DB to a final place, kill the machine

You should do a dry run to estimate expected downtime.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.