#
#Fichier de configuration (backup-mysql.conf)
#
###########################################################
# Fichier de configuration pour le script backup-mysql.sh #
###########################################################
#Serveur à backuper
SERVER='localhost'
#Utilisateur MySQL
DBUSER='login'
#Mot de passe MySQL
DBPASS='mdp'
#On specifie les bases a backuper ("all" pour tout backuper)
DB='all'
#Date format
DATE_FORMAT='+%d-%m-%Y'
#Repertoire de backup
BACKUP_DIR='/var/backup/mysql/'
#Repertoire du jour
TODAY_DIR=$(date $DATE_FORMAT)
#Nomre de backups a conserver (en jours)
HISTORY='7'
#Type de compression (bzip2/gzip)
COMP_TYPE='bzip2'
#Creer un repertoire pour chaque base de donnees (yes/no)
SEP_DB='yes'
#Separer les tables en differents fichiers (yes/no)
SEP_TABLE='yes'
#Options pour le dump
#Utiliser 'man mysqldump' ou aller sur la configuration pour plus de details
OPTS='--default-character-set=latin1 --skip-extended-insert'
#Fichier de logs
LOG_FILE='/tmp/backup.log'
#Adresse mail pour les logs d'execution du script
MAIL_ADDR='admin@domain.tld'
#
#Script de backup MySQL
#
#!/bin/bash
CONF_PATH='/etc'
CONF_FILE='backup-mysql.conf'
if [ ! -d "$CONF_PATH" ] || [ ! -f "$CONF_PATH/$CONF_FILE" ]; then
echo "File : $CONF_PATH/$CONF_FILE missing !"
exit 1;
else
. "$CONF_PATH/$CONF_FILE"
fi
send-mail () {
cat "$LOG_FILE" | mail -s "Rapport d'execution du script $(basename $0) sur $HOSTNAME" $MAIL_ADDR
rm "$LOG_FILE"
}
check_dirs () {
#On verifie si le repertoire de backup existe
if [ ! -d "$BACKUP_DIR" ]; then
echo "$BACKUP_DIR n'existe pas." >> "$LOG_FILE"
send-mail
exit 1;
fi
#On verifie si le repertoire du jour existe
if [ ! -d "$BACKUP_DIR/$TODAY_DIR" ]; then
mkdir "$BACKUP_DIR/$TODAY_DIR"
fi
#On supprime le backup le plus ancien
TO_DELETE=$(date --date "$HISTORY days ago" $DATE_FORMAT)
if [ -d "$BACKUP_DIR/$TO_DELETE" ]; then
rm -r "$BACKUP_DIR/$TO_DELETE"
fi
}
backup () {
if [ -z "$1" ]; then
echo 'Liste des bases non transmise' >> $LOG_FILE
send-mail
exit 1;
else
if [ "$SEP_DB" == 'yes' ]; then
for DB_TO_BACKUP in $1; do
#On cree le repertoire pour separer les bases
mkdir "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP"
if [ "$SEP_TABLE" == 'yes' ]; then
for TABLE_TO_BACKUP in $(mysql -h$SERVER -u$DBUSER -p$DBPASS --skip-column-names $DB_TO_BACKUP -e "show tables"); do
mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS "$DB_TO_BACKUP" "$TABLE_TO_BACKUP" | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP/$DB_TO_BACKUP-$TABLE_TO_BACKUP-$TODAY_DIR.$FILE_EXT"
done
else
mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS "$DB_TO_BACKUP" "$TABLE_TO_BACKUP" | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP/$DB_TO_BACKUP-$TODAY_DIR.$FILE_EXT"
fi
done
else
if [ "$SEP_TABLE" == 'yes' ]; then
for DB_TO_BACKUP in $1; do
for TABLE_TO_BACKUP in $(mysql -h$SERVER -u$DBUSER -p$DBPASS --skip-column-names $DB_TO_BACKUP -e "show tables"); do
mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS "$DB_TO_BACKUP" "$TABLE_TO_BACKUP" | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$DB_TO_BACKUP-$TABLE_TO_BACKUP-$TODAY_DIR.$FILE_EXT"
done
done
else
mysqldump -h"$SERVER" -u"$DBUSER" -p"$DBPASS" $OPTS --all-databases | "$COMP_TYPE" > "$BACKUP_DIR/$TODAY_DIR/$TODAY_DIR.$FILE_EXT"
fi
fi
fi
}
# On cree le fichier de log
touch "$LOG_FILE"
# on verifie que les bases a backuper sont bien renseignees
if [ -z "$DB" ]; then
echo 'variable $DB vide.' >> "$LOG_FILE"
send-mail
exit 1;
elif [ "$DB" == "all" ]; then
DB_LIST=$(mysql -h$SERVER -u$DBUSER -p$DBPASS --skip-column-names -e "show databases" | xargs)
else
DB_LIST="$DB"
fi
# On verifie que la compression est correcte
if [ "$COMP_TYPE" == 'bzip2' ]; then
FILE_EXT='sql.bz2'
elif [ "$COMP_TYPE" == 'gzip' ]; then
FILE_EXT='sql.gz'
else
echo '$COMP_TYPE non spécifié.' >> "$LOG_FILE"
send-mail
exit 1;
fi
check_dirs
backup "$DB_LIST"
#On verifie si le fichier de logs existe et qu'il est vide
if [ -f "$LOG_FILE" ] && [ "$(cat $LOG_FILE | wc -l)" -eq 0 ]; then
#Si il est vide on efface
rm "$LOG_FILE"
else
#Sinon on envoie le rapport
send-mail
fi
