How I sped up my MySQL restores
I want to share this with the world, as it may have been helpful up front for me. I had to move a database that is 13gb on the filesystem (not including the shared ibdata file) - the database is a mixture of MyISAM and InnoDB tables. That's not an extremely large or complex database, however, when I ran the export script, it only took a couple minutes. Great, I figured import would take longer, but not as long as it actually was originally.
I didn't do the math, but it would have probably taken over 10-15 hours to restore the database from the mysqldump. There's a couple easy tweaks I did not use. For one, I used --skip-opt and made my mysqldump files full INSERT statements (for verbosity and the ability to "diff" them if I ever needed to) - this was stolen from a backup script I wrote.
If you read the documentation/blogs, it says to use --opt when running mysqldump for faster imports. Well, duh! While I was at it, I also tweaked a couple other things. Right now it is moving MUCH faster. What did I do?
- On the source, I used mysqldump --opt (it seemed to dump the database faster too)
- On the destination, I set innodb_flush_log_at_trx_commit to "0" in my.cnf for the time being. This server isn't used yet, so that's safe.
- I also put "SET AUTOCOMMIT=0;" at the top of the script, and "COMMIT;" at the bottom of the script. I don't need any commits until the end, this is a fresh import.
The results are not very scientific, but here's how it breaks down so far (still in the middle of the process)
- Without these tweaks, at 107 minutes it was only at 2.2gb out of 13gb.
- Without these tweaks, at 12 minutes it was at 4.5gb out of 13gb.
I think this will save my bacon, I wish I had done this sooner and not wasted that two hours originally. Someone in #mysql recommended I look at XtraBackup, but it seemed like too much to learn and attempt my first run at it while I was having to do a production migration.