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

...

Comments

Popular posts from this blog

setup tomcat6 (with native library) with apache2 integration

Setting up UPS link with an ubuntu server

Open ssh connection through proxy with NTML authentication