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 IP 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
Pour se connecter sur les bases de données, il faut avoir le paquet « mysql-client » installé 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 NULL AUTO_INCREMENT,
Test VARCHAR(100) NOT NULL,
PRIMARY KEY (ID)
);
On crée un dump de la base et on l’envoie 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ée. 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 Maître-Esclave) est une relation dans laquelle chacun des deux serveurs aura son rôle. Ainsi le serveur maître 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 maître. Cette architecture permet de sauvegarder les données à deux endroits pour éviter leur perte.
Configuration du serveur maître
Sur le serveur maître, on ajoute la permission de se connecter au serveur maître depuis le serveur esclave avec l’utilisateur ‘replicationuser’ :
GRANT REPLICATION SLAVE ON *.* 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 maître :
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 maître. Vous pouvez aussi insérer une donnée sur le maître et vérifier qu’elle a bien été répliquée sur l’esclave.
La relation Master-Master
Dans la relation Master-Master (ou Maître-Maître), les deux serveurs peuvent lire et écrire dans la base de données et répliquent les modifications de l’autre serveur. Pour mettre en place cette relation, il suffit de créer une relation Maître-Esclave mais dans le sens opposé à celui créé dans la partie précédente.
Configuration du serveur maître
Sur le serveur maître, on ajoute la permission de se connecter au serveur maître depuis le serveur esclave avec l’utilisateur ‘replicationuser’ :
GRANT REPLICATION SLAVE ON *.* 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 maître :
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 maître. 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êtes 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.
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:!DSS
ssl-default-bind-options no-sslv3
defaults
log global
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 connaître 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 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. Renvoie « 1 » :
mysql -u haproxy_root -pMotDePasse -h 10.0.0.100
SELECT @@server_id;
Renvoie « 2 » :
mysql -u haproxy_root -pMotDePasse -h 10.0.0.100
SELECT @@server_id;
Renvoie « 1 » :
mysql -u haproxy_root -pMotDePasse -h 10.0.0.100
SELECT @@server_id;