Réplication Mysql

Mise en place d'une réplication Mysql maître/esclave.

La réplication Mysql consiste à avoir en temps réel deux bases de données Mysql identiques sur deux serveur différent afin de pouvoir basculer si besoin sur le deuxième serveur en cas de défaillance du premier.

Pré-requis :

Deux serveurs Mysql fonctionnel, vous pouvez vous aider de cet article si ce n'est pas le cas. Attention aux versions de vos Mysql certains ne sont pas bien copains.

Le Master :

On se connecte à la base de donnée du serveur maître.

mysql -h 192.168.100.1 -u root -ppassword
ou
mysql -u root -p (en local)

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

GRANT REPLICATION SLAVE ON *.* TO repli@'%' IDENTIFIED BY 'repli';

On interdit l’écriture sur les bases :

FLUSH TABLES WITH READ LOCK;

Pour annulé l'interdit (mais faites le pas maintenant :p) :

UNLOCK TABLES;

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

SHOW MASTER STATUS;

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

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 File et Position sont vides : Prenez comme nom de File : ‘’ et pour Position : 4. Quittez Mysql avec la commande quit

Arrêtez le serveur maître :

service mysql stop
ou
invoke-rc mysql stop

Editez le fichier my.cnf qui se trouve normalement dans /etc/ :

Ajoutez-y les lignes suivantes :

log-bin
server-id=1

Relancez le serveur Mysql :

service mysql start
ou
invoke-rc mysql start

On configure maintenant le serveur esclave (192.168.100.2) :

Éteignez le serveur comme vu plus haut ou avec cette commande :

mysqladmin -u root -ppassword -h 192.168.100.2 -P 3306 shutdown

Éditez le fichier my.cnf du serveur secondaire et :

Ajoutez-y les lignes suivantes :

server-id=2
master-host = 192.168.100.1
master-user = repli
master-password = repli
master-port = 3306

Relancez le serveur Mysql esclave comme vu plus haut pour le serveur maitre.

On se connecte au prompt Mysql :

mysql -u root -ppassword -h 192.168.1O0.2 -P 3306
ou
mysql -u root -p

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

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, tapez ceci:

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

Si tout c’est déroulé correctement, vous devriez voir quelque chose comme ceci :

SHOW SLAVE STATUS;
*************************** 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
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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 avec la commande SHOW PROCESSLIST:

SHOW PROCESSLIST;
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
| 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 serveur esclave :

mysql -u root -ppassword -h 192.168.100.2 -P 3306
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 la réplication.

Si vous apercevez une erreur aux 2 lignes suivantes :

Last_Errno:
Last_Error:

Voici comment faire :

Sur le master :

mysql -u  root -ppassword -h 192.168.100.1 –P 3306
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

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

Faire un DUMP de la base de donnée:

mysqldump -u root -ppassword -r /root/dump.sql nomdelasauvegarde

Envoyer le fichier dump du serveur maître vers le serveur esclave:

scp /root/dump.sql root@192.168.100.2:/root

Sur le Slave, on bloque l'écriture :

mysql -u root -ppassword -h 192.168.100.2 -P 3306
FLUSH TABLES WITH READ LOCK;
exit;

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

mysql -h 192.168.100.2 -u root -ppassword -P 3306 nomdelabase < /root/dump.sql

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

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

Normalement, la réplication est repartie correctement.

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 serveur esclave : On vérifie son état :

mysql -u root -ppassword -h 192.168.100.2 -P 3306
FLUSH TABLES WITH READ LOCK;

On crée un dump du slave:

mysqldump -u root -ppassword -h 192.168.100.2 -P 3306 -r /root/dump.sql nomdelabase

On envoi le dump sur le master:

scp /root/dump.sql root@192.168.100.1:/root

Sur le serveur maître :

mysql -h 192.168.100.1 -u root -ppassword -P 3306

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

GRANT REPLICATION SLAVE ON *.* TO repli@'%' IDENTIFIED BY 'repli';

On stop l’écriture sur la base:

FLUSH TABLES WITH READ LOCK;

On injecte le dump du slave sur le master:

mysql -h 192.168.100.1 -u admin -ppassword -P 3306 nomdelabase < /root/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.

mysql -u root -ppassword -h 192.168.100.1 -P 3306
SHOW MASTER STATUS;

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

Exemple:

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)
exit

Éteignez la base:

mysqladmin -u root -ppassword -h 192.168.100.1 -P 3306 shutdown

Éditez le fichier my.cnf :

Ajoutez-y les lignes suivantes:

log-bin
server-id=1

Relancez la base Mysql

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:

mysql -u root -ppassword -h 192.168.100.2 -P 3306
CHANGE MASTER TO
MASTER_LOG_FILE='',
MASTER_LOG_POS=POSITION_FICHIER_LOG;
START SLAVE;

Normalement, la réplication est repartie !

Vous pouvez vérifier l'etat du serveur esclave, avec les commandes SLAVE STATUS, et SHOW PROCESSLIST.

Testé avec une version 5.1 et 5.4

Si vous désirez aller plus loin : http://dev.mysql.com/doc/refman/5.0/fr/replication.html