linux:adminsys:check_replication_mysql
Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentesRévision précédenteProchaine révision | Révision précédente | ||
linux:adminsys:check_replication_mysql [13/04/2009 13:51] – modif user daniel | linux:adminsys:check_replication_mysql [11/02/2010 14:28] (Version actuelle) – apostrophes virées dans commentaires daniel | ||
---|---|---|---|
Ligne 2: | Ligne 2: | ||
Ce script vérifie les positions master et slave de deux noeuds mysql en réplication maitre-maitre. | Ce script vérifie les positions master et slave de deux noeuds mysql en réplication maitre-maitre. | ||
<code bash> | <code bash> | ||
- | #!/bin/bash | ||
- | |||
# Ce script loggue les positions sur le master local, le master distant et le slave | # Ce script loggue les positions sur le master local, le master distant et le slave | ||
# Avec en colonnes | # Avec en colonnes | ||
# 1 timestamp | # 1 timestamp | ||
- | # 2 master_log_on_remote_master | + | # 2 remote_master_master_log_file |
- | # 3 master_log-pos_on_remote_master | + | # 3 remote_master_master_log_pos |
- | # 4 master_log_on_local_slave | + | # 4 local_slave_master_log_file |
- | # 5 master_log-pos_on_local_slave | + | # 5 local_slave_master_log_pos |
- | # 6 master_log_on_local_master | + | # 6 local_master_master_log_file |
- | # 7 master_log-pos_on_local_master | + | # 7 local_master_master_log_pos |
- | # 8 master_log_on_remote_slave | + | # 8 remote_slave_master_log_file |
- | # 9 master_log-pos_on_remote_slave | + | # 9 remote_slave_master_log_pos |
# 10 date en plus clair | # 10 date en plus clair | ||
+ | # 11 local_delay | ||
+ | # 12 remote_delay | ||
+ | # le user mysql qui réplique doit avoir les droits REPLICATION CLIENT, REPLICATION SLAVE et PROCESS | ||
+ | |||
LOG=/ | LOG=/ | ||
ERRLOG=/ | ERRLOG=/ | ||
+ | LOCK=/ | ||
TMP=/ | TMP=/ | ||
- | DEST=' | + | DEBUG=0 |
# On regarde si on est sql1 ou sql2, à adapter suivant vos hostnames | # On regarde si on est sql1 ou sql2, à adapter suivant vos hostnames | ||
Ligne 28: | Ligne 31: | ||
local_host=< | local_host=< | ||
remote_host=< | remote_host=< | ||
+ | |||
TS=$(date ' | TS=$(date ' | ||
DATE=$(date -d " | DATE=$(date -d " | ||
+ | |||
user=$(sed -ne ' | user=$(sed -ne ' | ||
- | pass=$(sed -ne '5p'</ | + | pass=$(sed -ne '6p'</ |
- | remote_master=$(/ | + | init_vars() { |
- | local_master=$(/ | + | # marche pas si le $user a seulement les droits de réplication slave, il faut client |
- | local_slave=$(/ | + | # |
- | remote_slave=$(/ | + | |
- | echo "$TS $remote_master | + | # on rapproche le plus possible les mesures master/ |
+ | remote_master_status=$(/ | ||
+ | local_slave_status=$(/ | ||
+ | local_master_status=$(/ | ||
+ | remote_slave_status=$(/ | ||
- | # check index du fichier de log | + | |
- | remote_master_log_index=$(expr "$remote_master" | + | |
- | local_slave_log_index=$(expr "$local_slave" : ' | + | |
- | local_master_log_index=$(expr "$local_master" | + | |
- | remote_slave_log_index=$(expr "$remote_slave" : ' | + | local_slave_master_log_file=$(echo -e " |
- | # check positions | + | |
- | remote_master_log_pos=$(expr "$remote_master" | + | |
- | local_slave_log_pos=$(expr "$local_slave" | + | |
- | local_master_log_pos=$(expr "$local_master" | + | |
- | remote_slave_log_pos=$(expr " | + | |
+ | local_master_master_log_file=$(echo -e " | ||
+ | local_master_master_log_file_index=$(expr " | ||
+ | local_master_master_log_pos=$(echo -e " | ||
+ | remote_slave_master_log_file=$(echo -e " | ||
+ | remote_slave_master_log_file_index=$(expr " | ||
+ | remote_slave_master_log_pos=$(echo -e " | ||
+ | remote_slave_delay=$(echo -e " | ||
+ | remote_slave_error=$(echo -e " | ||
+ | } | ||
+ | |||
+ | if [ $DEBUG -gt 0 ]; then | ||
+ | init_vars | ||
+ | echo " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo " | ||
+ | echo " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo -e " | ||
+ | echo " | ||
+ | echo " | ||
+ | echo "Fin debug, on sort" | ||
+ | exit | ||
+ | fi | ||
+ | |||
+ | init_vars | ||
+ | echo "$TS $remote_master_master_log_file $remote_master_master_log_pos $local_slave_master_log_file $local_slave_master_log_pos $local_master_master_log_file $local_master_master_log_pos $remote_slave_master_log_file $remote_slave_master_log_pos $DATE $local_delay $remote_delay" | ||
>$TMP | >$TMP | ||
- | if [ $remote_master_log_index | + | # Check souci qcq |
+ | if [ -z "$remote_master_master_log_file" | ||
+ | [ -z " | ||
+ | [ -z " | ||
+ | [ -z " | ||
+ | [ -z " | ||
+ | [ " | ||
+ | [ " | ||
+ | [ " | ||
+ | [ " | ||
then | then | ||
- | | + | |
- | elif [ $remote_master_log_pos -gt $local_slave_log_pos ] | + | |
- | then | + | |
- | | + | |
- | master $remote_host : $remote_master | + | |
- | | + | |
fi | fi | ||
- | if [ $local_master_log_index | + | # connexion distante |
- | then | + | if [ -z "$remote_master_master_log_file" |
- | echo "Le fichier de log est plus recent | + | echo " |
- | elif [ $local_master_log_pos | + | # connexion locale |
- | then | + | elif [ -z " |
- | echo "Le slave de $remote_host | + | echo " |
- | master $local_host | + | # fichier slave local trop vieux |
- | slave $remote_host | + | elif [ " |
+ | echo "Le fichier de log est différent | ||
+ | echo -e "Sur le slave de $local_host on a l' | ||
+ | echo "On restart le slave de $local_host" | ||
+ | / | ||
+ | # position slave local en retard | ||
+ | elif [ "$remote_master_master_log_pos" | ||
+ | echo "Le slave de $local_host | ||
+ | master $remote_host | ||
+ | slave $local_host | ||
+ | echo -e "\nSur le slave de $local_host on a l' | ||
+ | echo "\nOn restart le slave de $local_host" | ||
+ | / | ||
fi | fi | ||
- | if [ $(wc -l <$TMP) -gt 0 ] | + | # on recommence avec le slave distant |
- | then | + | if [ -z " |
- | # on ajoute le check des Slave_running | + | echo " |
- | echo "Sur $local_host on a $(/ | + | elif [ -z " |
+ | echo " | ||
+ | elif [ " | ||
+ | echo "Le fichier de log est différent sur le master $local_host ($local_master_master_log_file) que sur le slave de $remote_host ($remote_slave_master_log_file)" | ||
+ | echo -e "Sur le slave de $remote_host on a l' | ||
+ | echo "On restart le slave de $remote_host" | ||
+ | / | ||
+ | elif [ " | ||
+ | echo "Le slave de $remote_host est en retard de ${$remote_slave_delay}s (slave $remote_slave_master_log_pos vs $local_master_master_log_pos pour le master local) : | ||
+ | master $local_host : $local_master_master_log_file $local_master_master_log_pos | ||
+ | slave $remote_host : $remote_slave_master_log_file $remote_slave_master_log_pos" | ||
+ | echo -e "Sur le slave de $remote_host on a l' | ||
+ | echo "On restart le slave de $remote_host" | ||
+ | / | ||
+ | fi | ||
+ | |||
+ | # et mail en cas de pb | ||
+ | if [ $(wc -l <$TMP) -gt 0 ]; then | ||
+ | # on ajoute le check des Slave_running | ||
+ | echo -e "\nSur $local_host on a $(/ | ||
echo "Sur $remote_host on a $(/ | echo "Sur $remote_host on a $(/ | ||
- | echo "Fin du script de check ($DATE)" | + | echo -e "\nFin du script de check ($DATE)" |
# log | # log | ||
cat $TMP >> $ERRLOG | cat $TMP >> $ERRLOG | ||
# on envoie un mail | # on envoie un mail | ||
- | mail -a " | + | mail -a " |
+ | # et on pose un lock | ||
+ | cat $TMP >> $LOCK | ||
+ | else | ||
+ | [ -f $LOCK ] && \ | ||
+ | echo -e " | ||
+ | mail -a " | ||
+ | rm -f $LOCK | ||
fi | fi | ||
</ | </ |
linux/adminsys/check_replication_mysql.1239623486.txt.gz · Dernière modification : 13/04/2009 13:51 de daniel