Skip navigation

Debian 9 : Mariadb réplication master-to-master

Réplication master-to-master de Mariadb sous Debian 9

IP Nom de l’hôte
172.16.0.10 db1
172.16.0.11 db2

: sur tous les nœuds

Installer Mariadb.

root@db1/db2~#: apt install -y mariadb-server mariadb-client

: sur chacun des nœuds individuellement

Configurer Mariadb pour la rélpication.

Sur db1.

root@db1~#: vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
[…]

#
# * replication setting
#
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1
bind-address            = 172.16.0.10
[…]

Sur db2.

root@db2~#: vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
[…]

#
# * replication setting
#
server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2
bind-address            = 172.16.0.11
[…]

: sur tous les nœuds

Rédémarrer le service Mariadb.

root@db1/db2~#: systemctl restart mariadb

: sur chacun des nœuds individuellement

Créer l’utilisateur du réplication

: L’IP renseigné est celle du nœud opposé.

Sur db1.

root@db1~#: mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.16.0.11' IDENTIFIED BY 'password';
MariaDB [(none)]> FLUSH PRIVILEGES;

Sur db2.

root@db2~#: mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.16.0.10' IDENTIFIED BY 'password';
MariaDB [(none)]> FLUSH PRIVILEGES;

Test de connection

Sur db1.

root@db1~#:mariadb -ureplication -p -h 172.16.0.11

Sur db2.

root@db2~#:mariadb -ureplication -p -h 172.16.0.10

Une fois le test de connection validé, quitter Mariadb.

Configuration de la réplication

Sur db1 se logger en root dans Mariadb.

root@db1~#: mariadb
MariaDB [(none)]> SHOW MASTER STATUS;

sortie

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      628 |              |                  |
+------------------+----------+--------------+------------------+

: noter le nom du fichier (File) et sa position qui seront reporté sur db2.

Sur db2 se logger en root dans Mariadb.

root@db2~#: mariadb
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO master_host='172.16.0.10', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=628;
MariaDB [(none)]> START SLAVE;

Toujours sur db2.

root@db2~#: mariadb
MariaDB [(none)]> SHOW MASTER STATUS;

sortie

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      628 |              |                  |
+------------------+----------+--------------+------------------+

: de la même façon, il faut repporter le ces résultats sur db1.

Sur db1.

root@db1~#: mariadb
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO master_host='172.16.0.11', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=628;
MariaDB [(none)]> START SLAVE;

Test de réplication

root@db1~#: mariadb
MariaDB [(none)]> CREATE DATABASE villes;

: sur tous les nœuds

root@db1/db2~#: mariadb
MariaDB [(none)]> SHOW DATABASES;

sortie

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

by | January 12, 2019 | No Comments | Système