My web host's backup server died recently, and I needed to hack something together to keep my websites backed up. I'll probably end up using [BackupMoxie][moxie] by my friends at [CrowdFavorite][cf] for [Wetpixel][wp], but it isn't appropriate for my personal websites because it's expensive. Also, the database and files at my personal websites don't change as much as they do on Wetpixel. I ended up hacking together a solution to automatically backup the databases to a Mac Mini here at my home using mysqldump, Transmit, AppleScript, and a cron job.
First, I added a cron job that runs the following script on my server to dump my SQL to files:
rm -f /my/backup/dir/mybackup.sql.gz mysqldump -u username --password=mypassword mysqldatabase > /my/backup/dir/mybackup.sql gzip /my/backup/dir/mybackup.sql
Actually, I use several scripts so I can have daily, weekly, and monthly backups.
Then, I created a Transmit favorite on my Mac that connects to the backup directory on the server. An AppleScript tells Transmit to connect and synchronize / download the directory to a local folder. This was a simple script to whip up; it's based on one of the sample scripts that you can download from the Transmit website. You can download my version, if it helps.
-- Transmit AppleScript to sync Eric's SQL backups -- by Eric Cheng
tell application "Transmit"
-- Create a new session window for the script make new document at before front document
-- send commands to the frontmost document window tell current session of document 1
if (connect to favorite with name "your Transmit favorite name") then
--Set your backup destination folder if (set your stuff to "/Volumes/Your/Directory/Here") then
synchronize method update direction download files with time offset 0
else display dialog ("An error occured: could not change local folder") end if end if end tell
close the front window quit
Finally, I made a cron job on my Mac that runs the script every day. [ChronniX][cron] is a good GUI for cron on Mac OS X.
I would have used rsync to do this, but I was too lazy to install Xcode tools. ;)
[moxie]: http://backupmoxie.com [cf]: http://crowdfavorite.com [wp]: http://wetpixel.com [cron]: http://www.versiontracker.com/dyn/moreinfo/macosx/9478