Le monde de MySQL et MariaDB

Guide de réplication MySQL : configuration, types et dépannage pour la haute disponibilité

Applications / Divers
目次

1. Qu’est-ce que la réplication MySQL ? Aperçu et cas d’utilisation

La réplication MySQL est une fonctionnalité qui synchronise une copie d’une base de données vers un autre serveur 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 où la réplication MySQL est utilisée et comment elle fonctionne.

Aperçu de la réplication MySQL

La réplication MySQL consiste en un serveur maître et un ou plusieurs serveurs esclaves, partageant le contenu de la base de données sur plusieurs serveurs. Plus précisément, le serveur maître enregistre les mises à jour dans le journal binaire, et le serveur esclave lit et applique ces mises à jour pour rester synchronisé. Cela permet aux services de continuer même si le serveur maître tombe en panne, en basculant vers un serveur esclave.

Cas d’utilisation de la réplication MySQL

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

  • Haute disponibilité : Minimiser les temps d’arrêt en basculant vers un serveur esclave en cas de panne.
  • Équilibrage de charge : Distribuer les requêtes en lecture seule vers les serveurs esclaves pour réduire la charge sur le maître.
  • Protection des données et sauvegarde : Puisque la réplication copie les données en temps réel, elle peut également servir de solution de sauvegarde.

Types de réplication

La réplication MySQL a les types suivants, en fonction de la manière dont les données sont synchronisées :

  • Réplication asynchrone : Le maître n’attend pas que l’esclave confirme la réception des mises à jour, ce qui permet des réponses plus rapides. Cependant, certaines données peuvent ne pas atteindre l’esclave en cas de panne.
  • Réplication semi-synchronisée : Le maître attend qu’au moins un esclave confirme la réception des données avant de procéder. Cela offre une fiabilité plus élevée mais peut être légèrement plus lent.

Dans la section suivante, nous expliquerons les concepts de base de la réplication MySQL, y compris les journaux binaires et les GTID.

2. Concepts de base de la réplication MySQL

Pour comprendre la réplication MySQL, il est essentiel de connaître le rôle du journal binaire et du GTID (Global Transaction ID), qui assurent tous deux une réplication précise des données.

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 dans le journal binaire et les distribue aux esclaves. Le serveur esclave applique ces journaux pour mettre à jour ses données, en maintenant le même état que le maître.

Journal binaire et journal de relais

La réplication MySQL repose sur deux journaux clés :

  1. Journal binaire
  • Le journal binaire enregistre les changements de données (INSERT, UPDATE, DELETE, etc.) sur le serveur maître. Cela garantit que l’esclave peut maintenir le même état que le maître.
  1. Journal de relais
  • Le journal de relais est stocké sur le serveur esclave et contient le journal binaire reçu du maître. Le thread SQL de l’esclave exécute ce journal de relais de manière séquentielle pour appliquer les changements.

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

Le GTID attribue un ID unique à chaque transaction, assurant la cohérence sur plusieurs esclaves. Avec le GTID, le suivi de la position du journal binaire n’est pas nécessaire, et seules les transactions non appliquées sont automatiquement appliquées, simplifiant la gestion.

Avantages du GTID

  • Identification unique : Chaque transaction a un GTID unique, rendant clair quelles transactions ont été appliquées.
  • Récupération facile : Après un redémarrage, seules les transactions non appliquées sont réappliquées automatiquement.
  • Gestion efficace : Le GTID simplifie la gestion de la réplication dans les environnements importants avec plusieurs esclaves.

Pour activer le GTID, définissez gtid_mode=ON et enforce_gtid_consistency=ON sur les serveurs maître et esclave. Cela active la réplication basée sur GTID.

Dans la section suivante, nous couvrons la configuration étape par étape de la réplication MySQL.

3. Étapes pour configurer la réplication MySQL

Cette section explique comment configurer la réplication MySQL étape par étape. En suivant ces instructions, vous pouvez mettre en place un environnement maître-esclave de base et obtenir une synchronisation des données en temps réel.

Configuration du serveur maître

D’abord, modifiez le fichier de configuration du serveur maître (généralement my.cnf ou my.ini) pour activer la journalisation binaire et assigner un ID de serveur.

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

Configuration du serveur esclave

Ensuite, modifiez le fichier de configuration du serveur esclave pour définir un ID de serveur unique et spécifier les informations du maître.

  1. Modifier le fichier de configuration
  • Attribuez un server-id unique (par ex., 2) au serveur esclave. L’ID du serveur doit différer de celui du maître.
    [mysqld]
server-id=2
  • Il est également courant d’activer read_only=ON pour empêcher les écritures non intentionnelles sur l’esclave.
  1. Configurer les informations maître sur l’esclave
  • Exécutez la commande suivante sur le serveur esclave, en spécifiant l’hôte maître, l’utilisateur, le fichier de journal binaire et la position du journal.
    CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123;
  • Utilisez les valeurs MASTER_LOG_FILE et MASTER_LOG_POS obtenues à partir de la sortie de SHOW MASTER STATUS du maître.
  1. Démarrer la réplication
  • Exécutez la commande suivante sur le serveur esclave pour démarrer la réplication.
    START SLAVE;

Vérifier le statut de la réplication

Vérifiez si la réplication entre le maître et l’esclave fonctionne correctement.

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

Dans la section suivante, nous explorerons les options de configuration avancées pour la réplication MySQL, y compris les différences entre la réplication asynchrone et semi‑synchrone ainsi que les configurations basées sur GTID.

4. Types de réplication et applications

La réplication MySQL se décline en deux types principaux selon la méthode de synchronisation : réplication asynchrone et réplication semi‑synchrone. Comprendre les différences et savoir quand utiliser chaque type aide à optimiser les performances et la fiabilité. Cette section couvre également les avantages d’utiliser GTID (Identifiant de Transaction Global) dans les configurations de 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 répond immédiatement au client dès qu’une transaction est terminée, sans attendre que l’esclave applique la mise à jour. Cela garantit une excellente réactivité, ce qui la rend adaptée aux systèmes de répartition de charge. Cependant, en cas de défaillance, les transactions qui n’ont pas encore été appliquées sur l’esclave peuvent être perdues.

2. Réplication semi‑synchrone

Dans la réplication semi‑synchrone, le serveur maître attend qu’au moins un esclave ait reçu les données avant de répondre au client. Cela améliore la cohérence des données mais augmente le temps réponse des transactions, car le maître doit attendre une confirmation. La réplication semi‑synchrone est idéale pour les environnements où la cohérence des données et la fiabilité sont prioritaires.

Réplication avec GTID

GTID (Identifiant de Transaction Global) attribue un identifiant unique à chaque transaction, assurant la cohérence entre les serveurs maître et esclave. L’activation de GTID simplifie 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 : GTID permet à l’esclave d’identifier automatiquement les transactions non appliquées, garantissant la cohérence.
  • Gestion Simplifiée : GTID élimine le besoin de spécifier manuellement les positions des journaux binaires, facilitant les opérations de basculement et de récupération.

Configuration de la Réplication GTID

Pour activer GTID, ajoutez les options suivantes aux 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

Une fois GTID activé, la configuration de la réplication sur l’esclave avec la commande CHANGE MASTER TO gérera automatiquement la réplication basée sur GTID.

Dans la section suivante, nous expliquerons les pratiques de maintenance et de surveillance de la Réplication MySQL.

5. Maintenance et Surveillance de la Réplication

Pour exploiter efficacement la Réplication MySQL, une maintenance et une surveillance régulières sont essentielles. Cette section explique comment vérifier l’état de la réplication et comment gérer les erreurs courantes.

Comment Vérifier l’État de la Réplication

Utilisez les commandes suivantes pour surveiller la synchronisation entre les serveurs maître et esclave.

Vérifier l’État du Maître

Sur le serveur maître, exécutez SHOW MASTER STATUS pour afficher le fichier de journal binaire actuel et la position. Cela montre les dernières mises à jour à envoyer aux esclaves.

SHOW MASTER STATUS;

Les champs clés incluent :

  • File : Nom du fichier de journal binaire actuel
  • Position : Position actuelle dans le journal binaire
  • Binlog_Do_DB et Binlog_Ignore_DB : Bases de données incluses ou exclues de la réplication

Vérifier l’État de l’Esclave

Sur le serveur esclave, exécutez SHOW SLAVE STATUS pour vérifier la santé de la réplication.

SHOW SLAVE STATUSG;

Les champs importants incluent :

  • Slave_IO_Running et Slave_SQL_Running : Les deux doivent être Yes si la réplication fonctionne normalement.
  • Seconds_Behind_Master : Indique de combien l’esclave est en retard en secondes. Idéalement, cela devrait être 0.

Dépannage de la Réplication

Les problèmes courants dans la réplication incluent les erreurs de connexion et les incohérences de données. Voici des cas d’erreur typiques et leurs solutions.

1. Erreurs de Connexion

Si Slave_IO_Running est No, l’esclave ne peut pas se connecter au maître. Solutions possibles incluent :

  • Vérifier l’Hôte/IP du Maître : Assurez-vous que l’adresse correcte est définie.
  • Paramètres du Pare-feu : Confirmez que le port 3306 est ouvert et accessible.

2. Incohérence des Données

Si des erreurs apparaissent dans Last_Error, le maître et l’esclave peuvent avoir des données incohérentes. Pour résoudre :

STOP SLAVE;
# Fix the data manually
START SLAVE;

Pour des incohérences majeures, resynchronisez en restaurant une sauvegarde complète du maître.

3. Retard de Réplication

Le retard de réplication peut résulter de limitations matérielles ou de problèmes réseau sur l’esclave. La mise à niveau du matériel ou l’optimisation des requêtes peut améliorer les performances.

La section suivante explique les problèmes courants de réplication et leurs solutions détaillées.

6. Problèmes Courants de Réplication et Solutions

Divers problèmes peuvent survenir pendant la Réplication MySQL. Cette section détaille les problèmes fréquents et comment les résoudre.

1. Slave_IO_Running est Arrêté

Problème : Si Slave_IO_Running est No, l’esclave ne peut pas se connecter au maître.

Causes et Solutions :

  • Problèmes Réseau : Vérifiez le pare-feu et la connectivité vers le maître.
  • Hôte/IP du Maître Incorrect : Vérifiez la configuration CHANGE MASTER TO.
  • Privilèges Utilisateur : Assurez-vous que l’utilisateur de réplication a les permissions correctes avec GRANT REPLICATION SLAVE .

2. Incohérence des Données de l’Esclave

Problème : Les données diffèrent entre le maître et l’esclave.

Causes et Solutions :

  • Correction Manuelle : Arrêtez l’esclave, corrigez les données incohérentes, puis redémarrez la réplication. STOP SLAVE; # Fix data START SLAVE;
  • Resynchronisation Complète : Pour des mismatches sévères, réimportez une sauvegarde du maître.

3. Retard de Réplication

Problème : Seconds_Behind_Master est supérieur à 0, ce qui signifie que l’esclave est en retard.

Causes et solutions :

  • Limitations matérielles : Mettez à niveau les spécifications du serveur esclave.
  • Optimisation des requêtes : Améliorez les index et requêtes pour réduire le temps de traitement sur l’esclave.

4. Erreurs de privilèges de l’utilisateur de réplication

Problème : Les erreurs dans Last_Error indiquent des privilèges insuffisants.

Solution :

  • Accorder les privilèges corrects : Assurez-vous que les droits de réplication sont appropriés.
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip'; FLUSH PRIVILEGES;

5. Croissance du journal binaire

Problème : Les journaux binaires du maître croissent de façon excessive, consommant l’espace disque.

Solution :

  • Rotation des journaux : Utilisez expire_logs_days pour purger automatiquement les anciens journaux.
    SET GLOBAL expire_logs_days = 7; # Purge les journaux de plus de 7 jours

En comprenant et en se préparant à ces problèmes courants, les administrateurs peuvent maintenir des opérations de réplication stables.

7. Conclusion

La réplication MySQL est une fonctionnalité essentielle pour assurer la cohérence des données et la fiabilité du système. Cet article a couvert les bases de la réplication, les procédures d’installation, la surveillance et le dépannage. Vous trouverez ci‑dessous un récapitulatif des points clés.

Points clés à retenir

  1. Choisir le bon type de réplication
  • La réplication asynchrone offre rapidité et équilibrage de charge, tandis que la réplication semi‑synchrone offre une fiabilité accrue. Choisissez en fonction des exigences du système.
  1. Exploiter le GTID
  • Le GTID simplifie la réplication en supprimant la nécessité de spécifier les positions du journal binaire, ce qui le rend précieux pour les environnements grands ou critiques.
  1. Surveiller régulièrement l’état
  • Utilisez SHOW MASTER STATUS et SHOW SLAVE STATUS fréquemment pour détecter les anomalies tôt et minimiser les risques.
  1. Compétences de dépannage du maître
  • Familiarisez‑vous avec la gestion des problèmes spécifiques à la réplication tels que les erreurs de connexion, les incohérences et le retard.
  1. Gérer les journaux binaires
  • Prévenez les problèmes d’utilisation du disque en configurant expire_logs_days et en faisant tourner les journaux régulièrement.

La réplication MySQL nécessite une surveillance et une maintenance continues, pas seulement une configuration initiale. En vérifiant régulièrement l’état et en ajustant les configurations au besoin, vous pouvez construire et maintenir un système de base de données hautement fiable.

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