Plan du site  
pixel
pixel

Articles - Étudiants SUPINFO

La réplication de données avec MySQL

Par Florian MASCARO Publié le 31/05/2016 à 16:34:47 Noter cet article:
(0 votes)
Avis favorable du comité de lecture

Introduction

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. 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 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 :

  • Serveur Maître : 192.168.10.80

  • Serveur Esclave : 192.168.10.81

La mise en place du serveur 'MAITRE'

Editez le fichier my.cnf qui se trouve normalement dans /etc/mysql/my.cnf

Ajoutez-y les lignes suivantes :

     [mysqld]
     server-id=1 log_bin = /var/log/mysql/mysql-bin.log
     expire_logs_days = 2 
     max_binlog_size = 200M
     relay-log = /var/log/mysql/mysql-relay-bin.log
   
    

Redémarrez la base:

     service mysql restart
   

Ensuite, connectez vous à mysql sur le serveur MAITRE et créez un utilisateur pour la réplication

     mysql -ulogin -ppassword
     mysql > GRANT REPLICATION SLAVE ON *.* TO nomUtilisateur@'%' IDENTIFIED BY 'password';
     mysql > FLUSH TABLES 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 le serveur ESCLAVE. Pour ce faire, tapez la commande suivante :

    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)

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

Si les colonnes 'File' et 'Position' sont vides, prenez les valeurs par défaut : File = '' et Position = 4.

Pour terminer, exécutez les requêtes suivantes :

    mysql > UNLOCK TABLES;
    mysql > exit;

La mise en place du serveur 'ESCLAVE'

Eteignez la base sur le serveur ESCLAVE

    service mysql stop

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

Ajoutez-y les lignes suivantes :

    [mysqld] 
    server-id=2
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 2
    max_binlog_size = 200M
    relay-log = /var/log/mysql/mysql-relay-bin.log

Relancez la base MySQL :

    service mysql restart

On se rend dans le prompt MySQL pour lancer le serveur ESCLAVE.Puis, on exécute les requêtes suivantes :

shell> mysql –uroot –ppassword

    
    mysql –uroot –ppassword
    mysql> CHANGE MASTER TO -> MASTER_HOST='master host name',
      -> MASTER_USER='replication user name',
      -> MASTER_PASSWORD='replication password',
      -> MASTER_LOG_FILE='recorded log file name',
      -> MASTER_LOG_POS=recorded log offset;
 

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

    mysql> CHANGE MASTER TO
    -> MASTER_HOST='master host name',
    -> MASTER_USER='replication user name',
    -> MASTER_PASSWORD='replication password',
    -> MASTER_LOG_FILE='',
    -> MASTER_LOG_POS=4;

    mysql> START SLAVE;
 

Si tout s’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
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    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
    1 row in set (0.00 sec)

    

Les cas d'erreur

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

    mysql –ulogin –ppassword
    mysql> 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:" ou "Last_Error:", voici la procédure:

Sur le maître :

    mysql –ulogin –ppassword
    mysql> FLUSH TABLES WITH READ LOCK;
    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”.

Ensuite, faites un DUMP de la base de données:

Sur le maître :

    mysqldump -u admin -ppassword database_name > database.sql

Ensuite, sur l'esclave :

    mysql –u admin –ppassword
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> exit;
    mysql –u admin -ppassword database_name /home/database.sql
 

On intègre le dump du maître dans la base “database_name”:

Ensuite, on redéfinit le bon fichier binaire du maître et on redémarre l'esclave:

    mysql –u admin –ppassword
    mysql>CHANGE MASTER TO
      -> MASTER_LOG_FILE='',
      -> MASTER_LOG_POS=POSITION_FICHIER_LOG;
    
    mysql> START SLAVE;
 

Normalement, la réplication est repartie !

En cas d'erreur du type: 'You cannot ALTER a log table if logging is enabled'

Exécuter les requêtes suivantes :

    mysql>STOP SLAVE;
    mysql>SET GLOBAL slow_query_log = 'OFF';
    mysql>SET GLOBAL sql_log_bin = 0;
    mysql> START SLAVE;
    mysql>SET GLOBAL slow_query_log = 'ON';
    mysql>SET GLOBAL sql_log_bin = 1;
 

Conclusion

La réplication des bases de données avec MySQL peut être très pratique pour avoir, par exemple, une copie de la base en cas de défaillance du serveur 'MAITRE' ou, d'avoir une base de données dédié à la lecture.

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Toeic, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels