I have a huge database approx 200GB (Some tables are more than 25GB in size).
We are willing to migrate to MySQL RDS Instance.but as it does not provide any ssh access so we can't copy file system over there(Like backup taken from Hot backup tools).
we will have to take the dump using mysqldump but it may take days to complete the backup and in same in restoring it into RDS.
As backup will lock the tables for a huge time so we will also require a long downtime.
How Can we do it Quickly?I am looking for Quick and dirty solution?
Also What are the limitation of the RDS Instance?
Best Answer
When we did something similar recently ~250GB, we first used an EC2 slave from production database as a staging slave with SSL and binary logging enabled -- to make table dumps, etc. Converting any tables to InnoDB if you need here. Load data dumps from EC2 to RDS. Then used a binary log feeding strategy to load data and keep it current. At cutover time, flush the last binary log-- and load it into RDS and you're done. I prepped the logs first (removing any set cmds that require super) and just fed them to the mysql client on rds. Next time I want to try Tungsten replicator.