rsnapshot Remote MySQL Backup Shell
Script
in Backup, MySQL
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 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 [email protected] "/root/rsnapshot.mysql"
unused1/
# Copy all databases fropm 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 [email protected]:/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 [email protected] "/root/rsnapshot.mysql"
unused1/
backup_script /usr/bin/scp -r [email protected]:/tmp/rsnapshot/mysql/
/.raiddisk/rsnapshots/tmp/ mysql/
#
# Backup webserver.example.com
#
backup [email protected]:/etc/ webserver.example.com/
backup [email protected]:/var/www/ webserver.example.com/
backup [email protected]:/root/ webserver.example.com/
backup [email protected]:/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 /
Ubuntu Linux.
Shell Script
#!/bin/bash
# A UNIX / Linux shell script to backup mysql server database using rsnapshot
utility.
# -------------------------------------------------------------------------
# Copyright (c) 2007 Vivek Gite <[email protected]>
# 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
# -------------------------------------------------------------------------
### SETUP MYSQL LOGIN ###
MUSER='YOUR-MySQL_USERNAME'
MPASS='YOUR-MySQL_PASSWORD'
MHOST="127.0.0.1"
### Setup dump directory ###
BAKRSNROOT=/tmp/rsnapshot/mysql
backup_mysql_rsnapshot(){
local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
local db="";
[ ! -d $BAKRSNROOT ] && /bin/mkdir -p $BAKRSNROOT
/bin/rm -f $BAKRSNROOT/* >/dev/null 2>&1
for db in $DBS
do
local tTime=$(date +"${TIME_FORMAT}")
local FILE="$BAKRSNROOT/${db}.${tTime}.gz"
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
}
backup_mysql_rsnapshot
Cricket on your mind? Visit the ultimate cricket website. Enter
http://beta.cricket.yahoo.com
[Non-text portions of this message have been removed]
