Réplication MySQL Master-Slave et Master-Master

La réplication d’un base de données permet de disposer du même jeu de données à tout moment sur deux serveurs ou plus. MySQL permet d’automatiser la recopie des données entre une machine principale et plusieurs secondaires de façon unidirectionnelle (réplication master-slave) ou de façon bidirectionnelle entre 2 serveurs (réplication master-master). Dans ce tutoriel, nous allons mettre en place ces deux types de systèmes, le second étant une extension du premier.

replication mysql master-master

Si vous n’avez pas lu mon article à propos de la création d’une architecture serveurs distribuée, je vous invite à le faire, et spécialement mon exemple de mise en en oeuvre avec 2 serveurs, vous comprendrez peut-être mieux l’utilité d’une configuration de ce type. Pour les plus pressées, sachez simplement que je proposais de créer 2 serveurs web accueillant chacun sa propre base de données, les données étant strictement identiques et chaque insertion ou modification de données étant immédiatement recopiée.

separateur

Plantons le décor : la configuration

Pour mettre en place ce système je vais utiliser deux serveurs MySQL 5.5.34 situés sur le même réseau local avec les adresses IP 192.168.10.10 (nous appellerons cette machine « rouge ») et 192.168.10.20 (nous l’appellerons « bleu »).

Sur ces 2 serveurs nous utiliserons un utilisateur appelé « replication_user », dont le mot de passe sera « motdepasse ». Cet utilisateur aura bien entendu les privilèges nécessaires sur les bases de données que nous allons répliquer (dans cet exemple je prendrai toutes les bdd sans distinction).

Les manipulations ci-dessous sont aussi valables pour toutes les versions de MySQL supérieures ou égales à 5.5 sans adaptations et pour les versions précédentes avec quelques modifications que nous verrons en temps utile.

A priori le système d’exploitation des serveurs n’a aucune importance, vous pouvez même utiliser 2 machines ayant des OS différents.

separateur

Mettre en place une réplication MySQL master-slave

Procédons par étape et commençons par mettre en place une réplication maître-esclave, donc unidirectionnelle, du serveur « rouge » vers le serveur « bleu ».

En premier lieu nous allons paramétrer « rouge » pour autoriser notre utilisateur à répliquer les données. Pour cela, connectez-vous à la console MySQL et utilisez la commande suivante :

GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@'192.168.10.20’ IDENTIFIED BY 'motdepasse';

Eh oui c’est tout. Exportons maintenant les données pour les injecter sur « bleu » par la suite. Toujours dans la console, nous allons vider les caches des privilèges et des tables, tout en les verrouillant en lecture seule, à l’aide de la commande FLUSH :

FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;

Il ne vous reste plus qu’à faire un dump de vos bases de données pour l’injecter sur « bleu » par la suite, et à noter l’index du log binaire qui servira pour la réplication (les deux serveurs doivent avoir le même index en permanence). En utilisant la commande

SHOW MASTER STATUS;

Vous obtiendrez un tableau qui ressemble à cela :

Mysql : position de l'index binaire

Notez bien la valeur qui se trouve dans la colonne « position » (ici c’est 123456).

Passons maintenant à « bleu » qui va agir comme esclave de la réplication. Commencez par injecter le dump que vous venez de créer, puis allez sur la console MySQL du serveur pour taper les commandes suivantes :

STOP slave;
CHANGE MASTER TO MASTER_HOST='192.168.10.10', MASTER_USER='replication_user', MASTER_PASSWORD='motdepasse', MASTER_LOG_POS=123456;
START slave;

Que signifie ces lignes ? Premièrement nous arrêtons la réplication, ensuite nous plaçons le serveur en tant que slave pour recopier sur « bleu » les données de « rouge » (vous pouvez identifier dans la 2° ligne l’adresse IP de « rouge », le login et le mot de passe de l’utilisateur de réplication et l’index du log binaire que nous avons noté juste avant), enfin nous réactivons la réplication.

Revenons à « rouge » pour déverrouiller les tables :

UNLOCK TABLES;

Il ne vous reste plus qu’à redémarrer MySQL sur les deux serveurs et tout devrait fonctionner.

Paramétrer le slave pour MySql < 5.5

Je vous avais prévenu d’une petite adaptation pour les versions plus anciennes de MySQL : le paramétrage de l’esclave va se faire via le fichier my.cnf et pas dans la console MySQL. Ainsi, localisez ce fichier et éditez-le pour que la section [mysqld] comporte ces lignes :

[mysqld]
server-id=20
master-host = 192.168.10.10
master-user = replication_user
master-password = motdepasse
master-port = 3306

Profitez-en aussi pour définir un server-id (par exemple 10) dans le fichier my.cnf du serveur maître.

Redémarrez bien les deux serveurs puisque le fichier my.cnf n’est lu qu’au démarrage du service.

separateur

Vérifier que la réplication fonctionne

Le moyen le plus simple de vérifier que tout fonctionne est le test : insérez une donnée sur le master (« rouge ») et vérifiez qu’elle est bien apparue sur le slave (« bleu »). A ce stade de notre tutoriel la réplication ne fonctionne que dans ce sens.

Pour en savoir un peu plus ou si ça ne fonctionne pas, vous devrez afficher les statuts des deux serveurs avec la commande

SHOW MASTER STATUS;

sur « rouge », que nous avons déjà vue plus haut et la commande opposée :

SHOW SLAVE STATUS;

sur « bleu » qui doit nous donner un tableau ressemblant à celui-ci

Etat de la réplication slave MySQL

Le plus important est la première colonne : le serveur attend une commande du maître, viennent ensuite les informations concernant la réplication. Dans ce tableau vous trouverez aussi les informations sur la dernière erreur générée sur le slave par une requête de réplication (il s’agit des colonnes Last_SQL_Errno et Last_SQL_Error).

separateur

Mettre en place la réplication MySQL master-master

Vous avez bien compris qu’une réplication master-slave sert à recopier les données du maître vers l’esclave dans un seul sens. Pour rendre cette recopie bidirectionnelle, il suffit simplement de créer 2 réplications unidirectionnelles ! Ainsi nous allons maintenant appliquer les opérations faites sur « rouge » à « bleu » et celle faite sur « bleu » à « rouge », bien entendu sans faire manipuler les données puisqu’elles sont déjà répliquées.

Commençons par promouvoir « bleu » en tant que master et récupérons son index :

GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@'192.168.10.10’ IDENTIFIED BY 'motdepasse';
SHOW MASTER STATUS;

Puis notons son index et donnons le rôle d’esclave à « rouge » :

STOP slave;
CHANGE MASTER TO MASTER_HOST='192.168.10.20', MASTER_USER='replication_user', MASTER_PASSWORD='motdepasse', MASTER_LOG_POS=123456;
START slave;

Redémarrons les deux services et testons : inscrivez une donnée sur « rouge », elle doit apparaître sur « bleu ». Effacez la de « bleu » et vérifiez qu’elle disparaît de « rouge ». Si cela fonctionne, vous avez réussi.

separateur

Moins contraignante que la mise en place d’un cluster, la réplication MySQL master-master est cependant limitée à deux serveurs (tandis que la réplication master-slave peut concerner un grand nombre d’esclaves). Elle est pleinement justifiée dans le cadre de la mise en place d’une architecture distribuée avec load balancing mais possède néanmoins quelques limites et peut s’avérer désastreuse en cas de désynchronisation des serveurs (rassurez-vous, il est toujours possible de resynchroniser les bases données).

Avez-vous déjà expérimenté ce type de configuration ? Quel est votre retour d’expérience ? Les commentaires sont à vous !


Sources :

Crédit clipart: https://openclipart.org/detail/36565/tango-network-server-by-warszawianka
Tutoriel 1: http://www.howtoforge.com/mysql_master_master_replication
Tutoriel 2: http://www.xaprb.com/blog/2007/08/01/why-mysql-server-not-configured-as-slave/

26 réponses

  1. […] La réplication d’un base de données permet de disposer du même jeu de données à tout moment sur deux serveurs ou plus.  […]

  2. chatenaisien dit :

    salut,
    intéressant,
    je comptais mettre ça en place et je vais m’y intéresser le week-end prochain.

    Pas encore tout lu mais tu ne donnes pas les configs ? tu es sur debian, connecté ça sur un ups, at home ?

    @+

  3. Renaud M.G. dit :

    Bon courage pour ton implémentation, sur MySQL ça roule tout seul.
    Question config, j’ai d’un côté 2 ubuntu qui marchent en master-master + 1 ubuntu posé en slave au cas où, ça fait tourner un petite 15aine de sites.
    Sur un autre réseau j’ai un master sous gentoo (le serveur frontal) et un slave sous OpenSuse pour les stats et la facturation.

    Bref les applications sont infinies et les configurations très variées puisque c’est MySQL qui gère et pas l’OS (enfin je ne testerais pas sous Windows quand même).

    Stay tuned, cette semaine je vais publier la partie « réplication des fichiers » avec Unison ça peut peut-être t’intéresser aussi

  4. Thierry dit :

    Bonjour,
    Article très synthétique et intéressant. Ayant essayé une configuration basique master/slave (mysql 5.5) je me posais les questions suivantes :

    – partant de deux serveurs mysql « vierges » de toute bdd utilisateur, la réplication doit elle refléter la création d’une nouvelle base de données . En clair, si la réplication est fonctionnelle et que l’on ajoute une nouvelle base de données sur le master, sera t elle créée aussi sur le slave ?

    – sur le master, suffit il d’activer les logs binaires par le fichier my.cnf pour que la commande « show master status » donne le résultat attendu dans votre article, ou bien y a t’il autre chose à faire ?

    Bonne continuation à vous

  5. Renaud M.G. dit :

    @Thierry, avant tout désolé pour le délai de réponse.
    Oui si la réplication est active, un create database sur le master sera répliqué sur le slave
    Sur le master, il faut impérativement donner les droits de réplication à un utilisateur pour que cela fonctionne.

  6. idir dit :

    Bonjour,

    En voulant appliquer ce topic, j’ai l’erreur suivante, je vais prendre le temps de chercher su le net, mais si d’ordinaire quelqu’un avait une idée, je prends.

    Merci d’avance..

    mysql> GRANT REPLICATION SLAVE ON BASE TO ‘repl’@’localhost’;
    ERROR 1144 (42000): Commande GRANT/REVOKE incorrecte. Consultez le manuel.

  7. Idir dit :

    Bonjour,

    Je me suis (déjà) débrouillé, j’ai autorisé la réplic sur toutes les bases, en même temps, j’en aurai qu’une, et j’ai aussi mis l’IP distante, au lieu du localhost qui était erroné.

    Bon topic sinon 😉

  8. Renaud M.G. dit :

    Eh bien ravi que tu t’en sois sorti ! merci pour le compliment.

  9. Yann dit :

    Bonjour
    Tout d abord bravo pour l article.
    J ai des questions.
    Quid des performances pour la configuration master/Master vs Master/esclave
    Quid si je mets en place deux bases de données chez deux fournisseurs d accès différents ?
    Puis je mettre un système master master et un système esclave
    Dans le cas d une conf master/esclave puis accéder en lecture à la base esclave
    D avance merci
    Yann

  10. Renaud M.G. dit :

    @Yann
    Pour ce qui est de la performance des 2 solutions, il s’agit d’une réplication d’index dans les 2 cas les performances doivent donc être les mêmes, sauf que l’écriture est permise sur les 2 serveurs dans la solution master/master et seulement sur 1 serveur dans le cas master/slave
    Forcément les performances seront meilleures et le risque d’erreurs de réplication bien plus faible si tes serveurs sont situés sur la même baie de la même salle serveur. Si tu as 2 hébergeurs différents tu augmentes la latence entre une action et sa réplication. A l’échelle de l’humain c’est infime, à l’échelle du serveur ça n’est pas l’idéal.
    Un master peut avoir une infinité d’esclaves donc on peut rajouter un 3ème serveur qui sera le slave de l’un des deux autres et oui on pourra accéder à toutes ses informations en lecture.

  11. barok dit :

    Très intéressant votre article. Autant je suis très à l’aise sur la réplication maitre-esclave, autant j’ai une question concernant la réplication maitre/maitre.
    Que ce passe t-il si au même moment, il y a 2 insertions faites sur une même table possédant un id autoincrément. En effet, le ping réseau fait qu’un INSERT sur la machine rouge sera répliqué sur bleu qq ms après, temps suffisant pour qu’un INSERT soit fait sur bleu avant de recevoir la commande de rouge.
    Chaque table fera un INSERT en augmentant de 1 l’autoincrément (ex : chacune de 10 à 11) et normalement, en recevant l’écriture de l’autre serveur, l’insertion devrait foirer (car id déja existant) et la réplication s’arreter.

  12. Renaud M.G. dit :

    @barok, ce genre de solution n’est pas utilisable pour de vrais systèmes haute disponibilité, il faudra dans ce cas se tourner vers MySQL Cluster qui gère tout ça de façon native. Sinon, non en théorie la situation que tu décris ne devrait pas « foirer » puisque ce sont des index de journal qui sont dupliqués et pas les id des lignes. Selon toute logique si le master envoie un index à 12:00:00 et que le slave a fait un insert à 12:00:01, MySQL doit faire un rollback de l’instruction de 12:00:01, injecter la requête de 12:00:00 et rééxécuter la commande de 12:00:01.
    Cela dit il arrive que les deux serveurs se désynchronisent et là c’est moins drôle… je t’invite à lire cet article pour savoir comment resynchroniser.

  13. Stéphane dit :

    Bonjour,

    D’abord merci pour cet article.
    J’aimerai apporter quelques précisions car j’ai eu des petits soucis pendant la mise en œuvre (MySQL 5.5.41)

    1/ Penser à autoriser les connexions du SLAVE vers MASTER en modifiant la directive « bind-address » dans la config (my.cnf) du MASTER. Soit on peut la désactiver pour autoriser toutes les connexions distantes, soit on précise l’IP du SLAVE (mieux évidemment).

    2/ Penser à activer les binary logs sur le MASTER si ce n’est pas fait (j’avais pour habitude de les désactiver systématiquement), toujours dans « my.cnf ».

    3/ Identifier les serveurs : 1 pour le MASTER, 2 pour le SLAVE (voir « server-id » dans « my.cnf »)

    4/ Au niveau de la mise en route de la synchro, j’ai été obligé de préciser le nom du fichier BIN (celui fourni par la commande « SHOW MASTER STATUS; ») :
    CHANGE MASTER TO MASTER_HOST=’192.168.10.10′, MASTER_USER=’replication_user’, MASTER_PASSWORD=’motdepasse’, MASTER_LOG_POS=123456, MASTER_LOG_FILE=’mysql-bin.000073′;

    5/ Pour limiter la synchro à une seule base, ajouter dans la config du SLAVE : « replicate-do-db = nom_de_la_base »

    6/ Penser à garder la session mysql ouverte sur le MASTER après avoir exécuté « FLUSH TABLES WITH READ LOCK; » sinon le lock est perdu…
    C’est à dire que le dump du MASTER doit être exécuté en parallèle.

    Et ne pas oublier de faire un restart de mysql après chaque modif de la config 😉

  14. Renaud M.G. dit :

    Merci @Stéphane pour ton retour d’expérience, le coup du restart de mysql est une vraie vacherie… je ne compte pas le temps que j’y ai perdu à faire, refaire et vérifier la même manipulation lors des premières manipulations sur mes serveurs MySQL !

  15. […] Perso. Tutoriel : réplication MySQL master-master ou master-slave. […]

  16. Cool dit :

    Tous d’abord merci à tous pour tous ces détails!!!!!
    j’ai lu et je voudrai aussi mettre en place cette architecture mais je ne parviens toujours pas à utiliser le fameux ‘mysqldump’ COMMENT CAS MARCHE? le lien y afférent sur ce site ne donne rien et quant je lance une recherche sur google et j’applique, on me renvoi l’erreur sous le console mysql c’est encore plus obscure!!! quelqu’un peut-il m’aider?
    toutefois le tout est bien bien sauf votre mysqldump et j’utilise mysql 5.5.8

    Merci d’avance!!!!!!!!!!!!!!

  17. Renaud M.G. dit :

    Merci @Cool, le lien était mort je l’ai mis à jour.
    Pour mysqldump c’est un utilitaire qui permet de créer un script sql qui permet de reconstituer toute une base de données, structure et données. La commande de base est
    mysqldump NomDeLaBdd > fichier.sql
    mais la plupart du temps il faut ajouter au moins les informations de connexion :
    mysqldump -u NomUtilisateur -pMotDePasse NomDeLaBdd > fichier.sql

    Si tu es sous windows ton PATH ne contient peut-être pas le chemin d’accès au programme. Dans ce cas il faut soit mettre à jour ton PATH, soit taper le chemin d’accès complet dans la ligne de commande, par exemple
    mysql/bin/mysqldump ….

  18. […] comment configurer 2 serveurs MySQL pour la réplication master-master : chaque mise à jour de données effectuée sur l’un des deux serveurs sera automatiquement répercutée sur l’autre ; […]

  19. […] Sans titre. Production.pdf. Nps-radius-vpn-2008-r2.pdf. Vpn-2008.pdf. Tutoriel : réplication MySQL master-master ou master-slave. […]

  20. […] comment configurer 2 serveurs MySQL pour la réplication master-master : chaque mise à jour de données effectuée sur l’un des deux serveurs sera automatiquement répercutée sur l’autre ; […]

  21. Caillaud Pierre-Antoine dit :

    Merci beaucoup pour cet article très intéressant qui m’est d’une grande aide.

    Bonne continuation.

  22. […] La sauvegarde en quelques clics sous GNU/Linux. SystemRescueCD – Utilitaire de dépannage complet. Tutoriel : réplication MySQL master-master ou master-slave. Un serveur mail complet : Présentation – Blog Debugo. Utiliser TeamViewer sous Linux sans serveur […]

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *