Reading links...
Show top rated links | LinkViewer
Tech Note: Database Backup Revamp
Thu, Apr 17, 2008 at 6:12:02 pm PST
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.
1335 comments
- Comments are open and unmoderated, and do not necessarily reflect the views of Little Green Footballs.
- Obscene, abusive, silly, or annoying remarks may be deleted, but the fact that particular comments remain on the site in no way constitutes an endorsement of their views by Little Green Footballs.
- Posts that contain phone numbers, addresses, or other personal information will also be deleted, as will posts that consist only of a variation on the word, "First!"
- Comments that advocate violence will be cause for immediate banning with no appeal.
- REMEMBER: posting comments at LGF is a privilege, not a right. Abuse that privilege, and your account will be blocked.











