After a meeting with a customer this week, we were posed with the question of how to backup a MySQL database, off site with the ability to recover records that have been deleted in error. To make the job more interesting, there are some ‘caveats’ which include, not buying Terrabytes of storage, not requiring enormous amounts of bandwidth and not using a massive overhead on te production server. Currently the end user is using a well know replication product which rhymes with ‘Trouble Bake’ but this is using 1GB of RAM on the 32Bit OS so is causing some issues. MySQL is running on Windows for that extra RAM overhead and can only have 4GB RAM (see 32Bit).
As fairly long time users of ZFS with FreeBSD, we are now testing a box which is replicating MySQL using the built in replication feature (in real time) and the snapshots the data every hour. Our Lab is using a ‘production database which is quite small and the data does not change much. The results are very good, so good in fact we are now using it to take backups of web servers in parallel to the normal backups. With great features like de-duplication, on the fly compression and quotas the ZFS system is really worth looking at. The new version of FreeBSD has the system installed out of the box as of course does Solaris and Linux even has a package called ZFS on Linux.
If you are motivated to have a look, here are some links: