Comprendre la Réplication MySQL

La réplication MySQL permet aux données d’un serveur de base de données (le primaire, historiquement appelé “master”) d’être automatiquement copiées vers un ou plusieurs serveurs supplémentaires (répliques, historiquement appelées “slaves”). Ce mécanisme offre des avantages critiques : haute disponibilité, scalabilité en lecture, distribution géographique et déchargement des sauvegardes.

Malgré sa maturité, la réplication MySQL n’est pas du genre “configure et oublie”. Les dérives de configuration, les changements de schéma, les disparités matérielles et les instabilités réseau conspirent pour produire des pannes de réplication qui peuvent se propager en incohérences de données, erreurs applicatives et même temps d’arrêt.

Ce guide couvre les problèmes de réplication MySQL les plus courants, leurs causes racines et les solutions étape par étape pour les topologies basées sur le journal binaire traditionnel et sur GTID.

Prérequis

  • Une configuration primaire/réplique MySQL fonctionnelle (MySQL 5.7+ ou MySQL 8.0+).
  • Accès shell aux deux serveurs, primaire et réplique.
  • Privilèges suffisants (SUPER ou REPLICATION CLIENT) pour exécuter SHOW SLAVE STATUS.
  • Familiarité avec le SQL de base et les outils en ligne de commande Linux.
  • Optionnel : Percona Toolkit installé pour les diagnostics avancés.

Problèmes de Réplication Courants

Les cinq modes de défaillance suivants représentent la grande majorité des problèmes de réplication MySQL :

  1. Retard de Réplique (Seconds_Behind_Master > 0) : La réplique ne peut pas appliquer les événements du journal binaire aussi vite que le primaire les produit.
  2. Erreurs de Clé Dupliquée (Error 1062) : Une ligne qui existe déjà sur la réplique est insérée à nouveau par une transaction répliquée.
  3. Erreurs de Ligne Manquante (Error 1032) : Un UPDATE ou DELETE répliqué fait référence à une ligne qui n’existe pas sur la réplique.
  4. Corruption du Relay Log : Le fichier de relay log sur la réplique devient illisible en raison d’erreurs de disque, de crashes ou d’arrêts incorrects.
  5. Divergence GTID : Dans la réplication basée sur GTID, l’ensemble des GTIDs exécutés de la réplique ne forme plus un sur-ensemble contigu de ce que le primaire attend.

Solution Étape par Étape

1. Évaluer le Statut Actuel de Réplication

Commencez toujours par inspecter la santé de la réplique. Sur le serveur réplique, exécutez :

SHOW REPLICA STATUS\G

Pour les versions MySQL antérieures à 8.0.22, utilisez SHOW SLAVE STATUS\G.

Champs clés à inspecter :

ChampValeur SaineSignification
Replica_IO_RunningYesLe thread I/O est connecté au primaire et lit les journaux binaires
Replica_SQL_RunningYesLe thread SQL applique les événements du relay log
Seconds_Behind_Source0Aucun retard de réplication
Last_Error(vide)Aucune erreur rencontrée

2. Corriger le Retard de Réplique

watch -n 1 "mysql -e 'SHOW REPLICA STATUS\G' | grep -E 'Seconds_Behind|Exec_Source_Log_Pos|Read_Source_Log_Pos'"

Causes courantes et solutions :

A) Requêtes lentes sur la réplique :

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/replica-slow.log';

B) Application SQL mono-thread (MySQL 5.6 et antérieur) :

STOP REPLICA;
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
START REPLICA;

C) Goulot d’étranglement matériel : L’I/O disque de la réplique est saturé. Surveillez avec iostat -x 1 et envisagez de passer aux SSD.

3. Résoudre les Erreurs de Clé Dupliquée (Error 1062)

Pour la réplication basée sur le journal binaire :

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Pour la réplication basée sur GTID :

STOP REPLICA;
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:42';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

4. Résoudre les Erreurs de Ligne Manquante (Error 1032)

-- Sur le PRIMAIRE :
SELECT * FROM mydb.mytable WHERE id = 12345;
-- Sur la RÉPLIQUE : comparer
SELECT * FROM mydb.mytable WHERE id = 12345;

5. Récupérer d’une Corruption du Relay Log

STOP REPLICA;
RESET REPLICA;
START REPLICA;

6. Corriger la Divergence GTID

SELECT @@gtid_executed;
  • Divergence mineure : Injectez des transactions vides sur le primaire pour les GTIDs supplémentaires.
  • Divergence majeure : Reconstruisez la réplique à partir d’une sauvegarde fraîche du primaire.

Prévention et Meilleures Pratiques

  • Rendez les répliques en lecture seule : Configurez read_only = 1 et super_read_only = 1.
  • Utilisez la réplication GTID : Simplifie grandement le basculement et le repositionnement des répliques.
  • Surveillez le retard de réplication en continu : Utilisez PMM, Prometheus avec mysqld_exporter, ou Datadog.
  • Exécutez pt-table-checksum hebdomadairement : Détectez proactivement la dérive des données.
  • Automatisez le basculement avec précaution : Orchestrator, MHA, ou MySQL InnoDB Cluster.

Résumé

  • Les pannes de réplication MySQL se résument généralement au retard, aux erreurs de lignes dupliquées/manquantes, à la corruption du relay log ou à la divergence GTID.
  • Commencez toujours le diagnostic par SHOW REPLICA STATUS.
  • Utilisez sql_slave_skip_counter ou des transactions GTID vides pour contourner les erreurs individuelles, mais validez toujours la cohérence des données ensuite.
  • Prévenez les problèmes en imposant read_only sur les répliques, en utilisant la réplication GTID et en surveillant en continu.

Articles Connexes