Mettre en oeuvre la réplication MySQL

Testé sur Debian Lenny et Debian Squeeze – MySQL 5.0 et 5.1

La réplication MySQL permet de maintenir en temps réel une copie d’une ou plusieurs bases d’un serveur maître vers un serveur esclave. Ce n’est pas à proprement parler une technique de sauvegarde, mysqldump est plus adapté pour ça, mais c’est très efficace pour implémenter un système à tolérance de panne. La mise en oeuvre n’est pas très compliquée mais un peu laborieuse et demande un minimum de rigueur dans la planification des étapes.

Les tâches à réaliser seront les suivantes :

sur le serveur maître

Créer un utilisateur MySQL, ici ce sera replicuser avec le privilège « REPLICATION SLAVE », et le droit de connexion depuis le serveur esclave.

CREATE USER 'replicuser'@'serveur.esclave' IDENTIFIED BY  '<mot_de_passe>';
GRANT REPLICATION SLAVE ON * . * TO  'replicuser'@'serveur.esclave' IDENTIFIED BY  '<mot_de_passe>' ;

Configurer l’enregistrement des logs binaires dans le fichier /etc/mysql/my.cnf

[mysqld]
# Un nombre entre 1 et 2^32-1, unique dans la communauté de réplication
server-id		= 123456
# Fichier des logs binaires
log_bin			= /var/log/mysql/mysql-bin.log
# Fichier des logs d'erreur (facultatif)
log_error		= /var/log/mysql/mysql-bin.err
# Liste des bases à répliquer
binlog_do_db	= base1, base2
# Si on veut répliquer toutes les bases, on ignorera binlog_do_db
# et on exclura les bases système avec bin_log_ignore_db :
#binlog_ignore_db	= mysql, information_schema
# Durée de rétention des logs
expire_logs_days	= 10
# Taille maxi des logs
max_binlog_size	= 100M

redémarrer le serveur MySQL :

/etc/init.d/mysql restart

sur le serveur esclave

Donner un server-id dans le fichier /etc/mysql/my.cnf

[mysqld]
# Un nombre entre 1 et 2^32-1, unique dans la communauté de réplication
server-id		= 456789

redémarrer le serveur MySQL :

/etc/init.d/mysql restart

Les deux serveurs sont maintenant configurés, il ne reste plus qu’à initialiser les bases sur le serveur esclave et démarrer la réplication. Les étapes détaillées sont les suivantes :

sur le serveur maître

Verrouiller les tables en écriture :

FLUSH TABLES WITH READ LOCK;

Noter les valeurs « File » et « Position » de l’enregistrement des logs binaires avec la commande « SHOW MASTER STATUS ». Ces valeurs serviront à positionner les paramètres « MASTER_LOG_FILE » et « MASTER_LOG_POS » sur l’esclave :

SHOW MASTER STATUS;
+------------------+----------+--------------------------+------------------+
| File             | Position | Binlog_Do_DB             | Binlog_Ignore_DB |
+------------------+----------+--------------------------+------------------+
| mysql-bin.000029 |      106 | base1, base2             |                  |
+------------------+----------+--------------------------+------------------+
1 row in set (0.00 sec)

sur le serveur esclave

Recopier les bases du serveur maître avec la commande mysqldump. On suppose ici que le serveur esclave puisse faire une connexion MySQL en root sur le serveur maître :

mysqldump --host serveur.maitre --user root --password=mot.de.passe base1 | mysql --user root --password=mot.de.passe base1
mysqldump --host serveur.maitre --user root --password=mot.de.passe base2 | mysql --user root --password=mot.de.passe base2

sur le serveur maître

Déverrouiller les tables :

UNLOCK TABLES;

sur le serveur esclave

Stopper les threads esclaves :

STOP SLAVE;

Appliquer les paramètres de réplication. Les paramètres « MASTER_LOG_FILE » et « MASTER_LOG_POS » ont été récupérés précédemment sur le maître :

CHANGE MASTER TO \
	MASTER_HOST='serveur.maitre', \
	MASTER_USER='replicuser', \
	MASTER_PASSWORD='mot_de_passe', \
	MASTER_LOG_FILE='mysql-bin.000029', \
	MASTER_LOG_POS=106;

Lancer la réplication :

START SLAVE;

On peut vérifier l’état de la réplication avec la commande MySQL « SHOW PROCESSLIST »

sur le serveur maître

SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 27037
   User: replicuser
   Host: serveur.maitre:53723
     db: NULL
Command: Binlog Dump
   Time: 1172
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

sur le serveur esclave

SHOW PROCESSLIST\G
 
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 155886
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1625
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

L’initialisation de la réplication étant assez fastidieuse, un petit script simplifiera la vie. En voici un plutôt basique, à configurer avec les valeurs de réplication réelles, et à exécuter sur le serveur esclave. Les tests sont sommaires, je ne donne aucune garantie sur le bon fonctionnement du script dans votre environnement!

#!/bin/bash
# --------------------------------------
# Template initialisation de la réplication MySQL
# A exécuter sur le serveur esclave
# François Grange 2012
# --------------------------------------
# Inits
# User MySQL esclave avec droits root
MYSQL_LUSER="root"
MYSQL_LPASSWD="" # Saisie interactive
# Serveur maitre
MYSQL_RHOST="adresse.du.serveur.maitre"
# User MySQL maitre avec droits root
MYSQL_RUSER="root"
MYSQL_RPASSWD="" # Saisie interactive
# User MySQL de réplication
MYSQL_REPL_USER="replicuser"
MYSQL_REPL_PASSWD="mot_de_passe"
# Port SSH du serveur maitre
PSSH=22
# Bases à synchroniser
dbl="base1 base2"
 
echo "+++$(date) Debut $0"
 
# Saisie du mot de passe root local
while [ ${#MYSQL_LPASSWD} -eq 0 ]; do
	read -r -s -p "Mot de passe $MYSQL_LUSER MySQL local : " MYSQL_LPASSWD
done
 
# Test de connexion à la base mysql locale
sql="USE mysql;"
req=`mysql -u $MYSQL_LUSER -p$MYSQL_LPASSWD -e "$sql"`
if [ $? -ne 0 ]; then
	echo -e "\n(E) $(date) Impossible de se connecter à la base locale mysql avec l'utilisateur $MYSQL_LUSER. Abandon du traitement"
	exit 1
else
	echo -e "\n(I) $(date) Connexion réussie à la base locale mysql."
fi
 
# Saisie du mot de passe root distant
while [ ${#MYSQL_RPASSWD} -eq 0 ]; do
	read -r -s -p "Mot de passe $MYSQL_RUSER MySQL $MYSQL_RHOST : " MYSQL_RPASSWD
done
 
# Test de connexion à la base mysql distante
sql="USE mysql;"
req=`mysql --host $MYSQL_RHOST -u $MYSQL_RUSER -p$MYSQL_RPASSWD -e "$sql"`
if [ $? -ne 0 ]; then
	echo -e "\n(E) $(date) Impossible de se connecter à la base mysql de $MYSQL_RHOST avec l'utilisateur $MYSQL_RUSER. Abandon du traitement"
	exit 1
else
	echo -e "\n(I) $(date) Connexion réussie à la base mysql de $MYSQL_RHOST."
fi
 
# Verrouillage tables maitre
sql="FLUSH TABLES WITH READ LOCK;"
req=`mysql --host $MYSQL_RHOST -u $MYSQL_RUSER -p$MYSQL_RPASSWD -e "$sql"`
if [ $? -ne 0 ]; then
	echo -e "(E) $(date) Impossible de verrouiller les tables du serveur maitre. Abandon du traitement"
	exit 1
fi
 
# Récupération de l'état du maitre
sql="SHOW MASTER STATUS;"
req=($(mysql --host $MYSQL_RHOST -u $MYSQL_RUSER -p$MYSQL_RPASSWD -e "$sql" -B -s))
if [ $? -ne 0 ]; then
	echo -e "(E) $(date) Echec de récupération de l'état du serveur maitre. Abandon du traitement"
	exit 1
fi
MYSQL_LOGFILE=${req[0]}
MYSQL_OFFSET=${req[1]}
echo "Fichier de log courant sur $MYSQL_RHOST : $MYSQL_LOGFILE"
echo "Offset courant sur $MYSQL_RHOST : $MYSQL_OFFSET"
 
# Dump individuel des bases
for db in $dbl
do
	echo "$(date) Dump base $db en cours..."
	mysqldump --host $MYSQL_RHOST --user $MYSQL_RUSER --password=$MYSQL_RPASSWD $db | mysql --user $MYSQL_LUSER --password=$MYSQL_LPASSWD $db
	if [ $? -gt 0 ]; then
		echo "$(date) Erreur Dump base $db sur $MYSQL_RHOST"
	else
		echo "$(date) Base $db synchronisée"
	fi
done
 
# Déverrouillage tables maitre
sql="UNLOCK TABLES;"
req=`mysql --host $MYSQL_RHOST -u $MYSQL_RUSER -p$MYSQL_RPASSWD -e "$sql"`
if [ $? -ne 0 ]; then
	echo -e "(E) $(date) Impossible de déverrouiller les tables du serveur maitre."
fi
 
# Arret des threads esclaves
sql="STOP SLAVE;"
req=`mysql -u $MYSQL_LUSER -p$MYSQL_LPASSWD -e "$sql"`
if [ $? -ne 0 ]; then
	echo -e "(E) $(date) Impossible de mettre fin à l'esclavage :-("
	exit 1
fi
 
# Application des paramètres de réplication sur l'esclave
sql="CHANGE MASTER TO \
	MASTER_HOST='$MYSQL_RHOST', \
	MASTER_USER='$MYSQL_REPL_USER', \
	MASTER_PASSWORD='$MYSQL_REPL_PASSWD', \
	MASTER_LOG_FILE='$MYSQL_LOGFILE', \
	MASTER_LOG_POS=$MYSQL_OFFSET;"
req=`mysql -u $MYSQL_LUSER -p$MYSQL_LPASSWD -e "$sql"`
echo $req
if [ $? -ne 0 ]; then
	echo -e "(E) $(date) Impossible d'appliquer les paramètres de réplication."
	exit 1
fi
 
# Démarrage de la réplication
sql="START SLAVE;"
req=`mysql -u $MYSQL_LUSER -p$MYSQL_LPASSWD -e "$sql"`
if [ $? -ne 0 ]; then
	echo -e "(E) $(date) Impossible de démarrer la réplication."
fi

Lien

La doc officielle

No votes yet.
Please wait...

3 réactions sur “Mettre en oeuvre la réplication MySQL”

  1. François

    Le script sert juste à initialiser la réplication, après ça marche tout seul!

    No votes yet.
    Please wait...
  2. email to fax

    Merci pour cette article, j’ai fait le teste ça marche très bien.

    Est ce que ce script doit etre lancer au démarrage de serveur

    No votes yet.
    Please wait...
  3. Réplication MySQL sécurisée avec stunnel | Ta vie on s'en fout!

    […] la redondance des bases MySQL entre plusieurs serveurs, j’en avais déjà parlé ici. L’inconvénient avec cette réplication basique est qu’elle ne pas sécurisée, les […]

    No votes yet.
    Please wait...

Laisser une réponse