Recommend Me


Mercredi 22 février 2006

Réplication avec MySQL

(Linux Magazine France HS n°18 - Février 2004)

MySQL est certainement la base de données la plus utilisée par le quidam souhaitant créer son propre si web ou simplement découvrir rapidement le monde des SGBD. Elle doit très certainement sa notoriété à sa grande simplicité d’utilisation et d’administration. Et bien que les amoureux du tunning la trouvent souvent trop simpliste, il n’en reste pas moins qu’elle permet de mettre en place la réplication avec un minimum d’efforts la ou d’autre obligent d’avoir sous la main une brochette d’experts.

Dans cet article nous allons mettre en place une réplication entre deux bases MySQL en mode unidirectionnelle/asynchrone. En fait la propagation synchrone n’est pas possible avec MySQL - tout au moins pas avec le système de réplication fournis en standard avec la base.

1. Installation

L’architecture minimale pour faire de la réplication est composée de deux machines. Il est tout à fait possible de faire de la réplication entre deux instances de bases placées sur la même machine mais l’intérêt est limité.

Dans le cas présent, nous aurons la machine “uranium” servant de master et “helium” pour le slave. C’est deux machines sont installées sous Linux Debian Sid. Nous allons faire simple pour le moment et considérer que ni la base master, ni la base slave ne sont installées sur les machines. Nous utiliserons la version 4.0.16 de MySQL.

Installation du master

Vous l’avez certainement compris, la première chose à faire consiste à installer la base. Pour cela je vous laisse faire en fonction de vos habitudes.

Une fois la base prête, nous devons créer un utilisateur spécial qui va contrôler la réplication. Il doit pouvoir accéder à la base master depuis la machine où se trouvera le slave.

uranium:~# mysql -uroot -p mysql Enter password: Welcome TO the MySQL monitor.  Commands end WITH ; OR \g. Your MySQL connection id IS 78 TO server version: 4.0.16-log Type 'help;' OR '\h' FOR help. Type '\c' TO clear the buffer. master> GRANT FILE, REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl'; Query OK, 1 rows affected (0.14 sec) master>

Nous attribuons à cet utilisateur “repl” les droits FILE et REPLICATION SLAVE. Si vous utilisez une version antérieure à la 3.23.29, le droit REPLICATION SLAVE n’existe pas et FILE suffit donc. Si votre version est supérieure (ou égale) à la 3.23.29, vous pouvez ignorer le droit FILE. Pour des raisons de sécurité évidentes, nous ne donnerons pas plus de droits à cet utilisateur en le cantonnant ainsi dans son rôle de contrôleur de la réplication.

La seconde étape implique d’arrêter la base.

master> exit Bye uranium:~# /etc/init.d/mysql stop Stopping MySQL database server: mysqld. uranium:~#

Il faut maintenant sauvegarder les données de la base afin de pouvoir les transférer, plus tard, sur le slave. Pour savoir où se trouve le répertoire de données, regarder l’entrée datadir dans le fichier de configuration de la base (my.cnf).

uranium:~# locate my.cnf /etc/mysql/my.cnf uranium:~# cat /etc/mysql/my.cnf | grep datadir datadir         = /var/lib/mysql uranium:~# tar zcf /tmp/mysql-data-master.tgz /var/lib/mysql/ uranium:~#

Avant de pouvoir relancer la base, vous devez modifier le fichier de configuration. Ouvrez ce fichier avec l’éditeur de votre choix et ajoutez les deux lignes suivantes à la fin de la section [mysqld] :

/etc/mysql/my.cnf :

... [mysqld] ... server-id = 1 log-bin   = /var/log/mysql-bin.log ...

L’entrée server-id sert à identifier de manière unique une base dans un groupe de réplication. Vous pouvez lui attribuer la valeur de votre choix en fonction de votre logique. log-bin permet d’activer le log binaire tout en précisant l’emplacement et le nom de ce fichier de log. C’est dans ce dernier que vont être stockés les ordres de modification faite sur le master avant d’être envoyés au slave.

C’est terminé pour le master, vous pouvez redémarrer la base.

Installation du slave

Comme pour le master, il faut bien entendu commencer par installer la base. Ma remarque sur le sujet sera la même que pour l’installation de la base master. Une seule différence cependant, il est inutile de démarrer la base (ou si votre système de package ne vous laisse pas le choix, arrêtez-la).

Editez le fichier de configuration de la base et ajouter à la fin de la section [mysqld] les lignes suivantes :

/etc/mysql/my.cnf :

... [mysqld] ... server-id       = 2 log-bin         = /var/log/mysql-bin.log master-host     = uranium master-user     = repl master-password = repl master-port     = 3306 master-connect-retry = 10 ...

Nous retrouvons le server-id. Faites bien attention de lui attribuer une valeur différente de celle du master. Les entrées master-host, master-user, master-password et master-port sont suffisamment explicites pour que je ne m’étende pas dessus. La valeur de master-connect-retry indique, en secondes, le délais entre deux tentatives de connections au master en cas d’échec. Vous pouvez vous demander pourquoi nous activons le log binaire ici. Il s’agit d’un principe de protection qui prendra tout son intérêt lorsque nous parlerons de la tolérance de panne.

Il faut ensuite récupérer l’archive de sauvegarde des donnés que nous avons fait lors de l’installation du master. Une fois ceci fait, vous pouvez supprimer le répertoire de données qui a été créé lors de l’installation de la base slave (ou en faire une copie de sauvegarde) et le remplacer par les données issues du master.

helium:~# cd / helium:/# scp root@uranium:/tmp/mysql-data-master.tgz /tmp/ root@uranium's password: mysql-data-master.tgz                          100%   3.7K   1.0MB/s   00:02 helium:/# mv /var/lib/mysql /var/lib/mysql_old helium:/# tar zxf /tmp/mysql-data-master.tgz helium:/#

C’est terminé pour le slave, vous pouvez démarrer la base.

2. Tester la réplication

Nous sommes maintenant prêts à vérifier si cela fonctionne correctement. Nous profiterons de ces tests pour voir quelques commandes utiles pour l’administration d’un tel système et pour expliquer plus précisément comment fonctionne la réplication avec MySQL.

Etant partis d’un “système vide”, nous n’avons pour le moment que deux bases : mysql et test, nous allons créer une base de test “lmtest” dans laquelle nous allons créer une unique table “tbltest”. Bien entendu, nous faisons tout cela depuis le master :

master> CREATE DATABASE lmtest; Query OK, 1 row affected (0.00 sec) master> connect lmtest; Connection id:    9 Current DATABASE: lmtest master> CREATE TABLE tbltest (     ->   id       INT(11) AUTO_INCREMENT PRIMARY KEY,     ->   username VARCHAR(128) NOT NULL,     ->   age      INT(11),     ->   ts       TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP'     -> ); Query OK, 0 rows affected (0.00 sec) master> SHOW TABLES; +------------------+ | Tables_in_lmtest | +------------------+ | tbltest          | +------------------+ 1 row IN SET (0.00 sec) master>

Remarque : pour éviter la confusion, j’utilise master> comme prompt sur le master et slave> pour le slave.

Bon, regardons maintenant ce que nous avons sur le slave. Si vous ne vous êtes pas trompé lors de l’installation, voici ce que vous devez obtenir :

slave> SHOW DATABASES; +----------+ | DATABASE | +----------+ | lmtest   | | mysql    | | test     | +----------+ 5 rows IN SET (0.00 sec) slave> connect lmtest; Connection id:    10 Current DATABASE: lmtest slave> SHOW TABLES; +------------------+ | Tables_in_lmtest | +------------------+ | tbltest          | +------------------+ 1 row IN SET (0.00 sec) slave> DESC tbltest; +----------+---------------+------+-----+---------+----------------+ | FIELD    | Type          | NULL | KEY | DEFAULT | Extra          | +----------+---------------+------+-----+---------+----------------+ | id       | int(11)       |      | PRI | NULL    | AUTO_INCREMENT | | username | varchar(128)  |      |     |         |                | | age      | int(11)       | YES  |     | NULL    |                | | ts       | timestamp(14) | YES  |     | NULL    |                | +----------+---------------+------+-----+---------+----------------+ 4 rows IN SET (0.00 sec) slave>

Première constatation, le système de réplication de MySQL se démarque des autres par le fait que tout est répliqué. En effet avec des bases tel que Oracle ou DB2, vous avez la possibilité de répliquer seulement une base, voir même d’être plus fin en ne sélectionnant que certaines tables. Avec MySQL c’est tout ou rien. Dans la plus part des cas on serait tenté de dire que cela n’est pas grave. Pourtant cela n’est pas neutre.

Bon, poussons un peu plus loin et insérons des données :

master> INSERT INTO tbltest( username, age ) VALUES ( 'greg', 30 ); Query OK, 1 row affected (0.11 sec) master> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | +----+----------+------+----------------+ 1 row IN SET (0.00 sec) master>

Vérification sur le slave :

slave> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | +----+----------+------+----------------+ 1 row IN SET (0.01 sec) slave>

Tout fonctionne sans problèmes.

3. Ecrire sur le slave

Pour aller plus loin et comprendre comment fonctionne la réplication, vérifions ce qui se passe si nous insérons une ligne sur la slave puis quand nous faisons une modification sur le master.

slave> INSERT INTO tbltest( username, age ) VALUES ( 'arthur', 5 ); Query OK, 1 row affected (0.00 sec) slave> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | |  2 | arthur   |    5 | 20040110180618 | +----+----------+------+----------------+ 2 rows IN SET (0.00 sec) slave>

Jusque la tout semble normal ;) Par contre si vous faite un select sur le master, vous pouvez attendre aussi longtemps que vous le voulez, jamais vous ne verrez s’afficher les nouvelles données. Pire, ajoutons une ligne sur le master :

master> INSERT INTO tbltest( username, age ) VALUES ( 'colyne', 2 ); Query OK, 1 row affected (0.00 sec) master> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | |  2 | colyne   |    2 | 20040110153627 | +----+----------+------+----------------+ 2 rows IN SET (0.00 sec) master>

voici ce que nous avons ensuite sur le slave :

slave> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | |  2 | arthur   |    5 | 20040110180618 | +----+----------+------+----------------+ 2 rows IN SET (0.00 sec) slave>

Harg ! La ligne n’a pas été répliquée ! Et vous pouvez ajouter autant de lignes que vous le souhaitez sur le master, jamais vous ne les verrez apparaître sur le slave. Bien entendu ce problème n’est heureusement pas insoluble. Mais encore faut-il savoir ou est le problème. Pour cela, sur le slave, tapez la commande suivant :

slave> SHOW SLAVE STATUS; ... | Last_errno | Last_error                                      | ... ... | 1062       | Error 'Duplicate entry '2' for key 1' ON query  | ...

J’ai légèrement tronqué la sortie pour ne vous montrer que la partie qui nous intéresse. Comme on pouvait si attendre, le fait d’avoir insérer des données sur le slave bloque la réplication. C’est somme toute logique dans une politique de conservation des données. En effet le système n’est pas à même de choisir si c’est la slave qui à raison ou le master. Il attend donc une intervention humaine (bonne nouvelle pour les parano de SF ;)).

Vu la faible quantité de donnée impacté dans le cas présent, le plus simple consiste à identifier qui a raison (slave ou master) et modifier les données en conséquences. Donc si nous considérons dans le cas présent que la ligne sur le slave est mauvaise, il suffit tout simplement de la supprimer.

slave> DELETE FROM tbltest WHERE id='2';

Cependant cela ne suffit pas. Si vous essayez, vous constaterez que la réplication ne se fera pas plus malgré la suppression de la ligne responsable du conflit. Il faut en effet relancer la réplication. Pour cela rien de plus simple il suffit d’arrêter puis de redémarrer l’esclave. Bien entendu si vous avez une base en production cela peut pauser des problèmes. En général on ne coupe pas une base de données. Et bien là aussi c’est la même chose. Nous n’allons pas arrêter la base, juste stopper le mode esclave et le relancer. Pour cela tapez les commandes suivantes :

slave> slave stop; Query OK, 0 rows affected (0.00 sec) slave> slave start; Query OK, 0 rows affected (0.00 sec) slave> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | |  2 | colyne   |    2 | 20040110153627 | +----+----------+------+----------------+ 2 rows IN SET (0.00 sec) slave>

Simple non ? En fait dans le cas présent c’est effectivement très simple, mais si vous vous lancez vraiment dans la réplication attendez-vous à devoir traiter des cas beaucoup plus épineux.

4. Tolérance de panne

Maintenant que vous savez que tout cela fonctionne, vous pouvez envisager de faire de la tolérance de panne. Imaginons pour cela que vous ayez un site web avec lequel vous utilisez une base de données - rien de plus classique en fait. Maintenant imaginer que la machine hébergeant votre base de données décide de rendre l’âme. Là généralement c’est la catastrophe. Mais non puisque vous avez mis votre base en réplication ! En effet toutes vos données sont sur le slave donc il suffit de demander à vos scripts d’attaquer le slave.

Bien entendu tout ceci se prépare. En effet il ne faut pas attendre que vous ayez remarqué que le master est dans les choux pour basculer sur le slave. Donc il peut être intéressant d’écrire un script vérifiant l’état du master et d’utiliser un DNS que vous pourrez alors modifier dynamiquement grâce à nsupdate si vous utilisez bind par exemple.

Le second problème interviendra lorsque vous aurez remonté votre (ancien) master. En effet pendant un certain temps c’est le slave qui a recueilli les données. Bien entendu, vous pouvez faire une sauvegarde de vos données du slave, l’arrêter, tout remettre sur le master et refaire la configuration. Malheureusement cela vous oblige à stopper la base et donc à rendre votre service indisponible pendant un certain temps (qui sera généralement plus long que celui que vous avez planifié) ce qui ne plaira certainement pas a tout le monde :).

La “bonne” solution consisterait à “inverser” slave et master. Ceci est tout à fait possible car nous avons activé le log binaire sur le slave. Il suffit donc pour le transformer en master de lui passer la commande SLAVE STOP. Ensuite nous devons récupérer les données pour les copier vers l’ancien master (qui deviens donc la slave - et oui, faut suivre !) Pour cela vous il faut créer une archive des données comme nous l’avons vue lors de la procédure d’installation. Il est impératif que les données ne soient pas modifiées lors de cette opération. Pour cela il suffit d’utiliser la commande FLUSH TABLES WITH READ LOCK, de créer l’archine (avec tar par exemple) puis de faire un UNLOCK TABLES à la fin :

slave> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) slave> quit Bye helium:~# tar zcf /tmp/mysql-data-slave.tgz /var/lib/mysql/ helium:~# mysql mysql Welcome TO the MySQL monitor.  Commands end WITH ; OR \g. Your MySQL connection id IS 24 TO server version: 4.0.16-log Type 'help;' OR '\h' FOR help. Type '\c' TO clear the buffer. slave> SHOW MASTER STATUS; +---------------+----------+--------------+------------------+ | File          | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | mysql-bin.012 | 1212     |              |                  | +---------------+----------+--------------+------------------+ 1 row IN SET (0.00 sec) slave> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) slave>

Il suffit maintenant de réinstaller la base sur(l’ancien) master comme nous l’avons vu plus haut (installation du slave). Il faut ensuite ajuster la position de lecture du log binaire, c’est pour cela que nous avons fait un SHOW MASTER STATUS avant de délocker les tables :

master> slave stop; Query OK, 0 rows affected (0.00 sec) master> CHANGE MASTER TO     -> MASTER_HOST='uranium',     -> MASTER_PORT=3306,     -> MASTER_USER='repl',     -> MASTER_PASSWORD='repl',     -> MASTER_LOG_FILE='mysql-bin.012',     -> MASTER_LOG_POS=1212; Query OK, 0 rows affected (0.00 sec) master> slave start; Query OK, 0 rows affected (0.01 sec) master>

A partir de la tout doit fonctionner comme avant :

slave> INSERT INTO tbltest( username, age ) VALUES ( 'arthur', 5 ); Query OK, 1 row affected (0.00 sec) slave> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | |  2 | colyne   |    2 | 20040110153627 | |  3 | morgane  |   30 | 20040110153820 | |  4 | maguy    |   60 | 20040110215139 | |  5 | arthur   |    5 | 20040111011340 | +----+----------+------+----------------+ 5 rows IN SET (0.00 sec) slave>

Et sur le master devenu slave :

master> SELECT * FROM tbltest; +----+----------+------+----------------+ | id | username | age  | ts             | +----+----------+------+----------------+ |  1 | greg     |   30 | 20040110152747 | |  2 | colyne   |    2 | 20040110153627 | |  3 | morgane  |   30 | 20040110153820 | |  4 | maguy    |   60 | 20040110215139 | |  5 | arthur   |    5 | 20040111011340 | +----+----------+------+----------------+ 5 rows IN SET (0.00 sec) master>

5. Aller plus loin

Maintenant que vous avez les connaissances nécessaires pour mettre en place la réplication vous pouvez envisager des architectures plus complexes avec plusieurs esclaves. Remarquez que vous pouvez parfaitement avoir une machine esclave qui sert de maître à d’autres. Ceci permet de mettre en place une architecture sous forme d’arbre. Dans tous les cas, n’oubliez jamais que vous devez toujours écrire sur la machine racine.

• • •

2 commentaires »

  1. Bonsoir,

    Je suis en train de démarrer avec la réplication mySQL et je trouve cet article fort intéressant.
    Cependant des erreurs du type Warning: “fread(): Length parameter must be greater than 0. in /var/alternc/html/g/greg/www/blog/wp-content/plugins/vimcolor/wp-vimcolor.php on line 96″ sont listées en haut de la page HTML et tous les “bloccode” sont vides.

    J’utilise Internet Explorer 6.0.
    Merci.

    Commentaire par phil.causse — Jeudi 7 décembre 2006 @ 18:05
  2. Le problème est corrigé… avec toutes mes excuses !

    Commentaire par greg — Jeudi 7 décembre 2006 @ 18:21

RSS des commentairesTrackBack URI

Laisser un commentaire

You must be logged in to post a comment.

Powered by: WordPress • Template adapted from the Simple Green' Wench theme - RSS