This is the WindEurope database backup script. It’s modified from the standard EWEA script because the DB server is a) not local and b) running on a non-standard port. Therefore we need to make a few changes to the mysql query. We run the script in the same way as usual.

#!/bin/bash
# db.sh
# Backup of database to offsite server
# Jason Bickley, Senior Web Manager
# Wind Europe
# 23 Jan 2016

#==== RECEIVE VARIABLES FROM COMMAND LINE ====#
FREQ=$1
SERVER=$2

#==== SCRIPT OPTIONS ====#
USER=backup
PASS="mwbubCEsxCU6XVsW"
DIR=/root/backup/mysql/
FILE=localhost.sql.gz
DEST=backup.ewea.org
DATE=$(date +"%Y%m%d %T")
LOG=/var/log/backupDB_log

#==== EXCUTION OF COMMANDS ====#
# Change operating directory
cd $DIR

#==== EXPORT DATABASE ====#
mysqldump -h192.168.0.113 --port=13306 -u$USER -p$PASS --all-databases --lock-all-tables | gzip > $SERVER.$FREQ.$FILE
chmod 600 $SERVER.$FREQ.$FILE

#==== RSYNC THE EXPORTED FILE TO OFFSITE SERVER ====#
rsync -aze "ssh -p 10022 -i /root/.ssh/"$SERVER"_prv_key" $SERVER.$FREQ.$FILE root@$DEST:/backups/$FREQ/$SERVER/db/

#==== DELETE THE LOCAL FILE ====#
rm -f $SERVER.$FREQ.$FILE

#==== CONFIRM SUCCESS IN LOG FILE ====#
echo $DATE Backup successful! $DEST:/backups/$FREQ/$SERVER/db/$SERVER.$FREQ.$FILE >> $LOG

To use it, you just have to run: db.sh {freq} {servername} {option}

cd /root/Scripts/backup/
./db.sh daily main

THIS POST IS OUT OF DATE NOW. PLEASE CHECK THE NEW SCRIPT HERE:
https://itblog.windeurope.org/2012/11/06/backup-database-and-rsync-to-offsite-server-2/

This the script I am using to back up the servers to offsite.ewea.org

#!/bin/bash
#
# Backup of database to offsite server
# Jason Bickley, Web Manager EWEA
# March 19 2012
#
#==== DEFINE YOUR SERVER VARIABLES HERE ====#
SERVER=events
FREQ=daily

#==== SCRIPT OPTIONS ====#
USER=backup
PASS="mwbubCEsxCU6XVsW"
LOCALDIR=/root/backup/mysql/
FILE=localhost.sql.gz
DEST=offsite.ewea.org

#==== EXCUTION OF COMMANDS ====#
# Change opearating directory
cd $LOCALDIR

# Export database
mysqldump -u$USER -p$PASS --all-databases | gzip > $FREQ.$FILE
chmod 600 $FREQ.$FILE

# rscync the export to offsite server
rsync -aze "ssh -p 10022 -i /root/.ssh/"$SERVER"_prv_key" $FREQ.$FILE root@$DEST:/backups/$SERVER/db/$FREQ/

# delete the exported file
rm -f $FREQ.$FILE

The file is stored here: /root/Scripts/backup, and there are “daily”, “weekly” and “monthly” variations:
db_daily.sh
db_monthly.sh
db_weekly.sh