Création d’un serveur MySQL Master-Master avec load balancing

28 mars 2018

Cet article traitera de la procédure de mise en place d’un serveur MySQL Master-Master (et Master-Slave) avec un système de load balancing utilisant l’algorithme round robin (grâce à HaProxy).
Dans l’exemple nous aurons trois serveurs, un gérera la répartition de charge (qui se nommera SRV-LBSQL et aura l’adresse IP 10.0.0.100) et deux serveurs MySQL connectés l’un avec l’autre (ayant respectivement pour nom SRV-MYSQL01 et SRV-MYSQL02 et les adresses IPs 10.0.0.1 et 10.0.0.2). Chaque serveur MySQL traitera à tour de rôle une requête SQL. Les serveurs de base de données répliqueront les informations l’un à l’autre grâce à une technique de double relation Master-Slave.

Installation des services MySQL
Pour installer les deux serveurs, il suffit d’installer le paquet MySQL. Pour cela utiliser la commande :

apt-get install mysql-server

Configuration des serveurs MySQL
Préparation
Pour se connecter sur les bases de données, il faut avoir le paquet « mysql-client » installer sur la machine de connexion, puis on tape la commande :

mysql -u root –p MotDePasse –h localhost

On crée la base de données « bdd » sur les deux serveurs grâce à la requête :

CREATE DATABASE bdd;

Puis on crée une table de test sur le serveur 1 :

CREATE TABLE test (
    ID INT NOT NUL AUTO_INCREMENT,
    Test VARCHAR(100) NOT NULL,
    PRIMARY KEY (ID)
);

On crée un dump de la base et on l’envoi au serveur 2 :

mysqldump -u root -p bdd > bdd.sql
scp bdd.sql [email protected]:/root/

Sur le serveur 2 on exécute le dump sur la base de données :

mysql -u root -p bdd < bdd.sql

On modifie ensuite le fichier /etc/mysql/my.cnf des deux serveurs.

On met ces valeurs sur le serveur 1 :

bind-address=10.0.0.1
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = bdd

Et on met ces valeurs sur le serveur 2 :

bind-address=10.0.0.2
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = bdd

bind-address contient l’adresse IP du serveur, server-id doit contenir un id unique et binlog_do_db contient le nom de la base de données qui sera synchronisé. Log_bin, expire_logs_days et max_binlog_size sont eux des paramètres permettant de gérer des fichiers contenant l’ensemble des modifications de la base de données et qui serviront à la redondance.

Finalement on redémarre les services mysql des deux serveurs :

service mysql restart

La relation Master-Slave
La relation Master-Slave (ou relation Maitre-Esclave) est une relation dans laquelle chaque un des deux serveurs aura son rôle. Ainsi le serveur maitre recevra les requêtes de modification des données et de lecture et le serveur esclave répondra aux demandes de lecture et copiera les données présentes sur le serveur Maitre. Cette architecture permet de sauvegarder les données à deux endroits pour éviter leurs pertes.

Configuration du serveur maitre
Sur le serveur maitre, on ajoute la permission de se connecter au serveur maitre depuis le serveur esclave avec l’utilisateur ‘replicationuser’ :

GRANT REPLICATION SLAVE *.* TO ‘replicationuser’@’10.0.0.2’ IDENTIFIED BY ‘motDePasse’;

On prépare la base de données :

USE bdd;
FLUSH TABLES WITH READ LOCK;

Enfin on utilise la commande suivante :

SHOW MASTER STATUS;

Cette dernière va nous permettre de récupérer la « position » et le « File » qui permettront à notre esclave de se connecter.

Configuration de l’esclave
Sur le serveur esclave, on configure les informations pour lui permettre de se connecter au maitre :

CHANGE MASTER TO MASTER_HOST=’10.0.0.1’, MASTER_USER=’ replicationuser’, MASTER_PASSWORD=’ motDePasse’, MASTER_LOG=‘mysql-bin.000005’, MASTER_LOG_POS=107;

Enfin on démarre l’esclave :

START SLAVE;

Test
Il est possible de voir l’état de la connexion avec le master en exécutant sur l’esclave la commande suivante :

SHOW SLAVE STATUS;

Vous pouvez vous servir de cette commande pour vérifier que l’esclave arrive bien à contacter son maitre. Vous pouvez aussi insérer une donnée sur le maitre et vérifier qu’elle a bien été répliquée sur l’esclave.

La relation Master-Master
Dans la relation Master-Master (ou Maitre-Maitre), les deux serveurs peuvent lire et écrire dans la base de données et réplique les modifications de l’autre serveur. Pour mettre en place cette relation, il suffit de créer une relation Maitre-Esclave mais dans le sens opposé à celui créé dans la partie précédente.

Configuration du serveur maitre
Sur le serveur maitre, on ajoute la permission de se connecter au serveur maitre depuis le serveur esclave avec l’utilisateur ‘replicationuser’ :

GRANT REPLICATION SLAVE *.* TO ‘replicationuser’@’10.0.0.1’ IDENTIFIED BY ‘motDePasse’;

On applique les nouvelles permissions :

FLUSH PRIVILEGES;

On prépare la base de données :

USE bdd;
FLUSH TABLES WITH READ LOCK;

Enfin on utilise la commande suivante :

SHOW MASTER STATUS;

Cette dernière va nous permettre de récupérer la « position » et le « File » qui permettront à notre esclave de se connecter.

Configuration de l’esclave
Sur le serveur esclave, on configure les informations pour lui permettre de se connecter au maitre :

CHANGE MASTER TO MASTER_HOST=’10.0.0.2’, MASTER_USER=’ replicationuser’, MASTER_PASSWORD=’ motDePasse’, MASTER_LOG=‘mysql-bin.000005’, MASTER_LOG_POS=1678;

Enfin on démarre l’esclave :

START SLAVE;

Test
Il est possible de voir l’état de la connexion avec le master en exécutant sur l’esclave la commande suivante :

SHOW SLAVE STATUS;

Vous pouvez vous servir de cette commande pour vérifier que l’esclave arrive bien à contacter son maitre. Pour tester la réplication, on se connecte au serveur 2 et on y insère une valeur. Puis on se connecte au serveur 1 et on vérifie que la donnée a bien été répliquée.

Load Balancing HaProxy
HaProxy va nous permettre de partager le flux de requête SQL sur les deux serveurs grâce à la méthode du round robin (la requête 1 va sur le serveur 1 ; la 2 sur le serveur 2 ; la 3 sur le serveur 1 etc…). HaProxy permet de faire cela grâce à sa capacité à analyser et rediriger la couche Transport du modèle OSI (couche 4).

Installation
Pour installer haProxy :

apt-get install haproxy;

Si le paquet n’est pas trouvé, ajouter les dépôts :

echo deb http://httpredir.debian.org/debian wheezy-backports main | \
tee /etc/apt/sources.list.d/backports.list
curl https://haproxy.debian.net/bernat.debian.org.gpg | \
apt-key add –
echo deb http://haproxy.debian.net wheezy-backports-1.4 main | \
tee /etc/apt/sources.list.d/haproxy.list
apt-get update
apt-get install haproxy -t wheezy-backports\*

Configuration
On modifie ensuite la configuration du fichier « /etc/haproxy/haproxy.cfg » pour mettre en place le load balancing :

global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon
# Default SSL material locations
ca-base /etc/ssl/certs
crt-base /etc/ssl/private
# Default ciphers to use on SSL-enabled listening sockets.
# For more information, see ciphers(1SSL). This list is from:
# https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
ssl-default-bind-ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:RSA+AES:RSA+3DES:!aNULL:!MD5:!D$
ssl-default-bind-options no-sslv3
defaults
log global
# mode http
# option httplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000
errorfile 400 /etc/haproxy/errors/400.http errorfile 403 /etc/haproxy/errors/403.http errorfile 408 /etc/haproxy/errors/408.http errorfile 500 /etc/haproxy/errors/500.http errorfile 502 /etc/haproxy/errors/502.http errorfile 503 /etc/haproxy/errors/503.http errorfile 504 /etc/haproxy/errors/504.http
listen mysql-cluster
bind *:3306
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server SRV-MYSQL01 10.0.0.1:3306 check
server SRV-MYSQL02 10.0.0.2:3306 check

On redémarre le service haproxy :

service haproxy restart

Nous allons maintenant créer l’utilisateur « haproxy_check » sur les deux serveurs MySQL qui permettra à HaProxy de connaitre l’état du serveur SQL (pour le failover). Pour cela nous utiliserons la commande :

INSERT INTO mysql.user (Host,User) values (‘10.0.0.100′,’haproxy_check’);
FLUSH PRIVILEGES;

Puis nous créons sur les deux serveurs MySQL l’utilisateur avec lequel nous nous connecterons finalement, il devra pouvoir se connecter depuis le l’adresse du load balancer :

GRANT ALL PRIVILEGES ON *.* TO ‘haproxy_root’@’10.0.0.100’ IDENTIFIED BY ‘MotDePasse’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

Test
Il n’y a plus qu’à tester le load balancing en se connectant et en se déconnectant plusieurs fois au serveur HaProxy.
Renvoi « 1 » :

mysql -u haproxy_root –pMotDePasse –h 10.0.0.100
SELECT @@server_id;

Renvoi « 2 » :

mysql -u haproxy_root –pMotDePasse –h 10.0.0.100
SELECT @@server_id;

Renvoi « 1 » :

mysql -u haproxy_root –pMotDePasse –h 10.0.0.100
SELECT @@server_id;

Flavien JOURDREN

Article