Réplication Mysql
Mise en place d'une réplication Mysql maître/esclave.
La réplication Mysql consiste à avoir en temps réel deux bases de données Mysql identiques sur deux serveur différent afin de pouvoir basculer si besoin sur le deuxième serveur en cas de défaillance du premier.
Pré-requis :
Deux serveurs Mysql fonctionnel, vous pouvez vous aider de cet article si ce n'est pas le cas. Attention aux versions de vos Mysql certains ne sont pas bien copains.
Le Master :
On se connecte à la base de donnée du serveur maître.
mysql -h 192.168.100.1 -u root -ppassword ou mysql -u root -p (en local)
On crée un utilisateur pour la réplication :
GRANT REPLICATION SLAVE ON *.* TO repli@'%' IDENTIFIED BY 'repli';
On interdit l’écriture sur les bases :
FLUSH TABLES WITH READ LOCK;
Pour annulé l'interdit (mais faites le pas maintenant :p) :
UNLOCK TABLES;
On récupère le nom du fichier binaire, et son offset. Notez les, nous en auront besoin après pour configurer l’esclave.
SHOW MASTER STATUS;
Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et noter aussi le numéro dans "Position".
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 File et Position sont vides : Prenez comme nom de File : ‘’ et pour Position : 4. Quittez Mysql avec la commande quit
Arrêtez le serveur maître :
service mysql stop ou invoke-rc mysql stop
Editez le fichier my.cnf qui se trouve normalement dans /etc/ :
Ajoutez-y les lignes suivantes :
log-bin
server-id=1
Relancez le serveur Mysql :
service mysql start ou invoke-rc mysql start
On configure maintenant le serveur esclave (192.168.100.2) :
Éteignez le serveur comme vu plus haut ou avec cette commande :
mysqladmin -u root -ppassword -h 192.168.100.2 -P 3306 shutdown
Éditez le fichier my.cnf du serveur secondaire et :
Ajoutez-y les lignes suivantes :
server-id=2
master-host = 192.168.100.1
master-user = repli
master-password = repli
master-port = 3306
Relancez le serveur Mysql esclave comme vu plus haut pour le serveur maitre.
On se connecte au prompt Mysql :
mysql -u root -ppassword -h 192.168.1O0.2 -P 3306 ou mysql -u root -p
On change les données comme ceci si vous aviez un nom de fichier et une position:
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, tapez ceci:
CHANGE MASTER TO -> MASTER_LOG_FILE='', -> MASTER_LOG_POS=4; On démarre l’esclave START SLAVE;
Si tout c’est déroulé correctement, vous devriez voir quelque chose comme ceci :
SHOW SLAVE STATUS; *************************** 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 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 avec la commande SHOW PROCESSLIST:
SHOW PROCESSLIST; +----+-------------+----------------------------+------+---------+------+----------------------------------------------------------------------+------------------+ | 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 serveur esclave :
mysql -u root -ppassword -h 192.168.100.2 -P 3306 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 la réplication.
Si vous apercevez une erreur aux 2 lignes suivantes :
Last_Errno: Last_Error:
Voici comment faire :
Sur le master :
mysql -u root -ppassword -h 192.168.100.1 –P 3306 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Noter le nom du fichier dans la colonne “File” qui doit ressembler normalement à log-bin-… et notez également le numéro “Position”.
Faire un DUMP de la base de donnée:
mysqldump -u root -ppassword -r /root/dump.sql nomdelasauvegarde
Envoyer le fichier dump du serveur maître vers le serveur esclave:
scp /root/dump.sql root@192.168.100.2:/root
Sur le Slave, on bloque l'écriture :
mysql -u root -ppassword -h 192.168.100.2 -P 3306 FLUSH TABLES WITH READ LOCK; exit;
On intègre le dump du master dans la base “nomdelabase” :
mysql -h 192.168.100.2 -u root -ppassword -P 3306 nomdelabase < /root/dump.sql
On définit le bon fichier binaire du master et on redémarre le Slave:
mysql -u admin -ppassword -h 192.168.100.2 -P 3306 CHANGE MASTER TO -> MASTER_LOG_FILE='', -> MASTER_LOG_POS=POSITION_FICHIER_LOG; START SLAVE;
Normalement, la réplication est repartie correctement.
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 serveur esclave : On vérifie son état :
mysql -u root -ppassword -h 192.168.100.2 -P 3306 FLUSH TABLES WITH READ LOCK;
On crée un dump du slave:
mysqldump -u root -ppassword -h 192.168.100.2 -P 3306 -r /root/dump.sql nomdelabase
On envoi le dump sur le master:
scp /root/dump.sql root@192.168.100.1:/root
Sur le serveur maître :
mysql -h 192.168.100.1 -u root -ppassword -P 3306
On crée un utilisateur pour la réplication:
GRANT REPLICATION SLAVE ON *.* TO repli@'%' IDENTIFIED BY 'repli';
On stop l’écriture sur la base:
FLUSH TABLES WITH READ LOCK;
On injecte le dump du slave sur le master:
mysql -h 192.168.100.1 -u admin -ppassword -P 3306 nomdelabase < /root/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.
mysql -u root -ppassword -h 192.168.100.1 -P 3306 SHOW MASTER STATUS;
Noter le nom du fichier dans la colonne File qui doit ressembler normalement à log-bin-… et noter également le numéro Position.
Exemple:
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) exit
Éteignez la base:
mysqladmin -u root -ppassword -h 192.168.100.1 -P 3306 shutdown
Éditez le fichier my.cnf :
Ajoutez-y les lignes suivantes:
log-bin
server-id=1
Relancez la base Mysql
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:
mysql -u root -ppassword -h 192.168.100.2 -P 3306 CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=POSITION_FICHIER_LOG; START SLAVE;
Normalement, la réplication est repartie !
Vous pouvez vérifier l'etat du serveur esclave, avec les commandes SLAVE STATUS, et SHOW PROCESSLIST.
Testé avec une version 5.1 et 5.4
Si vous désirez aller plus loin : http://dev.mysql.com/doc/refman/5.0/fr/replication.html