Configuration de la réplication MySQL asynchrone : un guide étape par étape

La réplication MySQL asynchrone reste l'un des éléments de base les plus utiles dans une configuration de base de données. Vous pouvez l'utiliser pour les réplicas de lecture, les sauvegardes plus sûres, les copies de rapport, le staging de migration et la reprise après sinistre. Le mot important est "asynchrone" : la source valide une transaction sans attendre que le réplica l'applique. Cela évite à la source de bloquer chaque écriture du réplica, mais cela signifie également que le réplica peut accuser un retard.

L'ancienne terminologie dans de nombreuses commandes MySQL utilise "maître" et "esclave". Les versions plus récentes de MySQL utilisent "source" et "réplica" dans des commandes telles que SHOW REPLICA STATUS et CHANGE REPLICATION SOURCE TO. Vous pouvez encore voir les anciennes commandes dans les anciens systèmes, exemples et scripts. Ce guide utilise source et réplica dans l'explication et montre d'abord la forme de commande la plus récente avec des notes lorsque la syntaxe plus ancienne diffère.

L'exemple utilise deux serveurs :

Source : 192.168.1.100
Réplica : 192.168.1.101
Utilisateur de réplication : repl_user
Périmètre : toutes les bases de données, sauf si vous filtrez intentionnellement

Faites-le d'abord dans un environnement de test si vous n'avez jamais exécuté la procédure auparavant. La configuration de la réplication est simple lorsque tout est propre. Elle devient stressante lorsque la source est occupée, que la sauvegarde est incohérente ou que le réplica contient déjà des données anciennes.

Avant de toucher à la configuration

Confirmez les bases :

Les deux serveurs exécutent des versions MySQL compatibles.
Le réplica peut atteindre la source sur le port MySQL, généralement 3306.
Vous disposez d'un accès administratif aux deux instances MySQL.
Vous pouvez modifier le fichier de configuration MySQL et redémarrer MySQL si nécessaire.
Le réplica est vide ou vous savez exactement quelles données existantes doivent être remplacées.
La source dispose de suffisamment d'espace disque pour les journaux binaires.

Sur l'hôte du réplica, testez l'accès réseau de base :

nc -vz 192.168.1.100 3306

Si nc n'est pas disponible, utilisez telnet ou les outils de connectivité de votre fournisseur de cloud. Corrigez les pare-feu, les groupes de sécurité, les adresses de liaison et le routage avant de configurer la réplication. Un utilisateur de réplication ne servira à rien si le chemin TCP est bloqué.

Configurer le serveur source

La source doit écrire les modifications dans les journaux binaires. Le réplica lit ces événements et les stocke dans les journaux de relais avant de les appliquer.

Modifiez le fichier de configuration MySQL sur la source. Les emplacements courants sont /etc/mysql/mysql.conf.d/mysqld.cnf, /etc/my.cnf ou un fichier inclus à partir de ces chemins. Ajoutez ou vérifiez ces paramètres sous [mysqld] :

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

server-id doit être unique sur chaque serveur de la topologie de réplication. log-bin active la journalisation binaire. binlog_format=ROW est une valeur par défaut pratique pour la plupart des configurations de réplication modernes car elle enregistre les modifications de ligne plutôt que de se fier au comportement de réexécution des instructions.

Soyez prudent avec binlog-do-db et binlog-ignore-db. Le filtrage semble pratique, mais il peut surprendre car le comportement basé sur les instructions dépend de la base de données par défaut sélectionnée par la session. Si vous avez besoin d'une réplication filtrée, concevez-la et testez-la délibérément. Pour une première configuration fiable, répliquez tout.

Redémarrez MySQL sur la source :

sudo systemctl restart mysql
# ou, sur certains systèmes
sudo systemctl restart mysqld

Vérifiez les paramètres :

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

log_bin doit être ON. Le server_id doit être non nul et unique.

Créer un utilisateur de réplication

Créez un compte dédié sur la source que le réplica utilisera. Limitez l'hôte à l'adresse du réplica si votre conception réseau le permet :

CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'use_a_real_secret_here';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101';

Le nom du privilège est toujours REPLICATION SLAVE dans les privilèges MySQL, même si la documentation plus récente dit souvent "réplica" ailleurs.

Testez la connexion à partir de l'hôte du réplica :

mysql -h 192.168.1.100 -u repl_user -p

Si cela échoue, corrigez l'authentification et le réseau maintenant. Les causes courantes sont bind-address sur la source, les règles de pare-feu, l'inadéquation de l'hôte utilisateur, la résolution DNS vers une adresse différente et l'incompatibilité du plugin d'authentification avec un client plus ancien.

Configurer le serveur réplica

Sur le réplica, configurez un server-id différent. Les journaux de relais sont généralement activés automatiquement pour la réplication, mais les nommer explicitement peut clarifier les opérations :

[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=ON

Pour une protection plus forte, envisagez super_read_only=ON après la configuration. read_only n'empêche pas tous les comptes privilégiés d'écrire. super_read_only est plus sûr pour les réplicas qui ne doivent jamais accepter les écritures d'applications, mais vous devrez peut-être le désactiver temporairement pour certaines tâches administratives.

Redémarrez MySQL sur le réplica et vérifiez :

sudo systemctl restart mysql

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';

Prendre un instantané initial cohérent

Le réplica doit démarrer à partir d'une copie de données qui correspond à une position spécifique du journal binaire. Si l'instantané et la position ne correspondent pas, la réplication peut démarrer et être toujours erronée.

Pour les bases de données fortement InnoDB, mysqldump --single-transaction est généralement la méthode cohérente la plus simple. Elle évite un verrouillage de lecture global long pour les tables transactionnelles. Incluez --source-data=2 sur les versions plus récentes de MySQL afin que la sauvegarde enregistre le fichier journal binaire source et la position sous forme de ligne commentée. Les versions plus anciennes utilisent --master-data=2.

Exécutez ceci sur un hôte de confiance pouvant se connecter à la source :

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --source-data=2 \
  > source_dump.sql

Si votre version de MySQL ne prend pas en charge --source-data, utilisez :

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  > source_dump.sql

--single-transaction est sûr pour la cohérence InnoDB, mais il ne rend pas les tables MyISAM non transactionnelles cohérentes de la même manière. Si vous avez encore des tables MyISAM, planifiez une fenêtre de maintenance ou utilisez une autre méthode de sauvegarde qui vous donne un instantané cohérent.

Vérifiez dans la sauvegarde les coordonnées de réplication enregistrées :

grep -m 1 -E "CHANGE (MASTER|REPLICATION SOURCE)" source_dump.sql

Vous devriez voir une ligne commentée contenant un fichier journal binaire et une position. Gardez-la. Vous l'utiliserez lorsque vous pointerez le réplica vers la source, sauf si vous choisissez la réplication basée sur GTID.

Importer l'instantané sur le réplica

Transférez la sauvegarde vers le réplica en utilisant votre méthode sécurisée normale :

scp source_dump.sql db-replica:/tmp/source_dump.sql

Sur le réplica, assurez-vous de ne pas écrire accidentellement sur un ensemble de données existant de type production. Si ce réplica doit être une copie propre, supprimez et recréez uniquement ce que votre plan de migration dit de remplacer. Importez ensuite :

mysql -u root -p < /tmp/source_dump.sql

Pour les grosses sauvegardes, exécutez l'importation dans screen ou tmux et surveillez l'espace disque. Une importation échouée parce que /var/lib/mysql ou /tmp est plein fait perdre du temps et peut vous laisser avec un réplica à moitié chargé.

Pointer le réplica vers la source

Sur MySQL 8.0.23 et plus récent, utilisez CHANGE REPLICATION SOURCE TO :

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1234;

Remplacez le fichier et la position par les valeurs de la sauvegarde. Si vous utilisez une syntaxe plus ancienne, l'équivalent est :

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='use_a_real_secret_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

Si votre environnement utilise les GTID, la configuration est différente. Vous configurez normalement le mode GTID sur les deux serveurs, restaurez une sauvegarde qui préserve l'état GTID et utilisez SOURCE_AUTO_POSITION=1 au lieu d'un fichier et d'une position. Ne mélangez pas les instructions GTID et fichier-position à la légère ; choisissez une approche et testez-la.

Une brève note sur la réplication GTID

La réplication GTID est souvent plus facile à utiliser après sa configuration car MySQL suit les transactions par identifiants de transaction globaux au lieu de vous obliger à gérer un fichier journal binaire et une position manuellement. Elle est particulièrement utile lors du basculement, des changements de source et des reconstructions de réplica.

Cela ne signifie pas que vous devez l'activer à la légère au milieu d'une migration. Les deux serveurs ont besoin de paramètres GTID compatibles, et votre processus de sauvegarde ou de vidage doit préserver le bon état GTID. Un modèle courant consiste à configurer :

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

Ensuite, après avoir restauré une sauvegarde compatible GTID, configurez le réplica avec :

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_AUTO_POSITION=1;

Utilisez ceci uniquement si l'historique GTID de la source et du réplica est propre et compris. Si vous n'êtes pas sûr, la réplication fichier-position est plus facile à raisonner pour une première configuration. Le pire choix est de mélanger des exemples des deux approches jusqu'à ce que la réplication démarre mais que l'historique des transactions ne soit pas ce que vous pensez.

Démarrez la réplication :

START REPLICA;

Syntaxe plus ancienne :

START SLAVE;

Vérifiez l'état :

SHOW REPLICA STATUS\G

Syntaxe plus ancienne :

SHOW SLAVE STATUS\G

Les champs clés sont :

Champ	Valeur saine	Ce qu'il vous indique
`Replica_IO_Running`	`Yes`	Le réplica peut se connecter et récupérer les événements du journal binaire.
`Replica_SQL_Running`	`Yes`	Le réplica peut appliquer les événements du journal de relais.
`Last_IO_Error`	vide	Les problèmes de réseau, d'identification ou de journal source s'affichent ici.
`Last_SQL_Error`	vide	Les conflits de données et les erreurs d'application s'affichent ici.
`Seconds_Behind_Source`	faible ou en baisse	Un indicateur approximatif du retard.

La sortie plus ancienne utilise Slave_IO_Running, Slave_SQL_Running et Seconds_Behind_Master.

Tester avec une petite écriture

Ne déclarez pas victoire après que les threads disent Yes. Créez une petite table de test ou insérez une ligne inoffensive dans un schéma de test existant sur la source, puis vérifiez qu'elle apparaît sur le réplica.

Exemple sur la source :

CREATE DATABASE IF NOT EXISTS repl_check;
CREATE TABLE IF NOT EXISTS repl_check.heartbeat (
  id INT PRIMARY KEY,
  checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
REPLACE INTO repl_check.heartbeat (id) VALUES (1);

Sur le réplica :

SELECT * FROM repl_check.heartbeat;

Cela détecte les erreurs simples telles que pointer vers la mauvaise source, filtrer la base de données ou utiliser des coordonnées obsolètes.

Sécuriser le canal de réplication

Si le trafic de réplication traverse un réseau non fiable, exigez TLS. Même à l'intérieur d'un réseau privé, de nombreuses équipes préfèrent désormais le trafic de base de données crypté car les limites du réseau changent avec le temps.

Au minimum, créez l'utilisateur de réplication et le canal afin que les informations d'identification ne soient pas partagées avec les comptes d'application. Pour les canaux basés sur TLS, configurez les certificats selon votre version de MySQL, puis incluez les options SSL dans la configuration de la source de réplication. Les options exactes varient selon la version et la politique de certificat, mais l'intention est la même : le réplica doit vérifier qu'il se connecte à la source attendue et protéger les informations d'identification et les modifications de lignes en transit.

Gardez également le privilège de l'utilisateur de réplication étroit. Il n'a pas besoin d'un accès DDL ou DML large. Si quelqu'un obtient ce mot de passe, le rayon d'explosion doit être limité à la lecture des journaux de réplication, et non à l'écriture des données d'application.

Problèmes de configuration courants

Si Replica_IO_Running est No, le réplica ne peut pas récupérer les événements. Vérifiez :

SOURCE_HOST est correct.
La source écoute sur l'adresse et le port attendus.
Les pare-feu et les groupes de sécurité autorisent le trafic.
L'hôte de l'utilisateur de réplication correspond à l'IP source du réplica.
Le mot de passe et le plugin d'authentification fonctionnent avec la version client/serveur du réplica.
Le fichier journal binaire demandé existe toujours sur la source.

Si Replica_SQL_Running est No, le réplica a récupéré les événements mais n'a pas pu les appliquer. Vérifiez Last_SQL_Error. Les clés en double signifient souvent que le réplica n'a pas été initialisé à partir de l'instantané correspondant exact ou que quelqu'un a écrit directement sur le réplica. Les lignes manquantes signifient souvent une dérive des données. Sauter une transaction avec SQL_SLAVE_SKIP_COUNTER peut faire avancer le thread, mais cela peut également rendre le réplica incorrect. Utilisez-le uniquement lorsque vous comprenez la transaction ayant échoué et acceptez le risque de divergence.

Si le retard est élevé juste après la configuration, laissez le réplica rattraper son retard et regardez si Seconds_Behind_Source diminue. Une importation de sauvegarde importante suivie du démarrage de la réplication peut laisser un arriéré. Si le retard augmente au lieu de diminuer, inspectez les E/S disque sur le réplica et le volume d'écriture sur la source.

Maintenir le réplica en bonne santé après la configuration

Configurez la conservation des journaux binaires sur la source afin que les réplicas puissent survivre à la maintenance et aux pannes. MySQL moderne utilise binlog_expire_logs_seconds :

[mysqld]
binlog_expire_logs_seconds=604800

Cet exemple conserve les journaux pendant environ 7 jours. Choisissez une valeur en fonction de vos besoins de récupération et de votre capacité disque. Les systèmes plus anciens peuvent utiliser expire_logs_days.

Surveillez l'état de la réplication et le retard. Au minimum, alertez lorsque l'un des threads de réplication s'arrête, lorsque le retard dépasse votre tolérance et lorsque l'utilisation du disque source augmente car les journaux binaires ne sont pas purgés. Pour les vérifications de cohérence des données, de nombreuses équipes utilisent les outils Percona Toolkit tels que pt-table-checksum et pt-table-sync, mais testez-les soigneusement avant de les exécuter sur des données de taille production.

Enfin, éloignez le trafic d'application du réplica jusqu'à ce que vous soyez sûr qu'il est en lecture seule, à jour et surveillé. Un réplica qui accepte des écritures accidentelles est pire que pas de réplica, car les dommages peuvent rester cachés jusqu'au basculement ou à la récupération.

La réplication asynchrone fonctionne bien lorsque l'instantané de départ, les coordonnées du journal binaire, les privilèges et la surveillance sont tous alignés. La plupart des configurations échouées proviennent de l'un de ces éléments supposé au lieu d'être vérifié.