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 suivanteMise en place de la configuration de réplication
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.
- Téléchargez l'archive : MySQL 4.0.22 (win-no-install).
Procédez comme suit pour installer et utiliser vos serveurs MySQL :
- Décompressez cette archive dans C:\mysql_client ;
- 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 :
- C:\mysql_client\mysql_client.bat ;
- C:\mysql_client\client.ini (fichier de configuration de serveur_client) ;
- C:\mysql_fournisseur\mysql_fournisseur.bat ;
- 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
2.
3.
4.
5.
[
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
2.
3.
4.
5.
[
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.
- Lancez une invite de commande DOS.
- Allez dans : C:\mysql_client\bin.
- Lancez :
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
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
#
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
- 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.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
#
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 !
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 :
2.
3.
4.
5.
6.
7.
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 :
2.
3.
4.
5.
6.
7.
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 :
2.
3.
4.
#
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 :
2.
3.
4.
#
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 :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
[
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
2.
3.
4.
5.
6.
7.
8.
[
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.
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 :
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.
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…