Abysse Tech

HiTech 4 All

systemeadministration

Mise en Place d’un système de réplication SQL Et Monitoring

by

Bien rentrons directement dans le coeur du sujet. La réplication permet de répliquer en quasi temps réel deux bases de données. Les utilités sont ici multiples à savoir de la redondance (master – master) pour du load balancing par exemple ou encore du backup.

Je ne vais pas m’étendre plus sur les utilités sachant que si vous lisez cet article c’est que vous savez pourquoi vous le voulez.

Bref, ici, il s’agit de mettre en place un sytème master – slave afin d’avoir un backup à jour en permanence. Si quelque chose devait arriver au serveur principal de l’entreprise à 23h58, ce serait dramatique car toute l’activité d’une journée serait à saisir manuellement.

Une solution peu viable serait un cronjob d’un MySQL dump toute les 2-3 heures, mais le serveur saturerait trop souvent, et les tables sont inutilisables dans ce laps de temps.

Toutefois, il ne s’agit pas d’un backup a part entière non plus. Un mysqldump quotidien est recommandé dans la mesure du possible. (on peut le lancer sur le slave du coup pour éviter une saturation des ressources du serveur principal. Ici, vu que j’utilise WHM / cPanel sur tous mes serveurs, j’utilise un petit addon qui est parfait pour un backup incrémental quotidien (on en parlera plus tard)

1) Bien, la première chose à vérifier sont les versions des 2 serveurs SQL ici (5.0 sur le master et 5.1 sur le slave). A noter que la slave peut être d’une version supérieure mais pas l’inverse. Attention à quelques exceptions toutefois, notamment Master en 4.0 et Slave en 5.1!

 

Pour connaître votre version :

root@server1 [~]# mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 420599
Server version: 5.0.96-community-log MySQL Community Edition (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Reading history-file /root/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Allez on rentre dans le vif du sujet

2) MySQL dans la réplication SQL va énormément s’appuyer sur le rDNS (PTR) et du coup cela ajoute un temps de latence pour la résolution DNS (en millisecondes mais bon). On va ainsi ajouter dans le fichier /etc/host les adresses / hostname respectifs du master et slave

Donc sur les deux vous faites :

root@server1 [~]# vi /etc/host

Et vous tapez dans le fichier sur le master :

192.168.0.2 slave.domaine.com slave

 

Sur le slave

192.168.0.1 master.domaine.com master

 

Bref, vous comprendrez que cela varie en fonction de vos hostnames et IP.

 

3) Vérifier que skip-networking ainsi que bind-address = 127.0.0.1 n’est pas dans le /etc/my.cnf ou qu’il sont commentés comme ci dessous

root@server1 [~]# cat /etc/my.cnf

Qui doit vous retourer quelque chose comme ceci :

log-slow-queries
safe-show-database
#bind-address = 127.0.0.1
open_files_limit=4754
#skip-networking

4) On ouvre l’édition de my.cf afin d’ajouter le logging des activités MySQL qui vont être utilisé par l’array des serveurs pour se syncroniser :

root@server1 [~]# vi /etc/my.cnf

On ajoute les ligne suivantes sur le master. En remplaçant « mybdd » par le nom de votre BDD et le server-id par le nombre que vous voulez. (il doit être différent entre le master et le slave).

log-bin = /var/lib/mysql/mysql-bin.log
binlog-do-db=mabdd
server-id=1

5) On redémarre le serveur SQL et on vérifie que le logging est bien parti :

root@server1 [~]# /etc/init.d/mysql restart

Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
root@server1 [~]#cd /var/lib/mysql

root@server1 [~]#ls

Ici on doit avoir ces deux fichiers :

mysql-bin.00001

mysql-bin.index

 

6) On va s’occuper de la gestion des droits. Ici, on va utiliser un user root avec un mot de passe commun aux deux serveurs MySQL, pour le script de monitoring. Vu le gain apporté il s’agit d’une concession acceptable. Pour modifier le password root vi /root/.my.cnf

root@server1 [~]# mysql -u root -p

Enter password:

Sur le master :

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'hostnameslave' IDENTIFIED BY 'passwordroot';
--Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
mysql> USE mabdd;
mysql> FLUSH TABLES WITH READ LOCK;

 

Sur le slave :

root@server1 [~]# mysql -u root -p<

Enter password:
mysql> CREATE DATABASE mabdd;
mysql> quit;

 

7) On va maintenant s’occuper du my.cnf du Slave et lui dire qu’il est un slave d’un master défini.

<pre>server-id=2
master-host=192.168.0.1
master-user=root
master-password=password
master-connect-retry=60
replicate-do-db=mabdd</pre>

On redémarre le serveur SQL :

root@server1 [~]# /etc/init.d/mysql restart

Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!

8) Bien, on a déjà bien avancé! Cette étape va dépendre de votre BDD, des capacités votre serveur, de son niveau de production, de sa criticité etc.. On va exporter la base de données du master vers le slave. Ici, je lance un mysql dump vu que les BDD ne sont en général pas très lourde. Après si vous gérez quelque chose de plus compliqué, vous pouvez utiliser ce que vous voulez.

Enfin, juste avant le dump, on va devoir relever la position du Master sur le log et le fichier log utilisé afin de le reporter dans la configuration du slave. Faites cette opération AVANT de lancer le dump et essayer de faire le plus court possible entre le dump et la fin du tuto.

root@server1 [~]# mysql -u root -p

Enter password:

mysql> SHOW MASTER STATUS;

Vous devez obtenir quelque chose comme ça :

 

+------------------+----------+----------------+------------------+
| File             | Position | Binlog_Do_DB   | Binlog_Ignore_DB |
+------------------+----------+----------------+------------------+
| mysql-bin.000004 |   982432 | mabdd |                  |
+------------------+----------+----------------+------------------+

exit;

Enfin, voici le MySQL dump :

root@server1 [~]# mysqldump -u root -pmotdepasse mabdd > mabdd.sql

!! Il ny’ a pas d’espace entre le p et le mot de passe. !!

 

On la transfère sur le slave. Un client SSH du genre Filezilla / CyberDuck fait très bien l’affaire ou rsync etc…

Une fois transférée, on l’importe dans notre base de données créée sur le slave :

root@server1 [~]# mysql -u root -pmotdepasse mabbd < /home/mabdd.sql

!! Il ny’ a pas d’espace entre le p et le mot de passe. !!

!! Ma base a été transférée dans /home donc /home/mabdd/sql !!

 

9) Maintenant, la base est importée et prête à être répliquée. On reste donc sur le slave et on effectue la commande suivante dans laquelle on remplace les valeurs du SHOW MASTER STATUS,

root@server1 [~]# mysql -u root -p

Enter password:

mysql> SLAVE STOP;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='root', MASTER_PASSWORD='monmotdepasse', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=982432;

mysql> START SLAVE;

Les partie variables sont bien sur master_host, master_user, master_password, master_log_file et master_log_pos

 

10) FINIS! La replication doit être déjà en cours. On peut vérifier en tapant la commande SQL suivante, et vous devriez obtenir quelque chose comme ça :

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 982682
Relay_Log_File: backend-relay-bin.000002
Relay_Log_Pos: 762478
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: hoststa2_whmcs
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 982682
Relay_Log_Space: 762635
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

PARTIE DEBUGGING

Si vous n’êtes pas trop familier avec toutes ses commandes et les actions entreprises vous pouvez tomber sur des petites erreurs. (problème de connection etc…).

Ainsi, verifier bien votre my.cnf file avec les bonnes configurations comme présentées dans l’article. Verifier aussi, que vous avez bien saisi la commande des privilèges et enfin vous pouvez tenter de vous connecter à partir du slave sur le master avec cette commande :

root@server1 [~]# mysql -u root -pmonmotdepasse --host=192.168.0.1 --port=3306

Si vous avez un problème de connection avec la commande ci dessus, vérifier bien votre parefeu et que votre serveur accepte bien les connections distantes (bind-address=127.0.0.1 commenté ou supprimé dans le my.cnf)

 

PARTIE MONITORING

 

Bien que si vous avez un projet de taille modéré, vous pouvez vous dire que globalement la réplication est assez stable, et qu’il y a rarement des soucis. Maintenant, suivant votre objectif de replication, il se peut que cela prenne du temps avant que vous en ayez vraiment besoin. Ainsi, vérifier que cela se passe bien et en être informé peut être utile.

Je vous fourni ainsi le script qui va vous permettre de recevoir des alertes mails quand il y a un soucis ou au contraire pour vous dire que tout est OK.

Le script va vérifier que le status est OK, qu’il n’ya pas de latence d’évènement trop importante ou des erreurs.

On peut rajouter des scripts complémentaires pour vérifier le checksum des bases de données par exemple pour être vraiment exhaustif.

Il existe des tonnes de solutions pour remplir le rôle du script ci dessous mais il aura l’avantage d’être utilisable en 5 minutes ;).

On place le script sur le SLAVE :

1) On installe un petit truc pour faire tourner le script

root@server1 [~]# yum install bc

2) On crée un dossier script et le fichier  :

root@server1 [~]# mkdir /scripts

root@server1 [~]# cd /scripts

root@server1 [~]# touch replicmonit.sh

root@server1 [~]# chmod 755 replicmonit.sh

root@server1 [~]# vi replicmonit.sh

3 )Ensuite on copie colle le script ci dessous en modifiant les valeurs (username, password, master et email)

#!/bin/bash

USERNAME=root
PASSWORD=monmotdepaase
EXPECTED_MASTER_HOST=192.168.0.1
EXPECTED_MASTER_PORT=3306

SLAVE_HOST=localhost
SLAVE_PORT=3306

MYSQL="mysql -u $USERNAME -p$PASSWORD "
MASTER="$MYSQL -h $EXPECTED_MASTER_HOST -P $EXPECTED_MASTER_PORT"
SLAVE="$MYSQL -h $SLAVE_HOST -P $SLAVE_PORT"

$MASTER -e 'SHOW MASTER STATUS\G' &gt; mstatus
$SLAVE -e 'SHOW SLAVE STATUS\G' &gt; sstatus

function extract_value {
FILENAME=$1
VAR=$2
grep -w $VAR $FILENAME | awk '{print $2}'
}

Master_Binlog=$(extract_value mstatus File )
Master_Position=$(extract_value mstatus Position )

Master_Host=$(extract_value sstatus Master_Host)
Master_Port=$(extract_value sstatus Master_Port)
Master_Log_File=$(extract_value sstatus Master_Log_File)
Read_Master_Log_Pos=$(extract_value sstatus Read_Master_Log_Pos)
Slave_IO_Running=$(extract_value sstatus Slave_IO_Running)
Slave_SQL_Running=$(extract_value sstatus Slave_SQL_Running)

ERROR_COUNT=0
if [ "$Master_Host" != "$EXPECTED_MASTER_HOST" ]
then
ERRORS[$ERROR_COUNT]="the slave is not replicating from the host that it is supposed to"
ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Master_Port" != "$EXPECTED_MASTER_PORT" ]
then
ERRORS[$ERROR_COUNT]="the slave is not replicating from the host that it is supposed to"
ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Master_Binlog" != "$Master_Log_File" ]
then
ERRORS[$ERROR_COUNT]="master binlog ($Master_Binlog) and Master_Log_File ($Master_Log_File) differ"
ERROR_COUNT=$(($ERROR_COUNT+1))
fi

POS_DIFFERENCE=$(echo ${Master_Position}-$Read_Master_Log_Pos|bc)

if [ $POS_DIFFERENCE -gt 1000 ]
then
ERRORS[$ERROR_COUNT]="The slave is lagging behind of $POS_DIFFERENCE"
ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Slave_IO_Running" == "No" ]
then
ERRORS[$ERROR_COUNT]="Replication is stopped"
ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Slave_SQL_Running" == "No" ]
then
ERRORS[$ERROR_COUNT]="Replication (SQL) is stopped"
ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ $ERROR_COUNT -gt 0 ]
then
EMAIL=monemail@mondomaine.com
SUBJECT="ERRORS in replication"
BODY=''
CNT=0
while [ "$CNT" != "$ERROR_COUNT" ]
do
BODY="$BODY ${ERRORS[$CNT]}"
CNT=$(($CNT+1))
done
echo $SUBJECT
echo $BODY
echo $BODY | mail -s "$SUBJECT" $EMAIL
else
EMAIL=monemail@mondomaine.com
SUBJECT="REPLICATION OK"
BODY="REPLICATION OK"
echo $SUBJECT
echo $BODY
echo $BODY | mail -s "$SUBJECT" $EMAIL
fi
<code>

4) On sort de l'éditeur vi

echap

<code>:wq!

5) On peut tester le script tout de suite pour voir ce qu’il nous donne :

 

root@server1 [~]# ./replicmonit.sh
REPLICATION OK
REPLICATION OK

 

6) Bien sur faire tourner ce script manuellement est complètement inutile alors on va faire un cronjob qui se lance toute les 12 heures (2 fois par jour est suffisant, pas la peine d’être submergé de mail toutes les minutes.) On peut aussi modifier le script pour éviter de nous dire que tout est OK, et du coup le lancer plus souvent. Enfin personnellement j’aime bien quand on me parle et je me méfie des scripts autarciques ;)..

root@server1 [~]# crontab -e

On ajoute la ligne suivante :

0 0.12 * * * /scripts/replicmonit.sh

7) On sort de l’éditeur pico (ctrl + X, y, y)

Et voila! Vous avez donc une base de données repliquées en temps

On fait tourner le script toutes les 12 heures

 

Leave A Comment