Tutoriel de réplication sous MySQL

Après avoir cherché pas mal de solutions pour sauvegarder mes bases de données MySQL et pouvoir automatiquement les réinjecter en cas de problèmes, je me suis finalement tourné vers le système de réplication offert par la base de données open source en standard.
La réplication permet d'avoir une copie directe des données. Elle fonctionne selon le principe maître - esclave. L'esclave se connecte à intervalles réguliers sur le maître afin de maintenir à jour sa propre base de données.

Pour ce tutoriel, nous allons nous appuyer sur une petite étude de cas afin d'apprendre à utiliser la réplication sous MySQL.
Nous possédons deux serveurs de base de données MySQL (serveur_client et serveur_fournisseur).

L'un (serveur_client) héberge notre base de données « client » et l'autre (serveur_fournisseur) héberge la base de données « fournisseur ». Nous souhaitons que serveur_client réplique la base de données « fournisseur » de serveur_fournisseur et que serveur_fournisseur réplique la base « client » de serveur_client.

Nous allons installer deux serveurs de base de données MySQL sur une machine afin de pouvoir tester notre étude de cas. Chaque serveur possédera une installation particulière et fonctionnera sur un port différent.

1. Téléchargez l'archive : MySQL 4.0.22 (win-no-install).

Procédez comme suit pour installer et utiliser vos serveurs MySQL :

1. Décompressez cette archive dans C:\mysql_client ;
2. Décompressez une deuxième fois cette archive dans C:\mysql_fournisseur.

Vous devriez vous retrouver avec les répertoires suivants

Nous allons maintenant créer quatre fichiers pour pouvoir lancer nos serveurs :

1. C:\mysql_client\mysql_client.bat ;
2. C:\mysql_client\client.ini (fichier de configuration de serveur_client) ;
3. C:\mysql_fournisseur\mysql_fournisseur.bat ;
4. C:\mysql_fournisseur\fournisseur.ini (fichier de configuration de serveur_fournisseur).

C:\mysql_client\bin\mysqld-nt.exe --defaults-file= C:\mysql_client\client.ini --console

[mysqld]
datadir= C:\mysql_client\data
basedir= C:\mysql_client\
skip-innodb
port=3306

C:\mysql_fournisseur\bin\mysqld-nt.exe --defaults-file=C:\mysql_fournisseur\fournisseur.ini --console

[mysqld]
datadir= C:\mysql_fournisseur\data
basedir= C:\mysql_fournisseur\
skip-innodb
port=3305

En lançant C:\mysql_fournisseur\mysql_fournisseur.bat vous devriez voir :

En lançant C:\mysql_fournisseur\mysql_client.bat vous devriez voir :

Si vous voyez ces deux écrans, vos deux serveurs fonctionnent (ils indiquent sur quel port ils écoutent les connexions).

Nous allons maintenant créer la base de données client sur serveur_client.

1. Lancez une invite de commande DOS.
2. Allez dans : C:\mysql_client\bin.
3. Lancez :

mysql -u root -P 3306

Vous vous trouvez alors dans le shell MySQL et vous pouvez commencer à entrer des ordres SQL.

Copiez la totalité des ordres suivants, puis faites un clic droit dans l'invite de commande ouvert sur le shell MySQL (vos ordres seront alors exécutés sur serveur_client). Ceci aura pour effet de créer la base de données « client » et de créer la table mesclients en insérant quelques lignes.

Vous pouvez également utiliser ce script et le lancer via mysql -u root < C:\createClient.sql

# Création de la base de données client
CREATE DATABASE client ;

# Base client
 USE client;

# Création de la table mesclients
CREATE TABLE mesclients ( 
  id int(4) NOT NULL UNIQUE AUTO_INCREMENT,
  nom VARCHAR(50) NOT NULL,
  adresse VARCHAR(200) NOT NULL);

# Insertion de données
INSERT INTO mesclients VALUES('','client 1','adresse du client 1');
INSERT INTO mesclients VALUES('','client 2','adresse du client 2');
INSERT INTO mesclients VALUES('','client 3','adresse du client 3');
INSERT INTO mesclients VALUES('','client 4','adresse du client 4');
INSERT INTO mesclients VALUES('','client 5','adresse du client 5') ;

De la même manière, lancez une invite de commande DOS et connectez-vous à serveur_fournisseur.

Vous pouvez aussi utiliser ce script et le lancer via mysql -u root < C:\createFournisseur.sql

1. Lancez :

mysql -u root -P 3305

Copiez les ordres suivants afin de créer la base de données « fournisseur » et la table mesfournisseurs et ses données.

# Création de la base de données fournisseur
CREATE DATABASE fournisseur;

# Base fournisseur
USE fournisseur;

# Création de la table mesfournisseurs
CREATE TABLE mesfournisseurs ( 
  id int(4) NOT NULL UNIQUE AUTO_INCREMENT,
  nom VARCHAR(50) NOT NULL,
  adresse VARCHAR(200) NOT NULL);

# Insertion de données
INSERT INTO mesfournisseurs VALUES('',' fournisseur 1','adresse du fournisseur 1');
INSERT INTO mesfournisseurs VALUES('',' fournisseur 2','adresse du fournisseur 2');
INSERT INTO mesfournisseurs VALUES('',' fournisseur 3','adresse du fournisseur 3');
INSERT INTO mesfournisseurs VALUES('',' fournisseur 4','adresse du fournisseur 4');
INSERT INTO mesfournisseurs VALUES('',' fournisseur 5','adresse du fournisseur 5');

À présent, nos deux serveurs fonctionnent et possèdent chacun leur base de données et leurs tables.

Entrons maintenant dans ce qui nous intéresse  !

Avant de commencer à paramétrer nos serveurs, il faut leur donner un identifiant unique permettant de faire cohabiter plusieurs réplications sur notre réseau. Cet identifiant est spécifié dans le fichier de configuration à l'aide de la directive  : server-id.

Un serveur maître peut avoir plusieurs esclaves, mais un serveur esclave ne peut pas avoir plusieurs maîtres.
Bien sûr, un serveur maître peut également être esclave d'un 3e serveur.

MySQL utilise un format de log binaire afin de stocker son état. Le serveur esclave va se connecter au maître afin de regarder sa position dans le log binaire. Si la position du maître est différente de la sienne, il va mettre à jour sa base de données afin de se retrouver à la même position que son maître. Pour activer les logs binaires, il faut utiliser la directive  : log-bin.

Pour que l'esclave puisse se connecter au maître, il faut créer un utilisateur ayant les droits de REPLICATION SLAVE, SELECT, RELOAD et SUPER sur le maître.

Après s'être connecté à serveur_client (en root), on lance la commande SQL suivante :

GRANT REPLICATION SLAVE, 
SELECT, 
SUPER, 
RELOAD 
ON client.* 
TO replication@'localhost' 
IDENTIFIED BY 'replication' ;

De la même manière sur serveur_fournisseur (en root), on lance la commande SQL suivante :

GRANT REPLICATION SLAVE, 
SELECT, 
SUPER, 
RELOAD 
ON fournisseur.*
TO replication@'localhost' 
IDENTIFIED BY 'replication' ;

Il faut maintenant modifier les fichiers de configuration de nos deux serveurs afin d'activer la réplication.

Par défaut, l'esclave va répliquer toutes les bases de données du maître, mais nous pouvons le paramétrer afin que seules les bases qui nous intéressent soient prises en considération.

Ajoutons les lignes suivantes dans le fichier de configuration de serveur_fournisseur :

# réplique la base de données client
replicate-do-db=client
# réplique les requêtes multibases de client
replicate-wild-do-table=client.%

Puis dans le fichier de configuration de serveur_client :

# réplique la base de données fournisseur
replicate-do-db=fournisseur
# réplique les requêtes multibases de fournisseur
replicate-wild-do-table=fournisseur.%

Voici la totalité de chaque fichier de configuration :

[mysqld]
# activation des logs binaires
log-bin
# définition de l'identifiant unique
server-id=1

# nom d'hôte du maître
master-host=serveur_fournisseur
# port sur lequel écoute le serveur maître
master-port=3306
# nom utilisé pour se connecter au maître
master-user=replication
# mot de passe pour se connecter au maître
master-password=replication

[mysqld]
log-bin
server-id=2

master-host=serveur_client
master-port=3306
master-user=replication
master-password=replication

Nos deux serveurs sont maintenant paramétrés pour implémenter la réplication. Il suffit de charger les données du maître vers l'esclave et de démarrer le processus esclave (sur l'esclave) pour que la réplication fonctionne.

Relancez vos serveurs afin que les modifications soient prises en compte.

Il faut en premier lieu créer une base de données portant le nom de la base que nous répliquons. Comme nous répliquons la base client, nous allons créer cette base sur serveur_fournisseur.

Après s'être identifié en root sur serveur_fournisseur, lançons la commande SQL : CREATE DATABASE client ;

La base de données client existe maintenant sur serveur_fournisseur :

Lançons maintenant la commande MySQL : LOAD DATA FROM MASTER ;

Cette commande a pour effet de lire le fichier de configuration afin de charger les données que nous répliquons. Dans notre cas, MySQL va se connecter sur serveur_client afin de charger les données de la base « client ».
Il ne reste plus qu'à démarrer le processus esclave sur serveur_fournisseur : SLAVE START ;

Le processus esclave va maintenant vérifier en continu s'il est synchronisé avec son maître.

Essayons de lancer une requête d'insertion sur la table mesclients de la base client du serveur serveur_client afin de voir si elle sera répliquée sur serveur_fournisseur :

INSERT INTO client.mesclients VALUES ('','REPLICATION','Adresse REPLICATION') ;

SELECT * FROM client.mesclients ;

Bravo, nous constatons que l'insertion effectuée sur serveur_client a bien été répliquée sur serveur_fournisseur.

Si le test a bien fonctionné, il suffit alors d'effectuer l'étape « mise en œuvre » sur serveur_client afin qu'il réplique la base fournisseur de serveur_fournisseur.

Ce tutoriel rapide n'est qu'une introduction à la réplication sur MySQL, mais permet déjà de se faire une petite idée de la puissance d'un tel mécanisme. On peut paramétrer beaucoup plus finement les options de réplications afin par exemple de répliquer une base dans une autre, une table dans une autre, ou encore une table d'une base dans une autre table d'une autre base, etc.

Il est également possible de faire des réplications en chaîne avec par exemple trois serveurs (A-> B -> C -> A) ou plus (N -> N+1 -> N+2 -> N+n -> N) assurant ainsi un maximum de sécurité pour vos données.

Ce système offre donc la possibilité de garder en temps réel une copie de nos bases de données, mais c'est encore nous qui devons effectuer le basculement de la configuration d'une application si notre serveur tombe en panne. Lorsqu'il s'agit d'un site Web personnel, le problème est assez vite réglé, mais lorsque nous parlons de bases transactionnelles, ou d'infrastructures multiesclaves, le problème est tout autre.

Heureusement, MySQL prévoit une implémentation de type Cluster afin d'assurer une haute disponibilité, mais ça, c'est une autre histoire…