4 ms·

I've been planning my move of a 25gb MySQL db over to RDS. I decided to have a downtime window in the middle of the night, versus doing a live move. I am movin

I've been planning my move of a 25gb MySQL db over to RDS. I decided to have a downtime window in the middle of the night, versus doing a live move. I am moving from a dedicated datacenter to AWS all at once, so I'm in a different situation than those moving from an EC2 mysql instance to RDS, which should be a lot easier. I followed the instructions from the RDS instructions, which tell you to break up mysqldump files great than 1GB and use mysqlimport to send them over. I did some benchmarking and found the import time wasn't linear on file size. This may have to do with the fact that I was using the 1.7GB instance (which i'm upgrading to 15gb for the live site).

djjose 16y ago
We had to do this a few times with large databases. To save some time we recreated the table structure we had (use something like Navicat to make life really easy and copy over the structure). We then exported our DBs into CSV files and used mysqlimport with --compress flag. Took about 4 - 6 hours to get all the data up. Definitely a weekend or latenight job. Of course with significantly less data this could take much less time. Our smaller DBs took less than an hour and those we literally just used the copy functions within Navicat.
- djjose 16y ago
  forgot to add, to minimiza downtime we perform this dump and import during a slow period. If you're using PKs just note where your dumped tables leave off. When ready to roll to prod just put up your maintenance page, export from production your dbs into csv files again, but now from the PKs upward (this saves considerable time, you could always do the whole db again but wouldn't recommend it), import (add) the missing data and voila - a migrated DB. Switch over your DNS or domain settings (however you handle this) and you're up and running on RDS. Hope this helps anyone.

djjose 16y ago

We had to do this a few times with large databases. To save some time we recreated the table structure we had (use something like Navicat to make life really easy and copy over the structure). We then exported our DBs into CSV files and used mysqlimport with --compress flag. Took about 4 - 6 hours to get all the data up. Definitely a weekend or latenight job. Of course with significantly less data this could take much less time. Our smaller DBs took less than an hour and those we literally just used the copy functions within Navicat.

djjose 16y ago
forgot to add, to minimiza downtime we perform this dump and import during a slow period. If you're using PKs just note where your dumped tables leave off. When ready to roll to prod just put up your maintenance page, export from production your dbs into csv files again, but now from the PKs upward (this saves considerable time, you could always do the whole db again but wouldn't recommend it), import (add) the missing data and voila - a migrated DB. Switch over your DNS or domain settings (however you handle this) and you're up and running on RDS. Hope this helps anyone.