Reflectiv's blog

#!/bin/bash
#
# Shell script (BASH) used in cron to made a backup of all the MySQL databases

# In order to run this script, you must have following tools installed:
# - /usr/bin/mysql
# - /usr/bin/mysqlcheck
# - /usr/bin/mysqldump
# - /bin/tar
# - /bin/gzip
# - /usr/bin/gpg
#
#
# Installation
# Customize the script according to your need. You need to setup :
# - The directory where to put the backups
# - A MySQL User and his Password
# - A GPG passphrase
# - The number of days the backups are stored (default 5)
#
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2005 ReFlectiv project.
# Feedback/comment/suggestions : http://www.reflectiv.net/
# -------------------------------------------------------------------------
#
# @See : http://www.ruas-blog.com/index.php/2006/03/06/33-script-de-sauvegarde-des-bases-mysql
# @See : http://www.shelldorado.com/articles/mailattachments.html

# Directory where the backups goes
BACKUP="/root/backups/mysql/"; # Don't forget the last "/" !

# User & Password for the MySQL user used for the backup
DB_USER="root"; # Could be another user with good rights
DB_PASS="password";

# Password for the symetric encryption in GPG
GPG_PASS="Gpg_password";

# Email where to send the Email
EMAIL="root";

# The number of days the backup are stored
KEEPING_DAYS=`date --date '5 days ago' "+%Y-%m-%d"`;

# Log file
LOG="/var/log/backup.log";

# Day when sending the mail backup (if no day, no mail will be sent)
DAY_MAIL="dim";

# Only change if your UNIX stores bin in diffrent location
MYSQL="/usr/bin/mysql";
MYSQLCHECK="/usr/bin/mysqlcheck";
MYSQLDUMP="/usr/bin/mysqldump";
TAR="/bin/tar";
GPG="/usr/bin/gpg";


#######################################################################
# Do not change anything below
#######################################################################

DATE="$(date +"%Y-%m-%d")";
DATE_NOW=`date +"%d/%m/%Y - %H:%I:%S"`;

cd $BACKUP;

TMP_LOG="tmp_backup.log";
>$TMP_LOG;

echo "["$DATE_NOW"] - MySQL Complete DUMP started" >> $TMP_LOG;

# WE create te temporary directory for the dump
if [ ! -d $DATE ]; then
       mkdir $DATE"/";
       chmod 500 $DATE;
fi

# We lists the bases
DATABASES="$(mysql -u $DB_USER -p$DB_PASS -Bse 'show databases;')";

# An error occured while retrieving the list of all the databases
if [ ! $? -eq 0 ]; then
       rm -rf $DATE;
       echo "["$DATE_NOW"] - An error occured while retrieving the list of all the databases" >> 
$TMP_LOG;
       echo $TMP_LOG >> $LOG;
       rm $TMP_LOG;
       exit 1;
fi

# For each Databases
for BASE in $DATABASES
do
       # We analyse each bases
       mysqlcheck -u $DB_USER -p$DB_PASS -c -a $BASE > /dev/null

       echo "["$DATE_NOW"] - Dump of the database '"$BASE"'" >> $TMP_LOG;

       # And we saving them in a file
       mysqldump -u $DB_USER -p$DB_PASS --add-drop-database  --add-drop-table --complete-insert
 --routines --triggers --allow-keywords --max_allowed_packet=50M --force $BASE -R > 
$DATE"/"$BASE".sql";

       # An error occured while dumping the database
       if [ ! $? -eq 0 ]; then
               rm -rf $DATE;
               echo "["$DATE_NOW"] - An error occured while dumping the database '"$BASE"'" >> 
$TMP_LOG;
               echo $TMP_LOG >> $LOG;
               rm $TMP_LOG;
               exit 1;
       fi
done

# Name of the Archive
ARCHIVE="mysql_backup_"$DATE".tar.gz";
OLD_ARCHIVE="mysql_backup_"$KEEPING_DAYS".tar.gz";

# We made the archive
tar -czf $ARCHIVE $DATE"/";

# An error occured while creating the archive
if [ ! $? -eq 0 ]; then
       rm -rf $DATE;
       echo "["$DATE_NOW"] - An error occured while creating the archive '"$ARCHIVE"'" >> 
$TMP_LOG;
       echo $TMP_LOG >> $LOG;
       rm $TMP_LOG;
       exit 1;
fi

# Deleting the temporary directory
rm -rf $DATE;

# Creating a GPG version of the archive
gpg --yes -c --passphrase $GPG_PASS --s2k-cipher-algo RIJNDAEL256 $ARCHIVE;

# An error occured while creating the encrypted archive
if [ ! $? -eq 0 ]; then
        rm -f $ARCHIVE;
        echo "["$DATE_NOW"] - An error occured while creating the encrypted archive '"$ARCHIVE"'" >>
 $TMP_LOG;
        echo $TMP_LOG >> $LOG;
        rm $TMP_LOG;
        exit 1;
fi

# Deleting the original archive
rm -f $ARCHIVE;

ARCHIVE=$ARCHIVE".gpg";

# Securying current archive
chmod 400 $ARCHIVE;

echo "["$DATE_NOW"] - Archive '"$ARCHIVE"' successfully created" >> $TMP_LOG;

# Deleting old archives
if [ -f $OLD_ARCHIVE ]; then
       rm $OLD_ARCHIVE;
       echo "["$DATE_NOW"] - Archive '"$OLD_ARCHIVE"' deleted" >> $TMP_LOG;
fi

#if [ `date +"%a"` == $DAY_MAIL ]; then
#       mail -s "MySQL Backup "$DATE $EMAIL -a "Content-Type: application/octet-stream" 
-a "Content-Disposition: inline; filename="$ARCHIVE > $TMP_LOG;
#fi

mail -s "MySQL Daily Backup ("$DATE")" $EMAIL > $LOG;
rm $TMP_LOG;

exit 0;