Blog ENI : Toute la veille numérique !
💥 Un livre PAPIER acheté
= La version EN LIGNE offerte pendant 1 an !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. MySQL 8
  3. Réplication
Extrait - MySQL 8 Administration et optimisation
Extraits du livre
MySQL 8 Administration et optimisation
2 avis
Revenir à la page d'achat du livre

Réplication

Généralités sur la réplication

MySQL dispose d’un système natif de réplication, simple à mettre en place et pouvant être utile dans de nombreuses situations. Quand un serveur (l’esclave) réplique les données d’un autre serveur (le maître), l’esclave est automatiquement synchronisé par rapport à son maître. La plupart des applications ayant MySQL pour SGBD utilisent la réplication pour aider à tenir la charge, pour augmenter la disponibilité de la base de données, pour simplifier la mise en place de sauvegardes, pour décharger le maître de grosses requêtes consommatrices de ressources et pour bien d’autres raisons encore.

Ce chapitre va, entre autres, vous expliquer quels sont les problèmes que la réplication peut résoudre, comment configurer un maître et un esclave et ce qu’il convient de faire si la réplication ne fonctionne plus.

1. Utilité de la réplication

La réplication est susceptible de vous aider pour un grand nombre de problèmes courants avec les bases de données :

  • La tenue à la montée en charge des lectures : bien souvent, une application ne fonctionne au départ qu’avec un seul serveur de bases de données. Mais si le trafic augmente, on arrive très vite à un point où le serveur n’a plus les capacités de faire face à toutes les lectures et toutes les écritures. Dans les applications Web, les lectures sont généralement majoritaires par rapport aux écritures. Dans ce cas, en mettant en place des serveurs esclaves, il devient possible de transmettre les écritures sur le maître et de lire sur l’un des esclaves. Attention pour les applications où les écritures sont majoritaires, ce modèle ne fonctionne pas (nous y reviendrons).

  • Une aide aux sauvegardes : sauvegarder ses données est bien sûr indispensable mais l’impact sur le serveur est souvent loin d’être négligeable. En effectuant la sauvegarde sur un esclave, qui contient une copie des données du maître, il est beaucoup plus simple de diminuer l’impact des sauvegardes sur l’application. Attention, faire une sauvegarde sur un esclave signifie qu’il...

Mise en place de la réplication

1. Réplication maître-esclave(s)

a. Configuration

images/07EI02N.png

Commençons par le cas le plus simple : celui où le maître et l’esclave sont fraîchement installés, c’est-à-dire sans aucune donnée. Il vous faudra accomplir les étapes suivantes :

  • Créer un utilisateur pour la réplication sur le maître :

    L’esclave doit pouvoir se connecter sur le maître (IO_THREAD). Il faut donc créer un compte dédié sur le maître avec le droit REPLICATION SLAVE. En réalité, bien souvent, il est pratique de créer aussi un compte symétrique sur l’esclave (utile si l’esclave peut être promu en maître, dans ce cas la réplication devra s’exécuter dans le sens opposé) et d’ajouter le droit REPLICATION CLIENT qui permet des commandes importantes pour le monitoring de la réplication :

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *.* to 
'repli_user'@'IP_esclave' IDENTIFIED BY 'mon_mdp'; 
  • Configurer le maître :

    Il faut activer les journaux binaires sur le maître et déclarer un identifiant appelé server_id, qui devra être unique parmi tous les serveurs associés par la réplication. Ces modifications se font dans le fichier my.cnf/my.ini et nécessitent un redémarrage du serveur :

[mysqld] 
log_bin = /var/lib/mysql/mysql-bin 
server_id = 100 
  • Configurer l’esclave :

    Chaque esclave doit lui aussi avoir un server_id unique. Il est inutile d’activer les journaux binaires sauf si l’esclave est lui-même maître d’autres esclaves :

[mysqld] 
server_id = 101 
  • Saisir les coordonnées de réplication :

    Vous obtenez les coordonnées de réplication sur le maître avec l’instruction suivante :

mysql> SHOW MASTER STATUS; 
+---------------+----------+--------------+------------------+ 
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+---------------+----------+--------------+------------------+ 
| mysql-bin.004 | 106      |              |                  | 
+---------------+----------+--------------+------------------+...

Résolution des problèmes opérationnels courants

1. Empêcher la réplication de certaines requêtes

Par défaut, toutes les écritures du maître sont écrites dans les journaux binaires. Ces journaux binaires sont copiés sur les esclaves, donc les esclaves répliquent toutes les requêtes du maître. Cependant, dans certains cas, vous ne souhaiterez pas répliquer certaines requêtes, par exemple si vous voulez avoir un esclave qui ne contient pas un des schémas du maître. Dans ce cas, vous devrez utiliser un filtre de réplication.

Le filtrage est possible sur le maître à l’aide des options binlog-do-db et binlog-ignore-db ou sur les esclaves à l’aide des options replicate-do-db et replicate-ignore-db. Sauf exception, vous préférerez toujours les filtres sur les esclaves. En effet, un filtre sur le maître va empêcher certaines requêtes d’être enregistrées dans les journaux binaires. Or, dans le pire des cas, vous pouvez avoir besoin de restaurer vos données en restaurant d’abord une sauvegarde puis en appliquant toutes les écritures stockées dans les journaux binaires. Si des requêtes n’ont pas été enregistrées dans les journaux binaires, les écritures correspondantes seront perdues.

Un autre point important est que les règles de filtrage ne fonctionnent pas de la même manière selon le format des journaux binaires.

Avec le format SBR (Statement-Based Replication), seule la base courante est vérifiée. Par exemple, si vous utilisez binlog-do-db=db1, l’ordre INSERT suivant sera tout de même répliqué :

mysql> USE db1;  
Database changed  
mysql > INSERT INTO db2.t1 values(10); 

Inversement, la requête suivante ne sera pas répliquée, bien que la table t2 appartienne à la base db1 que nous répliquons :

mysql> USE db2;  
Database changed  
mysql > INSERT INTO db1.t2 values(10); 

Avec le format RBR (Row-Based Replication), les ordres de modification sont répliqués si les objets auxquels ils s’appliquent sont concernés par les règles. Si la table modifiée n’appartient pas à la base db1 alors elle ne sera pas répliquée...

Réplication et scalabilité

1. Scalabilité en lecture

L’une des utilisations les plus courantes de la réplication est de permettre d’offrir à une application plusieurs serveurs pour effectuer les lectures. En effet, si toutes les écritures doivent être exécutées sur le maître, les lectures peuvent en théorie être effectuées indifféremment sur le maître ou sur n’importe quel esclave, et dans la majorité des applications utilisant MySQL, les lectures sont majoritaires par rapport aux écritures.

Comme il a déjà été mentionné, la réalité est différente : la réplication MySQL est asynchrone, il n’est donc pas possible de savoir si un esclave a exactement les mêmes données que le maître. Notez que le retard de réplication donné par la variable Seconds_behind_master dans le résultat de SHOW SLAVE STATUS n’est d’ailleurs pas un indicateur fiable puisque la granularité du compteur est la seconde.

Si le maître exécute 1000 écritures par seconde, un retard de 100 ms (qui apparaîtra comme étant 0 avec Seconds_behind_master) signifie un retard de 100 requêtes.

Par conséquent, l’application devra sans doute avoir une logique suffisamment fine pour savoir quelles lectures peuvent...

Fonctionnalités avancées

1. Identifiants de transaction

Depuis la version 5.6, on peut configurer MySQL pour que chaque transaction ait un identifiant unique (GTID - Global Transaction IDentifier). Cet identifiant est beaucoup plus intéressant que les classiques coordonnées de réplication car il est le même sur tous les serveurs impliqués dans un système de réplication. Lors de la promotion d’un esclave, le principal problème est de reconfigurer correctement la réplication sur tous les esclaves restants. En effet, il faut calculer les nouvelles coordonnées de réplication pour chacun des esclaves restants, ce qui est en pratique très compliqué et fastidieux (voir le chapitre Haute disponibilité pour plus de détails). Les GTID résolvent ce problème.

Pour pouvoir utiliser les GTID, il faut d’abord activer quelques options sur chacun des serveurs :

[mysqld] 
log-bin 
log_slave_updates 
gtid_mode = ON 

Il faut ensuite redémarrer chacun des serveurs. La réplication se configure maintenant légèrement différemment :

mysql> CHANGE MASTER TO MASTER_HOST = 'ip_master', MASTER_USER = 'mon_user', 
MASTER_PASSWORD = 'mon_mdp', MASTER_AUTO_POSITION = 1; 

Notez qu’avec MySQL 5.6, il est obligatoire de redémarrer l’ensemble des serveurs au même moment, car la réplication ne peut pas fonctionner si certains serveurs utilisent les GTID et pas d’autres. Cette limitation est levée depuis MySQL 5.7. Une procédure est disponible pour mettre à jour la configuration serveur par serveur. Cette procédure étant assez complexe, le mieux est de consulter la documentation en ligne : https://dev.mysql.com/doc/refman/8.0/en/replication-mode-change-online-enable-gtids.html

Une procédure similaire est disponible pour la version 5.6 avec Percona Server.

Autre amélioration depuis MySQL 5.7 : il n’est plus nécessaire d’activer les journaux binaires sur tous les serveurs. Vous pouvez donc seulement conserver les journaux binaires sur les esclaves qui sont susceptibles d’être promus maîtres.

L’administration de la réplication avec les GTID peut être déroutante si vous n’êtes pas préparé....