Everybody knows it: backups ARE important…But not everybody does backup 😉
I decided to start backing up my Mysql databases to a remote server, you know, just in case…
I wrote a short shell script that does the job automatically for me: it uses commands like mysqldump, gzip and ssh to do the whole stuff. It also rotates the backups on the remote server. That is, simply rename the existing copies on the remote server instead of directly removing it. After some rotations, it removes the oldest backup.
I do this it to let me some time to detect a database problem (data corruption, etc) before overwriting the good backup by a corrupted version…
You can choose the rotation amount (default: 7) by modifying the script, and the backup rate by configuring cron.
Here is the full script. It’s also available from the “linux -> files” section of my site.
#!/bin/bash
# Script Name: backupMysql.sh
# Purpose: creates a backup of all Mysql databases:
# - dumps all databases and zip them (.sql.gz)
# - rotates old backups on remote server (7 rotations)
# - copies new backup to remote server (rotation 1)
# - Optional: mail result
# Author: Harck
# Date: 21AUG06
# Mysql Settings:
mysql_user="username"
mysql_password="password"
mysql_host="host" # usually localhost
# SSH Settings:
rhost="ssh_host"
ruser="ssh_user"
rport="ssh_port" # typically 22
working_dir="/tmp/backupMysl"
backup_dir="EXISTING directory on remote host"
# Other settings:
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
SSH="/usr/bin/ssh"
SCP="/usr/bin/scp"
GZIP="/bin/gzip"
SENDMAIL="/usr/sbin/sendmail"
CAT="/usr/bin/cat"
mailto="" #leave empty to disable e-mail notification
# Check for working dir
if [ ! -d $working_dir ]; then
mkdir $working_dir
fi
# Dump the databases in the working dir:
dbs=`$MYSQL -u $mysql_user -h $mysql_host -p$mysql_password \
-Bse 'show databases'`
for db in $dbs
do
$MYSQLDUMP -u $mysql_user -h $mysql_host -p$mysql_password $db | \
$GZIP -9 > $working_dir/$db.sql.gz
done
# Rotate old backups on remote host
for prev in 6 5 4 3 2 1
do
(( next = prev + 1 ))
$SSH -p $rport $ruser@$rhost mv $backup_dir/rotation_$prev/* \
$backup_dir/rotation_$next/ 2> /dev/null
done
# Copy all backups to backup_dir/rotation_1
$SCP -P $rport -r $working_dir/* $ruser@$rhost:$backup_dir/rotation_1/
# Mail notification:
if [ ! -z $mailto ]; then
temp_email=/tmp/mysql.email
echo "Subject: Mysql Backup" > $temp_email
echo "Mysql Backup completed on "`date` >> $temp_email
echo "Successfully backed up:" >> $temp_email
echo "" >> $temp_email
$SSH -p $rport $ruser@$rhost ls -l $backup_dir/rotation_1 >> \
$temp_email
$CAT $temp_email | $SENDMAIL $mailto
rm $temp_email
fi
# Clean working dir
rm -r $working_dir
# Exit
exit 0
