Hub de Connaissances DevOps
Hub de Connaissances DevOps

Dépannage rapide des pannes de réplication MySQL courantes

Résolvez rapidement les pannes de réplication MySQL courantes grâce à ce guide pratique. Apprenez à interpréter les codes d'erreur de `SHOW REPLICA STATUS`, à inspecter les journaux d'erreurs MySQL et à comprendre l'objectif des journaux binaires. Cet article propose des étapes concrètes et des meilleures pratiques pour diagnostiquer des problèmes tels que les entrées en double, les fichiers binlog manquants et la divergence de données, vous aidant ainsi à maintenir une configuration de réplication saine.

44 vues

Dépannage rapide des défaillances courantes de la réplication MySQL

La réplication MySQL est une fonctionnalité puissante qui vous permet de maintenir plusieurs copies de votre base de données, ce qui est crucial pour la haute disponibilité, la mise à l'échelle en lecture et la reprise après sinistre. Cependant, la mise en place et la maintenance de la réplication peuvent parfois entraîner des défaillances inattendues. Ce guide propose une approche pratique pour diagnostiquer et résoudre rapidement les problèmes courants de réplication MySQL en se concentrant sur la compréhension des codes d'erreur et l'inspection des journaux pertinents.

Lorsque la réplication s'interrompt, elle peut bloquer des opérations critiques ; il est donc essentiel de disposer d'un processus de dépannage systématique. Nous allons couvrir les problèmes les plus fréquents, vous fournissant les connaissances nécessaires pour identifier la cause première et mettre en œuvre des solutions efficaces. En comprenant les symptômes et en sachant où chercher des indices, vous pouvez minimiser les temps d'arrêt et garantir que votre configuration de réplication reste saine.

Comprendre les bases de la réplication MySQL

Avant de plonger dans le dépannage, un rappel rapide du fonctionnement de la réplication MySQL est bénéfique. Dans une configuration typique maître-esclave (ou primaire-réplique) :

  • Journal binaire (Binlog) sur le Primaire : Le serveur primaire enregistre tous les événements modifiant les données dans ses fichiers de journal binaire.
  • Threads de réplication sur la Réplique : Le serveur réplique possède deux threads :
    • Thread d'E/S (I/O Thread) : Se connecte au primaire, lit les événements du journal binaire du primaire et les écrit dans son propre journal de relais (relay log).
    • Thread SQL (SQL Thread) : Lit les événements du journal de relais et les exécute sur la base de données de la réplique.

Les défaillances de réplication se produisent généralement lorsque le thread d'E/S ne peut pas récupérer les événements, ou lorsque le thread SQL ne peut pas les appliquer.

Codes d'erreur de réplication courants et leurs significations

MySQL fournit des codes d'erreur qui offrent des informations précieuses sur les problèmes de réplication. La commande SHOW REPLICA STATUS (ou SHOW SLAVE STATUS sur les versions antérieures) est votre principal outil pour vérifier l'état de la réplication.

SHOW REPLICA STATUS\G

Regardez les champs clés suivants :

  • Replica_IO_Running : Devrait être Yes.
  • Replica_SQL_Running : Devrait être Yes.
  • Last_IO_Errno et Last_IO_Error : Erreurs liées au thread d'E/S.
  • Last_SQL_Errno et Last_SQL_Error : Erreurs liées au thread SQL.
  • Seconds_Behind_Source : Indique le retard de la réplique par rapport au primaire.

Voici quelques numéros d'erreur courants et leurs causes typiques :

Erreur 1062 : Entrée en double

  • Last_SQL_Errno: 1062
  • Last_SQL_Error: Error 'Duplicate entry '...' for key '...' on query. Default database: '...'.

Cause : Le thread SQL essaie d'appliquer un événement provenant du primaire qui entraîne une violation de clé en double sur la réplique. Cela se produit souvent lorsque la réplique a pris du retard et traité d'autres écritures qui auraient pu créer les mêmes données, ou s'il y a eu une incohérence introduite manuellement sur la réplique.

Résolution :
1. Identifier la requête problématique : Le message d'erreur inclut généralement la requête qui a échoué.
2. Ignorer la transaction (avec prudence) : Si vous êtes certain qu'il est sûr de l'ignorer, vous pouvez utiliser SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; suivi de START SLAVE SQL_THREAD; (ou START REPLICA SQL_THREAD;). Avertissement : Ignorer des transactions peut entraîner une divergence des données. Comprenez les implications avant de continuer.
3. Enquêter sur l'incohérence des données : Si l'ignorance n'est pas une option, vous devrez peut-être réconcilier manuellement les données ou déterminer pourquoi le doublon s'est produit. Cela pourrait impliquer de réinitialiser la réplication à partir d'un point précis si la réplique est gravement désynchronisée.

Erreur 1236 : Impossible de trouver le nom du premier fichier journal dans l'index du journal binaire

  • Last_IO_Errno: 1236
  • Last_IO_Error: Error 'Could not find first log file name in binary log index' when trying to read event from the http client side...

Cause : Le thread d'E/S ne parvient pas à localiser le fichier de journal binaire spécifié par le primaire. Cela signifie généralement que les fichiers de journal binaire ont été purgés du primaire avant que la réplique ne puisse les lire, ou que la réplique essaie de se connecter en utilisant un fichier binlog qui n'existe plus.

Résolution :
1. Vérifier la rétention du binlog du primaire : Assurez-vous que expire_logs_days (ou binlog_expire_logs_seconds) sur le primaire est défini sur une valeur qui conserve les journaux suffisamment longtemps pour que la réplique puisse rattraper son retard.
2. Réinitialiser la réplique : La solution la plus courante consiste à arrêter la réplication, à réinitialiser les données maître de la réplique, et à la réinitialiser à partir d'une sauvegarde ou d'un instantané (snapshot) frais du primaire, en s'assurant que le nouveau fichier journal et la position du primaire sont correctement définis.

Erreur 1577 : La position du journal binaire du primaire est requise

  • Last_IO_Errno: 1577
  • Last_IO_Error: Error: The primary's binary log position is required for this operation.

Cause : Cette erreur se produit généralement lorsque vous essayez de démarrer la réplication sans spécifier le nom du fichier de journal binaire et la position corrects sur la réplique. Cela peut se produire après certains changements de configuration ou interventions manuelles.

Résolution :
1. Vérifier la commande CHANGE MASTER TO (ou CHANGE REPLICATION SOURCE TO) : Assurez-vous d'avoir correctement spécifié MASTER_LOG_FILE et MASTER_LOG_POS (ou SOURCE_LOG_FILE et SOURCE_LOG_POS) lors de la configuration de la réplication.
2. Réinitialiser et reconfigurer : Arrêtez la réplication, réinitialisez l'état de la réplique et réappliquez la commande CHANGE MASTER TO avec les paramètres corrects obtenus à partir du primaire.

Erreur 1032 : Impossible de trouver l'enregistrement dans la table '...'

  • Last_SQL_Errno: 1032
  • Last_SQL_Error: Error 'Can't find record in '...' table' on query. Default database: '...'.

Cause : Semblable à l'Erreur 1062, cela indique que le thread SQL essaie d'effectuer une opération UPDATE ou DELETE sur un enregistrement qui n'existe pas sur la réplique. Cela implique une divergence des données, souvent due à une transaction précédemment ignorée ou à une modification manuelle.

Résolution :
1. Identifier la requête et la table : Le message d'erreur fournit des détails.
2. Enquêter sur la dérive des données : Comparez l'état de la table affectée sur le primaire et la réplique.
3. Ignorer (avec une extrême prudence) : Si l'enregistrement manquant n'est pas significatif ou a été géré par d'autres moyens, vous pouvez ignorer la transaction en utilisant SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; et START REPLICA SQL_THREAD;.
4. Correction manuelle des données : Dans les cas critiques, vous pourriez avoir besoin d'insérer manuellement l'enregistrement manquant ou de resynchroniser la table/base de données.

Inspection des journaux de réplication

Au-delà de SHOW REPLICA STATUS, le journal d'erreurs MySQL et le journal binaire lui-même sont des ressources inestimables.

Journal d'erreurs MySQL

Situé généralement à /var/log/mysql/error.log (ou similaire, selon votre OS et votre configuration), ce journal contient des informations détaillées sur les erreurs rencontrées par le serveur MySQL, y compris celles liées aux threads de réplication.

Éléments à rechercher :
* Traces de pile détaillées pour les erreurs (Detailed stack traces).
* Problèmes de connexion entre le primaire et la réplique.
* Délais d'attente (Timeouts) et problèmes liés au réseau.

Journal binaire du Primaire

Bien que les journaux de relais de la réplique soient cruciaux pour le thread SQL, l'examen du journal binaire du primaire peut parfois aider à comprendre la séquence des événements menant à une défaillance. Vous pouvez utiliser l'utilitaire mysqlbinlog à cette fin.

Exemple : Pour afficher les événements d'un fichier de journal binaire spécifique :

mysqlbinlog /path/to/mysql-bin.000001

Exemple : Pour afficher les événements autour d'une heure ou d'une position spécifique :

mysqlbinlog --start-datetime="2023-10-27 10:00:00" --stop-datetime="2023-10-27 11:00:00" /path/to/mysql-bin.000001

Cas d'utilisation :
* Comprendre la transaction exacte qui a causé une erreur SQL de la réplique.
* Vérifier la cohérence des événements en cours d'écriture.

Étapes générales de dépannage

Lorsque la réplication s'interrompt, suivez ces étapes :

  1. Vérifier SHOW REPLICA STATUS : Commencez toujours par là. C'est le moyen le plus rapide d'obtenir un résumé du problème.
  2. Examiner Last_IO_Error et Last_SQL_Error : Comprendre le code d'erreur et le message spécifiques.
  3. Consulter le journal d'erreurs MySQL : Recherchez un contexte plus détaillé côté serveur.
  4. Vérifier la connectivité réseau : Assurez-vous que la réplique peut atteindre le primaire (pare-feu, DNS).
  5. Vérifier les privilèges utilisateur : L'utilisateur de réplication sur le primaire doit disposer des autorisations nécessaires (REPLICATION SLAVE).
  6. S'assurer que le primaire est configuré pour la réplication : Vérifiez que log_bin est activé et que server_id est unique.
  7. Vérifier le paramètre read_only de la réplique : Si read_only est activé sur la réplique, elle n'appliquera pas les écritures provenant du primaire, sauf si des conditions spécifiques sont remplies ou si le paramètre est temporairement désactivé.

Meilleures pratiques pour prévenir les défaillances

  • Surveiller le retard de réplication : Utilisez des outils de surveillance pour vous alerter lorsque Seconds_Behind_Source augmente excessivement.
  • Sauvegardes régulières : Maintenez des sauvegardes cohérentes de votre primaire pour pouvoir réinitialiser une réplique rapidement.
  • Rétention suffisante du Binlog : Configurez expire_logs_days de manière appropriée sur le primaire.
  • server_id unique : Assurez-vous que chaque serveur de votre topologie de réplication possède un server_id unique.
  • Tester les procédures de basculement (Failover) : Entraînez-vous régulièrement à changer les rôles pour garantir la robustesse de votre configuration de réplication.

Conclusion

Le dépannage des défaillances de réplication MySQL nécessite une approche méthodique. En comprenant les codes d'erreur courants, en sachant interpréter la sortie de SHOW REPLICA STATUS et en exploitant les journaux d'erreurs de MySQL ainsi que l'utilitaire mysqlbinlog, vous pouvez diagnostiquer et résoudre efficacement la plupart des problèmes de réplication. La surveillance proactive et le respect des meilleures pratiques minimiseront davantage l'apparition de ces problèmes, assurant la stabilité et la disponibilité de votre environnement de base de données.