MariaDB - Réplication master-master

La réplication maître-maître (master-master) est une façon relativement simple d'avoir deux SGBD synchronisées via le réseau et donc identiques.

C'est cette technologie qui est utilisée pour permettre la redondance entre nos deux Wiki: https://doc.ycharbi.fr et https://doc.lesmorin.fr. Elle est beaucoup plus fiable que Galera car bien que certains trucs chelous puissent apparaître lors de l'installation, une fois en place, c'est béton (contrairement à ce dernier).

Nous partons du postula que nous avons deux serveurs à synchroniser. À quelques choses près, les mêmes actions sont à effectuées sur les deux nœuds.

Sur le nœud 1

Afin d'être indifférent face à un éventuel changement d'adresse IP, je vous conseils d'utiliser un nom d'hôte plutôt qu'une IP pour mettre en place la synchronisation. Après, vous êtes libres de vous torcher avec mon conseil... Pour ma part, j'utilise ceci:

vim /etc/hosts

192.168.1.201	noeud1
192.168.1.202	noeud2

Tester la connectivité

ping noeud1
ping noeud2

Installation

apt install mariadb-server

Configuration

Fichier de configuration

Éditer le fichier de configuration serveur

vim /etc/mysql/mariadb.conf.d/50-server.cnf

(son emplacement et son nom changent à chaque versions, adaptez-vous à votre époque...)

Commenter (ligne 29)

bind-address          = 127.0.0.1

Dé-commenter (ligne 74)

server-id               = 1

Dé-commenter (ligne 75)

log_bin                 = /var/log/mysql/mysql-bin.log

Redémarrer le service

systemctl restart mariadb.service

Requêtes de synchronisation

Se connecter à MariaDB en root

mysql -u root -proot

Depuis peu, MariaDB ne configure plus de mot de passe par défaut pour la connexion root SQL lorsque l'on est connecté en tant que root POSIX. On peut donc mettre n'importe quoi comme mot de passe et ça fonctionne. Par contre, il est impossible de s'y connecté depuis un autre utilisateur (il faudra configurer un mot de passe root SQL pour ce faire).

Créer un utilisateur dédié à la réplication

GRANT replication slave on *.* to 'replica'@'%' identified by 'replica';

Cet utilisateur n'a que les droits de réplication et ceux, sur toute les bases de données.

Arrêter le mode esclave

stop slave;

Récupérer les informations à donner au nœud 2

show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      497 |              |                  |
+------------------+----------+--------------+------------------+

Ce qui est important dans ce qui s'affiche, c'est File et Position. Ce sont ces deux informations qui seront entrées dans le noeud 2. Pour notre part, nous allons entrer celles du nœud 2 dans notre nœud 1.

CHANGE MASTER TO MASTER_HOST='noeud2', MASTER_USER='replica', MASTER_PASSWORD='replica', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=497;

ATTENTION

À ce stade, il faut attendre que le nœud 2 ai changé son server_id (cette étape est expliqué plus loin). Une fois ceci fait, vous pouvez continuer. Si vous taper la commande start slave; avant ce pré-requis, le paramètre Slave_IO_Running: sera à No au lieu de Yes. Pour corriger ça après coup, il faudra faire un stop slave; suivi d'un start slave; après le changement indiqué sur le nœud 2.

Démarrer l'esclave

start slave;

Afficher le statut de l'appairage

show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: noeud2
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 497
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 292
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 497
              Relay_Log_Space: 591
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

Sur le nœud 2

Nous allons sensiblement effectuer les mêmes opérations à ceci prêt que nous changerons le server_id du nœud 2 afin d'éviter une erreur bloquante.

vim /etc/hosts

192.168.1.201	noeud1
192.168.1.202	noeud2

Tester la connectivité

ping noeud1
ping noeud2

Installation

apt install mariadb-server

Configuration

Fichier de configuration

Éditer le fichier de configuration serveur

vim /etc/mysql/mariadb.conf.d/50-server.cnf

Commenter (ligne 29)

bind-address          = 127.0.0.1

Dé-commenter (ligne 74)

server-id               = 1

Dé-commenter (ligne 75)

log_bin                 = /var/log/mysql/mysql-bin.log

Redémarrer le service

systemctl restart mariadb.service

Requêtes de synchronisation

Se connecter à MariaDB en root

mysql -u root -proot

Créer un utilisateur dédié à la réplication

GRANT replication slave on *.* to 'replica'@'%' identified by 'replica';

Arrêter le mode esclave

stop slave;

Récupérer les informations à donner au nœud 1

show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      497 |              |                  |
+------------------+----------+--------------+------------------+

C'est à ce moment là que nous récupérons les informations du nœud 1 pour les mettre dans notre requête de réplication sur le nœud 2

CHANGE MASTER TO MASTER_HOST='noeud1', MASTER_USER='replica', MASTER_PASSWORD='replica', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=497;

Afin de prévenir l'erreur suivante:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

il faut configurer un server_id différent.

Afficher le server_id actuel

show variables like 'server_id';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

Changer cette valeur

set global server_id=2;

Démarrer l'esclave

start slave;

Une fois ceci fait sur les deux nœud, un

show slave status\G

affichera:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: noeud1
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 497
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 537
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 497
              Relay_Log_Space: 836
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

INFORMATION

Ce qui est important ce sont les Slave_IO_Running: Yes et Slave_SQL_Running: Yes. Si il sont à No (ou l'un des deux), c'est que vous êtes dans le cas chelou qui fait que ça ne fonctionne pas (ce n'est peu être pas de votre faute car comme dit au début, la mise en place est parfois assez hasardeuse). Ne lâchez rien, on fini toujours par trouver.

Résultante et test

Si tout fonctionne comme prévu, la création d'une base de donnée, d'une table ou d'une entrée sur un des nœuds aura pour effet de se créer sur le deuxième, la suppression et la modification également.

Pour ma part, je test ceci avec une table toto sur le nœud 1:

CREATE DATABASE toto;
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| toto               |
+--------------------+

Que j'affiche sur le nœud 2

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| toto               |
+--------------------+

et que je supprime sur celui-ci

DROP DATABASE toto;

et je reviens sur le nœud 1 pour vérifier la suppression

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Si tout fonctionne, votre réplication est prête à accueillir votre production. Gardez bien à l'esprit qu'une réplication, à l'image d'un RAID1, n'est en rien une sauvegarde ! Si une catastrophe (piratage, suppression malencontreuse...) se produit, elle se répliquera elle aussi. Ceci ne vous dispenses donc pas d'une sauvegarde régulière. La réplication est là pour assurer une haute disponibilité des informations, pas leur intégrité.

Désactiver complètement la réplication

Si vous n'avez plus besoin de la réplication mise en œuvre dans cette documentation ou que vous rencontrez des difficultés et que vous voulez recommencer depuis une base propre. Vous pouvez désactiver les opérations effectués avec ceci sur les deux nœuds:

stop slave;
reset slave;
quit

systemctl restart mariadb.service

Sources

• https://www.youtube.com/watch?v=XTIXeiWPE10
• http://niutuku.com/tech/Mysql/237830.shtml
• https://dba.stackexchange.com/questions/21119/how-do-i-completely-disable-mysql-replication