Probably one of the most important things you will have to do in your life as a systems admin is BACKUPS. Typically, you can rsync things from A to B, and then reverse to restore. HOWEVER, there are certain things that this should never be done for. One of those very critical things is MySQL.

Now, there are those that would say “Oh, you can simply rsync mysql over, no big deal”, and those individuals are partially correct. HOWEVER, it is key to note that this is not the safe way to do this. Why? Well, let’s examine this, and you’ll see why.

Mysql, in it’s raw format is binary. What happens when you try to click on an .exe that is only partially downloaded? Oops, you can’t do that. The very same risk is there with mysql, only MORESO, because of what mysql is, and how it handles itself.

Firstly, mysql, on a properly setup server can handle hundreds of queries a second. I’ve had my own 2 servers going at 500+ each repeatedly. Now that’s not good on processing and whatnot, but hey, it’s not a completely BAD thing.
Secondly, mysql, when properly setup, locks tables on execution. So, if it has to so much as go to the bathroom on another table, it locks a table so nothing else can deal with it. Smart thinking there.
Thirdly, mysql is god. Seriously. Ok, maybe not GOD, but it is used in EVERYTHING from blogs (this one is powered by it) to forums (phpbb,smf,ipb,vbulletin, all use it)to you name it, they all use it.

Now, let’s just say that you’re running along and doing a backup at midnight, and at the same time Joe Bloe invites his 5 friends to his blog, and they all make a comment on different entries. Well, if you’re doing things the RIGHT way, nothing happens, maybe those entries aren’t made, but maybe they are copied. No harm, no foul, right? However, IF you’re doing things the wrong way, the entire blog can be seriously messed up, due to the binary structure of mysql.

Let’s say you have to restore from those backups, the very next day. What’s going to happen to Joe Bloe’s blog? If you did things properly, nothing at all. if you did things the way that others mention to do it (rsync), it can , in fact, be a VERY big (and messy) deal. Joe Bloe’s blog (at minimum) is not going to be right, and will need repairing, and that’s ONLY if you’re lucky. Typically, much more will break.

Let’s say you’re moving from server A to server B . Server A has mysql4 on it, while server B has mysql 5 on it. OOPS, your backups are going to screw the entire server over, literally.

So, that said and done, how do we do backups PROPERLY, so that they will work in (virtually) any scenario with minimal issues? That’s easy.
First, we need our script:

DATE=`date +%m%d%y`
THISNEWDATE=`date +%m%d%y-%T`
THISMONTH=`date +%b-%y`
BACKUP=/backup/sql
OLDBACKUPDIR=/backup/sql-archived
BACKMONTH=$BACKUP/$THISMONTH
BACKDEST=$BACKMONTH/$DATE
echo “SQL Backup Started : $THISNEWDATE”;
if [ ! -d $BACKMONTH ];then
tar jcpf $OLDBACKUPDIR/$THISMONTH.tar.bz2 $BACKUP
rm -rf $BACKUP
mkdir $BACKUP
mkdir $BACKMONTH
fi

if [ $BACKDEST ];then
rm -rf $BACKDEST
fi

if [ ! -d $BACKDEST ];then
mkdir $BACKDEST
fi

for var in `find /var/lib/mysql/ -type d | \
sed -e “s/\/var\/lib\/mysql\///”`; do
mysqldump –add-drop-table $var >> $BACKDEST/$var.sql
done
THISENDATE=`date +%m%d%y-%T`
echo “SQL Backup Ended : $THISENDATE”;

Now, 4 variables need to be changed there:
A> backdest needs to point to an actual backup destination
B> oldbackupdir needs to be changed to the directory you want your ARCHIVED backups sent to

SO, an explanation as to what this does to your srever, how it’s run (as root), and the like:

Firstly, this checks to see if the backup destination exists. If it doesn’t, it takes care of that.
Secondly, it checks to see if the backup destination / month exists. If it doesn’t, then it archives everything from the PREVIOUS month to your archived directory (what you created in B)
Thirdly, it then makes a backup of the entire database structure in .sql dump format, adding the necessary –add-drop-table format to this.

I can’t tell you how many times over the years that this has worked for me. It’s literally a lifesaver. SQL is so critical that you SHOULD back it up every day, while flat files are usually good with once a week (though I do every day as well), and incremental backups.

So, now that we have the script in a file, how to use it? We call it through cron:
From the shell (as root)

crontab -e

add this to the cron tab

0 1 * * * /path/to/sqlback.sh > /dev/null 2>&1

Good luck with your backups!