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

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