Publié le: 2014-11-07

MySQL replication

MySQL est un serveur de bases de données. Comme tout serveur de base de données, les données contenues à l’intérieur sont extrêmement importantes voire parfois vitales. Après vous avoir enseigné les quelques sécurités MySQL je vais désormais vous apprendre à manipuler la réplication MySQL, un mécanisme complexe d’utilisation mais permettant de redéployer rapidement une base de données plantée, de sauvegarder ses données ou encore de gérer du Load Balancing sur des données pratiquement statiques.

Avant de plonger dans le vif du sujet, je vais vous expliquer comment fonctionne la réplication.

Principe du mécanisme de réplication MySQL

La réplication MySQL se joue au moyen d’un principe de Maître-Esclave. Dans la configuration 5.x de MySQL, il ne peut y avoir qu’un seul maître par esclave, ou alors il faut ruser. Je vais vous expliquer pourquoi.

Les développeurs de MySQL ont choisi de définir le maître comme étant celui sur lequel se trouve la base utilisée et l’esclave comme réplicat.

La réplication s’effectue de la manière suivante :

  • L’esclave se connecte au maître et lui envoie sa version des logs binaires
  • Le maître regarde où il en est et envoie tous les logs binaires manquants
  • L’esclave exécute les logs binaires

Configuration du Master

Afin d’activer la réplication, votre serveur MySQL doit déjà écouter le réseau. Pour se faire, changez l’option bind-address. Si vous avez plusieurs interfaces réseau indiquez l’adresse IP de l’interface d’écoute. Si vous souhaitez mettre l’écoute sur toutes les interfaces tapez

bind-address = 0.0.0.0

Ensuite il faut activer l’identification serveur afin de pouvoir gérer la réplication. Décommentez la ligne server-id et donnez un identifiant unique à votre serveur.

server-id = 1

Activez maintenant les logs binaire, servant à la réplication

log-bin=mysql-bin

Nous allons définir les bases à répliquer ou non. Afin de sélectionner une base à répliquer, décommentez la ligne binlog_do_db. Si vous avez plusieurs bases entrez autant de lignes que de bases.

binlog_do_db = db1
binlog_do_db = db2
binlog_do_db = db3

ensuite nous allons ignorer les bases de données sensibles de MySQL, sur le même schéma que précédemment

binlog_ignore_db = mysql
binlog_ignore_db = information_schema

Voilà qui termine la configuration software. Redémarrez mysql et lancez la console d’administration.

service mysql restart
mysql -p

Nous allons tout d’abord créer un utilisateur de réplication sécurisé et lui donner les droits nécessaires pour répliquer.

CREATE USER 'replic-1-fwd'@'10.16.64.1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON db1.* TO 'replic-1-fwd'@'10.16.64.1' IDENTIFIED BY 'password';

Dans l’exemple précédent, nous avons créé l’utilisateur replic-1-fwd qui possède un mot de passe et pourra se connecter uniquement depuis la machine 10.16.64.1. Nous l’autorisons à répliquer la base db1 et toutes ses tables. Répétez l’action autant de fois qu’il y a de base à répliquer pour ce réplica.

Pour terminer, vérifiez la configuration du Maître en tapant :

SHOW MASTER STATUS;

Une fois ceci fait nous allons maintenant préparer le réplica.

Configuration du Slave

Le réplica nécessite aussi un server-id. Ouvrez cette fois-ci le fichier my.cnf sur le réplica et entrez un server-id non utilisé sur votre réseau. En revanche, a contrario du Master, le réplica n’a pas besoin d’écouter sur le réseau .

server-id = 2

Passons maintenant en console MySQL et configurons le réplica:

replica# mysql -p
Enter your password: ******
mysql> CHANGE MASTER TO
MASTER_HOST='10.16.64.3',
MASTER_USER='replic-1-fwd',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.00001',
MASTER_LOG_POS=105;

Remplacez mysql-bin.00001 par la donnée que vous a renvoyé SHOW MASTER STATUS sur le maître et 105 par la position donnée.

Note: la configuration interactive du slave n’est possible que depuis MySQL 5.5. Il faut la configurer dans le my.cnf pour les versions inférieures

Mise en place de la réplication

Il faut maintenant répliquer la base. Pour ce faire nous avons besoin d’un DUMP complet de celle-ci à l’instant T. Nous allons utiliser l’utilitaire mysqldump, l’envoyer sur le réplica et verrouiller provisoirement la base.

master# mysqldump db1 > db1.sql -p
Enter your password: ******
master# scp db1.sql root@10.16.64.1:/root/
master# mysql -p
Enter your password: ******
mysql> FLUSH TABLES WITH READ LOCK;

Passez maintenant sur le réplica. Nous allons créer et injecter la base à répliquer depuis la console MySQL.

mysql> CREATE DATABASE db1;
mysql> USE db1;
mysql> SOURCE /root/db1.sql

Voilà votre base est identique sur les 2 postes. Nous allons maintenant réactiver le mécanisme de réplication.

Sur le maître :

mysql> UNLOCK TABLES;

Sur l’esclave :

mysql> START SLAVE;
mysql> LOAD DATA FROM MASTER;

Voilà votre réplication SQL est terminée. Vous pouvez tester en insérant une donnée sur le maître, elle devrait être répliquée d’ici 1 minute sur l’esclave.

ATTENTION ! N’insérez JAMAIS de données sur l’esclave, vous risqueriez d’endommager la réplication. Il est possible de positionner un flag sur le slave pour qu’il soit uniquement en read-only.