Replication Master/Slave temps réel MySQL

La réplication MySQL consiste à avoir en temps réel deux bases de données MySQL identiques afin de pouvoir basculer sur un deuxième serveur en cas de défaillance du premier.

La réplication MySQL est basée sur le fait que le serveur va garder la trace de toutes les évolutions de vos bases (modifications, effacements, etc.) dans un fichier de log binaire et les esclaves vont lire les requêtes du maître dans ce fichier de log, pour pouvoir exécuter les mêmes requêtes sur leurs copies. Il est très important de comprendre que le fichier de log binaire est simplement un enregistrement des modifications depuis un point fixe dans le temps (le moment où vous activez le log binaire). Tous les esclaves que vous activez auront besoin de la copie des données qui existaient au moment du démarrage du log. Si vous démarrez vos esclaves sur sans qu’ils ne disposent des données identiques à celles du maître au moment du démarrage du log binaire, votre réplication va échouer.

Nous partons avec l’architecture suivante:
Deux serveurs A, et B sous Linux (réalisé sous Debian) ayant chacun une base de donnée MySQL.
IP Serveur A: 192.168.0.1
IP Serveur B: 192.168.0.2

Principe de fonctionnement en image:

MISE EN PLACE DE LA REPLICATION MySQL

On commence par le MASTER (192.168.0.1)

On se connecte à la base de donnée.

mysql -h 192.168.0.1 -u admin –ppassword –P 3306

On crée un utilisateur pour la réplication:

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';

On stop l’écriture sur la base:

mysql> FLUSH WITH READ LOCK;

On récupère le nom du fichier binaire, et son offset.
Il faut bien les noter car nous en auront besoin après pour configurer l’esclave.

mysql> SHOW MASTER STATUS;

Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter
également le numéro dans « Position”.
Exemple:

mysql > SHOW MASTER STATUS;
+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.003 | 73 | test,bar | foo,manual,mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.06 sec)

Si le File et le Position sont vides, PAS DE PANIQUE
Prenez comme nom de File -> ‘’ et pour Position -> 4.
Quittez le prompt mysql. -> quit
Eteignez la base:

shell> mysqladmin –u admin –ppassword –h 192.168.0.1 –P 3306 shutdown

Editez le fichier my.cnf qui se trouve normalement dans /var/lib/mysql/my.cnf
Ajoutez-y les lignes suivantes :

[mysqld]
log-bin
server-id=1

Relancez la base MySQL, en étant dans le répertoire MySQL (ne pas oublier le “&” qui permet de faire tourner le processus en arrière-plan):

safe_mysqld &

On configure maintenant le SLAVE (192.168.0.2)

Eteignez la base:

shell> mysqladmin –u vadmin –ppassword –h 192.168.0.2 –P 3306 shutdown

Editez le fichier my.cnf qui se trouve dans /var/lib/mysql/my.cnf
Ajoutez-y les lignes suivantes :

[mysqld]
server-id=2
master-host = 192.168.0.1
master-user = repl
master-password = repl
master-port = 3306

Relancez la base MySQL, en étant dans le répertoire MySQL (ne pas oublier le “&” qui permet de faire tourner le processus en arrière-plan):

safe_mysqld &

On se rend dans le prompt MySQL pour lancer le slave.

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306

On change les données comme ceci si vous aviez un nom de fichier et une position:

mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=;

Si vous n’aviez pas de nom de fichier et de position en faisant le SHOW MASTER STATUS sur le master,
entrez ceci:

mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=4;
On démarre l’esclave
mysql> START SLAVE;

Si tout c’est déroulé correctement, vous devez avoir ceci :
(Les lignes en gras sont les plus importantes)

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ServerA-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: ServerB-relay-bin.000002
Relay_Log_Pos: 238
Relay_Master_Log_File: ServerA-bin.000001
<strong>Slave_IO_Running: Yes</strong>
<strong>Slave_SQL_Running: Yes</strong>
Replicate_Do_DB:
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: 98
Relay_Log_Space: 238
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
1 row in set (0.00 sec)

Et dans SHOW PROCESSLIST:

mysql&gt; <strong>SHOW PROCESSLIST</strong>;
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
| 6 | system user | | NULL | Connect | 1944 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 1944 | Has read all relay log; waiting for the slave I/O thread to update it | NULL
| 8 | admin | serverB.lan:45148 | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

En cas d’erreur de réplication,
On vérifie l’état du SLAVE:

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql>SHOW SLAVE STATUS ;

Il arrive que certaines requêtes réussissent sur le maître mais échouent sur l’esclave. Cela ne devrait
pas arriver si vous avez pris la bonne sauvegarde du maître, et que vous n’avez jamais modifié les
données sur le serveur esclave, autrement que par le truchement de l’esclave de réplication.

Si vous apercevez une erreur aux lignes:

Last_Errno:
Last_Error:

Voici la procédure:
Sur le master :

shell> mysql –u admin –ppassword –h 192.168.0.1 –P 3306
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;

Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter
également le numéro dans “Position”.

Faire un DUMP de la base de donnée:

shell> mysqldump -u admin -ppassword -h 192.168.0.1 -P 3306 -r /home/dump.sql nomdelabase

Envoyer le dump vers le slave:

shell> scp –e /home/dump.sql root@192.168.0.2:/home

Sur le Slave, on bloque les tables:

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql>FLUSH TABLES WITH READ LOCK;
mysql>exit;

On intègre le dump du master dans la base “nomdevotrebase”:

shell> mysql –h 192.168.0.2 –u admin -ppassword –P 3306 nomdelabase <
/home/dump.sql

On redéfinit le bon fichier binaire du master et on redémarre le Slave:

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql>CHANGE MASTER TO
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=POSITION_FICHIER_LOG;
mysql>START SLAVE;

Normalement, la réplication est repartie !

En cas de crash du MASTER – Remise à niveau du master et de la réplication.

Nous allons partir du fait que le MASTER est tombé, vous l’avez remis à niveau, réinstallé vTiger.
Nous allons maintenant remettre la base MySQL et réactiver la réplication.
A ce moment, le SLAVE est LA base de référence. Donc nous allons commencer par stopper le slave,
créer un DUMP du slave, le répliquer sur le MASTER, et relancer le tout.

SUR LE SLAVE
On vérifie l’état du SLAVE:

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql>FLUSH TABLES WITH READ LOCK;

On crée un dump du slave:

shell> mysqldump -u admin -ppassword -h 192.168.0.2 -P 3306 -r /home/dump.sql nom de la base

On envoi le dump sur le master:

shell> scp –e /home/dump.sql root@192.168.0.1:/home

SUR LE MASTER

Mysql -h 192.168.0.1 -u admin –ppassword –P 3306

On crée un utilisateur pour la réplication:

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';

On stop l’écriture sur la base:

mysql> FLUSH WITH READ LOCK;

On ré-insert le dump du slave sur le master:

shell> mysql –h 192.168.0.1 –u admin -ppassword –P 3306 nomdelabase <
/home/dump.sql

On récupère le nom du fichier binaire, et son offset. Il faut bien les noter car nous en auront besoin
après pour configurer l’esclave.

shell> mysql –u admin –ppassword –h 192.168.0.1 –P 3306
mysql> SHOW MASTER STATUS;

Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter
également le numéro dans « Position”.

Exemple:

mysql > SHOW MASTER STATUS;
+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.003 | 73 | test,bar | foo,manual,mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.06 sec)

Quittez le prompt mysql. -> quit

Eteignez la base:

shell> mysqladmin –u admin –ppassword –h 192.168.0.1 –P 3306 shutdown

Editez le fichier my.cnf qui se trouve dans /var/lib/mysql/my.cnf

Ajoutez-y les lignes suivantes:

[mysqld]
log-bin
server-id=1

Relancez la base MySQL, en étant dans le répertoire MySQL (ne pas oublier le “&” qui permet de faire tourner le processus en arrière-plan):

safe_mysqld &

SUR LE SLAVE:

Nous allons maintenant re-synchroniser le Slave avec le master.

On redéfinit le bon fichier binaire du master et on redémarre le Slave:

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql>CHANGE MASTER TO
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=POSITION_FICHIER_LOG;
mysql>START SLAVE;

Normalement, la réplication est repartie !

Vous pouvez vérifier le statut du Slave, grâce aux commandes SLAVE STATUS, et SHOW PROCESSLIST.

Annexes: HOWTO Replication MySQL (Mysql official Documentation).

Benoit

Network engineer at CNS Communications. CCIE #47705, focused on R&S, Data Center, SD-WAN & Automation.

More Posts - Website

Follow Me: