Réplication de MySQL - Configuration du serveur MySQL pour répliquer une base de données

Publication : 3 avril 2020

Vous pouvez mettre en miroir votre base de données MySQL Server vers d'autres serveurs MySQL Database automatiquement en utilisant la fonction de réplication de MySQL. Vous apprendrez les bases de la réplication MySQL et la configuration d'un environnement de base de données MySQL répliqué.

Voici une introduction sur la façon de mettre en place ce système à Ubuntu. Testé sur Ubuntu 18.04 et 16.04.

Là, comme serveur maître (dans cet exemple : 192.168.0.1) qui a notre base de données qui sera répliquée (en miroir). Le serveur maître enregistre toutes les commandes de base de données exécutées ("CREATE", "INSERT", "UPDATE",...) dans son fichier journal binaire (mysql-bin.log).

Le ou les serveurs esclaves (dans cet exemple : 192.168.0.10) sont connectés au serveur maître. Le serveur esclave lit le fichier journal binaire du serveur maître et copie les données de ce dernier dans le fichier journal relais (mysql-relay-bin) du serveur esclave. Les événements et les commandes du fichier journal de relais sont ensuite exécutés pas à pas sur le serveur de base de données du serveur esclave.

Il peut y avoir plusieurs serveurs esclaves, mais un seul serveur maître. Les serveurs esclaves n'ont pas de privilèges d'écriture dans la base de données du serveur maître, mais seulement des privilèges de réplication.

Utilisation

Un serveur de base de données répliqué peut être utilisé, par exemple, pour équilibrer la charge de votre base de données. L'équilibrage de charge dans MySQl peut être réalisé avec le logiciel proxy "HaProxy".
Votre serveur proxy "HaProxy" est connecté à tous les serveurs de base de données utilisés. L'accès au serveur de base de données se fait par "HaProxy" qui vous connecte ensuite à un serveur de base de données en cours d'exécution.
Les demandes adressées au serveur de base de données sont réparties entre les différents serveurs de base de données en cours d'exécution. Le serveur proxy vérifie également si vos serveurs MySQL fonctionnent en utilisant le module "mysql-check".

Ce tutoriel ne couvre cependant que la création d'un environnement de serveur de base de données répliqué. Vous pouvez ensuite ajouter un proxy à équilibrage de charge ou utiliser le serveur de base de données esclave comme sauvegarde pour restaurer vos données de base de données plus rapidement.

Note: Veuillez vérifier si le port MySQL 3306 est ouvert dans tous les serveurs de base de données utilisés (paramètres du pare-feu).

1. Installer MySQL à la fois sur le serveur maître et sur le serveur esclave

sudo apt-get install mysql-client mysql-server

Do not forget to add also a secure root password.

sudo mysql_secure_installation

2. Configuration du serveur MySQL sur le serveur maître

2.1 Configuration du serveur MySQL

Open your MySQL configuration file:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Set in "bind-address" your public IP address of the master server or "0.0.0.0".

bind-address = 192.168.0.1

Set the "server-id" to "1".

server-id = 1

Add also the paths to the files relay_log, relay_log_index, log_bin and log_bin_index:

relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index

If you want do replicate only a certain database, then add this with your database name:

binlog_do_db = YOUR_DATABASE_NAME

If you want to exclude a database from replication, then add this with your database name:

binlog_ignore_db = YOUR_DATABASE_NAME

2.2 Ajout d'un utilisateur qui est utilisé pour la réplication

sudo mysql -u root -p

Create a new user in MySQL that is used for the replication of this database server. The user should be referenced with the public ip address of the slave server. In this example it is the IP address "192.168.10".

CREATE USER 'replicator'@'192.168.0.10' IDENTIFIED BY 'REPLICATOR_PASSWORD';

Add the following privileges for the new user:

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.10';

FLUSH PRIVILEGES;

These are the privileges of the MySQL user that does do the replication of your database. If you use a load balancer with an CMS software such as Joomla, then you should set a read only privilege for the used databases in your slave server.

If you want to replicate a database that is already created and used, then please change to that database and lock that database with this command:

USE MY_DATABASE;
FLUSH TABLES WITH READ LOCK;

If you are done with setting up the database replication server, then do not forget to unlock your database with this command: UNLOCK TABLES;

Check now the status of your database master server and note the first two values ("File" and "Position").

SHOW MASTER STATUS\G

The two first values are later needed when we set up the slave server.

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.001202
Position: 1017657
Binlog_Do_DB: 
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

In this example they are "mysql-bin.001202" and "1017657".

Create a dump of your database server.

sudo mysqldump -u root -p --add-drop-database --all-databases > /database_master_dump.sql

If you want to replicate only a certain (running) database, then create a dump of that certain database with this command:

sudo mysqldump -u root -p --single-transaction --databases MY_DATABASE > /database_master_dump.sql

3. Mise en place du serveur MySQL sur le serveur esclave

3.1 Configuration du serveur MySQL

Open your MySQL configuration file.

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Set in "bind-address" your public IP address of the slave server or "0.0.0.0":

bind-address = 192.168.0.10

Set the "server-id" to "2":

server-id = 2

Add also the paths to the files relay_log, relay_log_index, log_bin and log_bin_index:

relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index

Restart the MySQL server.

sudo systemctl restart mysql

sudo mysql -u root -p

Stop the slave server.

STOP SLAVE;

Run this command with the values of the MySQL configuration of the master server, that were shown in Step 2 of this tutorial.

CHANGE MASTER TO MASTER_HOST = '192.168.0.1', MASTER_USER ='replicatior', MASTER_PASSWORD ='REPLICATOR_PASSWORD', MASTER_LOG_FILE = 'mysql-bin.001202', MASTER_LOG_POS = 1017657;

In this example the IP address "192.168.0.1" is the public IP of the master server. The "MASTER_USER" and "MASTER_PASSWORD" are the credentials of the user that was created in the master server for the database replication.
"MASTER_LOG_FILE" and "MASTER_LOG_POS" are values that we got from following command that we run on the MySQL server in the master server: SHOW MASTER STATUS\G

Don't forget to import the created MySQL dump of the master database server into the database server of the slave server. Example command:

mysql -u root -p < /database_master_dump.sql

4. Démarrage du serveur maître et esclave

Now start the slave server.

START SLAVE;

mysql -u root -p

START MASTER;

5. Vérifiez le statut de votre serveur maître et esclave

Status of the slave server:

SHOW SLAVE STATUS\G

Status of the master server:

SHOW MASTER STATUS\G

This was an introduction into MySQL replication. You can also add additional slave servers by repeating the steps from 2.1 to 4.

En savoir plus sur la réplication de MySQL :
https://dev.mysql.com/doc/refman/8.0/en/replication.html