Résoudre rapidement les échecs courants de réplication MySQL

Les échecs de réplication MySQL sont plus faciles à corriger lorsque vous séparez deux questions : le réplica peut-il récupérer les événements de la source, et peut-il appliquer les événements déjà récupérés ? Ce sont des échecs différents. Un problème réseau, un journal binaire manquant, un mot de passe incorrect ou une autorisation de l'hôte erronée arrêtent généralement le thread I/O. Une clé en double, une ligne manquante, une incompatibilité DDL ou une dérive des données arrêtent généralement le thread SQL.

Commencez par la sortie de statut. Sur MySQL moderne :

SHOW REPLICA STATUS\G

Sur les systèmes plus anciens :

SHOW SLAVE STATUS\G

Utilisez la commande que votre serveur supporte. Les sorties plus récentes utilisent des noms tels que Replica_IO_Running, Replica_SQL_Running et Seconds_Behind_Source. Les sorties plus anciennes utilisent Slave_IO_Running, Slave_SQL_Running et Seconds_Behind_Master.

La première lecture utile est :

Replica_IO_Running : indique si le réplica est connecté et lit les journaux binaires source.
Replica_SQL_Running : indique si le réplica applique les événements du journal de relais.
Last_IO_Errno et Last_IO_Error : pourquoi la récupération a échoué.
Last_SQL_Errno et Last_SQL_Error : pourquoi l'application a échoué.
Relay_Master_Log_File, Exec_Master_Log_Pos ou les champs de position source plus récents : où se trouve le réplica dans le flux.

Ne sautez pas directement à une correction. Copiez d'abord la sortie complète du statut dans vos notes d'incident. Une fois que vous exécutez RESET REPLICA, sautez une transaction ou re-pointez le réplica, certaines des meilleures preuves disparaissent.

Si le thread I/O est arrêté

Lorsque Replica_IO_Running est No, le réplica ne lit pas avec succès la source. Le thread SQL peut encore appliquer des événements de journal de relais plus anciens pendant un certain temps, mais finira par manquer.

Les causes courantes sont :

L'hôte ou le port source est incorrect.
Un pare-feu, un groupe de sécurité ou une règle de routage bloque la connexion.
Le mot de passe de l'utilisateur de réplication est incorrect.
L'utilisateur de réplication est autorisé depuis un hôte différent de celui que le réplica utilise réellement.
La journalisation binaire est désactivée sur la source.
La source a purgé le fichier de journal binaire demandé par le réplica.
Les paramètres TLS ont changé et le réplica ne peut plus s'authentifier.

Testez depuis l'hôte du réplica :

mysql -h source-db.example.com -u repl_user -p

Si une connexion directe échoue, la réplication échouera également. Vérifiez le compte sur la source :

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

Le compte a besoin du privilège REPLICATION SLAVE. Le nom du privilège utilise encore "SLAVE" dans les autorisations MySQL.

Vérifiez également si la journalisation binaire est activée :

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

Sur les versions plus récentes, SHOW BINARY LOG STATUS peut être disponible. Le point est le même : la source doit avoir des journaux binaires, et le fichier demandé doit encore exister.

Erreur 1236 : Journal binaire manquant ou illisible

Last_IO_Errno: 1236 est l'une des erreurs qui signifie généralement que le réplica demande un fichier de journal binaire ou une position que la source ne peut pas fournir. Le message exact varie. Il peut indiquer que le premier fichier journal n'a pas pu être trouvé, qu'un événement de journal n'a pas pu être lu, ou que la source a fermé la connexion pendant la lecture.

Le cas opérationnel le plus courant est simple : le réplica était hors ligne trop longtemps et la source a purgé les journaux binaires dont il avait besoin.

Vérifiez quels journaux restent sur la source :

SHOW BINARY LOGS;

Comparez ensuite cette liste avec le fichier nommé dans le statut du réplica. Si le réplica a besoin de mysql-bin.000120 et que la source commence maintenant à mysql-bin.000140, le réplica ne peut pas rattraper son retard à partir des journaux binaires.

Vous avez trois choix réalistes :

Restaurer ou reconstruire le réplica à partir d'une sauvegarde fraîche prise depuis la source.
Utiliser un autre réplica qui a encore les données nécessaires comme source de clonage, si votre processus le supporte.
Si vous utilisez GTID et que les transactions manquantes existent ailleurs, reconfigurer à partir d'une source valide qui peut les fournir.

Ne devinez pas une position de journal plus récente juste pour démarrer la réplication. Cela crée un réplica avec des transactions manquantes. Il peut sembler sain tout en retournant silencieusement des données incorrectes.

Après la récupération, augmentez la rétention des journaux binaires si la capacité du disque le permet :

[mysqld]
binlog_expire_logs_seconds=604800

Cet exemple correspond à environ 7 jours. Choisissez une valeur en fonction de la durée pendant laquelle les réplicas peuvent être hors ligne lors de la maintenance ou des incidents.

Si le thread SQL est arrêté

Lorsque Replica_SQL_Running est No, le réplica a récupéré des événements mais n'a pas pu en appliquer un. C'est souvent un problème de cohérence des données, pas un problème de connexion.

Lisez le Last_SQL_Error complet. Il vous indique généralement la table, la clé, l'opération ayant échoué, et parfois la position du journal source. Inspectez ensuite la ligne concernée sur la source et le réplica avant de modifier quoi que ce soit.

Pour un événement ayant échoué autour d'une position de journal binaire connue, mysqlbinlog peut montrer l'événement :

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

Si les journaux binaires source ne sont pas sur l'hôte local, utilisez des options distantes ou inspectez un fichier journal copié. Soyez prudent avec les événements basés sur les lignes : ils peuvent nécessiter des options de décodage et des métadonnées de table pour être lisibles.

Erreur 1062 : Entrée en double

Last_SQL_Errno: 1062 signifie que le réplica a essayé d'insérer ou de mettre à jour une ligne et a rencontré une clé unique qui existe déjà.

Les causes typiques incluent :

Quelqu'un a écrit directement sur le réplica.
Le réplica a été initialisé à partir du mauvais instantané.
Une erreur de réplication précédente a été ignorée.
Les paramètres d'auto-incrémentation sont incorrects dans une conception multi-source ou active-active.
Des écritures d'application sont allées par erreur sur deux serveurs accessibles en écriture.

La correction tentante est :

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

La syntaxe plus ancienne utilise STOP SLAVE et START SLAVE. Cela peut être acceptable pour un réplica de rapport jetable après avoir confirmé que la ligne n'a pas d'importance. C'est dangereux pour un réplica qui pourrait être promu plus tard. Ignorer signifie que le réplica n'a plus le même historique de transactions que la source.

Un processus plus sûr est :

Identifier la table et la clé en conflit.
Comparer la ligne sur la source et le réplica.
Décider si la ligne du réplica doit être supprimée, mise à jour, ou si le réplica doit être reconstruit.
Enregistrer la décision, car il s'agit maintenant d'un événement de cohérence des données.

Si le réplica est destiné au basculement, la reconstruction est souvent plus propre que de corriger manuellement plusieurs différences inconnues.

Erreur 1032 : Impossible de trouver l'enregistrement

Last_SQL_Errno: 1032 signifie généralement que le réplica a essayé de mettre à jour ou de supprimer une ligne qui n'existe pas localement. C'est l'image miroir de nombreux problèmes de clé en double. La source avait une ligne ; le réplica ne l'avait pas.

Les causes courantes sont :

Une ligne a été supprimée manuellement sur le réplica.
Une transaction précédente a été ignorée.
Le dump initial a manqué des données.
Les filtres de réplication ont exclu des écritures antérieures.

Ne supposez pas que la ligne manquante est inoffensive. Si une UPDATE ne trouve pas de ligne, le réplica est déjà différent de la source. Comparez les comptages et les échantillons de données autour de la clé affectée. Si la table est petite, un rechargement de table peut être raisonnable. Si elle est grande ou critique, utilisez un outil de cohérence ou reconstruisez le réplica.

Problèmes d'authentification et d'autorisation de l'hôte

Un échec très courant après une rotation de mot de passe ou des changements réseau est une erreur I/O qui ressemble à un accès refusé :

Access denied for user 'repl_user'@'10.0.2.15'

L'hôte dans l'erreur est celui que MySQL voit. Il peut ne pas correspondre au nom d'hôte que vous attendiez, surtout avec NAT, proxys ou réseaux de conteneurs.

Sur la source, inspectez les utilisateurs :

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

Si le réplica se connecte depuis 10.0.2.15, une autorisation pour 'repl_user'@'replica.internal' peut ne pas correspondre à moins que la résolution de noms et les autorisations soient alignées. Préférez des modèles d'hôte explicites qui correspondent à votre conception réseau.

Si le plugin diffère, les clients plus anciens peuvent échouer contre des comptes utilisant des plugins d'authentification plus récents. Mettre à jour le client est généralement mieux que d'affaiblir l'authentification, mais dans des environnements à versions mixtes, vous pouvez avoir besoin d'un changement de compatibilité planifié.

Problèmes de journal de relais

Parfois, la connexion source est bonne, mais le réplica a une corruption du journal de relais ou un problème de disque local. L'erreur peut mentionner un échec de lecture du journal de relais, un événement tronqué ou une position du journal de relais.

Vérifiez d'abord la santé du disque et l'espace libre. Un disque plein peut créer plusieurs symptômes étranges de réplication :

df -h
iostat -xz 1

Si le journal de relais est corrompu mais que la source a encore les journaux binaires nécessaires, vous pouvez souvent réinitialiser les journaux de relais et laisser le réplica récupérer à nouveau. La commande exacte dépend de la version et de la topologie. N'exécutez pas les commandes de réinitialisation à la légère ; confirmez que vous connaissez le fichier et la position du journal source qui ont déjà été exécutés.

Dans de nombreux cas, ce type de problème est un signe que l'hôte du réplica a eu un problème de stockage sous-jacent. Corrigez cela avant de faire à nouveau confiance au réplica.

Le retard de réplication n'est pas toujours un échec

Seconds_Behind_Source peut être élevé alors que les deux threads sont en cours d'exécution. Cela signifie que la réplication est active mais en retard. Traitez le retard différemment d'un thread arrêté.

Vérifiez :

Le disque du réplica est-il saturé ?
La source génère-t-elle une rafale d'écritures ?
Des lectures longues sur le réplica sont-elles en compétition avec le thread SQL ?
Le réplica est-il plus petit ou plus lent que la source ?
Un travail de sauvegarde ou un instantané a-t-il démarré en même temps ?

Si le retard diminue, le réplica rattrape son retard. Si le retard augmente, supprimez la charge ou ajoutez de la capacité. Redémarrer un réplica en retard résout rarement un goulot d'étranglement de ressources soutenu.

Filtres et réplication multi-source

Les filtres de réplication peuvent rendre les échecs plus difficiles à lire. Un réplica peut intentionnellement ignorer certaines bases de données ou tables, mais l'application peut toujours s'attendre à ce que des données connexes existent. Si vous utilisez des filtres, inspectez-les avant de supposer que le réplica est corrompu :

SHOW REPLICA STATUS\G

Recherchez les champs qui mentionnent Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table ou des règles de réécriture. Les sorties plus anciennes utilisent les mêmes noms généraux sous SHOW SLAVE STATUS.

Le filtrage est particulièrement risqué avec des écritures inter-bases de données. Si une transaction met à jour app.orders et audit.order_events, mais que le réplica filtre audit, la copie résultante peut être techniquement cohérente avec le filtre et toujours inutile pour un workflow qui attend des lignes d'audit. La journalisation basée sur les instructions peut rendre les filtres de base de données encore plus surprenants car la base de données par défaut sélectionnée peut influencer si un événement est répliqué.

La réplication multi-source ajoute une autre couche. Un canal peut être sain tandis qu'un autre est arrêté. Dans ce cas, vérifiez le statut de tous les canaux au lieu de lire uniquement le premier bloc de sortie :

SHOW REPLICA STATUS\G

Sur les configurations basées sur les canaux, la sortie de statut inclut un nom de canal. Corrigez le canal défaillant sans réinitialiser les canaux sains. Si deux sources peuvent écrire des clés qui se chevauchent dans la même table, les erreurs de clé en double sont souvent un problème de conception plutôt qu'un échec de réplication ponctuel.

Évitez la dérive cachée des données

Le pire échec de réplication est celui qui dit Yes et contient toujours des données incorrectes. La dérive peut se produire après des transactions ignorées, des écritures directes sur les réplicas, des importations échouées, des filtres incorrects ou des réparations manuelles.

Pour les réplicas importants, planifiez des vérifications de cohérence. Le pt-table-checksum de Percona Toolkit est couramment utilisé pour cela, et pt-table-sync peut aider à réparer les différences dans des situations contrôlées. Ces outils peuvent créer de la charge, alors testez-les d'abord et exécutez-les avec des limites qui correspondent à votre environnement de production.

Protégez également les réplicas des écritures accidentelles :

[mysqld]
read_only=ON
super_read_only=ON

Utilisez des identifiants séparés pour les lectures d'application. Ne laissez pas les utilisateurs d'application avoir des privilèges d'écriture larges sur les réplicas "au cas où".

Une checklist rapide pour les incidents

Utilisez cet ordre lorsque la réplication se casse :

Sauvegardez la sortie de SHOW REPLICA STATUS\G.
Vérifiez si le thread I/O ou le thread SQL s'est arrêté.
Lisez Last_IO_Error ou Last_SQL_Error ; ne vous fiez pas uniquement au numéro d'erreur.
Vérifiez le journal d'erreurs MySQL pour des horodatages correspondants.
Pour les échecs I/O, testez le réseau, les identifiants, les autorisations, TLS et la disponibilité du journal binaire.
Pour les échecs SQL, inspectez la ligne ou la table affectée sur la source et le réplica.
Décidez de réparer, d'ignorer avec un risque documenté, de recharger une table ou de reconstruire le réplica.
Après la récupération, exécutez un vrai test d'écriture et surveillez le retard.

La plupart des échecs de réplication MySQL ne sont pas résolus par une seule commande magique. Ils sont résolus en préservant les preuves, en identifiant quel thread a échoué et en choisissant une correction qui ne vous laisse pas avec un réplica qui fonctionne mais n'est pas fiable.