One thing I usually forget to do when I backup a computer is back up my MySQL databases. Partly, because they’re not stored in my Library (I don’t think); partly because I forget how many I have. mysqldump only backs up one database at a time, unfortunately. What would be great is something that dumps all of the databases in the system.
Anyhow, whilst on hold to my ISP this morning, I decided to solve this problem once and for all.
The end result is a pair of Ruby scripts which you can get from github.
The first will iterate over every db on your system (when run with an appropriate username and password) and spit out a .sql file with a filename corresponding to that database. The second look at a folder of .sql files named similarly, and for each one, drop a databases with that name, re-create it, and restore from the .sql file.
I’m sure I could do it just fine in a bash script, but it made sense to use the tool that comes most quickly to my hands, and that means Ruby. Once you’ve got Ruby installed, the rest is easy. Clone them, patch them, fix them; they’re basic, as maintenance goes, but handy.
Stew | 15 Aug 2008
Nice n’ short. I like it.
mysqlhotcopy instead of mysqldump would be much faster for larger databases. I guess you wouldn’t get the human readable .sql files though.
James Stewart | 15 Aug 2008
mysqldump has an optional –all-databases switch which will dump everything you have permissions for.
It’s pretty helpful on systems with small databases or plenty of RAM/CPU, but breaks down a bit once the dump files get huge.