Mise en place d'une réplication MySQL

Vous souhaitez sauvegarder en temps réel votre serveur de bases de données MySQL ? Ou tout simplement commencez une architecture simple de répartition de charge ? La réplication unidirectionnelle de type Maître-Esclave est peut-être la solution :

1. Je considère que vous avez 2 serveurs MySQL installés de version 5 ou supérieure avec des tables au format MyISAM

2. Connectez-vous au maître en ligne de commande avec l'utilisateur root :

 mysql -uroot -p 

C'est l'Esclave qui va aller récupérer ses données régulièrement sur le Maître. Il faut donc créer sur le Maître un utilisateur MySQL pour que le serveur Esclave puisse s'y connecter :

 mysql > GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '<password>';

Cette instruction crée sur le maitre l'utilisateur "repl" qui peut se connecter de n'importe où (%) avec le mot de passe "<password>"

3. Sur le maître, on vide les tampons et on bloque les écritures sur les tables :

 mysql > FLUSH TABLES WITH READ LOCK;

4. Sauvegardez la base de données qui sera répliquée, soit avec la commande mysqldump :

 $ > mysqldump -uroot -p database > database.sql

Cela va créer un fichier database.sql qui contient les requêtes SQL nécessaires à la création de la structure de votre base de données et l'insertion de ses données.

Soit directement les fichiers dans le répertoire de MySQL avec la commande tar :

 $ > tar -cvzf database.tar.gz /var/lib/mysql/database

Cette commande crée un fichier database.tar.gz qui est une archive tar compressée du répertoire de votre base de données

"database" est à remplacer par le nom de votre base

5. Encore sur le Maître, nous allons récupérer la position qui représente la dernière opération effectuée par le maître afin que l'Esclave soit synchronisé avec le Maître :

 mysql > SHOW MASTER STATUS;

Notez dans un coin les informations des colonnes "File" et "Position"

6. Toujours sur le Maître, vous pouvez remettre en route l'écriture sur vos tables :

 mysql > UNLOCK TABLES;

8. Enfin, sur le Maître, modifiez la configuration en ajoutant les lignes suivantes dans votre fichier /etc/mysql/conf.d/custom.cnf (à créer si inexistant) :

 [mysqld] log-bin # Active la création de logs binaires ; ces logs contiennent les opérations effectués par la Maître ; ils seront lus par l'Esclave server-id=1 # Donnez un identifiant unique pour votre serveur Maître bind-addr=0.0.0.0 # Permet à MySQL d'écouter sur toutes les interfaces afin d'être joignable de l'extérieur 

Redémarrez votre serveur Maître :

 /etc/init.d/mysql restart

9. Sur votre serveur Esclave, stoppez le service MySQL :

 $ > /etc/init.d/mysql stop

10. Sur l'Esclave, modifiez également la configuration. Dans le fichier /etc/mysql/conf.d/custom.cnf (à créer si inexistant), ajoutez la ligne :

 [mysqld] server-id=2 # Donnez un identifiant unique pour votre serveur Esclave différent de l'identifiant du Maître bind-addr=0.0.0.0 # Permet à MySQL d'écouter sur toutes les interfaces afin d'être joignable de l'extérieur 

11. De nouveau sur l'Esclave, récupérez votre sauvegarde de base de données du Maître et importez la base :

Copiez votre sauvegarde du Maître à partir de l'Esclave

 $ > scp root@:/root/database.sql(.tar.gz) /root

A partir de l'export SQL :

 $ > mysql -uroot -p < database.sql

S'il vous indique l'erreur "No database selected", commencez par créer une base de données :

 $ > mysql -uroot -p mysql > CREATE DATABASE <nom de la base>; mysql > exit 

Puis ajouter dans votre commande le nom de la base en paramètre :

 $ > mysql -uroot -p <nom de la base> < database.sql

A partir de l'archive tar.gz :

 $ > cd /var/lib/mysql $ > tar xvzf database.tar.gz

12. C'est bientôt fini : sur l'Esclave, créez la réplication avec la commande :

 mysql > CHANGE MASTER TO MASTER_HOST='<master host name>', MASTER_USER='<replication user name>',  MASTER_PASSWORD='<replication password>',  MASTER_LOG_FILE='<recorded log file name>',  MASTER_LOG_POS=<recorded log offset>;

<master host name> est à remplacer par l'adresse IP ou le nom de domaine du serveur Maître

<replication user name> est à remplacer par l'utilisateur créé sur le Maître (repl dans l'exemple du point 2)

<replication password> est à remplacer par le mot de passe de l'utilisateur créé en 2

<recorded log file name> est à remplacer la valeur de la colonne "File" lue en point 6

<recorded log offset> est à remplacer la valeur de la colonne "Position" lue en point 6

13. Activez la réplication avec la commande suivante sur l'Esclave :

 mysql > START SLAVE;

14. Testez ! Insérez une donnée sur le Maître : celle-ci devrait apparaitre quasi-instantanément sur l'Esclave.

Sur l'Esclave, surveillez l'état de la connexion au Maître avec la commande :

 mysql > SHOW SLAVE STATUS;

Testé sur Debian Squeeze avec MySQL 5.

Pour sécuriser les échanges Maître-Esclave, il est possible de mettre en place une couche SSL