I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here:
My concerns are the following:
Amazon recommends using
mysqldump only for "small amounts of data", which they define as less than 1GB. The database I intend to migrate is over 20GB.
One thing that's nice about
mysqldump, however, is that it has the
--single-transaction flag, which allows me to ensure a DB state that is consistent with a single point in time.
For larger amounts of data, Amazon's recommendation is to export the database into flat (e.g., CSV) files and then use
mysqlimport to import those to RDS. The best way I know how to do this, however, is through the
SELECT ... INTO OUTFILE command, which only operates one table at a time. The downside to this, of course, is that it doesn't provide the consistency guarantee of
I suppose I could ensure consistency by taking the entire DB down temporarily; but I'd like to avoid that if at all possible.
- What's the best way to get my large (> 20GB) database into flat files so that I can then use
- If it is indeed the
SELECT ... INTO OUTFILEcommand, how do I export all of the tables in the database (preferably without having to do one at a time)?
- Is there any good way to ensure consistency throughout all this?