Réplication

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 :

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

a. Configuration

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      |              |                  | 
+---------------+----------+--------------+------------------+...

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...

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...

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

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é....