#!/bin/sh # Incremental backup script using rdiff # Author: Driantsov Alexander # Requirements: # * rdiff-backup - http://www.nongnu.org/rdiff-backup/ # * rsync # * ssh ;) #### BACKUP_ADMIN_EMAIL="yourmail@yourserver.com" BACKUP_MAIL_SUBJECT="`hostname`: SQL Backup Synchronization Result" BACKUP_LOG="/var/log/backup-sql" BACKUP_PRIO="20" # Priority for the MySQL dump and rdiff-backup Min: 20 Max: -20 BACKUP_TMP_DIR="/var/backup/mysql_tmp" # New dumps will be stored here BACKUP_DIFF_DIR="/var/backup/hosting/mysql" # Diffs of dumps will be stored there SYNC_SRV="BAC.KUP.SER.VER" # Remote server for backup storage SYNC_USER="backup_user" # User at remote storage SYNC_SPEED="200" # Limit Synchronization Bandwidth to this number of KB/s SYNC_DIR="/backup/hosting/mysql" #Directory on Remote server to synchronize backups in MYSQL_USER="admin" # MySQL user MYSQL_PASSWD=`cat /etc/psa/.psa.shadow` # Password for MySQL. You may obtain password from /etc/psa/.psa.shadow if you are using Plesk on your server. #Implementaition RSCONSTR="$SYNC_USER@$SYNC_SRV" # Dump echo "Backup Started at `date`" > $BACKUP_LOG load_average=`uptime|awk '{print $10" "$11" "$12}'` echo "Load overage at start: $load_average" >> $BACKUP_LOG echo "\nBackingUP MySQL:" >> $BACKUP_LOG for i in `mysql -u$MYSQL_USER -p$MYSQL_PASSWD -Bse "show databases"`; do echo "Backing up database $i..." >> $BACKUP_LOG ; nice -n $BACKUP_PRIO mysqldump --single-transaction --quick --skip-extended-insert -u$MYSQL_USER -p$MYSQL_PASSWD $i > $BACKUP_TMP_DIR/$i.dump; done echo "MySQL dump completed at `date`" >> $BACKUP_LOG load_average=`uptime|awk '{print $10" "$11" "$12}'` echo "\nLoad overage after MySQL dumps: $load_average\n" >> $BACKUP_LOG # Diff echo "Looking for difference in database" >> $BACKUP_LOG nice -n $BACKUP_PRIO rdiff-backup $BACKUP_TMP_DIR $BACKUP_DIFF_DIR/$HOSTNAME 2>&1 >> $BACKUP_LOG echo "Done" >> $BACKUP_LOG echo "Rdiff-backup completed at `date`" >> $BACKUP_LOG load_average=`uptime|awk '{print $10" "$11" "$12}'` echo "\nLoad overage after rdiff-backup: $load_average\n" >> $BACKUP_LOG # Synchronize sql_result=`rsync -avz --bwlimit $SYNC_SPEED $BACKUP_DIFF_DIR $RSCONSTR:$SYNC_DIR|tail -n 2` free_space_info=`ssh $RSCONSTR df -h --sync -t ext3` echo -en " MySQL backup synchronization:\n $sql_result \nSynchronization completed at `date` \n\nInformation on free space on remote backup server: \n $free_space_info \n\n Backup Log: \n\n`cat $BACKUP_LOG`"| mail -s "$BACKUP_MAIL_SUBJECT" $BACKUP_ADMIN_EMAIL