MySQL Réplication

La Réplication MySQL

La réplication permet de copier les données d'un serveur de base de données

MySQL (appelé source) vers un ou plusieurs serveurs de base de données
MySQL (appelés répliques). La réplication est asynchrone par défaut ; les
répliques n'ont pas besoin d'être connectées en permanence pour recevoir les
mises à jour d'une source. Selon la configuration, vous pouvez répliquer toutes
les bases de données, des bases de données sélectionnées ou même des tables
sélectionnées au sein d'une base de données.

Les avantages de la réplication dans MySQL incluent :

 Solutions de scale-out : répartition de la charge entre plusieurs

répliques pour améliorer les performances. Dans cet environnement,
toutes les écritures et mises à jour doivent avoir lieu sur le serveur
source. Les lectures, en revanche, peuvent avoir lieu sur une ou
plusieurs répliques. Ce modèle peut améliorer les performances des
écritures (puisque la source est dédiée aux mises à jour), tout en
 augmentant considérablement la vitesse de lecture sur un nombre
croissant de répliques.
 Sécurité des données : étant donné que la réplique peut suspendre le
processus de réplication, il est possible d'exécuter des services de
sauvegarde sur la réplique sans corrompre les données sources
correspondantes.
 Analyse - des données en direct peuvent être créées sur la source,
tandis que l'analyse des informations peut avoir lieu sur la réplique
sans affecter les performances de la source.
 Distribution de données longue distance : vous pouvez utiliser la
réplication pour créer une copie locale des données à utiliser sur un
site distant, sans accès permanent à la source.

La réplication peut être utilisée dans de nombreux environnements différents à

des fins diverses. Cette section fournit des notes générales et des conseils sur
l'utilisation de la réplication pour des types de solutions spécifiques.

Utilisation de la réplication pour les sauvegardes

 Sauvegarde d'une réplique à l'aide de mysqldump

 Sauvegarde des données brutes à partir d'une réplique
 Sauvegarde d'une source ou d'une réplique en la mettant en lecture seule

Pour utiliser la réplication comme solution de sauvegarde, répliquez les données

de la source vers une réplique, puis sauvegardez la réplique. La réplique peut
être suspendue et arrêtée sans affecter le fonctionnement en cours de la source,
ce qui vous permet de produire un instantané efficace des données « en
direct » qui nécessiteraient autrement l'arrêt de la source.
La manière de sauvegarder une base de données dépend de sa taille et de la
façon dont vous sauvegardez les données uniquement ou les données et l'état de
la réplique afin de pouvoir reconstruire la réplique en cas de panne. Il existe
donc deux choix :

 Si vous utilisez la réplication comme solution pour sauvegarder les

données sur la source et que la taille de votre base de données n'est
pas trop importante, l’outil mysqldump peut être adapté.

 Pour les bases de données plus volumineuses, où mysqldump serait

peu pratique ou inefficace, vous pouvez sauvegarder les fichiers de
données brutes à la place. L'utilisation de l'option des fichiers de
données brutes signifie également que vous pouvez sauvegarder les
journaux binaires et relais qui permettent de recréer la réplique en cas
de défaillance de la réplique.

L'utilisation de mysqldump pour créer une copie d'une base de données vous
permet de capturer toutes les données de la base de données dans un format qui
permet d'importer les informations dans une autre instance de MySQL Server.
Comme le format des informations est des instructions SQL, le fichier peut
facilement être distribué et appliqué aux serveurs en cours d'exécution dans le
cas où vous auriez besoin d'accéder aux données en cas d'urgence. Cependant, si
la taille de votre ensemble de données est très importante, mysqldump peut
s'avérer peu pratique.

L' utilitaire client mysqldump effectue des sauvegardes logiques , en produisant

un ensemble d'instructions SQL qui peuvent être exécutées pour reproduire les
définitions d'objet de base de données et les données de table d'origine. Il vide
une ou plusieurs bases de données MySQL pour les sauvegarder ou les
transférer vers un autre serveur SQL. La commande mysqldump peut également
générer une sortie au format CSV, autre texte délimité ou XML.
La commande mysqldump est fréquemment utilisée pour créer une instance vide
ou une instance comprenant des données sur un serveur de réplication dans une
configuration de réplication. Les options suivantes s'appliquent au vidage et à la
restauration des données sur les serveurs sources de réplication et les réplicas.

Les différents types de réplications

 Asynchrone
 Semi-synchrone
 Synchrone complète

La réplication Asynchrone
La réplication asynchrone est le mode de réplication par défaut de MySQL. Elle
permet à un serveur primaire de transmettre les modifications de données à un
ou plusieurs serveurs secondaires, sans attendre une confirmation immédiate de
ces derniers.

Avantages de la réplication asynchrone

1. Performances élevées :
  Le primaire n'a pas besoin d'attendre une confirmation des serveurs
secondaires, ce qui réduit la latence et maintien des performances
élevées pour les transactions locales.

2. Simplicité :
 Facile à configurer et ne nécessite pas de gestion complexe des
threads ou des accusés de réception (accusés de réception).

3. Bonne scalabilité en lecture :

 Les serveurs secondaires peuvent gérer des charges de lecture
importantes sans impacter le primaire.

4. Compatible avec de nombreux scénarios :

 Idéal pour les scénarios où un léger décalage entre le primaire et les
secondaires est acceptable.

Inconvénients de la réplication asynchrone

1. Risque de perte de données :

 Si le serveur primaire tombe en panne avant que les modifications
ne soient transférées aux serveurs secondaires, ces modifications
peuvent être perdues.

2. Décalage possible (lag) :

 Les serveurs secondaires peuvent prendre du retard par rapport au
primaire si la charge est élevée ou si la connexion réseau est lente.

3. Non adapté pour les environnements critiques :

 Les environnements qui nécessitent une forte cohérence des
données (par exemple, les applications bancaires) ne doivent pas
utiliser ce mode.
La réplication Semi-synchrone

La réplication semi-synchrone est un mode de réplication dans MySQL qui

équilibre la sécurité des données et la performance. Contrairement à la
réplication asynchrone, où le serveur primaire continue son exécution sans
attendre que les répliques confirment la réception des données, la réplication
semi-synchrone impose un certain niveau de synchronisation entre le primaire et
au moins une réplique.

Caractéristiques principales :

1. Engagement des transactions :

 Lorsque le serveur principal exécute une transaction, il envoie les
modifications (événements du binlog) à ses réponses.
 Le primaire ne considère la transaction comme terminée qu'après
avoir reçu l’accusé de réception (ACK) d'au moins une réponse,
indiquant qu'elle a reçu et écrit les événements dans son propre log
relay.
2. Pas besoin d'attendre l'application :
 La réponse n'a pas besoin d'appliquer les modifications dans sa
base de données pour envoyer l'accusé de réception. Elle doit
seulement confirmer qu'elle a reçu et stocké les modifications.
3. Tolérance aux pannes :
 Si aucune réponse ne répond (par exemple, si toutes tombent en
panne), la réplication retourne automatiquement en mode
asynchrone pour que le primaire continue de fonctionner sans
attendre.

Comparaison des modes :

 Laten Cohére Risque de perte
Mode Cas d'utilisation
ce nce de données
Asynchrone Faible Faible Élevé Sites web, analyses
Moyen Moyenn Commerce
Semi-synchrone Faible
ne e électronique, journaux
Critiques
Groupe Élevée Forte Très faible
d'applications
Synchrone Variab
Faible Moyenne Réporting, archivage
complète le

Comparaison avec d'autres modes :

Quand le primaire
Mode considère la transaction Avantages Inconvénients
comme confirmée

Après avoir écrit la Risque de perte de

Rapide, peu de
Asynchrone transaction dans son données si le primaire
supplément.
binlog. tombe en panne.

Après réception d'un Compromis

Semi-
ACK d'au moins une entre sécurité Légère latence ajoutée.
synchrone
réponse. et performance.

Après application des

Synchrone Sécurité Très prêté, surtout avec
modifications sur toutes
complète maximale. plusieurs répliques.
les répliques.

Avantages de la réplication semi-synchrone :

 1. Réduction du risque de perte de données :
 En mode asynchrone, si le primaire tombe en panne avant que les
réponses reçoivent les données, ces données sont perdues.
 Avec la semi-synchronisation, le primaire garantit qu'au moins une
réplique dispose des données.

2. Meilleure performance que la réplication synchrone complète :

 Contrairement à la synchronisation complète, la semi-
synchronisation n'attend pas que toutes les réponses appliquent les
transactions, ce qui minimise la latence.

3. Tolérance à un certain niveau de panne :

 Si une réplique devient inaccessible, le primaire peut continuer de
fonctionner après un délai, bien qu'il passe temporairement en
mode asynchrone.

Avantages de la réplication semi-synchrone :

1. Réduction du risque de perte de données :

 En mode asynchrone, si le primaire tombe en panne avant que les

réponses reçoivent les données, ces données sont perdues.
 Avec la semi-synchronisation, le primaire garantit qu'au moins une
réplique dispose des données.

2. Meilleure performance que la réplication synchrone complète :

 Contrairement à la synchronisation complète, la semi-

synchronisation n'attend pas que toutes les réponses appliquent les
transactions, ce qui minimise la latence.

3. Tolérance à un certain niveau de panne :

  Si une réplique devient inaccessible, le primaire peut continuer de
fonctionner après un délai, bien qu'il passe temporairement en
mode asynchrone.

Cas d'utilisation :

1. Environnements critiques :
o Idéal pour les systèmes où la perte de données est inacceptable
(ex. : banques, e-commerce), mais où la performance reste
importante.

2. Basculement rapide :
o Associée à un système de basculement (failover) automatisé,
comme MySQL Inno DB Cluster ou Group Replication, la semi-
synchronisation assure que les répliques disposent des données
nécessaires pour reprendre rapidement les opérations.

La réplication semi-synchrone offre un bon compromis entre sécurité et

performances, en garantissant que les données sont disponibles sur au moins une
réplique avant que le primaire ne poursuive. C'est un choix judicieux pour les
systèmes où une perte de données limitée est tolérable, mais où la performance
reste critique.

La Réplication de Groupe MySQL (Group Replication)

La réplication de groupe (Group Replication) est une fonctionnalité native de

MySQL qui fournit une solution de haute disponibilité et de tolérance aux
pannes pour les bases de données. Elle repose sur une architecture distribuée où
plusieurs serveurs (nœuds) travaillent ensemble comme un cluster. Tous les
nœuds peuvent être actifs et accepter des modifications, ce qui permet une
réplication multi-primaire.
Caractéristiques principales

1. Multi-primaire :
 Tous les nœuds du groupe peuvent accepter des écritures, mais la
réplication s'assure que les modifications sont cohérentes sur tous
les nœuds.
2. Tolérance aux pannes :
 Si un nœud tombe en panne, les autres continuent à fonctionner
sans interruption.
3. Consensus automatique :
 Les modifications sont validées à l'aide de protocoles de consensus
comme le Paxos ou le Raft, garantissant une cohérence forte.
4. Isolation transactionnelle :
 Utilise des protocoles pour garantir que les transactions sont
conformes à la règle ACID même dans un environnement distribué.
5. Scalabilité en lecture :
 Les nœuds secondaires peuvent être utilisés pour répartir les
charges de lecture.
6. Réplication basée sur des certificats :
 Une transaction est d'abord validée localement, puis certifiée pour
s'assurer qu'elle ne crée pas de conflits avec d'autres transactions
dans le groupe.
Types de Modes

 La réplication de groupe prend en charge deux modes principaux :

1. Mode Multi :

 Tous les nœuds acceptent des écritures.

 Un protocole de consensus gère les conflits en cas de modifications
simultanées.

2. Mode avec un seul maître :

 Seul un nœud (le primaire) accepte des écritures.

 Les autres nœuds sont en mode lecture seule.
 Ce mode est utilisé pour simplifier la gestion des conflits.

1. Vérifiez les permissions du répertoire

Assurez-vous que l'utilisateur sous lequel vous exécutez la commande dispose

des droits d'écriture sur le répertoire /etc/mysqlrouter/.
  Vérifiez les permissions actuelles :

bash
ls -ld /etc/mysqlrouter/

Si le répertoire n'existe pas, créez-le avec les permissions appropriées :

sudo mkdir -p /etc/mysqlrouter/

sudo chown $USER:$USER /etc/mysqlrouter/

3. Désactivez temporairement AppArmor pour MySQL Router

Si le problème est lié à AppArmor, vous devez ajuster son profil pour autoriser
MySQL Router à écrire dans le répertoire.

 Éditez le fichier AppArmor lié à MySQL Router :

bash
CopierModifier
sudo nano /etc/apparmor.d/[Link]

 Ajoutez les lignes suivantes à la fin pour autoriser l'accès au répertoire

/etc/mysqlrouter/ :

/etc/mysqlrouter/ rw,

/etc/mysqlrouter/** rw,

Pour configurer MySQL Router avec votre architecture de réplication multi-

primaire (Group Replication), voici les étapes à suivre pour une configuration
optimale :
1. Vérification des prérequis

 Adresse Bind : Assurez-vous que chaque nœud utilise une adresse

bind_address correcte correspondant à son IP dans le fichier [Link].
 Utilisateur administratif : Vous utilisez déjà sifec_admin avec tous les
privilèges nécessaires (prérequis respecté).
 Cluster Inno DB : Vérifiez que le cluster Inno DB est bien opérationnel
sur votre réplication multi-primaire.
 Ports : Assurez-vous que les ports nécessaires pour la communication
sont ouverts entre les serveurs et MySQL Router (par défaut, 3306 pour
MySQL et 6446 pour MySQL Router).

2. Installation de MySQL Router

Vous avez déjà installé MySQL Router sur une machine séparée. Assurez-vous
qu'il est configuré pour communiquer avec les nœuds du cluster.

Commande pour configurer MySQL Router :

Exécutez la commande suivante sur la machine où MySQL Router est installé :

mysqlrouter --bootstrap sifec_admin@[Link]:3306 --user=mysqlrouter --

directory /etc/mysqlrouter --conf-base-port=6446 --conf-bind-address=[Link]

 --bootstrap sifec_admin@[Link]:3306 : Utilise l'utilisateur

sifec_admin pour récupérer la configuration du cluster.
 --user=mysqlrouter : Définit l'utilisateur système pour exécuter MySQL
Router.
  --directory /etc/mysqlrouter : Spécifie le répertoire où les fichiers de
configuration seront générés.
 --conf-base-port=6446 : Définit le port de base pour MySQL Router.
 --conf-bind-address=[Link] : Permet à MySQL Router d'écouter sur
toutes les interfaces réseau.

3. Configuration du fichier [Link]

Une fois la commande exécutée, un fichier de configuration sera généré

(souvent dans /etc/mysqlrouter/[Link]).

Vérifiez ou modifiez les paramètres suivants pour correspondre à votre

architecture :

[routing:read_write]
bind_address=[Link]
bind_port=6446
mode=read-write
destinations=[Link]:3306,[Link]:3306,[Link]:3306

[routing:read_only]
bind_address=[Link]
bind_port=6447
mode=read-only
destinations=[Link]:3306,[Link]:3306,[Link]:3306

4. Démarrage de MySQL Router

Une fois la configuration finalisée, démarrez MySQL Router avec la commande
suivante :

mysqlrouter --config /etc/mysqlrouter/[Link]

Pour l'exécuter en tant que service :

sudo systemctl start mysqlrouter

Vérifiez son statut :

sudo systemctl status mysqlrouter

5. Tester la connexion via MySQL Router

Pour vérifier que tout fonctionne correctement, essayez de vous connecter à

votre base de données via MySQL Router :

Connexion Read-Write :
mysql -u sifec_admin -p -h [Link] -P 6446
Connexion Read-Only :
mysql -u sifec_admin -p -h [Link] -P 6447

6. Points de validation supplémentaires

 Vérifiez que MySQL Router redirige correctement les connexions vers les
nœuds en testant les fonctionnalités de lecture et écriture.
 Consultez les journaux de MySQL Router pour identifier d'éventuelles
erreurs :

tail -f /var/log/mysqlrouter/[Link]
En cas de problème ou de besoin de redémarrage après des modifications,
utilisez :

sudo systemctl restart mysqlrouter

Avec cette configuration, votre MySQL Router sera correctement intégré à votre
architecture multi-primaire et redirigera efficacement les connexions selon le
mode souhaité (lecture-écriture ou lecture seule).

Pour supprimer un cluster MySQL Inno DB existant, suivez les étapes ci-
dessous. Assurez-vous de disposer des privilèges suffisants avec votre
utilisateur administrateur (par exemple, sifec_admin).

1. Se connecter à un nœud du cluster

Connectez-vous à un des nœuds du cluster avec l'utilisateur administrateur

:

mysql -u sifec_admin -p -h [Link] -P 3306

2. Vérifier le cluster existant

Pour vérifier les informations sur le cluster actuel, exécutez la commande

suivante dans MySQL Shell :

SHOW STATUS LIKE 'group_replication%';

 Si vous utilisez MySQL Shell (mysqlsh), vous pouvez également lister les
nœuds du cluster en mode JavaScript :

[Link]().status();

3. Désactiver la réplication de groupe sur tous les nœuds

Sur chaque nœud du cluster, désactivez la réplication de groupe avec la

commande SQL suivante :

STOP GROUP_REPLICATION;

4. Supprimer le cluster avec MySQL Shell

Une fois la réplication arrêtée sur tous les nœuds, ouvrez MySQL Shell
(mysqlsh) et connectez-vous au cluster :

mysqlsh --uri sifec_admin@[Link]:3306

7. Supprimez le cluster en exécutant cette commande JavaScript :

8. var cluster = [Link]();
9. [Link]();
10.

11.5. Vérifier la suppression

12.Vérifiez que le cluster a été dissous avec succès :

[Link] --uri sifec_admin@[Link]:3306

[Link]écutez cette commande pour confirmer qu'aucun cluster n'existe :

[Link]();
Si le cluster est bien supprimé, vous obtiendrez un message indiquant
qu'aucun cluster n'est configuré.

6. Nettoyer les métadonnées

Pour nettoyer les métadonnées spécifiques au cluster, exécutez ces

commandes SQL sur chaque nœud :

RESET PERSIST group_replication_group_name;

RESET PERSIST group_replication_start_on_boot;
RESET PERSIST group_replication_bootstrap_group;
RESET PERSIST group_replication_local_address;
RESET PERSIST group_replication_group_seeds;
RESET PERSIST group_replication_single_primary_mode;
RESET PERSIST group_replication_enforce_update_everywhere_checks;

7. Optionnel : Supprimer les utilisateurs et bases spécifiques au cluster

Si vous avez des utilisateurs spécifiques (comme

mysql_innodb_cluster_metadata), vous pouvez les supprimer :

DROP USER 'mysql_innodb_cluster_metadata'@'%';

Supprimez également les bases spécifiques au cluster si elles existent

(assurez-vous qu'elles ne contiennent pas de données critiques) :

DROP DATABASE mysql_innodb_cluster_metadata;

8. Redémarrer les nœuds

Pour finaliser le nettoyage, redémarrez les serveurs MySQL sur chaque nœud
:

sudo systemctl restart mysql

Résultat attendu

Après avoir suivi ces étapes, le cluster InnoDB sera complètement supprimé,
et les nœuds seront disponibles pour une utilisation indépendante ou pour
configurer un nouveau cluster.