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