#!/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 admin@$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 "=========================================================================="
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.
Inscription à :
Publier les commentaires (Atom)
S’abonner à
Articles
Commentaires
Aucun commentaire:
Enregistrer un commentaire