Full Version of this article can be found here

MySQL DB backup - Automated shell script

There's been quite a bit of interest in my guide to using cron. Lately one of my highest priorities has been data backups! For quite a while now I've had a rolling backup going so that I have 1 backup for every day of the week. There's been quite a few questions floating around about how I'm doing it, so now I'm going to show you exactly what I did. I have full access to the server so I can put things wherever I want :) If you're on a shared server you will have to put the file someplace that you have access to. I prefer placing everything in non web accessible directories.

If you guys would like to see more Linux related content on this site please post a message or 5 in the forum asking for help in our Linux forum. The forum is primarily windows users, so I've been writing guides to make their lives easier. I have plenty of tricks for the linux users too.

Step 1: Create the file

/usr/dbbackup

This can be done using MANY different programs. The most powerful might be vi, but pico is the easiest I've found. If you don't know how to use either of those apps you'll want to google it and/or get yourself out of ssh/telnet immediately and hire an administrator to do it for you! We do have a few linux server admins floating around the forum if you need them.

Step 2: insert the contents (Bold means customize)

#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each separated by a space
databases="DBNAME"
backupdir=/FULL/PATH
mysqldumpcmd=/usr/bin/mysqldump
userpassword=" --user=USER --password=PASS"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi

#get currentdate
today=`date +%w`

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}-$today.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}-$today.sql.gz
$gzip ${backupdir}/${database}-$today.sql
done
echo "Done Compressing"

# And we're done
# This displays the data
ls -l ${backupdir}
# Sets permissions for our next step ;) FTP synchronization use
chown backup *.*
chgrp psacln *.*

Step 3: Customize it

DBNAME = the name of your database
/FULL/PATH = the full path to where you'd like to put the DB dumps
mysqldumpcmd=/usr/bin/mysqldump In a lot of cases this will work, you'll want to use locate mysqldump to find the location of yours.
USER and PASS = Whatever username and password you need to connect to your MySQL Database
backup and psacln = This is for setting your FTP permissions for you to be able to FTP in and download this backup file.

Step 4: Execute Permissions

chmod +x /usr/dbbackup

Step 5: Schedule

Use Cron to schedule this task to be run as often as you would like. I personally do them every single night! My article on cron can be found here.

Step 6: Download the DB!

Of course don't forget to download those files every so often! I've written a guide to having windows automatically grab these files and posted it here.

The End:

I hope you enjoyed this, there's plenty of customizations that you can do to this system, if you need any help, please take it to the forum and somebody will do their best to help you solve your problem.