[MySQL] Réplication MySQL

MySQL permet de mettre en place de la réplication de bases de données afin d'avoir de la redondance de bases.
Il y a plusieurs interêts à mettre en place la réplication :

assurer une redondance de serveur : si le serveur de base de données vient à tomber, la machine en réplication peut devenir éligible afin de prendre sa place,
mettre en place de la répartition de charge sur les requêtes, par exemple en utilisant une solution avec ipvsadm.

La réplication n'est pas de la magie, bien que facile à mettre en place, elle peut apporter des contraintes et peut être quelques complications si le retard accusé par la machine en réplication devient trop important. Il est bien entendu nécessaire pour que la base de données ne soit pas corrompue qu'il convient de ne faire aucune action d'écriture sur le serveur en réplication....
Nous verrons dans la suite de cette article comment mettre en place la réplication mais aussi comme la gérer...

Un peu de vocabulaire
Comment fonctionne la réplication ?
Mise en place
Maintenance
Changer de maitre
Synchroniser un esclave à partir d'un esclave

Un peu de vocabulaire

Tout d'abord, un peu de vocabulaire :

Le maitre, ou master, est le serveur de bases de données de référence.
L'esclave, ou slave, est la machine qui "subit" la réplication.
Les binlogs sont des fichiers binaires activables sur tous serveurs MySQL. Ils ont comme fonction de logger les requêtes de mise à jour (INSERT, UPDATE et autre CREATE TABLE par exemple) et ce pour assurer la cohérence des données en cas de crash du serveur (les binlogs sont rejoués au redémarrage) mais ils sont aussi utilisés lors de la réplication.

Comment fonctionne la réplication ?

Pour que la réplication fonctionne, il faut que le serveur maitre ait ses binlogs activés. Comme indiqué dans la partie précédente, le maitre écrit dans ses binlogs chacune des requêtes d'écriture.
Une fois correctement configuré, un esclave activé possède 2 threads :

l'IO thread qui lit les binlogs de son maitre et les écrits sur son disque en les baptisant relay-logs
la SQL thread qui lit les relay-logs et exécute les requêtes.

Donc pour résumé, la réplication se déroule en 2 phases : la récupération des requêtes puis leur exécution.

Mise en place

Comme toute application fonctionnant sur le réseau, il y a deux parties : le serveur et le client.
Ici le serveur est le serveur maitre et le client est le serveur exécutant la réplication elle-même.

Configuration du maitre :

Dans le fichier de configuration de MySQL, le fichier my.cnf il faut ajouter les lignes suivantes:

[mysqld]
log-bin=mysql-bin
server-id=1

Quelques explications :

La première ligne indique la section à modifier du fichier my.cnf.
La seconde ligne est l'activation des binlogs. La valeur à affecter est le chemin vers lequel seront stockés les binlogs. Si aucun chemin n'est précisé, le répertoire de data de MySQL sera pris.
La troisième ligne est "l'authentification" du serveur. Il s'agit d'un entier et doit impérativement être unique au sein du réseau pour que tout fonctionne correctement.

Une fois ces modifications faites, redémarrer le serveur MySQL.

Attention :

Les binlogs sont des fichiers qui peuvent devenir nombreux et surtout important en taille. Par conséquent, il vaut mieux prévoir une partition suffisamment grande.

Il faut en suite créer un compte spécifique qui sera utilisé par l'esclave :

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'replication';
FLUSH PRIVILEGES;

Et voilà, le maitre est configuré.
Maintenant, il faut faire un dump de la base de données afin de mettre à jour l'esclave. Bien entendu, cette phase est optionnel si la base de données est vide...

Mise à jour du slave :

Le maitre configuré, il faut maintenant mettre à jour l'esclave.
Pour mettre à jour, l'esclave, il y a plusieurs méthodes :

La plus simple : arrêter le serveur mettre et copier le répertoire contenant les datas et les fichiers ibdata. Cette méthode est simple et rapide mais il faut que les fichiers de données soient copiés dans la même arborescence que sur la machine maitre et aient les mêmes droits et propriétaires.
Utiliser la commande LOAD DATA INFILE de MySQL mais ceci peut prendre du temps et cette méthode n'a pas été testée.
Faire un backup de la base de données avec mysqldump.

Si la troisième méthode est choisie, il faut bloquer toutes les actions d'écriture :

FLUSH TABLES WITH READ LOCK;

Une fois le dump effectué et avant de restaurer les écritures sur le maitre, vérifié la position du maitre dans ses binlogs :

SHOW MASTER STATUS;

Du résultat de cette requête, ce sont les champs File et Position qu'il faut noter.

Sur l'esclave, modifier le fichier my.cnf afin d'ajouter un server-id différent du maitre, relancer le service MySQL et injecter le dump fait précédemment.
Une fois le dump intégré, configurer l'esclave afin qu'il connaisse son maitre et à partir de quel fichier et quelle position commencer sa réplication :

CHANGE MASTER TO
MASTER_HOST='IP du maitre',
MASTER_USER='slave',
MASTER_PASSWORD='replication',
MASTER_LOG_FILE='nom du binlog',
MASTER_LOG_POS='position dans le binlog';

Quelques explications :

MASTER_HOST est la définition de la machine maitre;
MASTER_USER est l'utilisateur de réplication définit sur le maitre;
MASTER_PASSWORD est le mode passe de l'utilisateur de réplication sur le maitre;
MASTER_LOG_FILE est le fichier donné par le champ FILE de la requête 'SHOW MASTER STATUS' précédente;
MASTER_LOG_POS est la position dans le fichier binlog donnée par le champ Position de la requête 'SHOW MASTER STATUS' précédente.

Puis démarrer la réplication :

START SLAVE;

Et libérer les écritures sur le maitre :

UNLOCK TABLES;

Maintenance

Suivi de la réplication

Afin de suivre l'état de la réplication, il est possible d'interroger l'esclave comme suit :

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event       
Master_Host: prod                    
Master_User: slave                   
Master_Port: 3306                    
Connect_Retry: 60    
Master_Log_File: preprod-bin.000036       
Read_Master_Log_Pos: 501664152                
Relay_Log_File: dev3-relay-bin.000035    
Relay_Log_Pos: 501664573                
Relay_Master_Log_File: preprod-bin.000036
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: 501664152
Relay_Log_Space: 501664573
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

Quelques explication :

Slave_IO_State : Etat de la thread IO, ici la thread est en attente du maitre
Master_Host : le serveur maitre
Master_User : utilisateur MySQL de la réplication
Master_Log_File : le fichier binlog du maitre récupéré par la thread IO
Read_Master_Log_Pos : position du binlog lu par la thread IO
Relay_Log_File : fichier relay-log écrit par la thead IO pour le stockage local des requêtes à exécuter
Relay_Log_Pos : position d'écriture au sein du relay-log par la thread IO
Slave_IO_Running : Etat de fonctionnement de la thread IO
Slave_SQL_Running : Etat de fonctionnement de la thread SQL
Replicate_Do_DB : liste des bases de données à répliquer
Replicate_Ignore_DB : listes des bases de données à ne pas répliquer
Les champs Replicate_Do_Table et Replicate_Wild_Do_Table listes des tables à répliquer. La déclinaison avec wild permet une syntaxe par expression régulière
Les champs Replicate_Ignore_Table et Replicate_Wild_Ignore_Table : listes des tables à ne pas répliquer. La déclinaison avec wild permet une syntaxe par expression régulière
Exec_Master_Log_Pos : la position de la requête exécutée dans le binlog du maitre
Seconds_Behind_Master : retard de l'esclave par rapport au maitre.

Il est à noter que l'exécution de la réplication ne se fait que grâce à une unique thread et c'est pour cela que le retard d'un esclave ne traduit en fait que le décalage de temps entre l'exécution de la requête en cours sur le maitre et le moment de l'exécution sur l'esclave. Par conséquent, plus la requête en cours est longue plus les requêres à venir s'empilent, d'où le retard.

Erreur de réplication

La réplication peut avoir des soucis et s'interrompre.
Plusieurs cas peuvent se présenter :
- l'esclave ne contacte plus son maitre et la thread IO est interrompue
- l'esclave n'a pas pu exécuter une requête.

Quelquesoit le problème, il est indiqué par la commande SHOW SLAVE STATUS\G qui indique la cause de l'erreur et son numéro de référence MySQL pour dépanner.
La majeure partie des erreurs peuvent ignorés (erreur 1062 : Duplicate key). Pour ignorer ces erreurs, voici comment faire :

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE SQL_THREAD;

Quelques explications :

La première ligne demande à l'esclave de passer la requête sur laquelle il a échoué
La seconde ligne relance la thread SQL. Si c'était la thread IO qui était arrêtée, faire START SLAVE IO_THREAD. (START SLAVE; seul fonctionne aussi dans les 2 cas).

Gestion des binlogs

Les relay-logs de l'esclave sont consommés dès que toutes les requêtes de chacun ont été exécutée. Par conséquent, aucune action n'est nécessaire dessus.
Par contre, ceci n'est pas vrai pour les binlogs du maitre car celui-ci n'a pas conscience des ses esclaves et donc les binlogs s'empilent.

Afin de supprimer les binlogs qui deviennent consommateurs d'espace disque avec le temps, il convient de faire la requête suivante :

SHOW MASTER LOGS;
PURGE MASTER LOGS TO 'mysql-bin.000047';

Quelques explications :

A la première ligne, le résultat indique le fichier binlog en cours. Il ne faut surtout pas le supprimer.
La seconde ligne supprime tous les binlogs disponibles jusqu'à celui indiqué exclus.

Une autre solution plus facile est de supprimer les fichiers au niveau système (rm) mais c'est fortement déconseillé car dans ce cas MySQL ne gèrera plus ses binlogs correctement et donc la requête PURGE MASTER ne fonctionnera plus.

Changement de master

Il peut être nécessaire de changer le serveur MySQL maitre (par exemple le maitre initial est tombé et irrécupérable auquel cas il est nécesaire de promouvoir un esclave en nouveau maitre et donc de reconfigurer les autres esclaves pour qu'ils pointent vers leur nouveau maitre).

Voici la procédure :

Déconnecter les clients
Attendre la fin des exécutions des requêtes sur le maitre
RESET MASTER; (sur le maitre)
Attendre la fin des exécutions des requêtes sur chaque slave
Arrêter le maitre
Déterminer le nouveau maitre parmis les esclaves (le plus à jour)
Connecter les clients sur le nouveau master
Sur les différents esclaves faire CHANGE MASTER TO ...

Synchroniser un esclave à partir d'un esclave

Il est parfois nécessaire d'ajouter un esclave à sa plateforme.
Comme vu précédement, la mise en place d'un esclave implique de faire une interruption de service de la base de données maitre. Si il existe déjà un esclave, il est possible de l'utiliser afin d'en installer un autre.
Pour se faire, il faut procéder à un dump de l'esclave après avoir bloqué les écritures (voir ici), injecter ce dump sur le futur esclave et positionner la réplication de celui-ci.
Pour initialiser la réplication du nouvel esclave, faire un SHOW SLAVE STATUS sur l'esclave d'où est issu le dump avant de lever les locks et dans la commande CHANGE MASTER mettre les valeurs des champs Relay_Master_Log_File et Exec_Master_Log_Pos comme valeurs à MASTER_LOG_FILE et MASTER_LOG_POS.

En résumé :

Arrêter la réplication de l'esclave source
Locker les écritures sur l'esclave souce
Effectuer un dump
Faire un SHOW SLAVE STATUS et relever les champs Relay_Master_Log_File et Exec_Master_Log_Pos
Lever les locks
Relancer la réplication
Injecter le dump dans le nouvel esclave
Exécuter la requête CHANGE MASTER STATUS TO MASTER_HOST='192.168.0.1', MASTER_USER='slave', MASTER_PASSWORD='replication', MASTER_LOG_FILE='Fichier relevé précédemment',MASTER_LOG_POS='Position relevée précédemment';
Exécuter la requête START SLAVE;

Problèmes rencontrés

Si la base de données contient des champs BLOB, il est possible que la réplication des tables les contenant ne fonctionne pas (erreur 1064 de MySQL).
Arrêter une requête en cours sur le maitre peut arrêter la réplication.