Comment configurer une base de données esclave MySQL

MySQL Replication permet à plusieurs copies de la même base de données d'être sur plusieurs serveurs en même temps, en copiant automatiquement les données du serveur principal vers le serveur "esclave".

Grâce à ce mécanisme, toutes les instructions exécutées sur le maître, qui modifient d'une manière ou d'une autre les données, peuvent être enregistrées, transmises et exécutées sur les bases de données répliquées.

Ce guide vous aidera à installer MySQL sur votre serveur, à configurer correctement une réplique et à vérifier son fonctionnement avec des données de test.

Tout d'abord, connectez-vous à votre serveur via une connexion SSH. Si vous ne l'avez pas encore fait, il est recommandé de suivre notre guide pour vous connecter en toute sécurité avec le protocole SSH. Dans le cas d'un serveur local, passez à l'étape suivante et ouvrez votre terminal serveur.

Installation MySQL

Pour configurer la réplication Maître/Esclave sur les deux serveurs, installez une version de MySQL, puis exécutez la procédure d'installation suivante sur les deux machines, avant de passer au chapitre suivant.

Mettez à jour les référentiels de distribution pour vous assurer de télécharger la version la plus récente de MySQL :

$ sudo apt update

Si vous avez déjà lancé cette commande auparavant, passez à l'étape suivante.

Procédez maintenant à l'installation de MySQL :

$ sudo apt install mysql-server

Pour vous assurer que l'installation a réussi, vérifiez la version de MySQL :

$ mysqld --version

Si la procédure a été effectuée avec succès, la version de MySQL installée sera imprimée à l'écran.

Procédez à la sécurisation de MySQL en exécutant un script inclus dans MySQL, qui augmentera la sécurité et limitera l'accès à vos bases de données :

$ sudo mysql_secure_installation

À ce stade, une procédure guidée vous guidera à travers la configuration du niveau de sécurité MySQL.

Tout d'abord, il vous sera demandé si vous souhaitez activer le système de validation de mot de passe. Si activé, lors de la définition du mot de passe d'un utilisateur, le mot de passe est évalué et s'il ne répond pas aux critères de sécurité, il est rejeté avec une erreur.

Plus tard, il vous sera demandé si vous souhaitez changer le mot de passe root avec celui de votre choix (si vous activez le système de validation de mot de passe, un mot de passe qui répond aux critères de sécurité doit être saisi).

Il est recommandé de suivre plusieurs bonnes pratiques pour créer un mot de passe sécurisé. Cela inclut :

l'utilisation de lettres majuscules et minuscules
l'utilisation de lettres et de chiffres
l'utilisation de caractères non alphanumériques comme @ # $% ˆ & !
l'utilisation de mots de passe qui n'ont jamais été utilisés auparavant.

Enfin, choisissez de supprimer les utilisateurs anonymes, de tester les bases de données et de désactiver la connexion à distance avec l'utilisateur root. Pour un niveau de sécurité adéquat, il est recommandé de confirmer tous ces changements.

À ce stade, confirmez les mises à jour du tableau des autorisations affiché pour appliquer tous les nouveaux critères de sécurité.

Redémarrez enfin le service pour appliquer les modifications :

$ sudo service mysql restart

Configuration de la réplication Maître/Esclave

Après l'installation sur les deux serveurs (maître et esclave), commencez par configurer le serveur maître.

Tout d'abord, modifiez le fichier de configuration dans /etc/mysql/mysql.conf.d/mysqld.cnf comme suit :

server-id               = 1
	
	log_bin                 = /var/log/mysql/mysql-bin.log
	
	expire_logs_days        = 10
	
	max_binlog_size   = 100M

Étant donné que le serveur Esclave devra nécessairement se connecter à distance au serveur Maître, vous devez vous assurer que le service MySQL peut accepter les connexions externes, en changeant l'adresse de liaison comme suit :

bind-address            = 0.0.0.0

Une fois terminé, redémarrez le service pour appliquer les modifications :

$ sudo service mysql restart

A des fins de démonstration et d'évaluation, créez une base de données de test sur le serveur maître (appelée ici "tutoriel") avec une table nommée "nombres" avec des données fictives. Après avoir terminé la procédure de configuration, vérifiez si toutes les données saisies ici sont synchronisées avec le serveur esclave :

$ sudo mysql -u root -p

mysql> CREATE DATABASE tutorial;
	Query OK, 1 row affected (0.00 sec)
	
	mysql> USE tutorial;
	Database changed
	
	mysql> CREATE TABLE numbers ( value REAL );
	Query OK, 0 rows affected (0.09 sec)
	
	mysql> INSERT INTO numbers VALUES ( 1 ), ( 2 ), ( 3 );
	Query OK, 3 rows affected (0.04 sec)
	
	Records: 3  Duplicates: 0  Warnings: 0

Assurez-vous donc que les valeurs que vous venez de saisir sont présentes dans la table de test :

mysql> SELECT * FROM numbers;
		
	+-------+
	| value |
	+-------+
	|     1 |
	|     2 |
	|     3 |
	+-------+
	3 rows in set (0.00 sec)

Le tableau des nombres doit contenir 3 exemples de lignes comme indiqué ci-dessus.

Procédez ensuite en créant les données d'accès de l'utilisateur à utiliser par la base de données Esclave :

mysql> GRANT REPLICATION SLAVE ON *.* TO 'usr_replica'@'%' IDENTIFIED BY 'SMDipmf#23$42';
	mysql> FLUSH PRIVILEGES;

Avant de poursuivre la configuration du serveur Esclave, l'état actuel du Maître sera imprimé à l'écran et les informations suivantes seront enregistrées :

mysql> SHOW MASTER STATUS;
	
	+------------------+----------+--------------+------------------+-------------------+
	
	| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
	
	+------------------+----------+--------------+------------------+-------------------+
	
	| mysql-bin.000001 |     1238 | |               | |
	
	+------------------+----------+--------------+------------------+-------------------+
	
	1 row in set (0.00 sec)

Vous aurez besoin de ces données lors de la configuration du serveur esclave.

Continuez à quitter le shell MySQL :

mysql> exit
	Bye

Ensuite, enregistrez un vidage complet de la base de données pour le charger ultérieurement sur le serveur esclave :

$ sudo mysqldump -u root -p --opt tutorial > slave-init.sql
	Enter password:

Transférez le fichier nouvellement créé sur la machine esclave et procédez à sa configuration en modifiant le fichier /etc/mysql/mysql.conf.d/mysqld.cnf :

server-id               = 2
	
	log_bin                 = /var/log/mysql/mysql-bin.log
	
	expire_logs_days        = 10
	
	max_binlog_size   = 100M

Veuillez noter que l'identifiant du serveur a été modifié en utilisant le numéro 2 et n'oubliez pas de modifier l'adresse de liaison comme indiqué ici :

bind-address            = 0.0.0.0

Maintenant, redémarrez l'esclave pour appliquer les modifications :

$ sudo service mysql restart

Procédez maintenant en créant une base de données avec le même nom que le serveur maître :

$ sudo mysql -u root -p


	mysql> create database tutorial;
	
	Query OK, 1 row affected (0.00 sec)
	mysql> exit
	
	Bye

Importez maintenant la sauvegarde nouvellement créée et démarrez l'esclave en utilisant les informations précédemment enregistrées par la base de données maître (MASTER_LOG_FILE et MASTER_LOG_POS) :

$ sudo mysql -u root tutorial < /root/slave.sql
	$ sudo mysql -u root -p 
	mysql> CHANGE MASTER TO MASTER_HOST='195.231.4.201', MASTER_USER='usr_replica', MASTER_PASSWORD='SMDipmf#23$42', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1238;
	
	Query OK, 0 rows affected, 2 warnings (0.10 sec)
	mysql> START SLAVE;
	
	Query OK, 0 rows affected (0.00 sec)

A partir de ce moment, les bases de données sont synchronisées en temps réel et vous pouvez quitter le shell MySQL.

Vérification du bon fonctionnement de la réplication

Pour vérifier le fonctionnement de la réplication vous pouvez essayer d'insérer des données dans la base Maître et vérifier si elles arrivent correctement dans la base Esclave. Commencez par saisir de nouvelles données sur le Master comme suit :

$ sudo mysql -u root -p

mysql> use tutorial;
	Reading table information for completion of table and column names 
	Turn off this feature to get a quicker startup with -A   
	Database changed 
	
	mysql> INSERT INTO numbers VALUES ( 4 ) , ( 5 );
	
	Query OK, 2 rows affected (0.03 sec)
	Records: 2 
	Duplicates: 0 
	Warnings: 0

Procédez ensuite en interrogeant la base de données Esclave :

$ sudo mysql -u root -p


	mysql> use tutorial;
	
	Reading table information for completion of table and column names
	
	Turn off this feature to get a quicker startup with -A
	  changed
	
	mysql> SELECT * FROM numbers;
	
	+-------+
	| value |
	+-------+
	|     1 |
	|     2 |
	|     3 |
	|     4 |
	|     5 |
	+-------+
	
	5 rows in set (0.00 sec)

Comme vous pouvez le voir, les nouvelles valeurs (4 et 5) sont arrivées sur la base de données Slave, donc la synchronisation fonctionne correctement.