Today I set up a backup system for MySQL databases. 98% of our websites are using MySQL as the database that drives all the content for their CMS’s.
What I wanted was a script that would export all the database, zip it, then transfer it to the offsite web server. All this would be run as daily, weekly and monthly cron tasks.

First task:
Create a user with least possible access rights.

Using PhpMyAdmin, I created on each machine a user called “backup” with the password “*****************”.
For this user I gave the following permissions:

Second task:
Write the script

#!/bin/bash

#==== DEFINE YOUR SERVER VARIABLES HERE ====#
SERVER=events
FREQ=daily

#==== SCRIPT OPTIONS ====#
USER=backup
PASS="*************"
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

# 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