#!/bin/bash
#/**********************************************************************
#* Auteur : Martin VINCENT (MVincent) [email protected]
#* Fichier : backup_db.scp
#* Répertoire : db01b:/script/cron/
#* Créé le : 11 septembre 2005 11:30:16 (-0500)
#* Modifié le : 3 août 2011 14:16:04 (-0500) par : mvincent
#* Sauvegarde # : 80
#***********************************************************************/
# simply backup db structure and data
# specify the PATH so that the executables (rm, echo etc.) used in the code
# below will run even if $PATH is not set (as in the crontab)
PATH=${PATH}:/sbin:/bin:/usr/sbin:/usr/bin
START=$(/bin/date +%s)
# date suffix for output files
DATE_RAPPORT=`/bin/date +"%a-%Hh%M"`;
# database files path
DBPATH="/data/mysql"
USER="sql_writer -h*************** -p******************* ";
DB=""
TABLES=""
MODE="STRUCTURE"
TABLEEXCLUSION=""
PROGNAME=$(basename $0)
# directories for output files
OUTPUT_PATH="/data/weblogs/structure_et_data_bd"
# email param and success/error messages
j=0;
EMAILSUBJECTARR[j]='ERROR ['`/bin/date`'] mysqldump of structure failed on '`hostname -a`;
EMAILRECIP[j]="[email protected]"; # no spaces between addresses for CC field
EMAILCC[j++]="[email protected]";
EMAILSUBJECTARR[j]='ERROR ['`/bin/date`'] mysqldump of data & structure failed on '`hostname -a`;
EMAILRECIP[j]="[email protected]"; # no spaces between addresses for CC field
EMAILCC[j++]="[email protected]";
#########################################################################################################
# usage FUNCTION
#########################################################################################################
function usage()
{
echo " usage : ${PROGNAME} <-d database> [-t table] [-m mode ] [-e table to exclude from DATA dump]"
echo ""
echo " example: ${PROGNAME} -d main -m DATASTRUCT -e referral_log -e audit_data"
echo ""
echo " where :"
echo " -d : database name that contains tables to be backed up"
echo " -t : specific table name (defaults to ALL tables in database)"
echo " Can be used multiple times."
echo " ex : -t tableA -t tableB etc."
echo " -m : mode used to backup : DATA or STRUCTURE (defaults to 'STRUCTURE')"
echo " Use DATASTRUCT to use both modes on a db/table"
echo " -e : table to exclude from the DATA dump only. Can be used multiple times."
echo " ex : -e tableA -e tableB etc."
echo ""
echo ""
echo " NOTE : output directory for dump files is : ${OUTPUT_PATH}"
echo ""
exit;
}
#########################################################################################################
# mysqldump the DATA from db/table
#########################################################################################################
function structureDump()
{
DB="$1"
TABLES="$2"
echo "INFO : ["`/bin/date`"] starting database STRUCTURE dump for db ${DB} [output directory : ${OUTPUT_PATH}]..."
## database backup : complete db structure with drop-tables
/usr/bin/mysqldump --skip-opt \
--no-data \
--add-drop-table \
--quote-names \
--allow-keywords \
--lock-tables \
--quick \
--create-options \
--user $USER $DB ${TABLES[@]} > $OUTPUT_FILE.$DATE_RAPPORT 2> $OUTPUT_FILE.$DATE_RAPPORT".err";
RETCODE=$?
#if error doing the mysqldump...:
if [ $RETCODE -ne 0 ];
then
IDX=0; # index in the array for this error message/email etc.
echo "ERROR : ["`/bin/date`"] mysqldump failed with return code [ $RETCODE ] : \n";
/bin/cat $OUTPUT_FILE.$DATE_RAPPORT".err"
# send data to $EMAILRECIP[${IDX}] and cc to $EMAILCC[${IDX}]
echo "INFO : ["`/bin/date`"] sending mail to ${EMAILRECIP[${IDX}]} ..."
/bin/mail -s "${EMAILSUBJECTARR[${IDX}]} [db : ${DB}]" \
-c ${EMAILCC[${IDX}]} ${EMAILRECIP[${IDX}]} < $OUTPUT_FILE.$DATE_RAPPORT".err"
echo "ERROR : ["`/bin/date`"] mysqldump stucture error...exiting.";
exit;
fi
END=$(/bin/date +%s)
DIFF=$(( $END - $START ))
echo "INFO : ["`/bin/date`"] done dumping db structure without problems in [$DIFF] seconds"
}
#########################################################################################################
# mysqldump the DATA from db/table
#########################################################################################################
function dataDump()
{
DB="$1"
TABLES="$2"
TABLEEXCLUSION="$3"
## database backup : tables listed in $TABLES + their data tuples
echo "INFO : ["`/bin/date`"] starting database DATA backup for db ${DB} [output directory : ${OUTPUT_PATH}]...";
table_number_of_elements=${#TABLES[@]}
table_last_element_index=$(($table_number_of_elements - 1))
for (( i = 0 ; i < $table_number_of_elements ; i++ )); do
table=${TABLES[$i]};
# the FLAG code belowis used to filter out exluded tables, as
# requested by the user
if [ "$TABLEEXCLUSION" != "" ]
then
for extable in $TABLEEXCLUSION ; do
if [ "$extable" == "$table" ]
then
FLAG="1";
break;
fi
done
fi
if [ "$FLAG" == "1" ]
then
FLAG="0" ;
continue;
fi
STARTINTER=$(/bin/date +%s)
/usr/bin/mysqldump --no-create-info \
--skip-opt \
--add-locks \
--delayed-insert \
--quick \
--disable-keys \
--quote-names \
--allow-keywords \
${EXTAB} --user $USER $DB $table >> $OUTPUT_FILE.$DATE_RAPPORT;
RETCODE=$?
#if error doing the mysqldump...:
if [ $RETCODE -ne 0 ]
then
IDX=1; # index in the array for this error message/email etc.
echo "ERROR : ["`/bin/date`"] mysqldump failed with return code [ $RETCODE ] : \n";
/bin/cat $OUTPUT_FILE.$DATE_RAPPORT".err"
# send data to $EMAILRECIP[${IDX}] and cc to $EMAILCC[${IDX}]
echo "INFO : ["`/bin/date`"] sending mail to ${EMAILRECIP[${IDX}]} ..."
/bin/mail -s "${EMAILSUBJECTARR[${IDX}]} [db : ${DB}]" \
-c ${EMAILCC[${IDX}]} ${EMAILRECIP[${IDX}]} < $OUTPUT_FILE.$DATE_RAPPORT".err"
echo "ERROR : ["`/bin/date`"] mysqldump data error on table $table...exiting.";
exit;
else
PAUSE="";
ENDINTER=$(/bin/date +%s)
DIFF=$(( $ENDINTER - $STARTINTER ))
if [ $i -lt $table_last_element_index ] && [ $DIFF -gt 0 ]
then
# cooldown period if necessary
PAUSE="just finished long query; pausing for $DIFF seconds..."
/bin/sleep $DIFF;
fi
echo "INFO : ["`/bin/date`"] done with table $table ... $PAUSE"
fi
done
}
#########################################################################################################
# compress mysqldump files
#########################################################################################################
function compress()
{
file=$1
# compressions/cleanup
echo "INFO : ["`/bin/date`"] compressing $OUTPUT_FILE.$DATE_RAPPORT.gz..."
/usr/bin/gzip -9 -f $file
}
#########################################################################################################
# erase file (*.errr etc.)
#########################################################################################################
function cleanup()
{
FILE=$1 # ex : $OUTPUT_FILE.$DATE_RAPPORT".err"
MESSAGE=$2 # ex : 'erasing temp lock file : '
echo "INFO : ["`/bin/date`"] $MESSAGE $FILE ..."
/bin/rm -f $FILE
}
#########################################################################################################
# erase file (*.errr etc.)
#########################################################################################################
function synchronise()
{
SOURCE_PATH=$1
DEST_PATH=$2
# sync to other master server
if [[ `hostname -a` = "db01" ]]
then
REMOTEHOST="db02b"
else
REMOTEHOST="db01b"
fi
echo "INFO : ["`/bin/date`"] rsyncing $SOURCE_PATH/ to $REMOTEHOST:$DEST_PATH ..."
/usr/bin/rsync --delete -e /usr/bin/ssh -av --progress $SOURCE_PATH"/" $REMOTEHOST:$DEST_PATH
}
#########################################################################################################
## lock file grabbing/validation
## expects a lockfile name as input parameter
#########################################################################################################
function getlock()
{
LOCKFILE=${1}
if [ -e "${LOCKFILE}" ];
then
echo "INFO : ["`/bin/date`"] lockfile [${LOCKFILE}] already exists"
if [ ! -r "${LOCKFILE}" ];
then
echo "ERROR : ["`/bin/date`"] lockfile exists, but is not readable; exiting"
exit 1
fi
PID=`cat "${LOCKFILE}"`
kill -0 "$PID" 2>/dev/null
if [ $? == 0 ];
then
echo "INFO : ["`/bin/date`"] existing instance of this task [pid:$PID] is currently running; exiting"
echo "INFO : ["`/bin/date`"] end of processing : " `date`
echo "INFO : ["`/bin/date`"] --------------------------------------------------------------------------------";
exit 1
fi
echo "INFO : ["`/bin/date`"] process [pid:$PID] that created lockfile is no longer running - deleting lockfile"
rm -f "${LOCKFILE}"
if [ $? != 0 ];
then
echo"ERROR : ["`/bin/date`"] failed to delete lockfile; exiting"
exit 1
fi
fi
echo $$ >"${LOCKFILE}"
if [ $? != 0 ];
then
echo "ERROR : ["`/bin/date`"] failed to create lockfile; exiting"
exit 1
fi
echo "INFO : ["`/bin/date`"] successfuly aquired new lockfile [${LOCKFILE}]"
}
#########################################################################################################
# main entry point
#########################################################################################################
echo "INFO : ["`/bin/date`"] starting mysqldump processing ..."
# mysqldump -u dave -ppassword -h localhost --ignore-table=my_db_name.my_table_name my_db_name
# read/assign CLI params with getopts. MODE, TABLES have default values, which will be overwritten
# by getopts if the user specifies new values on the CLI
while getopts 'd:t:m:e:h' OPTION
do
case $OPTION in
d) DB=$OPTARG # we get the DB CLI params value
;;
t) TABLES="${TABLES} $OPTARG "
;;
m) MODE=$OPTARG # we get the MODE CLI params value
;;
e) TABLEEXCLUSION="${TABLEEXCLUSION} $OPTARG " # we get the MODE CLI params value
;;
h) usage #printf "Usage: %s: [-c client] [-d YYYY-mm-dd]\n" $(basename $0) >&2
;;
esac
done
shift $(($OPTIND - 1))
# set output filen name with some of the CLI param values
OUTPUT_FILE=$OUTPUT_PATH"/"$DB"_"$MODE"_backup";
# at a minimum, we want the user to specify the DB to be synchronised
# and we want to fetch the default tables list if none are specified
if [ "$DB" == "" ]
then
usage
elif [ "$TABLES" == "" ]
then
declare -a TABLES=(`/bin/ls $DBPATH/$DB/*.frm | /usr/bin/xargs -i /bin/basename \{\} .frm `)
fi
# call GetLock( lockfile name ) to make sure we are the only instance running this script with the given CLI params
LOCKFILENAME="/tmp/"${PROGNAME}"-"${DB}"-"${MODE}"-"
getlock ${LOCKFILENAME}".lck"
if [ "$MODE" == "STRUCTURE" ]
then
structureDump "$DB" "$TABLES"
elif [ "$MODE" == "DATA" ]
then
dataDump "$DB" "$TABLES" "$TABLEEXCLUSION"
elif [ "$MODE" == "DATASTRUCT" ]
then
structureDump "$DB" "$TABLES"
dataDump "$DB" "$TABLES" "$TABLEEXCLUSION"
fi
# compress output dump file
compress "$OUTPUT_FILE.$DATE_RAPPORT"
# source and dest are the same on both servers
synchronise $OUTPUT_PATH $OUTPUT_PATH
# cleanup error files
if [ ! -s "$OUTPUT_FILE.$DATE_RAPPORT.err" ]
then
cleanup "$OUTPUT_FILE.$DATE_RAPPORT.err" "deleting unused/empty error logs :"
fi
# remove lock file
cleanup ${LOCKFILENAME}".lck" "releasing lock file :"
END=$(/bin/date +%s)
DIFF=$(( $END - $START ))
echo "INFO : ["`/bin/date`"] done dumping/compressing/rsyncing db data without problems in [$DIFF] seconds"
mercredi 3 août 2011
backup_db.scp
Inscription à :
Articles (Atom)
S’abonner à
Articles
Commentaires