MySQL Group Replication et ProxySQL sur Docker Partie 1

Group Replication est une solution sortie récemment avec la version 5.7.17. Elle apporte un aspect haute disponibilité au sein du moteur MySQL.
Il a pour but de fournir une solution intégrée nativement. De nombreuses architectures sont possibles autour de MySQL (multimaster/DRBD, disques partagés, master/slave) Leurs mises en place est parfois longue et laborieuse.
MySQL Group Replication est un des composants de la solution Innodb Cluster actuellement en développement (sortie depuis la publication), mais cette partie est utilisable dès maintenant.

Le but est d’avoir un ensemble de serveur « virtuellement » synchrone, en mode master/slave avec bascule automatique ou un mode multi-master.
Au travers de mes interventions, je rencontre souvent des architectures master/slave mais très rarement avec une solution de basculement automatique.
Des solutions existent, comme les MySQL Utilities fournis officiellement, ou des solutions type MHA etc, elles sont cependant assez peu connus.
Certains utilisateurs préféreront gérer la bascule manuellement.

Nous allons mettre en place un Group Replication sur docker avec 3 containers dans la première partie, dans la seconde, je détaillerais l’aspect proxysql et routage des requêtes

L’installation se fera sur un docker sur windows, avec les images docker officielles MySQL 5.7.17, le groupe sera composé de 3 containers qui correspond au nombre minimal conseillé.

Afin de faire communiquer les différents containers, nous allons créer un sous réseau :

[code language= »powershell »]PS C:\Users\nicolas.moreau> docker network create -d bridge mynetwork[/code]

Pour récuperer les informations de sous réseau nouvellement créé:

[bash highlight= »15″]
PS C:\Users\nicolas.moreau> docker network inspect mynetwork
[
{
"Name": "mynetwork",
"Id": "1936b8474aaa9fed1be6c6cce73f0459d311d2288232565cee1fee409c316ccd",
"Created": "2017-03-22T10:02:55.0467063Z",
"Scope": "local",
"Driver": "bridge",
"EnableIPv6": false,
"IPAM": {
"Driver": "default",
"Options": {},
"Config": [
{
"Subnet": "172.19.0.0/16",
"Gateway": "172.19.0.1"
}
]
},
"Internal": false,
"Attachable": false,
"Containers": {},
"Options": {},
"Labels": {}
}
]
[/bash]

Notre sous réseau sera 172.19.0.0/16

MySQL Group Replication utilise 2 ports, le 3306 et un autre port pour lequel il n’y a pas de valeur par défaut à ma connaissance, nous allons utiliser le port 14000

Dans le cas de docker si vous souhaitez accéder à votre group replication il vous faudra réaliser un mappage de port

Pour le mapping (uniquement si vous souhaitez un accès externe) :

node1, port 3311 et 14001

node2 port 3312 et 14002

node3 port 3313 et 14003

Création des 3 containers, node1, node2 et node3

[ps]
PS C:\Users\nicolas.moreau> docker run –name node1 -e MYSQL_ROOT_PASSWORD=root –network=mynetwork –p=3311:3306 –p=14001:14000 -d mysql:5.7.17
PS C:\Users\nicolas.moreau> docker run –name node2 -e MYSQL_ROOT_PASSWORD=root –network=mynetwork –p=3312:3306 –p=14002:14000 -d mysql:5.7.17
PS C:\Users\nicolas.moreau> docker run –name node3 -e MYSQL_ROOT_PASSWORD=root –network=mynetwork –p=3313:3306 –p=14003:14000 -d mysql:5.7.17
[/ps]

En lançant un « docker ps » vous devriez voir la liste suivante :

[ps]
PS C:\Users\nicolas.moreau> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8cf9932cc08d mysql:5.7.17 "docker-entrypoint…" 2 seconds ago Up 2 seconds 0.0.0.0:3313->3306/tcp, 0.0.0.0:14003->14000/tcp node3
60b5c9f0449e mysql:5.7.17 "docker-entrypoint…" 13 seconds ago Up 12 seconds 0.0.0.0:3312->3306/tcp, 0.0.0.0:14002->14000/tcp node2
b0caacf6750b mysql:5.7.17 "docker-entrypoint…" 26 seconds ago Up 25 seconds 0.0.0.0:3311->3306/tcp, 0.0.0.0:14001->14000/tcp node1
[/ps]

Nous avons désormais nos 3 containers MySQL, ils ne sont pas encore configurés en Group Replication

Nous allons créer 3 fichiers de configuration, un pour chaque container :

node1.cnf

[bash highlight= »15,18″]

[mysqld]
log_error=/var/lib/mysql/mysqld.log
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "node1:14000"
loose-group_replication_group_seeds= "node1:14000,node2:14000,node3:14000"
loose-group_replication_bootstrap_group= off
loose-group_replication_ip_whitelist="172.19.0.0/16"
[/bash]

node2.cnf

[bash highlight= »15,18″]
[mysqld]
log_error=/var/lib/mysql/mysqld.log
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "node2:14000"
loose-group_replication_group_seeds= "node1:14000,node2:14000,node3:14000"
loose-group_replication_bootstrap_group= off
loose-group_replication_ip_whitelist="172.19.0.0/16"

[/bash]

node3.cnf

[bash highlight= »15,18″]

[mysqld]
log_error=/var/lib/mysql/mysqld.log
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "node3:14000"
loose-group_replication_group_seeds= "node1:14000,node2:14000,node3:14000"
loose-group_replication_bootstrap_group= off
loose-group_replication_ip_whitelist="172.19.0.0/16"

[/bash]

Les seuls qui changent entre tous les fichiers, correspond à l’adresse local du noeud

[powershell]PS C:\Users\nicolas.moreau> docker cp .\node1.cnf node1:/etc/mysql/mysql.conf.d/
PS C:\Users\nicolas.moreau> docker cp .\node2.cnf node2:/etc/mysql/mysql.conf.d/
PS C:\Users\nicolas.moreau> docker cp .\node3.cnf node3:/etc/mysql/mysql.conf.d/

PS C:\Users\nicolas.moreau> docker restart node1 node2 node3
node1
node2
node3

[/powershell]

Nous allons maintenant créer les utilisateurs nécessaires au groupe

[sql]– désactive les binlogs pour éviter la génération de transaction GTID qui poserait problème pour l’initialisation
SET sql_log_bin=0;
— création de l’utilisateur pour les opérations de recovery
CREATE USER repl@’%’ IDENTIFIED BY ‘Repl100!’;
— grant des privileges comme un simple slave
GRANT REPLICATION SLAVE ON *.* TO repl;
— création d’un utilisateur root@’%’
CREATE USER root@’%’ IDENTIFIED BY ‘Root@100!’;
GRANT ALL PRIVILEGES ON *.* TO root@’%’ WITH GRANT OPTION;
— un petit flush
FLUSH PRIVILEGES;
— on réactive les binlogs
SET sql_log_bin=1;
— on positionne les informations pour le channel de recovery
INSTALL PLUGIN group_replication SONAME ‘group_replication.so’
CHANGE MASTER TO MASTER_USER=’repl’, MASTER_PASSWORD=’Repl100!’ FOR CHANNEL ‘group_replication_recovery’;[/sql]

Exécution sur chaque noeud de ces commandes SQL

[bash]docker exec -it node1 bash
mysql -uroot -p
SET sql_log_bin=0;
— création de l’utilisateur pour les opérations de recovery
CREATE USER repl@’%’ IDENTIFIED BY ‘R[…]
[/bash]

Lancez les commandes pour la création des utilisateurs etc, et reproduisez la même procédure pour les 3 noeuds.
Le groupe est maintenant configuré, il ne reste plus qu’a le démarrer, cette opération s’appelle le bootstrap.

Sur un des noeuds, nous allons lancer cette opération, et ensuite ajouter les autres containers.

Connexion au shell:
docker exec -it node2 bash

[code highlight= »15,18,21″]root@b0caacf6750b:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.16 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
[/code]

Vérification avec la vue replication_group_members qui contient la liste des noeuds et leur statut :

Le bootstrap est maintenant terminé, nous allons ajouter les autres noeuds
le noeud 2 :

[powershell]
PS C:\Users\nicolas.moreau> docker exec -it node3 bash
root@8cf9932cc08d:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> start group_replication;
Query OK, 0 rows affected (2.52 sec)
[/powershell]

le noeud 3

[powershell]
PS C:\Users\nicolas.moreau> docker exec -it node2 bash
root@60b5c9f0449e:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> start group_replication;
Query OK, 0 rows affected (5.56 sec)
[/powershell]

Vérification depuis n’importe quel noeud :

Le groupe est maintenant opérationnel en mode master/slave.

il est nécessaire de connaître le noeud master :

[sql]mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME= ‘group_replication_primary_member’;
+————————————–+
| VARIABLE_VALUE |
+————————————–+
| 6e9e038e-0ee7-11e7-96ba-0242ac130002 |
+————————————–+[/sql]

Un GUID n’est pas très lisible, il est possible de faire une simple jointure pour un résultat plus lisible:

[sql]mysql> SELECT member_host as master FROM performance_schema.global_status s
INNER JOIN performance_schema.replication_group_members m ON s.variable_value = m.member_id WHERE VARIABLE_NAME=’group_replication_primary_member’;
+————–+
| master |
+————–+
| b0caacf6750b |
+————–+[/sql]

Sur docker le nom d’hôte est aussi l’id du container :

[code highlight= »5″]PS C:\Users\nicolas.moreau> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8cf9932cc08d mysql:5.7.17 "docker-entrypoint…" 27 hours ago Up 3 hours 0.0.0.0:3313->3306/tcp, 0.0.0.0:14003->14000/tcp node3
60b5c9f0449e mysql:5.7.17 "docker-entrypoint…" 27 hours ago Up 3 hours 0.0.0.0:3312->3306/tcp, 0.0.0.0:14002->14000/tcp node2
b0caacf6750b mysql:5.7.17 "docker-entrypoint…" 27 hours ago Up 3 hours 0.0.0.0:3311->3306/tcp, 0.0.0.0:14001->14000/tcp node1[/code]

On peut voir que le noeud 1 est le Master.

Si vous souhaitez passer en mode multimaster, il suffit simple de rajouter l’option suivante dans chaque fichier de configuration et relancer toutes les instances et faire un bootstrap du groupe :

[bash]group_replication_single_primary_mode=OFF[/bash]

Dans la deuxième partie nous allons aborder l’aspect ProxySQL et routing.