Couverture PHP Solution - Hors Série Février 2012

Réplication de bases de données MySQL

Article paru dans PHP Solutions Hors Série en Février 2012

Fonctionnalité introduite depuis la version 3.23.15 de MySQL, la réplication de bases de données permet de conserver une copie exacte des bases sur une seconde machine.

Cette copie permettra d’effectuer différentes tâches sans surcharger le serveur principal (exemple sauvegardes, etc…). En cas de panne matériel du serveur maître, le serveur esclave pourra facilement être mis en service à sa place.

Principe

Sur le serveur maître, toute modifications des bases de données sera consignée dans un fichier de log binaire. Le ou les esclave(s) consultent ce fichier et accomplissent toutes les actions qui y sont consignées.

En cas de perte de connexion, les esclaves tentent de ce re connecter au master toutes les « master-connect-retry » secondes. Ils reprennent le fichier de log et rejouent les actions qui y sont consignées depuis la position qu’ils avaient enregistrée.

Mise en place

La réplication étant le résultat de l’application des modifications des tables du maître, il est nécessaire que le maître ET l’esclave soient dans le même état au moment ou le processus commence.

Si toutes les tables sont au format « MyISAM », il est possible d’utiliser « LOAD DATA FROM MASTER » qui chargera les données depuis le maître (à la vitesse d’1 Mo/sec).

Dans le cas contraire, il faudra copier le contenu des bases manuellement (par un dump puis une importation par exemple).

Préparation du maître

Pour utiliser la réplication, le fichier de configuration du serveur doit contenir au moins, dans la partie « [mysqld] », les instructions « log-bin » spécifiant l’utilisation d’un fichier de log binaire et l’identifiant « server-id » unique dans la chaîne de réplication (0 < server-id < 2^32 ? 1).

srv ~ # cat /etc/mysql/my.cnf
...
[mysqld]
...
log-bin
server-id = 1
...

Si ces lignes n’existent pas ou qu’elles sont commentées, les ajouter ou les dé commenter et redémarrer le service MySQL.

Note : Vous pouvez préciser la localisation du fichier de log binaire avec la directive « log_bin ». Par exemple :

log_bin = /var/mysql/mysql-bin.log

Créer un utilisateur MySQL ayant la permission « REPLICATION » et pouvant ce connecter depuis le serveur esclave. Afin d’utiliser le chargement des données depuis le maître par la commande « LOAD DATA FROM MASTER », l’utilisateur doit disposer des droits « SUPER », « RELOAD » et « SELECT » sinon, seul le droit « REPLICATION » suffit.

Toujours dans le cadre d’un chargement depuis le serveur maître par « LOAD DATA FROM MASTER » les tables sur lesquelles l’utilisateur n’aura pas l’autorisation « SELECT » ne seront pas chargée sur l’esclave.

mysql> GRANT SUPER, RELOAD, SELECT, REPLICATION SLAVE ON *.* TO replicator@'10.0.2.100' IDENTIFIED BY 'Un mot de passe' ;

En cas de copie manuelle des données sur le serveur esclave la définition de l’utilisateur sera simplifiée en :

mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@'10.0.2.100' IDENTIFIED BY 'Un mot de passe' ;
Query OK, 0 rows affected (0.05 sec)

Préparation de l’esclave

La première étape de la configuration consiste à s’assurer que le maître accepte bien les connexions de l’esclave :

backup:~# mysql -h srv.rez0.lan -u replicator -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

En cas d’échec de connexion, vérifiez la configuration de votre firewall (port 3306 par défaut à ouvrir), l’absence de la directive « skip-networking » dans my.cnf (en cas de présence commentez la), ainsi que l’interface d’écoute de MySQL (valeur « bind-address » de my.cnf).

Dans le fichier de configuration de l’esclave modifier la valeur « server-id » de la rubrique « [mysqld] ». Cette valeur doit NÉCESSAIREMENT être différente de celle du maître.

backup ~ # cat /etc/mysql/my.cnf
...
server-id = 2
master-host=srv.rez0.lan
master-port=3306
master-user=replicator
master-password=Un mot de passe
master-connect-retry=60
...

NOTE : Les variables master-* seront lue la première fois que le serveur sera lancé en tant qu’esclave. Ensuite, elles seront consignées dans le fichier « master.info ». Ce fichier sera prioritaire sur le fichier my.cnf aussi, pour changer les paramètres du maître, il faudra utiliser, depuis MySQL directement, « CHANGE MASTER TO ».

Copie des données

Verrouiller les tables du maître

Pour mettre la réplication des bases de données en place, il faut copier un instantané de l’état des bases du maître sur l’esclave. Pour cela, nous commençons par verrouiller les bases de données du master. Sur le maître lancer MySQL puis verrouiller les tables :

srv:~# mysql -uroot -p
Enter password:
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.04 sec)

L’esclave doit effectuer les mêmes actions que le maître. Ces actions sont consignées dans un fichier de log binaire. Nous devons donc maintenant déterminer le nom du fichier de log courant et à partir de quelle ligne du fichier l’esclave doit commencer à reproduire les actions.

Pour cela, toujours sur le maître, lancer la commande « SHOW MASTER STATUS » qui retourne dans la colonne « File » le nom du fichier de log et dans la colonne « Position » la position de l’instantané :

mysql> SHOW MASTER STATUS;
+-------------------+-----------+--------------+------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| mysqld-bin.000058 | 490854443 |              |                  | 
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

Enregistrez ces informations.

A ce stades, plusieurs solutions sont offertes :

  • copier les fichiers binaires de base de donnée sur le serveur esclave,
  • faire un dump des bases de donnée et les ré injecter sur le serveur esclave.

Copie des fichiers binaires

  • Éteindre le serveur MySQL SANS DÉVERROUILLER LES TABLES,
  • srv ~ # /etc/init.d/mysql stop
     * Caching service dependencies ...    [ ok ]
     * Stopping mysql ...
     * Stopping mysqld (0)                 [ ok ]
  • localiser les données MySQL,
  • srv ~ #  grep datadir /etc/mysql/my.cnf
    datadir = /var/lib/mysql
  • créer une archive des bases à copier (il est inutile de copier la base « mysql » mais, cela oblige à re créer les utilisateurs des bases répliquées en cas de mise en production du backup) :
  • srv ~ # find /var/lib/mysql/ -maxdepth 1 -type d -exec tar uvf /tmp/20090925.db.tar {} \; ; bzip2 /tmp/20090925.db.tar
  • les copier sur le serveur esclave :
  • srv ~ # scp /tmp/20090925.db.tar.bz2 backup.rez0.lan:~/
    ...
  • sur l’esclave, arrêter le service MySQL puis extraire les bases :
  • backup ~ # cd /
    backup ~ # tar xf ~/20090925.db.tar.bz2
  • redémarrer le service « MySQL » sur le maître

Faire un dump des bases

  • En laissant le service « MySQL » en fonction, lancer un dump de la base de donnée du maître :
  • srv ~ # mysqldump --all-databases -uroot -p > /tmp/20090925.db.sql
    Enter password:
    srv ~ # tar cjvfp /tmp/20090925.db.tar.bz2 /tmp/20090925.db.sql
  • copier les données sur l’esclave,
  • srv ~ # scp /tmp/20090925.db.tar.bz2 backup.rez0.lan:~/
    ...
  • sur l’esclave, injecter le contenu de la sauvegarde dans MySQL,
  • backup ~ # tar xf 20090925.db.tar.bz2
    backup ~ # mysql -u root -p < 20090925.db.sql
    Enter password:

Déverrouillage des tables du maître

A ce stade, quelque soit la solution choisie, il est possible de déverrouiller les tables du maître :

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Fin du paramétrage de l’esclave

Sur l’esclave, ce connecter à MySQL et lancer la commande de paramétrage du maître :

backup ~ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> CHANGE MASTER TO MASTER_HOST='srv.rez0.lan',
    -> MASTER_USER='replicator',
    -> MASTER_PASSWORD='Un mot de passe',
    -> MASTER_LOG_FILE='mysqld-bin.000058',
    -> MASTER_LOG_POS=490854443 ;
Query OK, 0 rows affected (0.51 sec)
  • MASTER_USER est l’utilisateur créé pour la réplication sur le maître.
  • MASTER_PASSWORD est son mot de passe.
  • MASTER_LOG_FILE est le fichier de log binaire courant sur le maître au moment de l’export des données (valeur enregistrée avant la copie des bases et retournée par « SHOW MASTER STATUS » colonne « FILE »).
  • MASTER_LOG_POS est la position dans le fichier de log au moment du dump (valeur enregistrée avant la copie des bases et retournée par « SHOW MASTER STATUS » colonne « Position »).

Lancement de la réplication

Le processus de réplication est lancé depuis l’esclave :

mysql> START SLAVE;
Query OK, 0 rows affected (0.10 sec)

A partir de ce moment, l’esclave ce connecte au master et effectue toutes les commandes passée depuis la position précisée à « MASTER_LOG_POS ».

Les paramètres MySQL de l’esclave sont enregistrés dans le fichier « master.info » et seront ré appliqué à chaque démarrage du service.

Contrôler le processus

La commande « SHOW PROCESSLIST » donne des informations sur l’état de la réplication sur le serveur et / ou sur l’esclave.

Sur le serveur « SHOW PROCESSLIST » …

mysql> SHOW PROCESSLIST ;
+-----+------------+-----------------------+--------+-------------+------+----------------------------------------------------------------+------------------+
| Id  | User       | Host                  | db     | Command     | Time | State                                                          | Info             |
+-----+------------+-----------------------+--------+-------------+------+----------------------------------------------------------------+------------------+
...
| 533 | replicator | backup.rez0.lan:58962 | NULL   | Binlog Dump |   74 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             | 
| 543 | root       | localhost             | NULL   | Query       |    0 | NULL                                                           | SHOW PROCESSLIST | 
+-----+------------+-----------------------+--------+-------------+------+----------------------------------------------------------------+------------------+

… nous informe que la réplication sur « backup.rez0.lan » est à jour et que le système est en attente.

Sur le client …

mysql> SHOW PROCESSLIST ;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
|  9 | root        | localhost | NULL | Query   |    0 | NULL                                                                  | SHOW PROCESSLIST |
| 10 | system user |           | NULL | Connect |   79 | Waiting for master to send event                                      | NULL             |
| 11 | system user |           | NULL | Connect |    0 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

…la commande affiche 2 processus différents, l’un servant à lire les informations depuis le maître, l’autre appliquant les modifications consignées dans le fichier de log binaire.

Informations complémentaires

Un serveur maître peut être répliqué sur autant de serveur esclave que nécessaire. Dans ce cas chaque serveur disposera d’un « server-id » différent des autres.

Pour qu’un serveur devienne esclave d’un autre maître, il est nécessaire d’arrêter le processus esclave :

mysql> STOP SLAVE ;
Query OK, 0 rows affected (0.07 sec)

… puis de recopier les données du nouveau maître sur l’esclave comme vu précédemment ; …

… ensuite, de définir le nouveaux serveur maitre via « CHANGE MASTER TO » :

mysql> CHANGE MASTER TO MASTER_HOST='srv2.rez0.lan', MASTER_USER='replicator',
    -> MASTER_PASSWORD='Le mot de passe', MASTER_LOG_FILE='mysqld-bin.000061', MASTER_LOG_POS=5918882;
Query OK, 0 rows affected (0.53 sec)

… enfin, de re démarrer le processus esclave :

mysql> START SLAVE ;
Query OK, 0 rows affected (0.00 sec)

Problèmes liés à la réplication

La réplication des bases de données MySQL génère sur le maître des fichiers de logs binaires d’une taille plus ou moins importante qui, au fil du temps peuvent remplir la partition qui les contiens. Cette saturation intervient plus ou moins vite en fonction des modifications des bases de données.

La taille maximale des fichiers de logs binaire est définie par la variable « max_binlog_size » à 1Go par défaut :

mysql> show variables like 'max_binlog_size' ;
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| max_binlog_size | 104857600 | 
+-----------------+-----------+
1 row in set (0.00 sec)

Cette taille peut être re définie dans le fichier « my.cnf » dans la partie « [mysqld] » :

srv ~ # cat /etc/mysql/my.cnf
...
[mysqld]
...
log-bin
server-id = 1
max_binlog_size   = 250M
...

Vous pouvez consulter la liste des fichiers de logs présents sur le maître ainsi que leurs taille par la commande « SHOW BINARY LOGS » :

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       125 | 
| mysql-bin.000002 |       264 | 
...
| mysql-bin.000013 |       149 | 
| mysql-bin.000014 |       106 | 
+------------------+-----------+
14 rows in set (0.00 sec)

Afin d’éviter de saturer l’espace disponible sur le serveur maître, renseignez la la variable ‘expire_logs_days‘ dans « my.cnf » qui définit l’age maximum des fichiers de logs présents sur le serveur.

srv ~ # grep expire_logs_days /etc/mysql/my.cnf
expire_logs_days = 2

Concrètement, si la connexion entre le maître et l’esclave est perdue plus de 2 jours, l’esclave ne sera plus en mesure de ce re synchroniser avec le maître. En revanche, l’espace consommé par les fichiers de logs sur le maître sera bien moins conséquent.

Si la partition d’enregistrement des logs est déjà saturée, vous pouvez effacer les fichiers parasites par la commande « PURGE BINARY LOGS ». Attention, il est fortement déconseillé de supprimer manuellement les fichiers de logs… préférez laisser cette tâche à MySQL qui nettoiera également les fichiers d’index des logs.

Avant d’entamer la suppression des fichiers de logs du maître, vérifier l’état de la réplication sur l’esclave par la commande « SHOW SLAVE STATUS » :

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: pluton.e-galaxie.org
                Master_User: replicator
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000064
        Read_Master_Log_Pos: 1644295
             Relay_Log_File: mysqld-relay-bin.000013
              Relay_Log_Pos: 1272204
      Relay_Master_Log_File: mysqld-bin.000064
           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: 1644295
            Relay_Log_Space: 1272204
            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 sur le maître par « SHOW MASTER STATUS » :

mysql> SHOW master STATUS\G
*************************** 1. row ***************************
            File: mysqld-bin.000064
        Position: 1644295
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

Deux cas de figures ce présentent :

  • Le maître et l’esclave sont en phase (même fichier de logs à la même position). Dans ce cas, lancez la création d’un nouveau fichier de logs par « FLUSH LOGS » avant de lancer un nettoyage des fichiers binaires allant jusqu’au dernier fraîchement créé ce qui donne sur le maître :
  • mysql> FLUSH LOGS ;
    Query OK, 0 rows affected (0.30 sec)
     
    mysql> SHOW master STATUS;
    +-------------------+----------+--------------+------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +-------------------+----------+--------------+------------------+
    | mysqld-bin.000065 |       98 |              |                  | 
    +-------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
     
    mysql> PURGE BINARY LOGS TO 'mysqld-bin.000065';
    Query OK, 0 rows affected (0.01 sec)
  • Seconde possibilité, le maître est l’esclave sont en décalage… (fichiers de logs différents ou position différente). Dans ce cas, lancer le nettoyage des logs du maître jusqu’au fichier ou est arrêté l’esclave. Si le fichier que lit l’esclave est « mysqld-bin.000061 », vous lancerez sur le maître :
  • mysql> PURGE BINARY LOGS TO 'mysqld-bin.000061';
    Query OK, 0 rows affected (0.01 sec)

Enfin si votre partition est complètement saturée au point de ne pouvoir générer un nouveau fichier de log neuf, il est possible de supprimer tous les fichiers antérieur à une date donnée toujours par la commande « PURGE BINARY LOGS » :

mysql> SELECT NOW() ;
+---------------------+
| NOW()               |
+---------------------+
| 2009-10-04 17:15:06 | 
+---------------------+
1 row in set (0.00 sec)
 
mysql> PURGE BINARY LOGS BEFORE '2009-10-04 00:00:00';
Query OK, 0 rows affected (0.01 sec)

Références

Le manuel MySQL Chapitre 6. Réplication de MySQL : http://dev.mysql.com/doc/refman/5.0/fr/replication.html

Laisser un commentaire

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