Créer une base de données MySQL répliquée

Soumis par Sylvain Lasnier le 07/08/2009 , modifié le 08/09/2009

Web Technology

Oups, serveur crashé. Mes données s'envolent.

De quand date la dernière sauvegarde de votre base de données ?
Avec des bases de données MySQL répliquées, la réponse est quelques secondes avant le crash.
La réplication vous apporte sécurité, performance et distribution de vos précieuses données.

Ce tutoriel vous présente pas-à-pas la création d'une base de données maître répliquée avec une base de données esclave avec MySQL.

Pourquoi répliquer une base de données MySQL

Utiliser une base de données répliquée apporte plusieurs avantages par rapport à une installation simple :

sécurité : la sauvegarde de la base esclave évite les locks sur la base maître. Il n'y a donc pas d'indisponibilité. La réplication reprend dès la fin de la sauvegarde.
performance :
- en utilisant des moteurs de base de données différents sur les 2 serveurs ; le moteur InnoDB sur le serveur maître pour bénéficier des transactions sécurisant les écritures et le moteur MyISAM sur l'esclave pour les performances en lecture
- en utilisant le serveur maître pour les opérations de mises à jour et les serveurs esclaves pour les opérations de lectures. Des serveurs maîtres peuvent être cascadés pour augmenter encore les performances. Ce système a été présenté par les architectes du site Flickr.
- en effectuant les extractions de données asynchrones sur l'esclave.
distribuer géographiquement les bases de données sur des serveurs esclaves pour optimiser la bande passante et limiter les problèmes de confidentialité des données, chaque base pouvant avoir des utilisateurs avec des droits spécifiques.

Différences entre des bases de données répliquées et un cluster de bases MySQL ?

Des bases de données répliquées n'offrent pas les mêmes services qu'un cluster de bases de données.
Un cluster de base de données MySQL gère automatiquement la répartition de la charge, l'intégration de nouveaux serveurs, la haute disponibilité sans interruption de service.
Ces services nécessitent une intervention humaine pour les bases de données répliquées. En contre-partie, l'installation de bases de données répliquées est plus simple.

Paramétrez le serveur de données maître

Dans ce tutorial, l'adresse IP du serveur maître est 192.168.0.201.
Initialisez les paramètres du fichier /etc/mysql/my.cnf de la base de données maître suivant :

mysql-slave:/# vi /etc/mysql/my.cnf 


server-id               = 201
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 3
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db        = mysql
#bind-address           = 127.0.0.1
skip-innodb

et redémarrez le serveur MySQL :

mysql-master:/# /etc/init.d/mysql restart

Créez un utilisateur dédié à la réplication :

mysql-master:/# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@'%' IDENTIFIED BY 'replication';

Récupérez le statut de la base de données maître pour initialiser l'esclave :

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

mysql> exit

Configurez le serveur de données esclave

L'adresse IP du serveur esclave est 192.168.0.202.

Éditez le fichier /etc/mysql/my.cnf de la base de données esclave :

mysql-slave:/# vi /etc/mysql/my.cnf

Et initialisez les paramètres suivant :

server-id               = 202
#bind-address           = 127.0.0.1
skip-innodb

Redémarrez le serveur MySQL :

mysql-slave:/# /etc/init.d/mysql restart

Initialisez la base de données esclave en utilisant une sauvegarde.
Sauvegardez la base maître, transférez la sauvegarde sur le serveur esclave et initialisez la base de données esclave :

mysql-master:/# mysqldump -A -uroot -p > all.sql
mysql-master:/# scp all.sql mysql-slave:/

mysql-slave:/# mysql -uroot -p < all.sql

Définissez le serveur maître sur le serveur esclave à partir des informations du statut récupéré précédemment et démarrez la réplication :

mysql-slave:/# mysql -u root -p
mysql> CHANGE MASTER TO
->     MASTER_HOST='192.168.0.201',
->     MASTER_USER='replication',
->     MASTER_PASSWORD='replication',
->     MASTER_LOG_FILE='mysql-bin.000002',
->     MASTER_LOG_POS=106;

mysql> START SLAVE;

Contrôlez le statut du serveur esclave :

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.201
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000004
             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: 106
              Relay_Log_Space: 552
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

Les indicateurs Slave_IO_Running et Slave_SQL_Running sont à Yes. Tout va bien.

Remarque 1 : vous pouvez créer autant d'esclaves que vous voulez en répétant les étapes qui les concernent et en utilisant un server-id unique.

Remarque 2 : vous pouvez définir un serveur esclave comme le maître d'un autre esclave. L'esclave de second niveau utilise alors l'esclave de premier niveau comme source de réplication. Ceci est utile pour les configurations très chargées.
Exemple Flickr : le maître est utilisé pour les mises à jour et n'est sollicité que par 2 esclaves, ces 2 esclaves sont utilisés comme sources de réplication par 2 fermes d'esclaves de second niveau. L'une des fermes d'esclaves est utilisée pour les lectures de données, l'autre pour les opérations de recherche plus longues.
Exemple SkyRock : une architecture similaire à celle de Flickr avec des grappes d'esclaves dédiées à des fonctions ciblées (gestion de profil, commentaire, rating, contenu...) permet de délivrer 5 milliards de pages par mois.

Testez le résultat

Pour vérifier que la réplication est opérationnelle, nous allons créer une base sur le serveur maître. Elle devrait alors être répliquée sur le serveur esclave.

mysql-slave:/# mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)


mysql-master:/# mysqladmin -uroot -p create test
mysql-master:/# mysqlshow  -uroot -p
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| database           |
| mysql              |
| test               |
+--------------------+


mysql-slave:/# mysqlshow  -uroot -p
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| database           |
| mysql              |
| test               |
+--------------------+

La base test a bien été répliquée de la base de données maître vers la base de données esclave.

Que faire en cas d'erreur

Les erreurs surviennent toujours côté machine esclave. Pour diverses raisons la réplication s'interrompt.
Étudiez les logs de l'esclave, corrigez le problème sur le maître ou l'esclave et redémarrez la réplication :

mysql> START SLAVE;

Contrôlez le status de l'esclave :

mysql> SHOW SLAVE STATUS\G

Si Slave_IO_Running et Slave_SQL_Running ne sont pas à Yes et que vous n'arrivez pas à corriger le problème, vous pouvez ré-initialiser totalement la base esclave à partir d'une sauvegarde comme décrit dans le paragraphe sur la configuration du serveur de données esclave.

Que faire en cas de panne hardware

Les pannes de serveur peuvent survenir sur le maître et l'esclave.

Si l'esclave tombe en panne, remplacez les éventuelles connexions de vos applications vers cette base par une connexion vers un autre esclave ou vers le maître. Reconfigurez un esclave en déroulant la procédure.

Si le maître tombe en panne, branchez vos applications sur un serveur esclave. L'esclave devient le nouveau serveur maître. Si vous avez plusieurs esclaves, changez leurs configurations pour qu'ils utilisent ce maître.
Reconfigurez le serveur en panne et initialisez-le comme un esclave du nouveau maître.

Source : MySQL

Ajouter un commentaire
1438 lectures