If you use MySQL as part of your web hosting operation, which of course everyone does, you’ve probably run into your share of requests from customers to restore data.  This is all well and good if you’re fortunate enough to find that the tables in question were MyISAM tables; just restore to temp location, stop MySQL, replace the relevant MYI/MYD/frm files, start MySQL, good to go.

Then one day…. a restore request for an InnoDB table; aww shit.  Even if you’re using the MySQL setting (that I very very highly recommend) of innodb_file_per_table to store those little rascals in their own files similar to MyISAM, you still can’t simply restore them like you can with MyISAM.  InnoDB tables need to be properly backed up using a MySQL hot backup client of your choosing, or restored from a good ol’ fashioned dump file.

Assuming you’re not dealing with any super massive tables that take hours to dump, the following command will run through all of your MySQL db’s and dump them all to /var/lib/mysql.backups/DBNAME.sql without you having to do anything special (but you will need to include your MySQL root pass):

/usr/bin/find /var/lib/mysql/* -type d -printf "%f\0"|xargs -0 -n 1 -I{} /usr/bin/mysqldump --opt --events -u root --password='PASSWORD' -r /var/lib/mysql.backups/{}.sql {}

If you’re on a server running the Plesk control panel, use this:

/usr/bin/find /var/lib/mysql/* -type d -printf "%f\0"|xargs -0 -n 1 -I{} /usr/bin/mysqldump --opt --events -u admin --password=`cat /etc/psa/.psa.shadow` -r /var/lib/mysql.backups/{}.sql {}

And when you’re ready to turn either job into a cron job, you’re going to need to add a back slash in front of the percent sign that you see in the printf argument; i.e. make it look like: -printf “\%f\0”

When a customer needs a restore, you can import the entire file if it’s the entire DB they want, or if it’s just one table, edit the file down to just the drop and import for that one table and import that.

Obviously this solution requires enough disk space to accommodate a dump of every database on your server.  It also won’t work too well if you’re dealing with any truly huge databases as those can take hours or days to dump using mysqldump; you’re going to need an entirely different solution if you need to be able to restore those.  My suggestion there would be snapshot backups at the storage level and restore by way of bringing the snapshot up on a new ‘server’ to get the data out.