Replication MySQL : Configuration Master-Slave pour la Haute Disponibilite

TL;DR — Résumé Rapide

Guide complet de replication MySQL master-slave pour la haute disponibilite : binary logs, configuration des slaves, surveillance du lag, failover et ProxySQL.

La replication MySQL permet a un serveur (le master) de transmettre chaque modification de donnees vers un ou plusieurs serveurs replica (slaves) en temps quasi reel. Le resultat est une architecture de haute disponibilite ou le trafic en lecture est distribue entre plusieurs noeuds, les sauvegardes peuvent etre effectuees depuis un slave sans impacter le master, et un slave peut etre promu en master en quelques minutes en cas de defaillance du serveur primaire. Ce guide couvre la configuration complete de la replication master-slave sur MySQL 8.0 : de la configuration des binary logs et de la synchronisation initiale des donnees jusqu’au monitoring du lag, a la recuperation d’une replication cassee, aux procedures de failover et a la separation lecture/ecriture avec ProxySQL.

Architecture de Replication

La replication MySQL fonctionne via trois composants travaillant de concert :

• Binary log (binlog) — le master enregistre chaque transaction validee dans un fichier de log sequentiel. Il existe trois formats : STATEMENT (enregistre le texte SQL), ROW (enregistre les valeurs avant/apres des lignes modifiees) et MIXED (utilise STATEMENT par defaut et bascule vers ROW pour les fonctions non deterministes). Utilisez toujours ROW en production.
• IO thread — un thread sur le slave se connecte au master, lit les nouveaux evenements du binary log et les ecrit dans le relay log local.
• SQL thread — un second thread sur le slave lit le relay log et rejoue les evenements contre la base de donnees locale, maintenant les donnees synchronisees.

Modes de Replication

Mode	Fonctionnement	Quand l’utiliser
Asynchrone (defaut)	Le master n’attend pas l’ACK du slave	HA generale, mise a l’echelle des lectures
Semi-synchrone	Le master attend qu’au moins un slave ecrive le relay log	Donnees financieres, risque de perte reduit
Group Replication	Multi-master avec protocole de consensus (Paxos)	Actif-actif, failover automatique

Prerequis

• Deux serveurs Linux avec MySQL 8.0 ou 8.4 (Ubuntu 22.04+ ou RHEL 9+)
• Acces root ou sudo sur les deux serveurs
• Connectivite reseau entre master et slave (port 3306 ouvert)
• Le server-id du master et du slave doivent etre des entiers uniques dans toute la topologie

Etape 1 : Configurer le Serveur Master

Editez la configuration MySQL sur le master :

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Ajoutez ou modifiez ces parametres dans la section [mysqld] :

[mysqld]
# ID unique pour ce serveur — doit etre different sur chaque noeud
server-id = 1

# Active le binary log — obligatoire pour la replication
log-bin = /var/log/mysql/mysql-bin
binlog-format = ROW

# Conserver 7 jours de binary logs
expire_logs_days = 7

# Synchronise le binlog sur le disque a chaque commit — evite la perte de donnees
sync_binlog = 1

# Replication basee sur GTID (recommandee pour MySQL 8.0+)
gtid_mode = ON
enforce_gtid_consistency = ON

Appliquez la configuration :

sudo systemctl restart mysql

Verifiez que le binary log est actif :

SHOW VARIABLES LIKE 'log_bin';
-- log_bin | ON

SHOW MASTER STATUS\G
-- File: mysql-bin.000003
-- Position: 1573

Etape 2 : Creer l’Utilisateur de Replication

Sur le master, creez un utilisateur dedie avec uniquement les permissions necessaires a la replication :

CREATE USER 'repl'@'192.168.1.102' IDENTIFIED BY 'ReplStr0ng!Pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.102';
FLUSH PRIVILEGES;

Remplacez 192.168.1.102 par l’IP reelle du slave. N’utilisez jamais % (n’importe quel hote) pour les utilisateurs de replication — restreignez par IP pour la securite.

Etape 3 : Prendre un Snapshot Coherent

Vous avez besoin d’un snapshot coherent a un instant donne du master pour initialiser le slave. Le flag --master-data enregistre automatiquement la position du binary log dans le fichier de dump.

# Option A : mysqldump (adapte pour les bases de donnees inferieures a ~50 Go)
mysqldump -u root -p \
  --all-databases \
  --master-data=2 \
  --single-transaction \
  --flush-logs \
  --routines \
  --triggers \
  > /tmp/master_dump.sql

# Option B : Percona XtraBackup (recommande pour les grandes bases, sans verrou de table)
xtrabackup --backup --user=root --password=monpass \
  --target-dir=/tmp/xtrabackup/
xtrabackup --prepare --target-dir=/tmp/xtrabackup/

Transferez le dump sur le slave :

scp /tmp/master_dump.sql utilisateur@192.168.1.102:/tmp/

Etape 4 : Configurer le Serveur Slave

Sur le slave, editez mysqld.cnf :

[mysqld]
# Doit etre different du master et de tous les autres slaves
server-id = 2

# Emplacement du relay log
relay-log = /var/log/mysql/mysql-relay-bin

# Evite les ecritures accidentelles sur le slave
read_only = 1
super_read_only = 1

# Necessaire si ce slave sera lui-meme un master (replication en chaine)
log_slave_updates = 1

# Correspond aux parametres GTID du master
gtid_mode = ON
enforce_gtid_consistency = ON

sudo systemctl restart mysql

Etape 5 : Importer le Snapshot et Demarrer la Replication

Restaurez le dump du master sur le slave :

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

Configurez le slave pour se connecter au master. Avec le mode GTID (recommande) :

CHANGE MASTER TO
  MASTER_HOST          = '192.168.1.101',
  MASTER_USER          = 'repl',
  MASTER_PASSWORD      = 'ReplStr0ng!Pass',
  MASTER_AUTO_POSITION = 1;

Sans GTID (base sur la position traditionnelle), trouvez la position dans le commentaire du dump :

grep "MASTER_LOG_FILE\|MASTER_LOG_POS" /tmp/master_dump.sql | head -5
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1573;

CHANGE MASTER TO
  MASTER_HOST     = '192.168.1.101',
  MASTER_USER     = 'repl',
  MASTER_PASSWORD = 'ReplStr0ng!Pass',
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS  = 1573;

Demarrez la replication :

START SLAVE;

Etape 6 : Surveiller la Sante de la Replication

SHOW SLAVE STATUS\G

Champs cles a verifier :

Slave_IO_Running: Yes          -- thread IO connecte et en cours d'execution
Slave_SQL_Running: Yes         -- thread SQL rejoue les evenements
Seconds_Behind_Master: 0       -- slave completement a jour
Last_IO_Error: (vide)          -- aucune erreur de connexion
Last_SQL_Error: (vide)         -- aucune erreur de reproduction

Pour un monitoring continu via performance_schema (MySQL 8.0+) :

SELECT
  CHANNEL_NAME,
  SERVICE_STATE,
  LAST_ERROR_MESSAGE,
  LAST_HEARTBEAT_TIMESTAMP
FROM performance_schema.replication_connection_status;

Gestion du Lag de Replication

Le lag de replication (Seconds_Behind_Master croissant) est courant sous des charges d’ecriture intensives :

Cause	Solution
Thread SQL mono-thread	Activez les workers de replication parallele
Requetes lentes sur le slave	Identifiez avec SHOW PROCESSLIST ; optimisez
Goulot d’etranglement E/S sur le slave	Deplacez les relay logs vers un stockage plus rapide
Grandes transactions en masse	Divisez en lots plus petits sur le master
Latence reseau	Co-localisez master et slaves dans le meme datacenter

Activer la replication parallele (MySQL 8.0) :

STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
START SLAVE SQL_THREAD;

Reparer une Replication Cassee

Quand Slave_SQL_Running: No apparait dans SHOW SLAVE STATUS :

Ignorer une Transaction en Erreur

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS\G

Ignorer par GTID (Plus Sur avec le Mode GTID)

STOP SLAVE;
-- Remplacez par le GTID du message Last_SQL_Error
SET GTID_NEXT = 'a1b2c3d4-1111-2222-3333-444444444444:1234';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

Resynchronisation Complete depuis le Master

STOP SLAVE;
RESET SLAVE ALL;
-- Reprenez le snapshot depuis l'Etape 3 et reconfigurez

Failover : Promouvoir un Slave en Master

Quand le master tombe en panne et que vous devez promouvoir un slave :

-- Sur le slave a promouvoir :
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0

STOP SLAVE;
RESET SLAVE ALL;

-- Desactive le mode lecture seule — ce serveur est maintenant le master
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

Mettez a jour les chaines de connexion de l’application vers la nouvelle IP du master et reconfigurez les slaves restants :

-- Sur les slaves restants :
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST          = '192.168.1.102',
  MASTER_AUTO_POSITION = 1;
START SLAVE;

Etape 7 : ProxySQL pour la Separation Lecture/Ecriture

ProxySQL se place entre l’application et MySQL, routant les ecritures vers le master et les lectures vers les slaves de maniere transparente.

# Installer ProxySQL (Ubuntu)
wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | sudo apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.x/$(lsb_release -sc)/ ./" \
  | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update && sudo apt install proxysql
sudo systemctl enable --now proxysql

Configurez via l’interface admin de ProxySQL (port 6032) :

-- Ajouter les serveurs : hostgroup 0 = ecriture (master), hostgroup 1 = lecture (slave)
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
  (0, '192.168.1.101', 3306),
  (1, '192.168.1.102', 3306);

-- Ajouter l'utilisateur de l'application
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
  ('appuser', 'AppPass123!', 0);

-- Router les requetes SELECT vers le hostgroup de lecture
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (1, 1, '^SELECT.*', 1, 1);

-- Appliquer et persister les changements
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;   SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

L’application se connecte a ProxySQL sur le port 6033 et tout le routage est gere de maniere transparente.

Comparaison : Replication MySQL vs Alternatives

Solution	Architecture	Failover	Mise a l’echelle en ecriture	Complexite
Replication MySQL	Master + slaves	Manuel ou MHA	Non (master unique)	Faible
MySQL Group Replication	Multi-master Paxos	Automatique	Limite	Moyenne
Galera Cluster (MariaDB)	Multi-master synchrone	Automatique	Oui	Moyenne
Vitess	MySQL avec sharding	Automatique	Oui (sharding)	Elevee
PlanetScale	Vitess gere	Gere	Oui	Faible (gere)
PostgreSQL Streaming	Primaire + standbys	Patroni/manuel	Non	Faible-Moyenne

Points d’Attention

• server-id doit etre globalement unique sur tous les noeuds — deux noeuds avec le meme ID corrompent silencieusement la replication
• read_only=1 ne bloque pas les utilisateurs SUPER — utilisez super_read_only=1 pour bloquer completement le slave
• Les grandes transactions bloquent le thread IO — utilisez pt-online-schema-change pour les grandes operations DDL
• Les differences de fuseau horaire causent des erreurs de replication — definissez default-time-zone='+00:00' sur tous les noeuds

Resume

• La replication MySQL utilise les binary logs, les threads IO et les threads SQL pour maintenir les slaves synchronises
• Utilisez binlog-format=ROW, sync_binlog=1 et le mode GTID (gtid_mode=ON) en production
• Surveillez Slave_IO_Running, Slave_SQL_Running et Seconds_Behind_Master en permanence
• Activez les workers de replication parallele pour reduire le lag sous des charges d’ecriture importantes
• Utilisez RESET SLAVE ALL pour une reconfiguration propre plutot que d’essayer de corriger un etat casse
• ProxySQL route de maniere transparente les lectures vers les slaves et les ecritures vers le master

Articles Connexes

• Guide d’Optimisation des Performances MySQL
• Guide de Depannage de la Replication MySQL
• Analyse et Optimisation du Slow Query Log MySQL