Introduction
La réplication dans les bases de données SQL est le processus de copie des données de la base de données source vers une autre (ou plusieurs) et vice versa. Les données d'un serveur de base de données sont constamment copiées vers un ou plusieurs serveurs.
Vous pouvez utiliser la réplication pour répartir et équilibrer les demandes sur un ensemble de serveurs répliqués, assurer le basculement et la haute disponibilité des bases de données. MariaDB et MySQL permet d'utiliser deux types de réplication de base de données : Master-Master et Master-Slave.
Architecture
Dans un schéma de réplication Master-Master, n'importe lequel des serveurs de base de données MariaDB/MySQL peut être utilisé à la fois pour écrire ou lire des données.
La réplication est basée sur un fichier binlog spécial.
Diagramme de réplication
Pré requis
Caractéristique de notre plateforme de test.
Nous disposerons de 2 serveurs sous Ubuntu 18 LTS.
| IP | Nom d'hôte | vCpu | RAM | HDD |
|---|---|---|---|---|
| 192.168.1.101 | db1 | 2vCPU | 4 Go | 40 Go |
| 192.168.1.102 | db2 | 2vCPU | 4 Go | 40 Go |
Installation
Utilisez les commandes suivantes pour installer MariaDB sur chacun des nœuds :
root@db1~#: apt install -y mariadb-server mariadb-client root@db2~#: apt install -y mariadb-server mariadb-client
Exécutez la commande suivante pour sécurisée l'installation de MySQL. Il vous sera demandé de créer un mot de passe root. Il est recommandé de répondre oui à toutes les questions :
# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] y New password: Re-enter new password: Password update successfully! Reloading privileges tables .. ... Success ! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!
Configuration
Éditez le fichier “/etc/mysql/mariadb.conf.d/50-server.cnf” et ajoutez ou modifiez les valeurs suivantes sur les deux nœuds MariaDB:
Sur le nœuds “db1”.
root@db1 ~ #: nano /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 […]
Sur le nœuds “db2”.
root@db2 ~ #: nano /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 […]
Modifier la configuration de l'adresse de liaison “bind-address = x.x.x.x” afin d'utiliser les adresses IP privées pour chacun des nœuds. Sur le nœuds “db1”.
root@db1 ~ #: nano /etc/mysql/mariadb.conf.d/50-server.cnf [mysqld] […] bind-address = 192.168.1.101 […]
Sur le nœuds “db2”.
root@db2 ~ #: nano /etc/mysql/mariadb.conf.d/50-server.cnf [mysqld] […] bind-address = 192.168.1.102 […]
Redémarrer le service MariaDB sur les deux nœuds.
#: systemctl restart mariadb
User de réplication
Connecter vous sur la base de données MariaDB et configurez les utilisateurs de la réplication sur chaque des nœuds.
Sur le nœuds “db1”.
root@db1 ~ # sudo mysql MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.102' IDENTIFIED BY 'password'; MariaDB [(none)]> FLUSH PRIVILEGES;
Sur le nœuds “db2”.
root@db2 ~ # sudo mysql MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.101' IDENTIFIED BY 'password'; MariaDB [(none)]> FLUSH PRIVILEGES;
Exécutez la commande suivante pour tester la configuration. Utilisez l'adresse IP privée du nœud opposé :
Sur le nœuds “db1”.
root@db1 ~ # mysql -u replication -p -h 192.168.1.102
Sur le nœuds “db2”.
root@db2 ~ # mysql -u replication -p -h 192.168.1.101
Cette commande doit vous connecter à l'instance MariaDB du serveur distant.
Une fois le test de connexion validé, quitter MariaDB.
Configuration de la réplication
Connecter vous au nœud “db1” et interrogez le statut du maître :
MariaDB [(none)]> SHOW MASTER STATUS;
Notez les valeurs de “file” et de “position” qui sont affichées :
MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 1689 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Sur le nœud “db2”, configurez la fonctionnalité “slave” pour cette base de données. Remplacez la valeur de master_log_file par la valeur de “file” de l'étape précédente, et la valeur de master_log_pos par la valeur de la “position”.
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> CHANGE MASTER TO master_host='192.168.1.101', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000003', master_log_pos=1689; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
Connecter vous au nœud “db2” et interrogez le statut du maître :
MariaDB [(none)]> SHOW MASTER STATUS;
Notez les valeurs de “file” et de “position” qui sont affichées :
MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1938 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Sur le nœud “db1”, configurez la fonctionnalité “slave” pour cette base de données. Remplacez la valeur de master_log_file par la valeur de “file” de l'étape précédente, et la valeur de master_log_pos par la valeur de la “position”.
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> CHANGE MASTER TO master_host='192.168.1.102', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=1938; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
Test de réplication
Pour la partie test, nous allons simplement créer une base de données et vérifier sur les nœuds MariaDB.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) mysql> create database DB_CreateOnMySQL1; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | DB_CreateOnMySQL1 | | mysql | +--------------------+ 3 rows in set (0.00 sec)
Status après crash
Suite à un crash de l'un des deux serveurs, il convient de revérifier l'état des serveurs de base de données.
Dans l'exemple qui va suivre, nous allons arrêter brutalement l'un des deux nœuds et vérifier l'état de notre réplication.
Une fois le serveur arrêté brutalement de nouveaux disponible, vérifions l'état du service.
~$ sudo systemctl status mariadb ● mariadb.service - MariaDB 10.1.44 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: active (running) since Sun 2020-07-26 10:42:39 UTC; 53s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 1056 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Notre service est bien démarré. Maintenant, nous allons nous connecter au CLI MariaDB et vérifier.
MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: mysql-bin.000006 Position: 343 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
Parfois, la réplication “mariadb” se bloque avec des relay binlogs corrompus et il n'est pas possible de la redémarrer avec une simple commande “start slave”.
On peux vérifier l'état avec la commande “show slave status”
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.218 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1341 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 789 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1594 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_Counter: 2 Exec_Master_Log_Pos: 959 Relay_Log_Space: 3602 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. 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 1 row in set (0.00 sec)
Les valeurs importantes que vous devez noter sont Relay_Master_Log_File et Exec_Master_Log_Pos. Vous en aurez besoin pour relancer correctement la réplication sur votre esclave.
Pour redémarrer la réplication, exécutez les commandes suivantes :
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> RESET SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CHANGE MASTER TO master_log_file='mysql-bin.000003', master_log_pos=959; Query OK, 0 rows affected (0.06 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)
Pour vérifier si la réplication fonctionne à nouveau, exécutez à nouveau la commande :
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.218 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1341 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 919 Relay_Master_Log_File: mysql-bin.000003 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: 1341 Relay_Log_Space: 1217 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 1 row in set (0.00 sec) MariaDB [(none)]>
Avant de valider votre esclave comme étant synchronisé, vérifiez la valeur du paramètre Seconds_Behind_Master de la commande de statut.
Dans les minutes qui ont suivi, la réplication était à nouveau synchronisée avec le maître et le retard de réplication était de 0s : Seconds_Behind_Master : 0
C'est le moment où nous pouvons recommencer à utiliser notre noeud en production.