Automatic and regular backup of the MySQL database

I've recently set up a few databases for my company using MySQL. It all resides on one server and as such I would like to back up all the databases on that very server for error recovery purposes.

Now the first thing we need to know is how to make a backup of all the databases residing in the server. This is easily accomplished with an application called mysqldump whose sole purpose is to back up your databases. It's also part of the community edition of MySQL so you probably already have it installed if you are currently running MySQL.

So let's see how it works in the example below!

mysqldump -u root --single-transaction --all-databases > alldatabases.sql

This will effectively dump all your databases in SQL format to the file alldatabases.sql which you could later import in case you lose your data.

Now this is enough to backup your databases once, but you probably want to do this every day. So I wrote a small bash script to do the job. It's listed below and keeps 10 days of full backups.


#!/bin/bash

BACKUPDIR=/var/Backup/database
cd "$BACKUPDIR";
FILECOUNT=$(ls | wc -l)

echo "Backing up database....";
nice -19 mysqldump -u root -pmysecret --single-transaction \
--all-databases > alldatabases-$(date +%F).sql

if [ "$FILECOUNT" -gt 10 ]
then
OLDFILE=$(ls -tr | head -n 1)
rm -f "$OLDFILE";
echo "Removed oldest file $OLDFILE";
else
echo "We don't have 10 days of backup yet: Not removing anything.";
fi


Note that you will have to exchange the username and password in the mysqldump application call to match your own setup. Also it might be a good idea to set the BACKUPDIR variable so something more useful.