MySQL backup

In this post, I'll setup an automatic backup script that create an sql compressed with bz2 file per database.

sudo -s

Autologin with MySQL :

sudo -s
vi /root/.my.cnf

[client]
user=root
password=TheRootPassword
protocol=tcp

chmod 400 /root/.my.cnf

Test :

[root@dell1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 870724 to server version: 4.1.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
[root@dell1 ~]#

Script

vi /root/scripts/cron/backupDatabaseJob.sh

#!/bin/bash
TIME=`date`
echo "Starting MySQL Backup at $TIME"

BACKUPLOCATION=/home/backup/databases
CURRENTDATE=`date +%Y%m%d`
CURRENTLOCATION=$BACKUPLOCATION/MySQL_$CURRENTDATE

if [ ! -d $BACKUPLOCATION ]
then
echo "create directory for database saves $BACKUPLOCATION"
mkdir $BACKUPLOCATION
fi

echo "Databases saves at $CURRENTLOCATION";
mkdir -p $CURRENTLOCATION


mysql --defaults-extra-file=/root/.my.cnf -B -N -e "show databases" | while read db
do
echo "Backing up $db into $CURRENTLOCATION/${CURRENTDATE}_$db.sql"
/usr/bin/mysqldump --defaults-extra-file=/root/.my.cnf -aecqQ $db > $CURRENTLOCATION/${CURRENTDATE}_$db.sql
done

ls $CURRENTLOCATION | while read dbBackup
do
echo "compressing $CURRENTLOCATION/$dbBackup"
bzip2 -9 $CURRENTLOCATION/$dbBackup
done

echo "setting rights on files"
chmod 640 $CURRENTLOCATION/*
chmod 750 $CURRENTLOCATION

echo "changing ownership to backup:backup on files"
chown -R backup:bacula $CURRENTLOCATION
cd $BACKUPLOCATION
rm -f $BACKUPLOCATION/lastbackup
ln -s $CURRENTLOCATION $BACKUPLOCATION/lastbackup
cd $OLDPWD

TIME=`date`
echo "End of MySQL Backup at $TIME"

Crontab job

crontab -e

20      1       *       *       *       /root/scripts/cron/backupDatabaseJob.sh

And you'll get something like this :

[root@dell1 databases]# ll
total 1820
drwxr-xr-x  107 root   root   57344 Jun  7 01:20 .
drwxr-xr-x    5 root   root    4096 Jun  7 01:28 ..
-rw-r--r--    1 root   root       0 Apr  4 14:08 .bacula_exclude
lrwxrwxrwx    1 root   root      37 Apr  4 01:27 lastbackup -> /home/backup/databases/MySQL_20090607
drwxr-x---    2 backup bacula 12288 Feb 24 21:06 MySQL_20090221
drwxr-x---    2 backup bacula  4096 Feb 24 21:37 MySQL_20090224
drwxr-x---    2 backup bacula 12288 Feb 25 01:26 MySQL_20090225
...

[root@dell1 databases]# ll MySQL_20090607/
total 66424
drwxr-x---    2 backup bacula    12288 Jun  7 01:28 .
drwxr-xr-x  107 root   root      57344 Jun  7 01:20 ..
-rw-r-----    1 backup bacula      415 Jun  7 01:20 20090607_db1.sql.bz2
-rw-r-----    1 backup bacula      417 Jun  7 01:20 20090607_db2.sql.bz2
-rw-r-----    1 backup bacula    97078 Jun  7 01:20 20090607_db3.sql.bz2

...