Tech Note: Database Backup Revamp
Charles Johnson
Thu Apr 17, 2008 at 6:12 pm PDT • Views: 236
Stinky and I are finally getting around to a much needed redesign of the backup system for our MySQL database, to give us regular nightly automatic backups instead of the semi-manual backup we’ve been doing. Imagine the great noise that would issue forth from the lizard army if the server crashed and we lost a week’s worth of comments.
The new system has two parts:
* On the DB server, a cron job calls a shell script that renames the current backups with a .bak extension, then uses mysqldump to dump out the database tables needed to recreate LGF if disaster strikes.
* On our desktop Mac, a cron job calls the ‘rsync’ command to connect to the database server through SSH (an hour or so after the server’s cron job runs), and synchronizes the folder containing those dump files to a local folder.
Rsync is smart; when a file exists on the destination (our office Mac), it only downloads the changed section of the file and merges it with the existing one. This saves an enormous amount of time and bandwidth, especially on the 2.5GB+ dump of our comments table.
And because it connects through SSH, the transfer is encrypted and as secure as possible.
This system gives us a two level backup system on the DB server, but since our local computer also uses Apple’s Time Machine backup system, we actually have the ability to restore from even further back if it’s ever necessary.
Backups within backups.
And now… the open thread.
Frank says: