Réplication entre 2 serveurs MySQL

mysql

Cela fait maintenant plus de 15 ans que j’utilise MySQL comme principal Système de Gestion de Bases de Données (SGBD) car il est simple à installer, à administrer et à configurer.

MySQL est un moteur de bases de données à la fois léger, gratuit et relativement performant.

Mes serveurs sont la plupart du temps hébergés sur des serveurs dédiés et il arrive plusieurs fois par an que ceux-ci soient en maintenance.

Se pose alors le problème de savoir comment faire pour rendre disponible mes bases de données lors d’une maintenance ou d’un crash, sans avoir à faire des dizaines de manipulations contraignantes.

Mais comment assurer une certaine disponibilité de vos bases de données sous MySQL ? La réplication de vos bases de données répond parfaitement à cette question.

Fonctionnement de la réplication MySQL

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 ou plusieurs serveurs esclave.

Il ne s’agit absolument pas d’une sauvegarde de vos données: la réplication permet d’assurer la disponibilité de vos données, en aucun cas leur pérénité.

mysql replication

mysql replication

La réplication MySQL est basée sur le fait que le serveur maître va garder la trace de toutes les évolutions de vos bases (modifications, effacements, etc.) dans un fichier de log binaire.

Les esclaves pourront alors se connecter au maître pour lire les requêtes stockées dans ce fichier de log, puis les exécuter.

Il est très important de comprendre que le fichier de log binaire est simplement un enregistrement des modifications depuis un point fixe dans le temps (le moment où vous activez le log binaire).

Vos esclaves devront par conséquent avoir les mêmes données que votre MySQL maître avant l’activation des logs binaires.

Si vos esclaves n’ont pas le même jeu de données, alors votre réplication sera corrompue et échouera.

Notez que sous MySQL, plusieurs type de réplication sont envisageables:

  • maître -> un seul esclave
  • maître -> plusieurs esclaves
  • maître/esclave -> maître/esclave (double réplication dans les deux sens)

Dans ce tutoriel, nous mettrons en place une réplication maître -> esclave.

Vocabulaire lié à la réplication MySQL

Voici plusieurs définitions auxquelles vous pouvez vous reporter:

  • Le maître, ou master, est le serveur de bases de données de référence.
  • L’esclave, ou slave, est la machine sur laquelle les données seront répliquées.
  • Les binlogs sont des fichiers binaires activables sur tous serveurs MySQL qui permettent de logger les requêtes de mise à jour. Ces fichiers sont indispensables pour pouvoir répliquer un serveur MySQL.

Mise en place de la réplication sous MySQL

Tout d’abord sachez que les manipulations suivantes sont indépendantes de la plateforme. Vous pourrez donc mettre en place la réplication MySQL aussi bien sur des plateformes Linux que sur Windows.

D’autre part, les commandes SQL sont toutes saisies via une console MySQL avec l’utilisateur root.

Petit rappel: sous Windows la console MySQL est dans le fichier MySQL/bin/mysql.exe. Sous linux, il suffit de lancer mysql dans un shell.

Configuration du serveur MySQL Maitre

Edition du fichier de configuration

Editez le fichier my.cnf (ou my.ini sous Windows) et vérifier que les lignes suivantes sont présentes sinon ajoutez les:

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

 

Cela permettra d’activer les logs binaires et de donner un identifiant au serveur. Evidemment cet identifiant doit être unique sur le réseau.

Une fois les modifications effectuées, redémarrez le serveur MySQL.

 Connection au serveur MySQL

mysql -h ip_du_maitre -u root –pMotDePasse

 Création d’un utilisateur spécifique pour la réplication et affectation des droits

--on crée un utilisateur repli
GRANT REPLICATION SLAVE ON *.* TO repli@'%' IDENTIFIED BY 'password';
 
FLUSH PRIVILEGES;

 

 Arrêt de l’écriture sur la base

Pour que l’esclave dispose d’une copie de la base du maître, on va interdire l’écriture le serveur maître (mais la lecture est toujours possible):

FLUSH WITH READ LOCK;

Ensuite on récupère le nom du fichier binaire ainsi que la position courante:

SHOW MASTER STATUS;

ce qui retournera:

+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.007 | 126 | mabase,bar | foo,manual,mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.02 sec)

 

Le nom du fichier binaire est donc mysql-bin.007 et sa position est 126.

Au cas où le nom du fichier binaire et sa position ne seraient pas renseignés, prenez en nom de fichier “ et en position 4.

Faire un dump de la base de données

Il faut maintenant faire une sauvegarde de la base de données pour pouvoir la recharger sur le serveur esclave. Pour faire la sauvegarde on utilise mysqldump:

mysqldump -h ip_du_maitre -u root –pMotDePasse mabase > dump.sql

Configuration du serveur MySQL Esclave

 Chargement du dump

Copiez le dump fait précédemment sur le serveur MySQL maître sur le serveur esclave et chargez le via la commande suivante:

mysql -h ip_du_slave -u root –pMotDePasse mabase < dump.sql

ATTENTION: assurez-vous avant de lancer la commande qu’une base de données portant le même nom existe sur votre esclave et qu’elle est vide.

Edition du fichier de configuration

Editez le fichier my.cnf (ou my.ini sous Windows) et vérifier que les lignes suivantes sont présentes sinon ajoutez les:

[mysqld]
server-id=2

Configuration du maître sur le serveur MySQL esclave

Dans votre console MySQL, entrez la commande suivante en l’adaptant avec les informations récupérées:

CHANGE MASTER TO \
MASTER_HOST='ip_du_maitre', \
MASTER_USER='repli', \
MASTER_PASSWORD='password', \
MASTER_LOG_FILE='mysql-bin.007', \
MASTER_LOG_POS='126';

Démarrage de la réplication

Pour lancer la réplication, il suffit de taper la commande suivante:

START SLAVE;

Enfin sur la maître, on autorise de nouveau l’écriture sur la base de données via la commande:

UNLOCK TABLES;

Maintenance et commandes utiles

Voici plusieurs commandes qui vous permettrons de suivre l’état de votre réplication et de diagnostiquer divers problèmes:

  • SHOW SLAVE STATUS\G : qui affichera le status du serveur esclave
  • SHOW MASTER STATUS\G : qui affichera le status du serveur maître

Pour purger les fichiers de log binaire qui sont consommateurs en espace disque vous pourrez utiliser la commande suivante:

SHOW MASTER LOGS;
PURGE MASTER LOGS TO 'mysql-bin.000018';

Voilà j’espère que cet article vous sera utile :-)

2 Responses to Réplication entre 2 serveurs MySQL

  1. pon says:

    intéressant! mais vous n’avez pas expliqué comment, en cas de crash du master,faire que le slave devienne le master pour la disponibilité des données.
    Merci!

    • jhd says:

      Il suffit de passer alors le slave en master et les données seront de nouveau disponible. Une fois que votre ancien master sera réparé, il suffit de le déclarer en slave en refaisant la meme procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *