Une réplication MySQL est relativement simple à mettre en place, il y a malgré tout quelques points à ne pas négliger et à vérifier avant de vous lancer pour éviter toute surprise. 

Si j’ai souvent travaillé avec des serveurs MySQL répliqués, je n’avais encore jamais eu à en mettre en place. Cette opération est plutôt simple, mais cela nécessite tout de même un certain nombre de contrôles au préalable pour être sûr que tout se déroule de la meilleure manière. Nous décrirons dans cet article la description de la mise en place d’une réplication master/slave.

Préparation des serveurs pour la réplication

Pour pouvoir mettre en place une réplication, il faut à minima deux serveurs MySQL, un qui fera office de maître (master), un d’esclave (slave).
Pour que la réplication puisse être lancée, il faut déjà que les bases soient dans un état identique sur les deux machines. Cela passera soit par une duplication de la machine virtuelle si vous êtes dans cette configuration, soit par des dumps du master à charger sur le slave.

Paramètres de base pour la réplication MySQL

Paramétrage du master

Pour paramétrer votre master, vous devez modifier des lignes dans my.cnf. Il faudra préciser un id serveur (qui doivent être différents entre le master et le ou les slaves), un log binaire, un log d’erreur et le périmètre des bases à prendre en compte. Ce périmètre peut être défini en mode « répliquer toutes les bases sauf… » ou en mode « répliquer uniquement ces bases… ». Vous pouvez définir ces lignes vous-mêmes, ou si vous l’avez installé vous faire aider de phpMyAdmin qui à partir d’un formulaire vous donnera les lignes à insérer. Pour plus de détails sur la réplication avec phpMyAdmin, vous pouvez suivre ce lien.

Au final, vous aurez des lignes à insérer (dans la section [mysqld]) qui ressemblent à cela :

server-id=4218408
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_ignore_db=nom_db_a_ignorer

Si vous êtes en mode « ne répliquer que ces bases…. », vous aurez en dernière ligne « binlog_do_db » au lieu de « binlog_ignore_db ». Il faudra redémarrer le service MySQL.

Sur la master, vous aurez également à créer un compte dédié à la réplication (c’est conseillé) qui sera joignable par le ou les slaves.

Paramétrage de base du slave pour la réplication MySQL

Côté slave, il y a peu de choses à faire. Il faut définir un serveur id dans le my.cnf qui doit être différent du master. Ensuite, il faut se lier au master via le compte MySQL utilisé pour la réplication. Vous pouvez là encore sur le lien donné plus haut pour vous faire assister au besoin par phpMyAdmin. Il faudra redémarrer le service MySQL.

Points à vérifier

Une fois ce paramétrage de base effectué, il faudra voir de plus près certains points pour être certains que la réplication n’aura pas de soucis, voire que votre maître ne sera pas impacté par de nouvelles contraintes.

Fonctions MySQL développées

Si vous avez développé vos propres fonctions MySQL, il faudra vérifier si elles sont bien « deterministic », les fonctions non déterministes étant considérées comme non sûres (car elles peuvent potentiellement déclencher un résultat différent sur le master et le slave et du coup poser des soucis sur votre réplication). Si vos fonctions sont déterministes, il faudra déjà l’avoir idéalement indiqué dans sa déclaration :

CREATE FUNCTION ..... DETERMINISTIC

Si vous avez des fonctions non déterministes, que vous avez un historique de fonctions déterministes mais non indiquées comme telles, vous aurez des erreurs à l’exécution de ces fonctions une fois les logs binaires pour la réplication activés. Cela peut donc engendrer de gros soucis, car votre production en sera impactée.
Si vous estimez que vos fonctions sont dignes de confiance, vous pourrez ajouter cette ligne dans le my.cnf :

log_bin_trust_function_creators = On

Au niveau des fonctions systèmes, certaines fonctions non déterministes sont malgré tout considérées commes « sûres » pour une replication, par exemple « LAST_INSERT_ID() ».

Si vous souhaitez plus d’informations sur le sujet, vous pouvez aller voir cette page sur le site MySQL.

Format de fichiers binaires

Vous pouvez définir trois formats pour le binlog :

  • statement : basé sur les déclarations, format par défaut avant MySQL 5.7.7
  • row : basé sur les lignes, format par défaut à partir de 5.7.7
  • mixed : mode mixte

Si vous rencontrez une erreur comme celle-ci :

« Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED »

C’est que votre format n’est pas adapté. Si vous avez des bases et tables hétérogènes, il vous faudra passer par le mode mixte via cette déclaration dans le my.cnf :

binlog-format=mixed

Espace disque et purge des logs binaires

L’échange entre votre master et votre ou vos slave(s) se fera au travers du binlog, un log binaire où les instructions sont écrites par le master, récupérées et jouées par le slave. Mais ces fichiers de log peuvent représenter un volume conséquent, il faut donc prévoir de l’espace disque pour accueillir ces données.

Vous pouvez ensuite programmer une purge glissante des fichiers binaires. Prévoyez un historique suffisant pour pouvoir relancer la réplication sans avoir à repasser par la case dump.
Par exemple si vous souhaitez purger votre log binaire avec un historique de trois jours, vous pouvez lancer la commande suivante :

PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);

Pour que cette instruction soit jouée régulièrement, vous pouvez l’appeler via un script en cron, ou encore mieux via un event MySQL.

Monitoring et gestion d’erreurs

Monitoring

Vous avez maintenant tous les éléments pour lancer votre réplication. Une fois lancée, il va falloir la monitorer, histoire de pouvoir rapidement intervenir si un événement fait tomber cette réplication.
Pour cela, il faut surveiller la résultat de la requête suivante sur le slave :

SHOW SLAVE STATUS;

Cela vous donnera un certain nombre d’éléments pour connaître la bonne santé de votre réplication. Les informations à surveiller notamment sont :

  • Slave_IO_Running : permet de savoir si le fil d’exécution IO est en cours
  • Slave_SQL_Running : permet de savoir si le fil d’exécution SQL est en cours
  • Seconds_behind_master : le décalage en secondes entre le master et le slave

Les deux fils d’exécution doivent être en route, et en général le délai entre le master et le slave est de 0s.

Si vous utilisez Nagios, vous pourrez également trouver des plugins comme celui-ci pour monitorer votre réplication.

Gestion des erreurs

Pour redémarrer le slave :

START SLAVE;

Pour redémarrer le fil IO :

START SLAVE IO_THREAD;

Pour redémarrer le fil SQL :

START SLAVE SQL_THREAD;

Pour débloquer une erreur (en général une seule erreur bloque la réplication, le fait de l’ignorer permet en général de débloquer la situation) :

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Avec en chiffre (ici 1) le nombre de requêtes à sauter. Un start slave suivi d’un show slave status permettra de voir si le souci est résolu et si la réplication est repartie.

Si certaines des erreurs sont récurrentes et ne constituent pas un souci pour votre réplication, vous pouvez également choisir de les ignorer par une entrée dans my.cnf :

slave-skip-errors=1062,1053