WordPress & MySQL DB backup

First posted on the 17th March, 2011 – IT
Last modified on the 23rd April, 2012, at 7:26 pm

Automated script to backup word press MySQL database and main web documents directory each day and keep monthly backup. The script backup-wordpress.csh is run by cron each day at midnight:

0 0 * * * backup-wordpress.csh

The crontab in Mac OS X 10.6 is located at:

/usr/lib/cron/tabs/$username

Cron tabs are far more easily created and edited in Mac OS X using Cronnix.


#!/bin/csh
# backup-wordpress.csh
# script to backu wordpress setup
# creates a backup of all wordpress files and the wordpress mysql database

set timestamp = `date '+%Y-%m-%d'`
set password = 'replace-with-your-own-password'

# copies wordpress directory to user directory for time-machine backup
tar -czf ~/Library/WordPress/$timestamp-dir.tgz ~/Sites/wordpress/

# dump workpress database to user directory for time-machine backup
/usr/local/mysql/bin/mysqldump --add-drop-table -u wordpress --password=$password wordpress > ~/Library/WordPress/$timestamp-db.sql
tar -czf ~/Library/WordPress/$timestamp-db.sql.tgz ~/Library/WordPress/$timestamp-db.sql
rm ~/Library/WordPress/$timestamp-db.sql

# tidy up archive leaving only first of month
set dom = `date '+%d'`

if ($dom != 1) then
set xtimestamp = `date -v-29d '+%Y-%m-%d'`
rm ~/Library/Wordpress/$xtimestamp-dir.tgz
rm ~/Library/Wordpress/$xtimestamp-db.sql.tgz
#else
#echo first of month
endif

# to restore
# wordpress files simply uncompress tgz archive
# mysql database: mysql wordpress --password=$password --database=wordpress2 < dump.sql