Configuration de la réplication maître-maître avec MySQL sur Debian 8 (Jessie)

Ce tutoriel décrit une configuration MySQL répliquée (réplication Maître/Maître) avec 2 nœuds où les données peuvent être lues et écrites sur les deux nœuds en même temps. MySQL prend soin de répliquer les données sur l'autre nœud et s'assure que les clés primaires d'incrémentation automatique n'entrent pas en conflit.

Depuis la version 5, MySQL est livré avec un support intégré pour la réplication maître-maître, résolvant le problème qui peut survenir avec les clés auto-générées. Dans les anciennes versions de MySQL, le problème avec la réplication maître-maître était que les conflits survenaient immédiatement si le nœud A et le nœud B inséraient tous les deux une clé auto-incrémentée sur la même table. Les avantages de la réplication maître-maître par rapport à la réplication maître-esclave traditionnelle sont que vous n'avez pas à modifier vos applications pour faire des accès en écriture uniquement au maître, et qu'il est plus facile de fournir une haute disponibilité car si le maître tombe en panne, vous avez toujours l'autre maître.

1 Remarque préliminaire

Dans ce tutoriel, je vais montrer comment répliquer la base de données exampledb du serveur server1.example.com avec l'adresse IP 192.168.1.101 vers le serveur server2.example.com avec l'adresse IP 192.168.1.102 et vice versa. Chaque système est l'esclave de l'autre maître et le maître de l'autre esclave en même temps. Les deux systèmes exécutent Debian 8; cependant, la configuration devrait s'appliquer à presque toutes les distributions avec peu ou pas de modifications.

2 Installer MySQL 5.5

Si MySQL n'est pas déjà installé sur le serveur1 et le serveur2, installez-le maintenant :

serveur1/serveur2 :

apt-get -y install mysql-server-5.5 mysql-client-5.5

Pour s'assurer que la réplication peut fonctionner, nous devons faire écouter MySQL sur toutes les interfaces, donc nous commentons la ligne bind-address =127.0.0.1 dans /etc/mysql/my.cnf :

serveur1/serveur2 :

nano /etc/mysql/my.cnf

[...]# Au lieu de sauter la mise en réseau, la valeur par défaut est désormais d'écouter uniquement sur# localhost qui est plus compatible et n'est pas moins sécurisé.#bind-address =127.0.0.1[...]

Redémarrez MySQL ensuite :

serveur1/serveur2 :

service redémarrage mysql

Vérifiez ensuite avec

serveur1/serveur2 :

netstat -tap | grep mysql

que MySQL est vraiment à l'écoute sur toutes les interfaces :

netstat -tap | grep mysql
tcp 0 0 *:mysql *:* ÉCOUTEZ 15437/mysqld
server1:~#

Maintenant, nous configurons un utilisateur de réplication slave2_user qui peut être utilisé par server2 pour accéder à la base de données MySQL sur server1.

serveur1 :

Connectez-vous au shell MySQL :

mysql --defaults-file=/etc/mysql/debian.cnf

Sur le shell MySQL, exécutez les commandes suivantes :

serveur1 :

ACCORD REPLICATION SLAVE ON *.* TO [email protected]' %' IDENTIFIED BY 'secretpassword' ;
FLUSH PRIVILEGES ;
quitter ;

Remplacez le mot "mot de passesecret " avec un mot de passe sécurisé de votre choix. Nous recommençons maintenant les deux dernières étapes sur le serveur2 :

serveur2 :

mysql --defaults-file=/etc/mysql/debian.cnf

ACCORD REPLICATION SLAVE ON *.* TO [email protected]' %' IDENTIFIED BY 'secretpassword' ;
FLUSH PRIVILEGES ;
quitter ;

Remplacez le mot "mot de passesecret " avec un mot de passe sécurisé ici également. Notez les mots de passe car nous en aurons besoin plus tard. 

3 Quelques notes

Dans ce qui suit, je supposerai que les deux serveurs MySQL sont vides (ne contiennent pas encore de base de données à l'exception de la base de données 'mysql').

Si ce n'est pas le cas sur votre serveur, vous devez verrouiller et vider les bases de données sur le premier serveur et les importer sur le second avant de continuer. Ne déverrouillez pas les bases de données avant la configuration de la réplication. Ci-dessous quelques commandes qui montrent comment copier toutes les bases de données sur un nouveau serveur au cas où vous ne commenceriez pas avec une configuration MySQL "propre".

Exemple sur la façon de verrouiller toutes les tables de base de données dans une base de données MySQL.

FLUSH TABLES WITH READ LOCK ;
SET GLOBAL read_only =ON ;

Exemple sur la façon de vider toutes les bases de données dans un fichier all_databases.sql.

mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers --all-databases -r all_databases.sql 

 Exemple d'importation de toutes les tables sur le deuxième serveur à partir du fichier all_databses.sql.
 
mysql --defaults-file=/etc/mysql/debian.cnf  

 
 
4 Configuration de la réplication
 

 Maintenant, nous configurons la réplication maître-maître dans /etc/mysql/my.cnf. Les options de configuration cruciales pour la réplication maître-maître sont auto_increment_increment et auto_increment_offset :
 
 
auto_increment_increment contrôle l'incrément entre les valeurs AUTO_INCREMENT successives.
 
auto_increment_offset détermine le point de départ des valeurs de la colonne AUTO_INCREMENT.
 
 

 Supposons que nous ayons N nœuds MySQL (N=2 dans cet exemple), alors auto_increment_increment a la valeur N sur tous les nœuds, et chaque nœud doit avoir une valeur différente pour auto_increment_offset (1, 2, ..., N).
 

 Configurons maintenant nos deux nœuds MySQL :
 

 serveur1 :
 
nano /etc/mysql/my.cnf
 

 Recherchez la section qui commence par [mysqld] et mettez-y les options suivantes (en commentant tous les éléments en conflit existants option):
 
[...][mysqld]
# ID de serveur unique
server-id =1

# Ne pas répliquer les bases de données suivantes
binlog-ignore -db =mysql
replicate-ignore-db =mysql

# Décalage d'incrémentation automatique
auto-increment-increment =2

# Ne pas répliquer requêtes sql pour l'ID de serveur local
replicate-same-server-id =0

# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset =1
 
# Supprimer les données binlog après 10 jours
expire_logs_days =10

# Taille maximale du binlog
max_binlog_size =500M

# Chemin du fichier binlog
log_bin =/var/log/mysql/mysql-bin.log

[...]
 

 Puis redémarrez MySQL :
 

 serveur1 :
 
service redémarrage mysql
 

 Faites maintenant la même chose sur le serveur2 :
 

 serveur2 :
 
nano /etc/mysql/my.cnf
 
[...]

# Unique Server ID
server-id =2

# Ne répliquez pas les bases de données suivantes
binlog- ignore-db =mysql
replicate-ignore-db =mysql

# Décalage d'incrémentation automatique
auto-increment-increment =2

# Ne pas répliquer les requêtes sql pour l'ID du serveur local
replicate-same-server-id =0

# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset =2

# Supprimer les données binlog après 10 jours
expire_logs_days =10

# Taille maximale du binlog
max_binlog_size =500M

# Chemin du fichier binlog 
log_bin =/var/log/mysql/mysql-bin.log

[...]
 

 serveur2 :
 
service redémarrage mysql
 

 Ensuite, nous verrouillons la base de données exampledb sur server1, découvrons le statut principal de server1, créons un vidage SQL de exampledb (que nous importerons dans exampledb sur server2 afin que les deux bases de données contiennent les mêmes données), et déverrouillerons la base de données afin qu'elle peut être réutilisé :
 

 serveur2 :
 

 Nous commençons maintenant la réplication sur le serveur 2. Ouvrez le shell MySQL :
 
mysql --defaults-file=/etc/mysql/debian.cnf
 

 Et exécutez la commande SQL suivante pour activer la réplication du serveur1 vers le serveur2 :
 
CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_USER='repl', MASTER_PASSWORD='secretpassword';
 

 Remplacez mot de passesecret avec le mot de passe pour le repl Utilisateur MySQL que vous avez défini au chapitre 2.
 

 Vérifiez maintenant le statut de l'esclave en exécutant la commande "show slave status\G" dans le shell MySQL.
 
afficher l'état de l'esclave\G
 

 Le résultat ressemblera à ceci :
 
mysql> show slave status\G
*************************** 1. ligne ****** *********************
 Slave_IO_State :
 Master_Host :192.168.1.101
 Master_User :repl
 Master_Port :3306 
 Connect_Retry :60
 Master_Log_File :mysql-bin.000001
 Read_Master_Log_Pos :107
 Relay_Log_File :mysqld-relay-bin.000003
 Relay_Log_Pos :253
 Relay_Master_Log_File :mysql-bin.000001
 Slave_IO_Running :Non
 Slave_SQL_Running :Non
 Replicate_Do_DB :
 Replicate_Ignore_DB :mysql
 Replicate_Do_Table :
 Replicate_Ignore_Table :
 Replicate_Wild_Do_Table :
 Replicate_Wild_Ignore_Table :
 Last_Errno :0
 Last_Error :
 Skip_Counter :0
 Exec_Master_Log_Pos :107
 Relay_Log_Space :410
 Until_Condition : Aucun
 /> Jusqu'à_Log_File :
 Jusqu'à_Pos_Log_Log : 0
 Master_SSL_Allowed :Non
 Master_SSL_CA_File :
 Master_SSL_CA_Path :
 Master_SSL_Cert :
 Master_SSL_Cipher :
 Master_SSL_Key :
 Seco nds_Behind_Master :NULL
Master_SSL_Verify_Server_Cert :Non
 Last_IO_Errno :0
 Last_IO_Error :
 Last_SQL_Errno :0
 Last_SQL_Error :
 Replicate_Ignore_Server_Ids :
 Master_Server_Id :1
1 ligne dans le jeu (0.00 sec)
 

 Les lignes que vous devez vérifier sont les suivantes :
 
Master_Host :192.168.1.101
Master_User :repl
Master_Port :3306
Master_Log_File :mysql-bin.000001
Relay_Log_File :mysqld-relay-bin.000003
 Slave_IO_Running :Non
Slave_SQL_Running :Non
 

 Lancez maintenant la réplication avec cette commande sur le shell MySQL :
 
démarrer l'esclave ;
 

 puis vérifiez à nouveau l'état de l'esclave :
 
afficher l'état de l'esclave\G
 

 Les deux lignes suivantes doivent maintenant afficher "oui" :
 
Slave_IO_Running :Oui
Slave_SQL_Running :Oui
Seconds_Behind_Master :0
 

 Si "Seconds_Behind_Master" n'est pas 0, attendez quelques secondes et vérifiez à nouveau l'état. Ce champ indique si le maître et l'esclave sont synchronisés.
 

 Pour l'étape suivante, nous devons connaître les valeurs de "Master_Log_File" et "Read_Master_Log_Pos" la commande "show slave status\G". Dans mon cas, ce sont :
 
Master_Log_File :mysql-bin.000001
Read_Master_Log_Pos :107
 

 Notez les valeurs que vous obtenez sur votre serveur, nous en avons besoin pour la prochaine étape sur le serveur 1.
 

 Ensuite, vous pouvez quitter le shell MySQL :
 
quitter
 

 
 

 serveur1 :
 

 Nous continuons sur le premier serveur, ouvrons le shell MySQL sur le serveur 1 :
 
mysql --defaults-file=/etc/mysql/debian.cnf
 

 Et exécutez la commande MySQL suivante :
 
CHANGER LE MAITRE EN MASTER_HOST='192.168.1.102', MASTER_USER='repl', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 ;
 

 Vous devez remplacer quelques éléments dans la commande ci-dessus :
 
 
L'adresse IP doit être l'adresse IP de votre deuxième serveur MySQL.
 
Le mot de passe "secretpassword" doit être celui que vous avez choisi au chapitre 2 pour l'utilisateur remplaçant
 
Le MASTER_LOG_FILE et le MASTER_LOG_POS doivent être les valeurs que nous avons écrites à la dernière étape.
 
 

 Vérifiez maintenant avec :
 
afficher l'état de l'esclave\G
 

 sur le shell MySQL s'il n'y a pas d'erreurs.
 
mysql> show slave status\G
*************************** 1. ligne ****** *********************
 Slave_IO_State :
 Master_Host :192.168.1.102
 Master_User :repl
 Master_Port :3306 
 Connect_Retry :60
 Master_Log_File :mysql-bin.000001
 Read_Master_Log_Pos :107
 Relay_Log_File :mysqld-relay-bin.000001
 Relay_Log_Pos :4
 Relay_Master_Log_File :mysql-bin.000001
 Slave_IO_Running :Non
 Slave_SQL_Running :Non
 Replicate_Do_DB :
 Replicate_Ignore_DB :mysql
 Replicate_Do_Table :
 Replicate_Ignore_Table :
 Replicate_Wild_Do_Table :
 Replicate_Wild_Ignore_Table :
 Last_Errno :0
 Last_Error :
 Skip_Counter :0
 Exec_Master_Log_Pos :107
 Relay_Log_Space :107
 Until_Condition : Aucun
 /> Jusqu'à_Log_File :
 Jusqu'à_Pos_Log_Log : 0
 Master_SSL_Allowed :Non
 Master_SSL_CA_File :
 Master_SSL_CA_Path :
 Master_SSL_Cert :
 Master_SSL_Cipher :
 Master_SSL_Key :
 Deuxième s_Behind_Master :NULL
Master_SSL_Verify_Server_Cert :Non
 Last_IO_Errno :0
 Last_IO_Error :
 Last_SQL_Errno :0
 Last_SQL_Error :
 Replicate_Ignore_Server_Ids :
 Master_Server_Id :0
1 ligne dans le jeu (0.00 sec)
 

 Et démarrez l'esclave.
 
démarrer l'esclave ;
 

 Vérifiez à nouveau l'état de l'esclave :
 
afficher l'état de l'esclave\G
 

 Les deux lignes suivantes doivent maintenant afficher "oui" :
 
Slave_IO_Running :Oui
Slave_SQL_Running :Oui
 

 Ensuite, vous pouvez quitter le shell MySQL :
 
quitter
 

 Si tout va bien, la réplication MySQL maître-maître devrait maintenant fonctionner. Si ce n'est pas le cas, veuillez vérifier /var/log/syslog pour les erreurs MySQL sur le serveur1 et le serveur2.
 

 
 
5 Tester la réplication
 

 Il est maintenant temps de tester notre configuration de réplication. Je vais créer une base de données exampledb1 sur le serveur1, puis vérifier sur le serveur2 si la base de données a été répliquée sur le deuxième serveur :
 

 serveur1 :
 

 Connectez-vous à la console MySQL sur le serveur 1 et créez la base de données :
 
mysql --defaults-file=/etc/mysql/debian.cnf
 
CRÉER BASE DE DONNÉES exampledb1 ;
 

 serveur2
 

 Connectez-vous maintenant à la console MySQL sur le serveur2 et vérifiez si exampledb1 existe maintenant :
 
mysql --defaults-file=/etc/mysql/debian.cnf
 
afficher les bases de données ;
 

 Comme nous pouvons le voir, la nouvelle base de données apparaît également sur le serveur2.
 
mysql> affiche les bases de données ;
+--------------------+
| Base de données |
+--------------------+
| information_schema |
| exempledb1 |
| mysql |
| performance_schema |
+--------------------+
4 lignes dans l'ensemble (0.00 sec)
 

 Ensuite, je testerai si la réplication fonctionne également dans l'autre sens. Nous sommes toujours connectés sur server2 et y créons une base de données exampledb2 :
 
CRÉER BASE DE DONNÉES exampledb2 ;
 

 Revenez maintenant au serveur 1 et exécutez "afficher les bases de données" dans la console MySQL :
 

 serveur1
 
afficher les bases de données ;
 

 Le résultat montre notre nouvelle base de données exampledb2, donc la réplication fonctionne dans les deux sens.
 
mysql> affiche les bases de données ;
+--------------------+
| Base de données |
+--------------------+
| information_schema |
| exempledb1 |
| exempledb2 |
| mysql |
| performance_schema |
+--------------------+
5 lignes dans l'ensemble (0,01 sec)
 

 
 
6 liens
 
 
MySQL :http://www.mysql.com
 
Debian :http://www.debian.org