Script Shell de backup de base de données via FTP
Après avoir modifié mon script de backup de dossier pour y intégrer le transfert via FTP, j'ai aussi entrepris de modifier le script de backup de base de donnée pour faire de même.
Là aussi, pas de grandes difficultés, le code ayant été déjà en partie réalisé, j'ai juste implémenté la partie transfert via FTP. J'en ai aussi profité pour optimiser le code afin de proposer quelque chose de plus fonctionnel. Vous m'en direz des nouvelles :)
Comme les autres scripts (backups de dossier/base de données), mettez ceci en Cron pour être exécuté quotidiennement, et modifiez les paramètres pour vos besoins, et tout devrait fonctionner (dites le sinon ;)).
Voici le code :
#!/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;Comme pour le script de backup de dossier via FTP, je n'ai pas trouvé comment faire taire ncftp. Donc si vous trouvez comment faire, je suis preneur !!
Toutes remarques seront les bienvenues :)
Posted
by Cyril Nicodème
