#!/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
vendredi 29 juillet 2011
sync.scp
#! /bin/bash
#**********************************************************************
#* Auteur : Martin VINCENT (MVincent) [email protected]
#* Fichier : sync.scp
#* Répertoire : /ftp:[email protected]:/sacc/prod/cron/
#* Créé le : 20 juin 2007 15:35:00 (-0500)
#* Modifié le : 29 juillet 2011 12:55:01 (-0400) par : mvincent
#* Sauvegarde # : 36
#* Ver. CVS GMT : $Id: sync.scp,v 1.9 2009/11/10 21:18:20 admin Exp $
#**********************************************************************
# simplify synchronisation of data to servers, by default,
# without any params or arguments, it behaves like /usr/local/bin/sync_servers
################################################
## VARIABLES (SOME ARE DEFAULTS THAT COULD
## BE OVERWRITTEN BY PARAMETERS )
################################################
EXEC="/usr/bin/rsync "
EXECSSH="/usr/bin/ssh "
EXECSCP="/usr/bin/scp "
backupServersList="/www_sa/sacc/prod/php/conf/BACKUPserversList.txt";
execcfgServersList="/www_sa/sacc/prod/php/conf/EXECCFGserversList.txt";
dictioServersList="/www_sa/sacc/prod/php/conf/DICTIOserversList.txt";
SERVERS="";
# these can be overwritten by parameters on the CLI
EXCLUSIONS=" " # ex : EXCLUSIONS="--exclude=weblogs "
DEST="/local/htdocs "
SOURCE="/local/htdocs/ "
PARAMS=" -e /usr/bin/ssh -z -a "
VERBOSE=""
TMP_SERVERS=" "
BATCHMODE="0"
SERVERGROUPNAME=""
TMPDIR="/dev/shm/" #dir used for temp files in batch mode
# pour la fonction cecho plus bas
black='\E[30;38m'
red='\E[31;38m'
green='\E[32;38m'
yellow='\E[33;38m'
blue='\E[34;38m'
magenta='\E[35;38m'
cyan='\E[36;38m'
white='\E[37;38m'
bold="\033[1m"
NONE="\033[0;38m"
################################################
## MAIN FUNCTION
################################################
main()
{
# get default list
SERVERLIST="";
GetServersList $bakupServersList
SERVERLIST="$SERVERLIST $GSLSERVERS";
SERVERS=$SERVERLIST
# below, we override teh default list if the case be
# add -v if not quiet mode
PARAMS=$PARAMS$VERBOSE
#####
if [ "$TMP_EXCLUSIONS" != "" ]
then
EXCLUSIONS=$TMP_EXCLUSIONS
fi
#####
if [ "$TMP_SOURCE" != "" ]
then
SOURCE=$TMP_SOURCE
fi
#####
if [ "$TMP_DEST" != "" ]
then
DEST=$TMP_DEST
fi
#####
if [ "$TMP_PARAMS" != "" ]
then
PARAMS=$PARAMS$TMP_PARAMS
fi
#####
if [ "$TMP_SERVERS" != " " ]
then
SERVERS=$TMP_SERVERS
fi
# rsync en mode batch : devrait etre plus rapide surtout sur multitude de petits fichiers,
# les tests ont cependant jusqu'a maintenant demontrer le contraire :/
# Donc retester avant d'utiliser en prod
if [ "$BATCHMODE" == 1 ]
then
# transformation de la liste en tableau
SERVERS=( $SERVERS )
# faire write batch
BATCHFILENAME="$TMPDIR$SERVERGROUPNAME."`date +"%d%b%Y-%a-%Hh%Mm%Ns"`".tmp"
MACHINE=${SERVERS[0]};
WRITEPARAMS="--write-batch="$BATCHFILENAME" "
READPARAMS=" -a --read-batch=- "
cecho "INFO : synchronising " $NONE "-n";
cecho " $SOURCE" $cyan "-n";
cecho " on " $NONE "-n" ;
cecho "$MACHINE" $red "-n"
cecho ":$DEST" $cyan ""
if [ "$VERBOSE" != "" ]
then
cecho "INFO : using " $white "-n"
cecho "BATCH MODE " $yellow "-n"
cecho "command : $EXEC $WRITEPARAMS$PARAMS$EXCLUSIONS " $white "-n"
cecho "$SOURCE $MACHINE:$DEST" $white ""
fi
# DO THE ACTUAL RSYNC
$EXEC $WRITEPARAMS$PARAMS$EXCLUSIONS $SOURCE $MACHINE:$DEST
# on elimine le premier serveur, vu que la commande --write-batch l'a d�j� synchronis�
unset SERVERS[0]
for MACHINE in ${SERVERS[@]}
do
cecho "INFO : synchronising " $NONE "-n";
cecho " $SOURCE" $cyan "-n";
cecho " on " $NONE "-n" ;
cecho "$MACHINE" $red "-n"
cecho ":$DEST" $cyan ""
if [ "$VERBOSE" != "" ]
then
cecho "INFO : using " $white "-n"
cecho "BATCH MODE " $yellow "-n"
cecho "command : $EXECSSH $MACHINE $EXEC " $white "-n"
cecho "$READPARAMS$PARAMS$EXCLUSIONS $DEST < $BATCHFILENAME" $white ""
fi
# DO THE ACTUAL RSYNC
$EXECSSH $MACHINE $EXEC $READPARAMS$PARAMS$EXCLUSIONS $DEST < $BATCHFILENAME
echo
done
rm $BATCHFILENAME
rm $BATCHFILENAME.sh
else
# rsync classique
for MACHINE in $SERVERS ;
do
cecho "INFO : synchronising " $NONE "-n";
cecho " $SOURCE" $cyan "-n";
cecho " on " $NONE "-n" ;
cecho "$MACHINE" $red "-n"
cecho ":$DEST" $cyan ""
if [ "$VERBOSE" != "" ]
then
cecho "INFO : using command : $EXEC $PARAMS$EXCLUSIONS $SOURCE" $white "-n"
cecho "$MACHINE:$DEST" $white ""
fi
# DO THE ACTUAL RSYNC
$EXEC $PARAMS$EXCLUSIONS $SOURCE $MACHINE:$DEST
echo
done
fi
}
################################################
## FUNCTIONS
################################################
# set up a 'usage' routine
# ------------------------
usage()
{
[ "$1" ] && ( echo $* ; echo "" )
cat <<!
Usage : $0 [ -e "exclusion"] [ -d destination_dir ] [ -s source_dir ]
[ -p rsync_parameters ] [ -h for help] [ -v for verbose mode] [ -b for batch mode (faster ?) ]
[ -g "SAbackupServers" | "EXECCFGservers" | "DICTIOservers" ] [server_list]
NOTES : DEFAULT behavior (withour any parameter) is similar to
/usr/local/bin/sync_servers
ie : "$EXEC $PARAMS$EXCLUSIONS $SOURCE [SERVER]:$DEST"
: -e options must be enclosed in "", especially if they contain regexp { ie : configs_* }
: -e, -p and -v must be invoked more that once to specify more than one
exclusion, or parameter or increase verbosity
: -g : select group of Feed servers or SAcc servers
: -b : batch mode, use rsync\'s --write/read-batch for (faster ?) transfers
!
exit 4
}
################################################
# This function will tell us if the line has a comment or
# blank line and need to be skipped - $1 is the line in the file
# Returns 1 indicates skip the line else dont skip
################################################
CanLineBeSkipped()
{
echo $line | grep "^#" > /dev/null
if [ $? == 0 ]
then
return 1
fi
if [ "$line" == "" ]
then
return 1
fi
return 0
}
################################################
# GETSERVERSLIST FUNCTION
################################################
GetServersList()
{
serversList=$1;
GSLSERVERS="";
if [ -r $serversList ]
then
while read line; do
CanLineBeSkipped $line
if [ "$?" == "1" ] || [ "$?" == "2" ]
then
continue
fi
GSLSERVERS=$GSLSERVERS" ${line}";
done < $serversList
GSLSERVERS=$GSLSERVERS" ${line}";
else
MESSAGE="$serversList is not readable.";
echo $MESSAGE
fi
return 0;
}
################################################
# CECHO FUNCTION : outputs echo with color
################################################
cecho() # Color-echo.
# Argument $1 = message
# Argument $2 = color
{
local default_msg=""
# Doesn't really need to be a local variable.
local noCarriageReturn=""
message=${1:-$default_msg} # Defaults to default message.
color=${2:-$white} # Defaults to black, if not specified.
carriageReturn=${3:-$noCarriageReturn} # Defaults to no $noCarriageReturn
echo -n -e "$bold$color"
echo $carriageReturn "$message"
echo -n -e $NONE
return
}
# collect options and arguments
while getopts ":e:d:s:p:g:hvb" optname
do
case "$optname" in
"e")
echo "INFO : Option $optname is specified with value ${OPTARG} "
TMP_EXCLUSIONS=$TMP_EXCLUSIONS"--exclude=${OPTARG} "
;;
"d")
echo "INFO : Option $optname is specified with value ${OPTARG} "
TMP_DEST=$TMP_DEST"${OPTARG} "
;;
"s")
echo "INFO : Option $optname is specified with value ${OPTARG} "
TMP_SOURCE="${OPTARG} "
;;
"p")
echo "INFO : Option $optname is specified with value ${OPTARG} "
TMP_PARAMS=$TMP_PARAMS"${OPTARG} "
;;
"v")
echo "INFO : Option $optname is specified : verbose output is ON "
VERBOSE=$VERBOSE"-v "
;;
"b")
echo "INFO : Option $optname is specified : rsync BATCH MODE is ON "
BATCHMODE="1"
;;
"g")
echo "INFO : Option $optname is specified with value ${OPTARG} "
SERVERGROUPNAME=$OPTARG
if [ "$OPTARG" = "SAbackupServers" ]
then
GetServersList $backupServersList
SABACKUPSERVERS=$GSLSERVERS;
TMP_SERVERS=$SABACKUPSERVERS
fi
if [ "$OPTARG" = "EXECCFGservers" ]
then
GetServersList $execcfgServersList
EXECCFGSERVERS=$GSLSERVERS;
TMP_SERVERS=$EXECCFGSERVERS
fi
if [ "$OPTARG" = "DICTIOservers" ]
then
GetServersList $dictioServersList
DICTIOSERVERS=$GSLSERVERS;
TMP_SERVERS=$DICTIOSERVERS
fi
;;
"h")
usage
;;
"?")
echo "ERROR : Unknown option $OPTARG, try < $0 -h > for help"
;;
":")
echo "ERROR : No argument value for option $OPTARG, try < $0 -h > for help"
exit 3
;;
*)
# Should not occur
echo "ERROR : Unknown error while processing options, try < $0 -h > for help"
;;
esac
done
# OPTIND is now pointing at first argument
for p in "${@:$OPTIND}"
do
TMP_SERVERS=$TMP_SERVERS"$p "
done
# finally call main
echo "INFO : starting on " `date +"%Y%m%d-%a-%Hh%Mm%Ss"` ;
time main
echo "INFO : done on " `date +"%Y%m%d-%a-%Hh%Mm%Ss"` ;
# http://www.ibm.com/developerworks/library/l-bash-parameters.html?ca=drs-
# prints options
# ------------------------
#showopts () {
# while getopts ":e:" optname
# do
# case "$optname" in
# "e")
# echo "Option $optname is specified with value for option $OPTARG "
# TMP_EXCLUSIONS=$TMP_EXCLUSIONS" --exclude=$OPTARG"
# ;;
# "?")
# echo "Unknown option $OPTARG"
# ;;
# ":")
# echo "No argument value for option $OPTARG"
# ;;
# *)
# # Should not occur
# echo "Unknown error while processing options"
# ;;
# esac
# done
#
# return $OPTIND
#}
# prints arguments
# ------------------------
#showargs () {
# for p in "[email protected]"
# do
# echo "[$p]"
# done
#}
# optinfo contient l'output des 'echo' de la f() showopts
# optinfo=$(showopts "[email protected]")
# $? contient la val de OPTIND retourne par la f() showopts
# argstart=$?
# arginfo contient l'output des 'echo' de la f() showargs
#arginfo=$(showargs "${@:$argstart}")
#echo "Arguments are:"
#echo "$arginfo"
#echo "Options are:"
#echo "$optinfo"
vendredi 22 juillet 2011
synchro_bd.v2.scp
Nouvelle version qui fait qu'un mysqldump par table et sauvegarde le résultat dans un fichier GZ. Ce fichier est ensuite 'pippé' sur tout les serveurs.
#!/bin/bash
#********************************************************************************************************
#* Auteur : Martin VINCENT (MVincent) [email protected]
#* Fichier : synchro_bd.scp
#* Répertoire : /www/cron/
#* Créé le : 20 juillet 2011 14:27:12 (-0400)
#* Modifié le : 22 juillet 2011 15:28:27 (-0500) par : mvincent
#* Sauvegarde # : 47
#* Ver. CVS GMT : $Id: synchro_bd.scp,v 1.1 2011/07/22 15:28:27 admin Exp $
#********************************************************************************************************
# synchronise les tables d'une bd particulière vers un groupe de serveur
# NOTE, the mysql user we use in synchro_bd.scp is writer;
# and it needs DROP and LOCK mysql grants in addition to regular USAGE grants
# 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
# global script execution timer
STARTGLOBAL=$(date +%s.%N)
# server list location
SERVERLISTFILE='/www/scripts/ServersList.txt'
# valeur par defaut des param
DB=''
TABLE='*'
USER='admin'
SERVERS=''
MODE=0 # if mode == 0, then we use dumpfiles (ress. cheap), if MODE == 1 we use straight pipe (faster)
PROGNAME=$(basename $0)
DBPATH='/data/mysql/'
TMPFILESDIR='/data/archive/cron_temp_files/' # where we write the mysqldump files when NOT using 'super parallel mode'
#########################################################################################################
# usage FUNCTION
#########################################################################################################
usage()
{
echo " usage : ${PROGNAME} [-t table] [-u usager] [-h serveur] [-m]"
echo ""
echo " example: ${PROGNAME} -d main -t ad_group_has_profile -u admin -h web01b"
echo ""
echo " where :"
echo " -d : database name that contains tables to be synchronised"
echo " -t : specific table name (defaults to ALL tables in database)"
echo " -u : user name to be used to connect to remote host (defaults to 'admin')"
echo " -h : remote host to connect to (defaults to ALL web servers)"
echo " -m : use super parallel mode : more cpu, less time (default : less cpu, more time)"
echo
exit;
}
#########################################################################################################
## MAIN FUNCTION
## input params are as such :
## main( $DB $TABLE $USER $SERVERS $MODE )
#########################################################################################################
main()
{
if [ "$MODE" == "0" ]
then
main_with_tmp_files $DB $TABLE $USER $SERVERS
else
main_super_parallel $DB $TABLE $USER $SERVERS
fi
}
#########################################################################################################
## MAIN FUNCTION
## dumps mysqldump output files files to disk, and then loops on those in order to pipe
## them to each server. Slower but less expensive in ressources (cpu load). It uses disk space located
## in $TMPFILESDIR so you need to have enough disk space to store all dump files.
## input params are as such :
## main( $DB $TABLE $USER $SERVERS )
#########################################################################################################
main_with_tmp_files()
{
# loop on TABLES ###################
# we dump the table's contents, and gzip the output in a $TABLE.gz file
# we also build a string containing "$TABLE1 $TABLE2 ..." which we will then use to pipe
# the contents of the gz files in the ssh pipe to the remote server(s)
# this will permit us to synch the same tables to different servers
RANDO=$RANDOM;
for i in `ls $DBPATH/$DB/$TABLE.frm` ;
do
export FICHIER=`basename $i .frm` ;
export FICHIERTMP=$FICHIER"_a_effacer";
export FICHIEROLD=$FICHIER"_bak";
echo "INFO : preparing $DBPATH/$DB/$TABLE [$FICHIER] for synchronisation..." ;
# on execute mysqldump, que l'on pipe dans perl pour remplacer le nom de la table
# $FICHIER par FICHIERTMP puis on pipe cet output dans gzip puis ssh. Via ssh, on
# se retrouve a executer le reste de la commande directement sur le serveur distant,
# soit : on gunzip l'output de mysqldump, puis on le pipe a mysql pour
# insertion dans la bd. Ensuite, il ne reste que a renommer les tables et effacer
# les backups.
# NOTE, the mysql user we use in synchro_bd.scp is writer;
# and it needs DROP and LOCK mysql grants in addition to regular USAGE grants
mysqldump -uwriter -hwrite-sql.server.com -p******************* --add-drop-table --extended-insert --single-transaction $DB $FICHIER |
perl -pi \
-e 's/DROP TABLE IF EXISTS `'$FICHIER'`/DROP TABLE IF EXISTS `'$FICHIERTMP'`/g;' \
-e 's/CREATE TABLE `'$FICHIER'`/CREATE TABLE `'$FICHIERTMP'`/g;' \
-e 's/LOCK TABLES `'$FICHIER'`/LOCK TABLES `'$FICHIERTMP'`/g;' \
-e 's/INSERT INTO `'$FICHIER'`/INSERT INTO `'$FICHIERTMP'`/g;' \
-e 's/ALTER TABLE `'$FICHIER'`/ALTER TABLE `'$FICHIERTMP'`/g;' |
gzip > ${TMPFILESDIR}/${RANDO}_$FICHIER.gz &
# build table list for next step
TABLELIST="${TABLELIST} $FICHIER"
done # END loop on mysqldump TABLES ###################
# wait for all mysqldump sub-processes to end
echo
echo "INFO : waiting for mysqldump processes to end..." ;
wait
echo "INFO : done with mysqldump, preparing to synchronise..." ;
# loop on SERVERS ###################
for MACHINE in ${SERVERS[@]}
do
echo
echo "INFO : synchronisation to $USER @ $MACHINE..." ;
for FICHIER in ${TABLELIST[@]}
do
export FICHIERTMP=$FICHIER"_a_effacer";
export FICHIEROLD=$FICHIER"_bak";
# NOTE, the mysql user we use in synchro_bd.scp is writer;
# and it needs DROP and LOCK mysql grants in addition to regular USAGE grants
# /www/cron/synchro_bd.scp [email protected]$MACHINE $DB $TABLE \
# > /www/weblogs/tmp_admin_$MACHINE 2>&1 &
echo -n "INFO : preparing to send table $FICHIER to $USER @ $MACHINE..." ;
cat ${TMPFILESDIR}/${RANDO}_${FICHIER}".gz" | ssh ${USER}@${MACHINE} " gunzip |
mysql -uwriter -p*************** $DB ; \
mysql -uwriter -p**************** -e'CREATE table IF NOT EXISTS $FICHIER like $FICHIERTMP ;' $DB ; \
mysql -uwriter -p**************** -e'RENAME table $FICHIER to $FICHIEROLD, $FICHIERTMP to $FICHIER;' $DB ; \
mysql -uwriter -p**************** -e'DROP table $FICHIEROLD;' $DB; " &
echo "done."
# sleep a bit, otherwise ssh complains of 'connection errors'
usleep 200000
done # END loop on TABLELIST ###################
done # END loop on SERVERS ###################
# wait for all FICHIER in ${TABLELIST[@]}sub-processes to end
echo
echo "INFO : waiting for synchronisation processes to end..." ;
wait
# cleanup old table dump files
for FICHIER in ${TABLELIST[@]}
do
rm ${TMPFILESDIR}/${RANDO}_${FICHIER}".gz"
done
}
#########################################################################################################
## alternative MAIN FUNCTION
## dumps mysqldump's output for each table directly into the ssh pipe. Faster but also more expensive
## in load for the server.
## input params are as such :
## main( $DB $TABLE $USER $SERVERS )
#########################################################################################################
main_super_parallel(){
for MACHINE in ${SERVERS[@]}
do
( ## do not remove: required to indicate start of sub-shell for parallelisation
for i in `ls $DBPATH/$DB/$TABLE.frm` ;
do
export FICHIER=`basename $i .frm` ;
export FICHIERTMP=$FICHIER"_a_effacer";
export FICHIEROLD=$FICHIER"_bak";
echo "INFO : on prepare $FICHIER pour synchronisation vers $MACHINE..." ;
# on execute mysqldump, que l'on pipe dans perl pour remplacer le nom de la table $FICHIER par FICHIERTMP
# puis on pipe cet output dans gzip puis ssh. Via ssh, on se retrouve a executer le reste de la commande directement sur
# le serveur distant, soit : on gunzip l'output de mysqldump, puis on le pipe a mysql pour insertion dans la bd.
# Ensuite, il ne reste que a renommer les tables et effacer les backups.
# NOTE, the mysql user we use in synchro_bd.scp is writer;
# and it needs DROP and LOCK mysql grants in addition to regular USAGE grants
mysqldump -uwriter -hwrite-sql.server.com -p***************** --add-drop-table --extended-insert --single-transaction $DB $FICHIER |
perl -pi \
-e 's/DROP TABLE IF EXISTS `'$FICHIER'`/DROP TABLE IF EXISTS `'$FICHIERTMP'`/g;' \
-e 's/CREATE TABLE `'$FICHIER'`/CREATE TABLE `'$FICHIERTMP'`/g;' \
-e 's/LOCK TABLES `'$FICHIER'`/LOCK TABLES `'$FICHIERTMP'`/g;' \
-e 's/INSERT INTO `'$FICHIER'`/INSERT INTO `'$FICHIERTMP'`/g;' \
-e 's/ALTER TABLE `'$FICHIER'`/ALTER TABLE `'$FICHIERTMP'`/g;' |
gzip |
ssh ${USER}@${MACHINE} "cat |
gunzip |
mysql -uwriter -p**************** $DB ; \
mysql -uwriter -p**************** -e'CREATE table IF NOT EXISTS $FICHIER like $FICHIERTMP ;' $DB ; \
mysql -uwriter -p**************** -e'RENAME table $FICHIER to $FICHIEROLD, $FICHIERTMP to $FICHIER;' $DB ; \
mysql -uwriter -p**************** -e'DROP table $FICHIEROLD;' $DB; " &
# sleep a bit, otherwise ssh complains of 'connection errors'
usleep 200000
done;
echo "INFO : waiting for mysqldump processes to end..." ;
wait
echo "INFO : done with mysqldump, preparing to synchronise..." ;
)& ## do not remove: required to indicate end of sub-shell for parallelisation
done;
# wait for all sub-processes to end
echo "INFO : waiting for synchronisation processes to end..." ;
wait
}
#########################################################################################################
# This function will tell us if the line has a comment or
# blank line and need to be skipped - $1 is the line in the file
# Returns 1 indicates skip the line else dont skip
#########################################################################################################
CanLineBeSkipped()
{
echo $line | grep "^#" > /dev/null
if [ $? == 0 ]
then
return 1
fi
if [ "$line" == "" ]
then
return 1
fi
return 0
}
#########################################################################################################
# GETSERVERSLIST FUNCTION
#########################################################################################################
GetServersList()
{
serversList=$1;
GSLSERVERS="";
if [ -r $serversList ]
then
while read line; do
CanLineBeSkipped $line
if [ "$?" == "1" ] || [ "$?" == "2" ]
then
continue
fi
GSLSERVERS=$GSLSERVERS" ${line}";
done < $serversList
GSLSERVERS=$GSLSERVERS" ${line}";
else
MESSAGE="$serversList is not readable.";
echo $MESSAGE
fi
return 0;
}
#########################################################################################################
## lock file grabbing/validation
## expects a lockfile name as input parameter
#########################################################################################################
getlock()
{
LOCKFILE=${1}
if [ -e "${LOCKFILE}" ];
then
echo "INFO : lockfile [${LOCKFILE}] already exists"
if [ ! -r "${LOCKFILE}" ];
then
echo "ERROR : lockfile exists, but is not readable; exiting"
exit 1
fi
PID=`cat "${LOCKFILE}"`
kill -0 "$PID" 2>/dev/null
if [ $? == 0 ];
then
echo "INFO : existing instance of this task [pid:$PID] is currently running; exiting"
echo "INFO : end of processing : " `date`
echo "INFO : --------------------------------------------------------------------------------";
exit 1
fi
echo "INFO : process [pid:$PID] that created lockfile is no longer running - deleting lockfile"
rm -f "${LOCKFILE}"
if [ $? != 0 ];
then
echo"ERROR : failed to delete lockfile; exiting"
exit 1
fi
fi
echo $$ >"${LOCKFILE}"
if [ $? != 0 ];
then
echo "ERRRO : failed to create lockfile; exiting"
exit 1
fi
echo "INFO : successfuly aquired new lockfile [${LOCKFILE}]"
}
#########################################################################################################
# start main processing
#########################################################################################################
PARAMS=$*
echo "INFO : starting [${PROGNAME} ${PARAMS}] on " `date` ;
# read server list
GetServersList $SERVERLISTFILE
SERVERS="$GSLSERVERS"
# read/assign CLI params with getopts. USER,DB and SERVERS have default values, which will be overwritten
# by getopts if the user specifies new values on the CLI
while getopts 'd:t:u:h:m' OPTION
do
case $OPTION in
d) DB=$OPTARG # we get the DB CLI params value
;;
t) TABLE=$OPTARG # we get the TABLE CLI params value
;;
u) USER=$OPTARG # we get the USER CLI params value
;;
h) SERVERS=$OPTARG # we get the SERVER CLI params value
;;
m) MODE=1 # we get the MODE CLI params value
;;
?) usage #printf "Usage: %s: [-c client] [-d YYYY-mm-dd]\n" $(basename $0) >&2
;;
esac
done
shift $(($OPTIND - 1))
# at a minimum, we want the user to specify the DB to be synchronised
if [ "$DB" == "" ]
then
usage
fi
# call GetLock( lockfile name ) to make sure we are the only instance running this script with the given CLI params
LOCKFILENAME=${TMPFILESDIR}/${PROGNAME}"-"${DB}"-"${TABLE}"-"${USER}"-"${SERVERS}
getlock ${LOCKFILENAME}".lck"
echo
# call main()
main $DB $TABLE $USER $SERVERS
# cleanup old lockfiles
rm -f ${LOCKFILENAME}".lck"
TIMESPENT="$(echo "$(date +%s.%N) - $STARTGLOBAL" | bc )"
echo "INFO : total running time : $TIMESPENT seconds"
echo "INFO : done [${PROGNAME} ${PARAMS}] on " `date` ;
echo "=========================================================================="
jeudi 9 juin 2011
parallélisme
source
#adjust these as required
args_per_proc=1 #1 is fine for long running tasks
procs_in_parallel=4
xargs -n$args_per_proc -P$procs_in_parallel povray < list
# donne le nombre de processeur dispo (à utiliser comme valeur pour le param '-P' ci-dessus)
grep -c ^processor /proc/cpuinfo
# ou encore
nproc
mardi 17 mai 2011
usage de getopts
# getopts
while getopts 'c:d:' OPTION
do
case $OPTION in
c) client=$OPTARG
;;
d) date=$OPTARG
;;
?) printf "Usage: %s: [-c client] [-d YYYY-mm-dd]\n" $(basename $0) >&2
exit 2
;;
esac
done
shift $(($OPTIND - 1))
mardi 10 mai 2011
empecher 2 scripts de rouler en même temps
# lets make sure only one instance is running
EXEC=$(/bin/basename $0)
RES=$(/usr/bin/pgrep ${EXEC} | wc -l)
if [ ${RES} != 2 ]
then
echo "WARNING : ${EXEC} already running, exiting.";
exit;
fi
Il y a aussi :
#!/bin/sh
ME=`basename "$0"`;
LCK="/tmp/${ME}.LCK";
exec 8>$LCK;
flock --nonblock --exclusive 8
if [ $? = 0 ];
then
echo "Do job!!!"
sleep 20
echo "Jobs done"
fi
et aussi :
#!/bin/sh
IAM=(`pgrep -d " " -f ${0//*\//}`)
if [ ${#IAM[@]} -gt 1 ]
then
echo "Exiting because another instance of `basename $0` is already running with PID [$IAM]";
exit;
else
echo "Running now with PID [$$]"
sleep 10
fi
et aussi :
# check for existing lockfile
if [ -e "$LOCKFILE" ]; then
# lockfile exists
if [ ! -r "$LOCKFILE" ]; then
echo error: lockfile is not readable
exit 1
fi
PID=`cat "$LOCKFILE"`
kill -0 "$PID" 2>/dev/null
if [ $? == 0 ]; then
echo error: existing instance of this task is already running
exit 1
fi
# process that created lockfile is no longer running - delete lockfile
echo "process that created lockfile is no longer running - delete lockfile"
rm -f "$LOCKFILE"
if [ $? != 0 ]; then
echo "error: failed to delete lockfile"
exit 1
fi
fi
# create lockfile
echo $$ >"$LOCKFILE"
if [ $? != 0 ]; then
# error: failed to create lockfile
exit 1
fi
# -----
echo 'Simulated processing...sleeping 15secs'
sleep 15
# -----
# delete lockfile
rm -f "$LOCKFILE"
if [ $? != 0 ]; then
# error: failed to delete lockfile
exit 1
fi
autre méthode encore :
critical_section(){
echo "we are alone"
caller 0
sleep 15
}
lockfile=`basename $0`.`echo "[email protected]" | sed "s/[\/\ \'\"]/_/g"`.lck
echo ${lockfile}
if ( set -o noclobber; echo "$$" > "$lockfile") 2> /dev/null;
then
# trap 'rm -f "$lockfile"; exit $?' INT TERM EXIT
critical_section
rm -f "$lockfile"
# trap - INT TERM EXIT
else
echo "Failed to acquire lockfile: $lockfile."
echo "Held by $(cat $lockfile)"
echo "Attempting to unlock"
# The file exists so read the PID
# to see if it is still running
MYPID=`head -n 1 $LCK_FILE`
echo "[$MYPID]"
if [ -n "`ps -p ${MYPID} | grep ${MYPID}`" ];
then
echo `basename $0` is still running [$MYPID].
exit
else
echo "rmfing lckfile..."
rm -f "$lockfile"
fi
fi
mardi 5 avril 2011
recherche doublon/duplicata et génère un script pour les effacer
/[email protected]:/media/photos/ find "[email protected]" -type f -print0 | xargs -0 -n1 md5sum | sort --key=1,32 |uniq -w 32 -d | sed -r 's/^[0-9a-f]*( )*//;s/([^a-zA-Z0-9./_-])/\\\1/g;s/(.+)/rm \1/' | grep BACKUP
rm ./BACKUP.06j10m2010a_18h33m08s.dsc00510.jpg
rm ./BACKUP.06j10m2010a_18h08m25s.dsc00511.jpg
rm ./BACKUP.06j10m2010a_18h33m10s.dsc00511.jpg
/[email protected]:/media/photos/ md5sum ../01/*511* | sort --key=1,32
096b3497a6f709159991d9bebd3635aa ../01/BACKUP.06j10m2010a_18h08m25s.dsc00511.jpg
096b3497a6f709159991d9bebd3635aa ../01/BACKUP.10j07m2010a_13h51m10s.dsc00511.jpg
1649d8c8623903c7cfddee416d99f257 ../01/BACKUP.06j10m2010a_15h52m42s.dsc00511.jpg
mardi 1 mars 2011
grep et tri de logs apache
$ grep "Feb 10 13:" apache_access.2011-02-10 | grep adx-nfs01 | awk '{ print $13 }' | sort | uniq -c | sort -nr | more
lundi 21 février 2011
conditions dans crontab
[adx-db01]$ cat test.sh
#!/bin/bash
#[ $(TZ=GMT-48 date +%d) -eq "18" ] && echo "ok" || eco 'pas ok'
SVR=$(TZ=GMT-48 date +%d)
ADXDB01=[ $(TZ=GMT-48 date +%d) -eq "18" ]
[ $ADXDB01 ] && echo "ok" || echo "pas ok"
* * 8-14 * * if [ `date +\%a` = "Sat" ]; then /apps/scripts/test.ksh; fi >> testlog
These will not work:-
* * 8-14 * 0 if [ `date +\%a` = "Sat" ]; then /apps/scripts/test.ksh; fi >> testlog
or
* * 8-14 * 0 /apps/scripts/test.ksh >> testlog
---
@Don
@SathiyaMoorthy
To execute a script on some days of a week and only between some days of a month you can do it this way:
# Nur am Montag (1) oder am Donnerstag (4)
10 19 2-27 * * if [ "$(date +\%u)" = "1" ] || [ "$(date +\%u)" = "4" ] ; then ./yourscript.sh ; fi
This here will not work !!!
10 19 2-27 * 1,4
--
35 * * * * /usr/bin/test -x /usr/local/cpanel/bin/tail-check && /usr/local/cpanel/bin/tail-check
mercredi 26 janvier 2011
effacer doublons de fichiers en utilisant md5
source
http://www.commandlinefu.com/commands/view/2370/delete-all-those-duplicate-files-but-one-based-on-md5-hash-comparision-in-the-current-directory-tree
2009-06-07 03:14:06
User: masterofdisaster
Functions: find perl rm xargs
removed `./duplicate0.mp3'
removed `./1/duplicate1.mp3'
removed `./2/duplicate2.mp3'
DELETE all those duplicate files but one based on md5 hash comparision in the current directory tree
This one-liner will the *delete* without any further confirmation all 100% duplicates but one based on their md5 hash in the current directory tree (i.e including files in its subdirectories).
Good for cleaning up collections of mp3 files or pictures of your dog|cat|kids|wife being present in gazillion incarnations on hd.
md5sum can be substituted with sha1sum without problems.
The actual filename is not taken into account-just the hash is used.
Whatever sort thinks is the first filename is kept.
It is assumed that the filename does not contain 0x00.
As per the good suggestion in the first comment, this one does a hard link instead:
http://www.commandlinefu.com/commands/view/2370/delete-all-those-duplicate-files-but-one-based-on-md5-hash-comparision-in-the-current-directory-tree
2009-06-07 03:14:06
User: masterofdisaster
Functions: find perl rm xargs
find . -type f -print0|xargs -0 md5sum|sort|perl -ne 'chomp;$ph=$h;($h,$f)=split(/\s+/,$_,2);print "$f"."\x00" if ($h eq $ph)'|xargs -0 rm -v --
removed `./duplicate0.mp3'
removed `./1/duplicate1.mp3'
removed `./2/duplicate2.mp3'
DELETE all those duplicate files but one based on md5 hash comparision in the current directory tree
This one-liner will the *delete* without any further confirmation all 100% duplicates but one based on their md5 hash in the current directory tree (i.e including files in its subdirectories).
Good for cleaning up collections of mp3 files or pictures of your dog|cat|kids|wife being present in gazillion incarnations on hd.
md5sum can be substituted with sha1sum without problems.
The actual filename is not taken into account-just the hash is used.
Whatever sort thinks is the first filename is kept.
It is assumed that the filename does not contain 0x00.
As per the good suggestion in the first comment, this one does a hard link instead:
find . -xdev -type f -print0 | xargs -0 md5sum | sort | perl -ne 'chomp; $ph=$h; ($h,$f)=split(/\s+/,$_,2); if ($h ne $ph) { $k = $f; } else { unlink($f); link($k, $f); }'- Alternatively, if you want to keep the appearance but still save space, you can use the "hardlink" command. This searches recursively for duplicates and replaces them all with hard links.
- fdupes its just simple! If you just want one of the files type: fdupes -fN
Inscription à :
Articles (Atom)
S’abonner à
Articles
Commentaires