Sep 17
I was talking with some friends about automating MySQL backups – here’s a script to backup my wordpress mysql database and mail it to a gmail account for safekeeping. It cleans up after itself too.
I run it from cron every 4th day:
0 4 */4 * * /usr/local/bin/wordpress_backup.bash
-Tony
#!/bin/bash
# This requires 'nail' which takes attachments from the command line. Available
# with "sudo yum install -y nail"
DUMP_DIR=/home/USER/wordpress_backup
BASE_FILE_NAME=OSC_wordpress_db
# if the dir does not exist create it
if [ ! -d $DUMP_DIR ]
then
mkdir -p $DUMP_DIR
fi
# generate a file name with todays date
DUMP_FILE=$DUMP_DIR/${BASE_FILE_NAME}_`date +%Y-%m-%d`.sql.bz2
# run the mysqldump, pipe it through bzip2 and redirect it to the filename
mysqldump --add-drop-table -h localhost -u nnnnnn -pxxxxxx wp_dbase |
bzip2 -c > ${DUMP_FILE}
# generate an email body, pipe to nail with the bz2 dump attached
(echo; echo; echo "#########"; echo "###" ;date +%Y-%m-%d; echo "autogenerated blog dumpfile $DUMP_FILE" ) |
nail -a ${DUMP_FILE} -s "blog dumpfile $DUMP_FILE" MAILID+blogbackup@gmail.com
if [ ! -z ${BASE_FILE_NAME} -o ! -d $DUMP_DIR ]
then
cd ${DUMP_DIR}
# look for files that match the dump files that are older than 3 days
# and remove them
find $DUMP_DIR -name "${BASE_FILE_NAME}*" -mtime +3 |xargs -i rm -v {}
else
echo "not doing a find/delete"
fi