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 By Vivek Gite Explore and discover exciting holidays and getaways with Yahoo! India Travel http://in.travel.yahoo.com/ [Non-text portions of this message have been removed]
