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
# - /usr/bin/gpg
# - ncftp
#
# And we will use theses :
# - tar
# - rm
# - chown
# - chmod
#
#
# Installation
# Customize the script according to your need. You need to setup :
# - The MySQL credentials
# - The FTP credentials
# - The directory where to put the backups in the distant ftp
# - A GPG passphrase
#
# - The Email to send reports when error occurs
# - The file where to log the messages                         (default /var/log/backup.log)
# - The number of days the backups are stored (default 5)
#
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 3.0 or above
# Copyright (C) 2005 ReFlectiv project.
# Feedback/comment/suggestions : http://www.reflectiv.net/
# -------------------------------------------------------------------------

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

# FTP Credentials
FTP_SERVER="ftp.serveur.tld"
FTP_USER="user"
FTP_PASS="password"

# Backup directory on the FTP :
FTP_DIR="/backups/database"

# The password for the archive
GPG_PASS="my_password";

# Email to send when error occurs
USER_EMAIL="email"

# File name where to log the messages
LOG_FILE="/var/log/backup.log";

# Number of days the archives are keeped
KEEPING_DAYS=5;


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

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

# We mades some vars
DATE="$(date +"%Y-%m-%d")";
CURRENT_DATE=`date +"%d/%m/%Y - %H:%I:%S"`;
ARCHIVE="mysql_backup_$(date +"%Y_%m_%d").tbz";
GPG_ARCHIVE=$ARCHIVE".gpg";
OLD_ARCHIVE="mysql_backup_"`date --date $KEEPING_DAYS' days ago' "+%Y_%m_%d"`".tbz.gpg";

# Preparing the field :
mkdir -p /tmp/mysql_backup/
[ $? -eq 0 ] || exit 1;

cd /tmp/mysql_backup/
[ $? -eq 0 ] || exit 1;

# Adding new clear entry to the log file
echo "" >> $LOG_FILE."tmp";
echo "----------------------------------------------------------------------------------------------------" >> $LOG_FILE."tmp";
echo "" >> $LOG_FILE."tmp";

# Function called to log info into the file
function log {
    echo "["$CURRENT_DATE"] - "$1 >> $LOG_FILE".tmp";

    if [ ! $2 == "" ]; then
                mail -s "[$(hostname)] - Remote backup error" $USER_EMAIL > $LOG_FILE;
                cd /
                rm -rf "/tmp/mysql_backup";
                rm $LOG_FILE".tmp";
        exit 1;
    fi
}

log "MySQL Complete DUMP started";

# Preliminary tests
[ -x $MYSQL ] || log "mysql not found" 1;
[ -x $MYSQLCHECK ] || log "mysqlcheck not found" 1;
[ -x $MYSQLDUMP ] || log "mysqldump not found" 1;
[ -x $GPG ] || log "gpg not found" 1;
[ -x $NCFTP ] || log "ncftp not found" 1;

# We create te temporary directory for the dump
if [ ! -d $DATE ]; then
    mkdir -p $DATE"/";
    # If an error occured ...
        [ $? -eq 0 ] || log "An error occured with the command mkdir" 1;

    chmod 500 $DATE;
    # If an error occured ...
        [ $? -eq 0 ] || log "An error occured with the command chmod" 1;
fi

# We lists the bases
DATABASES="$(mysql -u $DB_USER -p$DB_PASS -Bse 'show databases;')";
# If an error occured ...
[ $? -eq 0 ] || log "An error occured while retrieving the list of all the databases" 1;

# For each Databases
for BASE in $DATABASES
do
        # We analyse each bases
        $MYSQLCHECK -u $DB_USER -p$DB_PASS -c -a $BASE > /dev/null
        # If an error occured ...
        [ $? -eq 0 ] || log "An error occured while checking the database "$BASE 1;

        log "Dump of the database '"$BASE"'";

        # 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
        [ $? -eq 0 ] || log "An error occured while dumping the database '"$BASE"'" 1;
done

# We made the archive
tar -cjf $ARCHIVE $DATE"/";
# If an error occured ...
[ $? -eq 0 ] || log "An error occured with the command tar" 1;

# We encrypt the archive
$GPG --yes -c --passphrase $GPG_PASS --s2k-cipher-algo RIJNDAEL256 $ARCHIVE;
# If an error occured ...
[ $? -eq 0 ] || log "An error occured with the command gpg" 1;

# We delete the archive
rm -f $ARCHIVE;
# If an error occured ...
[ $? -eq 0 ] || log "An error occured with the command rm" 1;

# We modify the rights for the file
chmod 400 $GPG_ARCHIVE;
# If an error occured ...
[ $? -eq 0 ] || log "An error occured with the command chmod" 1;

# We modify the owner and group of the file
chown $UID $GPG_ARCHIVE;
# If an error occured ...
[ $? -eq 0 ] || log "An error occured with the command chown" 1;

log "Starting update to FTP server "$FTP_SERVER;

$NCFTP -u$FTP_USER -p$FTP_PASS $FTP_SERVER > $LOG_FILE;
rm $LOG_FILE".tmp";

exit 0;