Practical MySQL & MariaDB

Replication MySQL expliquée : configuration, GTID, surveillance et guide de dépannage

Avancé / Autres
目次

1. Qu’est-ce que la réplication MySQL ? Vue d’ensemble et cas d’utilisation

La réplication MySQL est une fonctionnalité qui synchronise des copies d’une base de données vers d’autres serveurs en temps réel. Cela améliore la redondance et les performances de la base de données. Ci‑dessous, nous expliquons en détail quand la réplication MySQL est utilisée et comment elle fonctionne.

Vue d’ensemble de la réplication MySQL

La réplication MySQL partage le contenu de la base de données entre plusieurs serveurs à l’aide d’un serveur maître et d’un ou plusieurs serveurs esclaves. Concrètement, les mises à jour enregistrées dans le journal binaire du serveur maître sont lues et appliquées par les serveurs esclaves afin de garder les données synchronisées. Cela permet de garantir la continuité du service en basculant vers un serveur esclave si le serveur maître rencontre une défaillance.

Cas d’utilisation de la réplication MySQL

La réplication MySQL est largement utilisée dans les scénarios suivants :

  • Haute disponibilité : En cas de panne, le basculement vers un serveur esclave minimise le temps d’arrêt.
  • Équilibrage de charge : Les requêtes en lecture seule peuvent être distribuées aux serveurs esclaves, réduisant la charge sur le serveur maître.
  • Protection des données et sauvegarde : Comme la réplication duplique les données en temps réel, elle peut également servir de mécanisme de sauvegarde.

Types de réplication

La réplication MySQL comprend les types suivants selon la méthode de synchronisation :

  • Réplication asynchrone : Le maître continue sans attendre la confirmation de l’esclave. Cela permet des temps de réponse plus rapides mais peut entraîner que certaines données n’atteignent pas l’esclave en cas de panne.
  • Réplication semi‑synchrone : Le maître attend la confirmation que les données ont été reçues par l’esclave avant de continuer. Cela offre une fiabilité supérieure à la réplication asynchrone, bien que les temps de réponse soient légèrement plus lents.

La section suivante explique les concepts fondamentaux de la réplication MySQL, y compris les journaux binaires et le GTID.

2. Concepts de base de la réplication MySQL

Pour comprendre la réplication MySQL, il est important de saisir les rôles du journal binaire et du GTID (Global Transaction ID), qui jouent des rôles essentiels dans le processus de réplication. Ces éléments constituent la base d’une réplication de données précise.

Rôles du maître et de l’esclave

Dans la réplication MySQL, le serveur maître et le serveur esclave ont des rôles distincts. Le maître enregistre les mises à jour des données dans le journal binaire et les envoie à l’esclave. L’esclave applique les journaux reçus pour mettre à jour ses données. Ainsi, l’esclave conserve les mêmes données que le maître.

Journal binaire et journal de relais

La réplication MySQL repose sur les deux journaux suivants :

  1. Journal binaire
  • Le journal binaire enregistre les mises à jour des données (telles que INSERT, UPDATE et DELETE) sur le serveur maître. Cela permet au serveur esclave de maintenir le même état de données que le maître.
  1. Journal de relais
  • Le journal de relais stocke les événements du journal binaire reçus du maître sur le serveur esclave. Le thread SQL de l’esclave exécute le journal de relais séquentiellement pour appliquer les changements de données.

Qu’est-ce que le GTID (Global Transaction ID) ?

Le GTID est un mécanisme qui attribue un identifiant unique à chaque transaction, aidant à maintenir la cohérence entre plusieurs esclaves. En utilisant le GTID, il n’est plus nécessaire de spécifier les positions du journal binaire. Seules les transactions qui n’ont pas encore été récupérées du maître sont automatiquement appliquées à l’esclave, ce qui simplifie grandement la gestion.

Avantages du GTID

  • Identification unique : Chaque transaction se voit attribuer un GTID unique, identifiant clairement les transactions qui ont été appliquées.
  • Récupération plus facile : Lorsqu’on utilise le GTID, seules les transactions non appliquées sont retraitées après un redémarrage du maître ou de l’esclave.
  • Gestion opérationnelle efficace : Même dans des environnements à grande échelle avec plusieurs esclaves, la cohérence des transactions peut être maintenue avec une gestion simplifiée.

Pour utiliser le GTID, les paramètres gtid_mode=ON et enforce_gtid_consistency=ON sont requis. Configurer ces paramètres à la fois sur le maître et sur l’esclave active la réplication basée sur le GTID.

La section suivante explique les étapes spécifiques pour configurer la réplication MySQL.

3. Procédure d’installation de la réplication MySQL

Cette section explique les étapes nécessaires pour configurer la réplication MySQL. En suivant ces étapes, vous pouvez mettre en place une structure maître‑esclave basique et activer la synchronisation des données en temps réel.

Configuration du serveur maître

Tout d’abord, modifiez le fichier de configuration du serveur maître (généralement my.cnf ou my.ini) pour activer le journal binaire et définir l’ID du serveur.

  1. Modifier le fichier de configuration
  • Ajoutez les paramètres suivants sous la section [mysqld] et définissez un ID de serveur unique (par exemple, 1).
    [mysqld]
server-id=1
log-bin=mysql-bin
  • Le server-id doit être un numéro unique pour chaque serveur, et log-bin active le journal binaire.
  1. Créer un utilisateur de réplication
  • Créez un utilisateur dédié à la réplication sur le serveur maître et accordez les privilèges nécessaires.
    CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
  • Cet utilisateur est requis pour que le serveur esclave puisse accéder aux données du maître.
  1. Vérifier le statut du maître
  • Vérifiez le fichier de journal binaire actuel et sa position (emplacement du journal). Ces informations sont nécessaires lors de la configuration du serveur esclave.
    SHOW MASTER STATUS;
  • Le File (nom du fichier de journal) et la Position affichés par cette commande seront utilisés dans la configuration de l’esclave.

Configuration du serveur esclave

Ensuite, modifiez le fichier de configuration du serveur esclave et configurez l’ID du serveur ainsi que les informations du maître.

  1. Modifier le fichier de configuration
  • Définissez également un server-id unique sur le serveur esclave (par exemple, 2). Cette valeur doit différer de l’ID du serveur maître.
    [mysqld]
server-id=2

  • Il est également courant de définir read_only=ON pour empêcher les écritures de données sur le serveur esclave. 1. Configurer les informations du maître sur l’esclave

  • Exécutez la commande suivante sur le serveur esclave, en spécifiant le nom d’hôte du maître, l’utilisateur, le nom du fichier de journal binaire et la position.

    CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123;

  • Saisissez les valeurs confirmées précédemment sur le maître pour MASTER_LOG_FILE et MASTER_LOG_POS . 1. Démarrer la réplication

  • Exécutez la commande suivante sur le serveur esclave pour démarrer la réplication.

    START SLAVE;

Vérification du statut de la réplication

Vérifiez que la réplication entre le maître et l’esclave est correctement configurée.

  • Vérifier le statut du maître
    SHOW MASTER STATUS;
  • Vérifier le statut de l’esclave
    SHOW SLAVE STATUS\G;
  • Si Slave_IO_Running et Slave_SQL_Running affichent tous deux Yes, la réplication fonctionne normalement.

La section suivante explique les configurations avancées de réplication, y compris les différences entre la réplication asynchrone et semi‑synchrone ainsi que la configuration à l’aide de GTID.

4. Types de réplication et utilisation avancée

La réplication MySQL comprend deux types principaux basés sur la méthode de synchronisation des données : réplication asynchrone et réplication semi‑synchrone. En comprenant leurs caractéristiques et en choisissant en fonction de votre cas d’utilisation, vous pouvez améliorer à la fois les performances du système et sa fiabilité. Cette section explique également les avantages d’utiliser GTID (Global Transaction Identifier) pour la configuration de la réplication.

Différences entre la réplication asynchrone et semi‑synchrone

1. Réplication asynchrone

Dans la réplication asynchrone, le serveur maître renvoie une réponse au client immédiatement après la fin d’une transaction. En d’autres termes, le maître peut continuer à traiter de nouvelles requêtes même si la synchronisation vers le serveur esclave est retardée. Cela offre d’excellentes performances de réponse et convient aux systèmes axés sur la répartition de charge. Cependant, en cas de défaillance, il existe un risque que les données qui n’ont pas encore été appliquées sur le serveur esclave soient perdues.

2. Réplication semi‑synchrone

En réplication semi-synchronisée, le serveur maître ne retourne une réponse au client qu’après avoir confirmé que le transfert de données vers le serveur esclave est terminé. Cela améliore la cohérence des données, mais comme il attend l’esclave, les temps de réponse des transactions peuvent augmenter. La réplication semi-synchronisée est bien adaptée aux systèmes qui nécessitent une cohérence élevée des données ou aux environnements où la fiabilité des données est la priorité absolue.

Réplication en utilisant GTID

GTID (Global Transaction Identifier) attribue un ID unique à chaque transaction et maintient la cohérence des transactions entre le maître et l’esclave. L’activation de GTID facilite la gestion de la réplication par rapport à la réplication traditionnelle basée sur la position du journal binaire.

Avantages de GTID

  • Amélioration de la cohérence des données : Avec GTID, l’esclave peut identifier automatiquement les transactions qui n’ont pas encore été appliquées, facilitant ainsi la préservation de la cohérence.
  • Gestion simplifiée de la réplication : GTID améliore l’efficacité pour le basculement, le changement maître/esclave et les tâches de récupération. Comme vous n’avez plus besoin de spécifier les positions du journal binaire, les opérations deviennent plus simples.

Configuration de la réplication GTID

Pour utiliser GTID, vous devez ajouter et activer les options suivantes dans les fichiers de configuration du maître et de l’esclave.

Configuration du serveur maître

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON

Configuration du serveur esclave

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON

Dans un environnement avec GTID activé, la réplication s’effectue automatiquement via GTID simplement en définissant les informations du maître sur l’esclave à l’aide de la commande CHANGE MASTER TO.

La section suivante explique les méthodes de maintenance pour la réplication MySQL et les points de surveillance clés pour la gestion opérationnelle.

5. Maintenance et surveillance de la réplication

Pour exploiter correctement la réplication MySQL, une maintenance et une surveillance régulières sont essentielles. Cette section explique les commandes pour vérifier si la réplication fonctionne normalement et comment gérer les erreurs courantes.

Comment vérifier l’état de la réplication

Utilisez les commandes suivantes pour vérifier l’état de synchronisation entre le maître et l’esclave.

Vérification de l’état du maître

Vous pouvez vérifier l’état de réplication du serveur maître en utilisant la commande SHOW MASTER STATUS. Cette commande affiche le nom du fichier de journal binaire actuel et la position, vous permettant de confirmer les dernières mises à jour qui doivent être livrées à l’esclave.

SHOW MASTER STATUS;

La sortie inclut généralement les champs suivants :

  • File : Le nom du fichier de journal binaire actuel généré par le maître
  • Position : La position actuelle dans le journal binaire
  • Binlog_Do_DB et Binlog_Ignore_DB : Bases de données incluses/exclues de la réplication

Vérification de l’état de l’esclave

Vous pouvez vérifier l’état de réplication du serveur esclave en utilisant la commande SHOW SLAVE STATUS. Les résultats incluent les informations nécessaires pour déterminer si l’esclave fonctionne correctement.

SHOW SLAVE STATUS\G;

Les champs clés incluent :

  • Slave_IO_Running et Slave_SQL_Running : Si les deux sont Yes , l’esclave fonctionne normalement.
  • Seconds_Behind_Master : Indique de combien l’esclave est en retard par rapport au maître (en secondes). Idéalement, cette valeur devrait être 0.

Dépannage de la réplication

Les problèmes courants pendant l’exploitation de la réplication incluent les erreurs de connexion et les incohérences de données. Ci-dessous sont présentés des cas d’erreur typiques et comment les résoudre.

1. Erreurs de connexion

Si Slave_IO_Running est No, cela signifie que l’esclave ne peut pas se connecter au maître. Essayez ce qui suit :

  • Vérifiez le nom d’hôte ou l’adresse IP du maître : Assurez-vous que l’adresse du maître est correcte.
  • Vérifiez les paramètres du pare-feu : Confirmez que le port requis (généralement 3306) est ouvert.

2. Incohérence des données

Si Last_Error contient un message d’erreur, une incohérence de données peut s’être produite entre le maître et l’esclave. Dans de tels cas, arrêtez l’esclave et appliquez les corrections avant de redémarrer la réplication.

STOP SLAVE;
# Restart after applying fixes
START SLAVE;

3. Réduction du retard de réplication

Le retard de réplication peut être causé par des limitations matérielles de l’esclave ou des problèmes réseau. Si nécessaire, la mise à niveau de la configuration du serveur esclave peut améliorer les performances.

La section suivante explore les problèmes de réplication plus en détail et propose des solutions supplémentaires.

6. Problèmes courants et leurs solutions

Lors d’une opération de réplication MySQL, divers problèmes peuvent survenir. Cette section explique les problèmes courants et comment les résoudre. Détecter les problèmes tôt et appliquer les correctifs appropriés aide à maintenir une opération stable du système.

1. Lorsque Slave_IO_Running est arrêté

Symptôme : Si Slave_IO_Running indique No dans la sortie de SHOW SLAVE STATUS, l’esclave ne peut pas se connecter au maître.

Causes et solutions :

  • Problèmes de réseau : S’il y a un problème de connectivité réseau, l’esclave ne peut pas accéder au maître. Vérifiez les paramètres du pare-feu et confirmez que le maître est joignable.
  • Nom d’hôte ou adresse IP du maître incorrect(e) : Vérifiez que le nom d’hôte ou l’adresse IP spécifié(e) dans CHANGE MASTER TO est correct(e).
  • Problèmes de privilèges utilisateur : Si l’utilisateur de réplication sur le maître n’a pas les privilèges suffisants, la connexion échouera. Confirmez que les privilèges appropriés ont été accordés en utilisant GRANT REPLICATION SLAVE.

2. Incohérence de données sur l’esclave

Symptôme : Si les données ne correspondent pas entre le maître et l’esclave, l’esclave peut se retrouver dans un état incohérent.

Causes et solutions :

  • Correction manuelle des données : Arrêtez l’esclave, corrigez manuellement la transaction problématique, puis redémarrez la réplication. STOP SLAVE; # Corriger les données si nécessaire START SLAVE;
  • Resynchronisation : Si l’incohérence est à grande échelle, effectuez une sauvegarde complète du maître et resynchronisez l’esclave.

3. Retard de réplication

Symptôme : Si Seconds_Behind_Master n’est pas 0 dans la sortie de SHOW SLAVE STATUS, l’esclave a du retard par rapport au maître. Idéalement, cette valeur devrait être aussi proche de 0 que possible.

Causes et solutions :

  • Limitations matérielles de l’esclave : Si le serveur esclave dispose de ressources insuffisantes, il peut ne pas suivre. La mise à niveau du matériel peut être efficace.
  • Optimisation des requêtes : Si les requêtes reçues du maître prennent trop de temps à s’exécuter sur l’esclave, un retard de réplication peut survenir. Ajouter des index et optimiser les requêtes peut réduire le temps de traitement.

4. Erreurs de permission de l’utilisateur de réplication

Symptôme : Si Last_Error affiche un message lié aux permissions, l’esclave peut ne pas avoir les privilèges suffisants pour se connecter au maître.

Causes et solutions :

  • Reconfigurer les privilèges de l’utilisateur : Assurez-vous qu’un utilisateur avec les privilèges appropriés existe sur le maître. Reconfigurez si nécessaire. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip_address'; FLUSH PRIVILEGES;

5. Croissance du journal binaire

Symptôme : Les journaux binaires du maître peuvent croître de manière excessive, consommant de l’espace disque.

Causes et solutions :

  • Rotation des journaux binaires : Supprimez ou archivez régulièrement les journaux binaires pour éviter une croissance excessive. En configurant expire_logs_days, vous pouvez supprimer automatiquement les journaux plus anciens qu’une période spécifiée. SET GLOBAL expire_logs_days = 7; # Supprimer les journaux de plus de 7 jours

En comprenant ces problèmes courants de réplication MySQL et leurs solutions, vous pouvez assurer une gestion opérationnelle plus fluide. La section suivante résume les points clés pour la gestion de la réplication.

7. Conclusion

MySQL replication est une fonctionnalité cruciale pour améliorer la cohérence des données et la fiabilité du système. Dans cet article, nous avons couvert tout, des concepts de base de la réplication MySQL aux procédures d’installation, aux pratiques de surveillance et aux méthodes de dépannage. Vous trouverez ci‑dessous un résumé des points clés pour une gestion efficace de la réplication.

Points clés à retenir

  1. Choisir le bon type de réplication
  • La réplication asynchrone offre des temps de réponse plus rapides et est idéale pour la répartition de charge. Cependant, si la fiabilité est la priorité, la réplication semi‑synchrone est plus adaptée. Choisissez en fonction des exigences de votre système.
  1. Utilisation efficace du GTID
  • Le GTID élimine la nécessité de spécifier les positions du journal binaire et permet une gestion fluide des transactions. Il est particulièrement utile dans les environnements avec plusieurs esclaves ou où le basculement est critique.
  1. Surveillance régulière de l’état
  • Utilisez SHOW MASTER STATUS et SHOW SLAVE STATUS pour surveiller régulièrement l’état opérationnel des serveurs maître et esclave. Une action rapide dès la détection d’anomalies minimise les risques tels que l’incohérence des données ou le retard.
  1. Maîtriser le dépannage de base
  • Les problèmes courants de réplication incluent les erreurs de connexion des esclaves, les incohérences de données et le retard. Comprendre les solutions de base pour chaque problème assure un fonctionnement plus fluide.
  1. Gestion du journal binaire
  • Étant donné que les journaux binaires peuvent occuper un espace disque important, il est recommandé de configurer expire_logs_days pour un nettoyage automatique et d’effectuer une maintenance régulière.

La réplication MySQL n’est pas une tâche ponctuelle. Une surveillance continue et une maintenance appropriée sont essentielles. En révisant régulièrement l’état du système et en ajustant les configurations lorsque cela est nécessaire, vous pouvez construire et maintenir un système de bases de données hautement fiable.

Nous espérons que ce guide vous aidera à mieux comprendre et mettre en œuvre la réplication MySQL. Nous vous souhaitons des opérations de réplication fluides et stables.