MySQL propose plusieurs stratégies pour la réplication, introduites à partir de la version 5.1.12. Ces stratégies influencent la manière dont les modifications de données sont enregistrées dans le log binaire (binlog), permettant ainsi la synchronisation entre un serveur maître et un ou plusieurs serveurs esclaves.

Modes de réplication et formats du log binaire

Il existe trois modes de réplication principaux, chacun correspondant à un format de binlog :

• Réplication basée sur les instructions (Statement-Based Replication, SBR) : Le binlog enregistre les instructions SQL exécutées sur le maître. Le format de binlog correspondant est STATEMENT.
• Réplication basée sur les lignes (Row-Based Replication, RBR) : Le binlog enregistre les modifications de données au niveau des lignes. Le format de binlog est ROW.
• Réplication hybride (Mixed-Based Replication, MBR) : Combine les deux approches. Le format de binlog est MIXED. En mode MIXED, SBR est le comportement par défaut, mais le système peut basculer automatiquement vers RBR dans certaines situations.

Changement dynamique du format du log binaire

Le format du binlog peut être modifié dynamiquement pendant l'exécution, sauf dans les cas suivants :

• Lors de l'exécution de procédures stockées ou de triggers.
• Si NDB (MySQL Cluster) est activé.
• Si la session courante utilise le mode RBR et que des tables temporaires sont ouvertes.

Basculement automatique de SBR à RBR en mode MIXED

En mode MIXED, le système passe automatiquement de SBR à RBR dans les scénarios suivants :

• Mise à jour d'une table NDB par une instruction DML.
• Utilisation de la fonction UUID() dans une fonction.
• Mise à jour de deux tables ou plus contenant des champs AUTO_INCREMENT.
• Exécution d'une instruction INSERT DELAYED.
• Utilisation d'une fonction définie par l'utilisateur (UDF).
• Création d'une vue qui nécessite le mode RBR, par exemple, si elle utilise la fonction UUID().

Configuration du mode de réplication

La configuration du format du binlog se fait généralement dans le fichier de configuration de MySQL (my.cnf ou my.ini) :

# Activer le log binaire et spécifier le nom de base des fichiers
log-bin=mysql-bin

# Spécifier le format du log binaire
# Options : STATEMENT, ROW, MIXED
binlog_format="STATEMENT"
# binlog_format="ROW"
# binlog_format="MIXED"

Il est également possible de modifier le format du binlog à la volée pour la session courante ou globalement :

-- Pour la session courante
SET SESSION binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';

-- Globalement (nécessite des privilèges suffisants)
SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_format = 'MIXED';

Avantages et inconvénients des modes SBR et RBR

Réplication basée sur les instructions (SBR)

Avantages :

• Technologie mature et éprouvée.
• Fichiers binlog généralement plus petits.
• Le binlog contient toutes les modifications SQL, utile pour l'audit de sécurité.
• Permet la restauration ponctuelle (point-in-time recovery).
• Les versions de MySQL maître et esclave peuvent différer (l'esclave pouvant être plus récent).

Inconvénients :

• Difficulté à répliquer certaines instructions, notamment celles impliquant des opérations non déterministes.
• Problèmes potentiels avec les UDF non déterministes.
• Les instructions utilisant les fonctions suivantes peuvent poser problème : LOAD_FILE(), UUID(), USER(), FOUND_ROWS(), SYSDATE() (sauf avec l'option --sysdate-is-now).
• INSERT ... SELECT peut générer plus de verrous au niveau des lignes que RBR.
• Les instructions UPDATE nécessitant un scan complet de table (sans index dans la clause WHERE) peuvent demander plus de verrous que RBR.
• Pour les tables InnoDB avec AUTO_INCREMENT, les instructions INSERT peuvent bloquer d'autres INSERT.
• Les instructions complexes peuvent consommer plus de ressources sur l'esclave par rapport à RBR, qui n'affecte que les lignes modifiées.
• Les fonctions stockées appelant NOW() peuvent avoir un comportement différent.
• Les UDF déterministes doivent être présentes sur l'esclave.
• La structure des tables doit être identique sur le maître et l'esclave pour éviter les erreurs de réplication.

Réplication basée sur les lignes (RBR)

Avantages :

• Réplication fiable et complète, car elle enregistre chaque modification de ligne.
• Compatible avec les mécanismes de réplication de la plupart des autres systèmes de bases de données.
• Souvent plus rapide si les tables sur l'esclave ont des clés primaires.
• Moins de verrous au niveau des lignes pour : INSERT ... SELECT, INSERT avec AUTO_INCREMENT, UPDATE/DELETE affectant peu de lignes.
• Moins de verrous lors des opérations INSERT, UPDATE, DELETE.
• Permet potentiellement la réplication multithread sur l'esclave.

Inconvénients :

• Les fichiers binlog sont beaucoup plus volumineux.
• Les opérations d'annulation complexes peuvent générer beaucoup de données dans le binlog.
• Lors d'une instruction UPDATE sur le maître, chaque ligne modifiée est écrite dans le binlog, ce qui peut entraîner des problèmes de concurrence d'écriture sur le binlog, contrairement à SBR qui n'écrit qu'une fois.
• Les valeurs BLOB importantes générées par les UDF peuvent ralentir la réplication.
• Il est difficile de savoir quelles instructions ont été exécutées à partir du binlog (sauf s'il n'est pas chiffré).
• Pour les tables non transactionnelles, l'utilisation de SBR est préférable pour éviter les incohérences de données entre maître et esclave.

Gestion des tables du système mysql

Les modifications des tables dans la base de données système mysql sont gérées comme suit :

• Les opérations directes INSERT, UPDATE, DELETE sur ces tables sont enregistrées selon le format défini par binlog_format.
• Les instructions d'administration comme GRANT, REVOKE, SET PASSWORD sont toujours enregistrées en mode SBR, quelle que soit la configuration.

Note : Le mode RBR résout de nombreux problèmes de doublons de clés primaires qui pouvaient survenir avec SBR.

Exemple : Comparaison des logs binaires SBR et RBR

Considérons l'instruction INSERT INTO db_allot_ids SELECT * FROM db_allot_ids;

Format STATEMENT :

BEGIN
/*!*/;
# at 173
#090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1244793942/*!*/;
insert into db_allot_ids select * from db_allot_ids
/*!*/;

Format ROW :

BINLOG '
hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=
'/*!*/;

Architecture maître-esclave MySQL

L'architecture maître-esclave est une solution mature pour la réplication, offrant plusieurs avantages :

• Distribution de la charge de lecture : Les requêtes de lecture peuvent être dirigées vers les serveurs esclaves, réduisant la pression sur le serveur maître.
• Sauvegardes : Les sauvegardes peuvent être effectuées sur les serveurs esclaves sans impacter les performances du maître.
• Haute disponibilité : En cas de défaillance du maître, un esclave peut être promu pour prendre le relais.

Évolution de l'architecture des esclaves

Dans les anciennes versions de MySQL, un seul thread gérait la réception des logs binaires du maître, leur analyse et leur exécution sur l'esclave. Cette approche présentait des inconvénients majeurs :

• Performance limitée : Le processus séquentiel entraînait une latence de réplication notable.
• Risque de perte de données : Si le maître subissait une défaillance irréparable pendant que l'esclave analysait ou exécutait les logs, les modifications récentes pouvaient être perdues définitivement. Ce risque était accentué sous forte charge sur l'esclave.

Pour pallier ces problèmes, les versions plus récentes de MySQL ont divisé le travail de l'esclave en deux threads distincts :

• Thread IO : Responsable de la réception des logs binaires du maître et de leur stockage dans les fichiers relais (relay logs).
• Thread SQL : Responsable de la lecture des fichiers relais et de l'exécution des modifications de données sur l'esclave.

Cette architecture à deux threads améliore considérablement les performances et réduit le risque de perte de données, bien que la réplication reste asynchrone et puisse encore présenter une certaine latence.

Alternatives pour une cohérence stricte

Pour garantir une cohérence absolue des données, MySQL Cluster est une solution, mais elle exigeait traditionnellement que toutes les données et index résident en mémoire, ce qui impliquait d'importantes exigences en RAM. Les évolutions futures de MySQL Cluster visent à autoriser le chargement partiel des données en mémoire pour une meilleure applicabilité.

Réplication semi-synchrone (Semi-Synchronous Replication)

Avant MySQL 5.5, la réplication était purement asynchrone, autorisant une latence potentielle entre maître et esclave. Cela garantissait la performance du maître (pas d'attente de confirmation de l'esclave) mais présentait un risque de perte de données si le maître tombait en panne avant que toutes les transactions validées ne soient répercutées sur l'esclave.

Depuis MySQL 5.5, la réplication semi-synchrone (via un plugin) offre une garantie accrue : le maître attend la confirmation qu'au moins un esclave a reçu et écrit le binlog dans son fichier relais avant de valider la transaction. Si cette confirmation n'arrive pas dans un délai imparti (timeout, par défaut 10 secondes), le maître rebascule en mode asynchrone jusqu'à recevoir la confirmation. Ce mode permet de garantir la sécurité des données au détriment d'une légère diminution du débit.

Ce mécanisme est implémenté via des plugins distincts pour le maître et l'esclave, qui doivent être installés.

Filtrage de la réplication

Il est possible de contrôler quelles données sont répliquées, soit côté maître, soit côté esclave.

Filtrage côté maître

Utilise les paramètres binlog_do_db et binlog_ignore_db dans la configuration du maître.

• binlog_do_db : Spécifie les bases de données dont les modifications doivent être enrgeistrées dans le binlog.
• binlog_ignore_db : Spécifie les bases de données dont les modifications ne doivent pas être enregistrées.

Avantages :

• Réduit la quantité de données écrites dans le binlog, diminuant la charge IO sur le maître et le réseau.
• Peut améliorer les performances globales de réplication.

Inconvénients :

• Le filtrage est basé sur la base de données courante de la session lors de l'exécution de la requête, pas sur la base de données cible de la modification. Si la base de données courante n'est pas celle spécifiée dans la configuration, l'événement pourrait ne pas être répliqué (ou inversement, être répliqué à tort). Ceci peut mener à des incohérences de données.

Filtrage côté esclave

Utilise les paramètres replicate_* dans la configuration de l'seclave.

• replicate_do_db : Bases de données à répliquer.
• replicate_ignore_db : Bases de données à ignorer.
• replicate_do_table : Tables spécifiques à répliquer.
• replicate_ignore_table : Tables spécifiques à ignorer.
• replicate_wild_do_table : Comme replicate_do_table, mais supporte les jokers (wildcards).
• replicate_wild_ignore_table : Comme replicate_ignore_table, mais supporte les jokers.

Avantages :

• Garantit qu'il n'y aura pas d'incohérence due au contexte de la base de données courante, car le filtrage se fait après la réception des données par le thread IO.

Inconvénients :

• Moins performant que le filtrage côté maître, car tous les événements sont d'abord transférés sur l'esclave, augmentant la charge réseau et l'écriture dans les relay logs.

Note importante : Avant MySQL 5.0, les mécanismes de filtrage étaient peu fiables. Il était crucial de maintenir une parfaite synchronisation des schémas entre maître et esclave pour éviter les erreurs de réplication.