rsnapshot Remote MySQL Backup Shell Script

rsnapshot is an open-source backup and recovery tool. It can create daily, weekly, hourly and monthly file system backup. It can also create local and remote MySQL server backup.

How do I use this script?

Our sample setup:

backup.example.com ==> rsnapshot server with RAID protected disks.
mysql.example.com ==> Remote mysql server
webserver.example.com ==> Remote Apache webserver

Configuration on remote mysql.example.com server

Download the following script and install on remote mysql server at /root/rsnapshot.mysql location. Setup executable permissions:
# cd /root
# wget http://bash.cyberciti.biz/dl/408.sh.zip
# unzip 408.sh.zip
# mv 408.sh rsnapshot.mysql
# rm 408.sh.zip
# chmod +x rsnapshot.mysql
Customize the script and setup login info.

Configuration for rsnapshot server

First, find out your snapshot root, enter:
# grep snapshot_root /etc/rsnapshot.conf
Output:

snapshot_root /.raiddisk/rsnapshots/

rsnapshot will store mysql backup at /.raiddisk/rsnapshots/tmp/ before moving to /.raiddisk/rsnapshots/hourly.0/mysql/ directory. Open your rsnapshot.conf file and add the following configuration:

### start db backup for remote server called mysql.example.com ###


# dump all databases at remote server itself


backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql" unused1/


# Copy all databases from remote server to local server and rsnapshot will move it to /.raiddisk/rsnapshots/$level/mysql/ directory (where, $level can be hourly, monthly etc).


backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/

Here is sample configuration for both web and mysql server:

#


# Backup mysql.example.com


#


backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql" unused1/


backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/


#


# Backup webserver.example.com


#


backup root@webserver.example.com:/etc/ webserver.example.com/


backup root@webserver.example.com:/var/www/ webserver.example.com/


backup root@webserver.example.com:/root/ webserver.example.com/


backup root@webserver.example.com:/var/spool/ webserver.example.com/


 

Save and close the file. Test configuration:
# rsnapshot configtest
See rsnapshot tutorial for further details under RHEL / CentOS and Debian / UbuntuLinux.

Shell Script

#!/bin/bash


# A UNIX / Linux shell script to backup mysql server database using rsnapshot utility.


# -------------------------------------------------------------------------


# Copyright (c) 2007 Vivek Gite <vivek@nixcraft.com>


# This script is licensed under GNU GPL version 2.0 or above


# -------------------------------------------------------------------------


# This script is part of nixCraft shell script collection (NSSC)


# Visit http://bash.cyberciti.biz/ for more information.


# -------------------------------------------------------------------------


# Tested under RHEL / Debian / CentOS / FreeBSD oses


# Must be Installed on remote MySQL Server


# -------------------------------------------------------------------------


# Last update: Sun Jul 5 2009 : Added mysql ping support and binary checking 


# -------------------------------------------------------------------------


### SETUP MYSQL LOGIN ###


MUSER='YOUR-MySQL_USERNAME'


MPASS='YOUR-MySQL_PASSWORD'


MHOST="127.0.0.1"


 


### Set to 1 if you need to see progress while dumping dbs ###


VERBOSE=0


 


### Set bins path ###


GZIP=/bin/gzip


MYSQL=/usr/bin/mysql


MYSQLDUMP=/usr/bin/mysqldump


RM=/bin/rm


MKDIR=/bin/mkdir


MYSQLADMIN=/usr/bin/mysqladmin


GREP=/bin/grep


 


### Setup dump directory ###


BAKRSNROOT=/tmp/rsnapshot/mysql


 


#####################################


### ----[ No Editing below ]------###


#####################################


### Default time format ###


TIME_FORMAT='%H_%M_%S%P'


 


### Make a backup ###


backup_mysql_rsnapshot(){


        local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"


        local db="";


        [ ! -d $BAKRSNROOT ] && ${MKDIR} -p $BAKRSNROOT


        ${RM} -f $BAKRSNROOT/* >/dev/null 2>&1


 [ $VERBOSE -eq 1 ] && echo "*** Dumping MySQL Database ***"


 [ $VERBOSE -eq 1 ] && echo -n "Database> "


        for db in $DBS


        do


                local tTime=$(date +"${TIME_FORMAT}")


                local FILE="${BAKRSNROOT}/${db}.${tTime}.gz"


  [ $VERBOSE -eq 1 ] && echo -n "$db.."


                ${MYSQLDUMP} -u ${MUSER} -h ${MHOST} -p${MPASS} $db | ${GZIP} -9 > $FILE


        done


  [ $VERBOSE -eq 1 ] && echo ""


  [ $VERBOSE -eq 1 ] && echo "*** Backup done [ files wrote to $BAKRSNROOT] ***"


}


 


### Die on demand with message ###


die(){


 echo "$@"


 exit 999


}


 


### Make sure bins exists.. else die


verify_bins(){


 [ ! -x $GZIP ] && die "File $GZIP does not exists. Make sure correct path is set in $0."


 [ ! -x $MYSQL ] && die "File $MYSQL does not exists. Make sure correct path is set in $0."


 [ ! -x $MYSQLDUMP ] && die "File $MYSQLDUMP does not exists. Make sure correct path is set in $0."


 [ ! -x $RM ] && die "File $RM does not exists. Make sure correct path is set in $0."


 [ ! -x $MKDIR ] && die "File $MKDIR does not exists. Make sure correct path is set in $0."


 [ ! -x $MYSQLADMIN ] && die "File $MYSQLADMIN does not exists. Make sure correct path is set in $0."


 [ ! -x $GREP ] && die "File $GREP does not exists. Make sure correct path is set in $0."


}


 


### Make sure we can connect to server ... else die


verify_mysql_connection(){


 $MYSQLADMIN  -u $MUSER -h $MHOST -p$MPASS ping | $GREP 'alive'>/dev/null


 [ $? -eq 0 ] || die "Error: Cannot connect to MySQL Server. Make sure username and password are set correctly in $0"


}


 


### main ####


verify_bins


verify_mysql_connection


backup_mysql_rsnapshot