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 by my friends at CrowdFavorite for Wetpixel, 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

end tell

Finally, I made a cron job on my Mac that runs the script every day. ChronniX 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. ;)