Replication Master/Slave temps réel MySQL

La réplication MySQL consiste à avoir en temps réel deux bases de données MySQL identiques afin de pouvoir basculer sur un deuxième serveur en cas de défaillance du premier.

La réplication MySQL est basée sur le fait que le serveur va garder la trace de toutes les évolutions de vos bases (modifications, effacements, etc.) dans un fichier de log binaire et les esclaves vont lire les requêtes du maître dans ce fichier de log, pour pouvoir exécuter les mêmes requêtes sur leurs copies. 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). Tous les esclaves que vous activez auront besoin de la copie des données qui existaient au moment du démarrage du log. Si vous démarrez vos esclaves sur sans qu’ils ne disposent des données identiques à celles du maître au moment du démarrage du log binaire, votre réplication va échouer.

Nous partons avec l’architecture suivante:
Deux serveurs A, et B sous Linux (réalisé sous Debian) ayant chacun une base de donnée MySQL.
IP Serveur A: 192.168.0.1
IP Serveur B: 192.168.0.2

Principe de fonctionnement en image:
replication mysql

MISE EN PLACE DE LA REPLICATION MySQL

On commence par le MASTER (192.168.0.1)

On se connecte à la base de donnée.

mysql -h 192.168.0.1 -u admin –ppassword –P 3306

On crée un utilisateur pour la réplication:

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';

On stop l’écriture sur la base:

mysql> FLUSH WITH READ LOCK;

On récupère le nom du fichier binaire, et son offset.
Il faut bien les noter car nous en auront besoin après pour configurer l’esclave.

mysql> SHOW MASTER STATUS;

Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter
également le numéro dans « Position”.
Exemple:

mysql > SHOW MASTER STATUS;
+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.003 | 73 | test,bar | foo,manual,mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.06 sec)

Si le File et le Position sont vides, PAS DE PANIQUE
Prenez comme nom de File -> ‘’ et pour Position -> 4.
Quittez le prompt mysql. -> quit
Eteignez la base:

shell> mysqladmin –u admin –ppassword –h 192.168.0.1 –P 3306 shutdown

Editez le fichier my.cnf qui se trouve normalement dans /var/lib/mysql/my.cnf
Ajoutez-y les lignes suivantes :

[mysqld]
log-bin
server-id=1

Relancez la base MySQL, en étant dans le répertoire MySQL (ne pas oublier le “&” qui permet de faire tourner le processus en arrière-plan):

safe_mysqld &

On configure maintenant le SLAVE (192.168.0.2)

Eteignez la base:

shell> mysqladmin –u vadmin –ppassword –h 192.168.0.2 –P 3306 shutdown

Editez le fichier my.cnf qui se trouve dans /var/lib/mysql/my.cnf
Ajoutez-y les lignes suivantes :

[mysqld]
server-id=2
master-host = 192.168.0.1
master-user = repl
master-password = repl
master-port = 3306

Relancez la base MySQL, en étant dans le répertoire MySQL (ne pas oublier le “&” qui permet de faire tourner le processus en arrière-plan):

safe_mysqld &

On se rend dans le prompt MySQL pour lancer le slave.

shell> mysql –u admin –ppassword –h 192.168.0.2 –P 3306

On change les données comme ceci si vous aviez un nom de fichier et une position:

mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=;

Si vous n’aviez pas de nom de fichier et de position en faisant le SHOW MASTER STATUS sur le master,
entrez ceci:

mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=4;
On démarre l’esclave
mysql> START SLAVE;

Si tout c’est déroulé correctement, vous devez avoir ceci :
(Les lignes en gras sont les plus importantes)

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ServerA-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: ServerB-relay-bin.000002
Relay_Log_Pos: 238
Relay_Master_Log_File: ServerA-bin.000001
<strong>Slave_IO_Running: Yes</strong>
<strong>Slave_SQL_Running: Yes</strong>
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 238
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Et dans SHOW PROCESSLIST:

mysql&gt; <strong>SHOW PROCESSLIST</strong>;
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
| 6 | system user | | NULL | Connect | 1944 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 1944 | Has read all relay log; waiting for the slave I/O thread to update it | NULL
| 8 | admin | serverB.lan:45148 | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

En cas d’erreur de réplication,
On vérifie l’état du SLAVE:

shell&gt; mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql&gt;SHOW SLAVE STATUS ;

Il arrive que certaines requêtes réussissent sur le maître mais échouent sur l’esclave. Cela ne devrait
pas arriver si vous avez pris la bonne sauvegarde du maître, et que vous n’avez jamais modifié les
données sur le serveur esclave, autrement que par le truchement de l’esclave de réplication.

Si vous apercevez une erreur aux lignes:

Last_Errno:
Last_Error:

Voici la procédure:
Sur le master :

shell&gt; mysql –u admin –ppassword –h 192.168.0.1 –P 3306
mysql&gt;FLUSH TABLES WITH READ LOCK;
mysql&gt;SHOW MASTER STATUS;

Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter
également le numéro dans “Position”.

Faire un DUMP de la base de donnée:

shell&gt; mysqldump -u admin -ppassword -h 192.168.0.1 -P 3306 -r /home/dump.sql nomdelabase

Envoyer le dump vers le slave:

shell&gt; scp –e /home/dump.sql root@192.168.0.2:/home

Sur le Slave, on bloque les tables:

shell&gt; mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql&gt;FLUSH TABLES WITH READ LOCK;
mysql&gt;exit;

On intègre le dump du master dans la base “nomdevotrebase”:

shell&gt; mysql –h 192.168.0.2 –u admin -ppassword –P 3306 nomdelabase &lt;
/home/dump.sql

On redéfinit le bon fichier binaire du master et on redémarre le Slave:

shell&gt; mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql&gt;CHANGE MASTER TO
-&gt; MASTER_LOG_FILE='',
-&gt; MASTER_LOG_POS=POSITION_FICHIER_LOG;
mysql&gt;START SLAVE;

Normalement, la réplication est repartie !

En cas de crash du MASTER – Remise à niveau du master et de la réplication.

Nous allons partir du fait que le MASTER est tombé, vous l’avez remis à niveau, réinstallé vTiger.
Nous allons maintenant remettre la base MySQL et réactiver la réplication.
A ce moment, le SLAVE est LA base de référence. Donc nous allons commencer par stopper le slave,
créer un DUMP du slave, le répliquer sur le MASTER, et relancer le tout.

SUR LE SLAVE
On vérifie l’état du SLAVE:

shell&gt; mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql&gt;FLUSH TABLES WITH READ LOCK;

On crée un dump du slave:

shell&gt; mysqldump -u admin -ppassword -h 192.168.0.2 -P 3306 -r /home/dump.sql nom de la base

On envoi le dump sur le master:

shell&gt; scp –e /home/dump.sql root@192.168.0.1:/home

SUR LE MASTER

Mysql -h 192.168.0.1 -u admin –ppassword –P 3306

On crée un utilisateur pour la réplication:

mysql&gt; GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';

On stop l’écriture sur la base:

mysql&gt; FLUSH WITH READ LOCK;

On ré-insert le dump du slave sur le master:

shell&gt; mysql –h 192.168.0.1 –u admin -ppassword –P 3306 nomdelabase &lt;
/home/dump.sql

On récupère le nom du fichier binaire, et son offset. Il faut bien les noter car nous en auront besoin
après pour configurer l’esclave.

shell&gt; mysql –u admin –ppassword –h 192.168.0.1 –P 3306
mysql&gt; SHOW MASTER STATUS;

Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter
également le numéro dans « Position”.

Exemple:

mysql &gt; SHOW MASTER STATUS;
+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.003 | 73 | test,bar | foo,manual,mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.06 sec)

Quittez le prompt mysql. -> quit

Eteignez la base:

shell&gt; mysqladmin –u admin –ppassword –h 192.168.0.1 –P 3306 shutdown

Editez le fichier my.cnf qui se trouve dans /var/lib/mysql/my.cnf

Ajoutez-y les lignes suivantes:

[mysqld]
log-bin
server-id=1

Relancez la base MySQL, en étant dans le répertoire MySQL (ne pas oublier le “&” qui permet de faire tourner le processus en arrière-plan):

safe_mysqld &amp;

SUR LE SLAVE:

Nous allons maintenant re-synchroniser le Slave avec le master.

On redéfinit le bon fichier binaire du master et on redémarre le Slave:

shell&gt; mysql –u admin –ppassword –h 192.168.0.2 –P 3306
mysql&gt;CHANGE MASTER TO
-&gt; MASTER_LOG_FILE='',
-&gt; MASTER_LOG_POS=POSITION_FICHIER_LOG;
mysql&gt;START SLAVE;

Normalement, la réplication est repartie !

Vous pouvez vérifier le statut du Slave, grâce aux commandes SLAVE STATUS, et SHOW PROCESSLIST.

replication mysql
Annexes: HOWTO Replication MySQL (Mysql official Documentation).

Benoit

Network engineer at CNS Communications. CCIE #47705, focused on R&S, Data Center, SD-WAN & Automation.

More Posts - Website

Follow Me:
TwitterLinkedIn

14 Comments

  1. Mikes 9 juin 2009

    Hey, great post, really well written. You should write more about this.

  2. Katy 24 juin 2009

    Pretty nice post. I just stumbled upon your site and wanted to say
    that I have really liked reading your posts. Any way
    I’ll be subscribing to your feed and I hope you write again soon!

  3. livingdead_fr 24 novembre 2011

    Gorgeous, this work perfect. Thanks a lot.

  4. Benoit 24 novembre 2011

    You’re welcome ;)

  5. Stensys 30 novembre 2011

    Mais est-ce viable d’utiliser ce système pour faire tourner un LAMP sur le serveur backup qui se servirait de la base répliquée en local pour ses requetes, évitant ainsi de faire des requetes mysql distantes sur le serveur possédant la “vraie” base …
    Parce que si oui, ça m’intéresse…

  6. Sylvain 1 décembre 2011

    Bon guide pour une réplication Master-Slave mais personnellement je préfère les réplications master-master car la resynchro se fait toute seule, pas besoin de faire d’échange de rôle sur les serveurs.
    C’est ce que j’ai mis en place sur la plateforme d’un client et ça fonctionne super bien.

  7. Benoit 17 décembre 2011

    @stensys
    Si le serveur de backup ne fait que de la lecture (SELECT) alors je suppose que ça ne doit pas être un problème.
    Par contre, si le backup se met à écrire sur la base, alors elles ne sont plus homogènes.

  8. tarkan 3 juin 2012

    Rien à dire !
    Plus complet que le site de mysql. :)

  9. badr 23 août 2012

    Bonjour,
    je te remercie pour ce tuto mais j’ai un problème c’est que au lieu de deux bases de données j’ai 4 bases de données et je veux faire la synchronisation et aussi la réplication entre ces 4 base.
    alors est ce que je doit déclarer une base master et les 3 autre slave ou comment je doit procéder
    et merci d’avance pour vos réponses.

  10. clucas 25 octobre 2012

    Salut,

    Je pense que la première commande est plutôt :

    mysql> FLUSH TABLES WITH READ LOCK;

    Amicalement,
    Christophe

  11. Dodfr 11 décembre 2012

    Bonjour,

    Je n’ai pas compris si la resynchro Slave->Master sera automatique ou s’il faut faire une manip manuelle avant de rendre le Master de nouveau actif ?

    cdlt.

  12. melchioretto 3 janvier 2014

    Erreur 1062 sur une réplication Mysql (MASTER – SLAVE) pb de clé primaire

  13. ddirlyon 4 novembre 2014

    Bonjour,

    Pourquoi dans la procédure de rétablissement du Master HS, vous recréez un user pour la réplication, sachant qu’il y en a déja un qui a été créé précédemment.

    Cordialement

  14. Benoit 10 novembre 2014

    Bonjour, aucune idée, c’est un vieil article ;)

Laisser un commentaire

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