Tutoriel de réplication sous MySQL

Jérôme Grondin

Ce tutoriel va vous permettre de mettre en œuvre la réplication sous MySQL en seulement quelques minutes !

- Titre : Tutoriel de réplication sous MySQL

- Auteur : Jérôme Grondin

- Parution : 14 décembre 2004

- Mise à jour : 30 mars 2016

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2016 Jérôme Grondin. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Étude de cas

- III. Installation de deux serveurs MySQL sur la même machine

- IV. Mise en place de la configuration de réplication

- V. Mise en œuvre sur un serveur esclave (serveur_fournisseur)

- VI. Test du bon fonctionnement de la réplication

- VII. Conclusion

I - Introduction

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.

II - Étude de cas

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.

III - Installation de deux serveurs MySQL sur la même machine

Ceux qui possèdent déjà des serveurs fonctionnant sous MySQL n'ont pas besoin de lire cette partie.

Allez directement à la rubrique suivante

Les installations que nous utilisons ici ne doivent pas servir dans un environnement de production ! Il s'agit juste d'un apprentissage.

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).

Contenu de C:\mysql_client\mysql_client.bat

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

Contenu de C:\mysql_client\client.ini

1. [mysqld]

2. datadir= C:\mysql_client\data

3. basedir= C:\mysql_client\

4. skip-innodb

5. port=3306

Contenu de C:\mysql_fournisseur\mysql_fournisseur.bat

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

Contenu de C:\mysql_fournisseur\fournisseur.ini

1. [mysqld]

2. datadir= C:\mysql_fournisseur\data

3. basedir= C:\mysql_fournisseur\

4. skip-innodb

5. 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 :

1. mysql -u root -P 3306

-u :utilisateur, -P : port d'écoute du serveur.

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

1. # Création de la base de données client

2. CREATE DATABASE client ;

4. # Base client

5. USE client;

7. # Création de la table mesclients

8. CREATE TABLE mesclients (

9. id int(4) NOT NULL UNIQUE AUTO_INCREMENT,

10. nom VARCHAR(50) NOT NULL,

11. adresse VARCHAR(200) NOT NULL);

13. # Insertion de données

14. INSERT INTO mesclients VALUES('','client 1','adresse du client 1');

15. INSERT INTO mesclients VALUES('','client 2','adresse du client 2');

16. INSERT INTO mesclients VALUES('','client 3','adresse du client 3');

17. INSERT INTO mesclients VALUES('','client 4','adresse du client 4');

18. 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 :

1. 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.

1. # Création de la base de données fournisseur

2. CREATE DATABASE fournisseur;

4. # Base fournisseur

5. USE fournisseur;

7. # Création de la table mesfournisseurs

8. CREATE TABLE mesfournisseurs (

9. id int(4) NOT NULL UNIQUE AUTO_INCREMENT,

10. nom VARCHAR(50) NOT NULL,

11. adresse VARCHAR(200) NOT NULL);

13. # Insertion de données

14. INSERT INTO mesfournisseurs VALUES('',' fournisseur 1','adresse du fournisseur 1');

15. INSERT INTO mesfournisseurs VALUES('',' fournisseur 2','adresse du fournisseur 2');

16. INSERT INTO mesfournisseurs VALUES('',' fournisseur 3','adresse du fournisseur 3');

17. INSERT INTO mesfournisseurs VALUES('',' fournisseur 4','adresse du fournisseur 4');

18. 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  !

IV - Mise en place de la configuration de réplication

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 :

1. GRANT REPLICATION SLAVE,

2. SELECT,

3. SUPER,

4. RELOAD

5. ON client.*

6. TO replication@'localhost'

7. IDENTIFIED BY 'replication' ;

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

1. GRANT REPLICATION SLAVE,

2. SELECT,

3. SUPER,

4. RELOAD

5. ON fournisseur.*

6. TO replication@'localhost'

7. 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 :

1. # réplique la base de données client

2. replicate-do-db=client

3. # réplique les requêtes multibases de client

4. replicate-wild-do-table=client.%

Puis dans le fichier de configuration de serveur_client :

1. # réplique la base de données fournisseur

2. replicate-do-db=fournisseur

3. # réplique les requêtes multibases de fournisseur

4. replicate-wild-do-table=fournisseur.%

Voici la totalité de chaque fichier de configuration :

Fichier de configuration de serveur_client (C:\mysql_client\client.ini)

1. [mysqld]

2. # activation des logs binaires

3. log-bin

4. # définition de l'identifiant unique

5. server-id=1

7. # nom d'hôte du maître

8. master-host=serveur_fournisseur

9. # port sur lequel écoute le serveur maître

10. master-port=3306

11. # nom utilisé pour se connecter au maître

12. master-user=replication

13. # mot de passe pour se connecter au maître

14. master-password=replication

Fichier de configuration de serveur_fournisseur (C:\mysql_fournisseur\fournisseur.ini)

1. [mysqld]

2. log-bin

3. server-id=2

5. master-host=serveur_client

6. master-port=3306

7. master-user=replication

8. 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.

V - Mise en œuvre sur un serveur esclave (serveur_fournisseur)

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.

VI - Test du bon fonctionnement de la réplication

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 :

Sur serveur_client :

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

Sur serveur_fournisseur :

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.

VII - Conclusion

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…