Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication

janvier 9, 2018

Read this post in English

Toute architecture de base de données se doit de se reposer sur 3 piliers, la supervision (monitoring) , la sauvegarde/restauration et la haute disponibilité. Mon premier article de l’année 2018  concerne l’un des meilleurs combos du moment, en matière de haute disponibilité niveau base de données :
MySQLProxySQL

 

Note 1: la réplication native (MySQL replication) reste évidemment une alternative de premier plan pour les besoins de haute dispos. D’ailleurs ProxySQL la gère également nativement.

Note 2: ProxySQL a pléthore de fonctionnalités, toutefois le but de cet article est son utilisation dans un contexte MySQL Group Replication.

 

ProxySQL est compatible avec MySQL Group Replication depuis la version 1.3 voir : Configurer ProxySQL pour MySQL Group Replication. Cependant, la version 1.4 supporte nativement MySQL Group Replication. Il est donc plus facile d’utiliser ensemble ces 2 technologies populaires, et c’est ce que nous allons voir immédiatement.

 

Dans cet article je vais faire les hypothèses suivantes :

 

MySQL Group Replication

Caractéristiques

  • Version du serveur : 5.7.20
  • Version du plugin : 1.0
  • Nœud 1 : mysql_node1, 172.22.0.10 : 3306
  • Nœud 2 : mysql_node2, 172.22.0.20 : 3306
  • Nœud 3 : mysql_node3, 172.22.0.30 : 3306

Ces caractéristiques représentent donc un cluster MySQL Group Replication de 3 nœuds, installé, déployé et qui fonctionne :

MySQL Enterprise Monitor nous donne une vue graphique du cluster et de son état (click to enlarge) :

MySQL

 

Le cluster est en mode single primary, c’est à dire qu’un seul nœud est disponible en lecture & écriture à la fois (alors que les 2 autres nœuds ne sont accessibles qu’en lecture seule).

MySQL Enterprise Monitor nous montre (click to enlarge):

MySQL

 

Je vais enrichir le schéma sys de MySQL 5.7 avec le script: addition_to_sys.sql

 

Je charge donc les fonctions et les vues dans le noeud primaire (mysql_node1) du cluster :

 

Ce script va permettre à ProxySQL de superviser l’état des nœuds du cluster.
e.g.

 

La dernière étape de configuration coté cluster, consiste en la création des utilisateurs de supervision qui vont être utilisés par ProxySQL (oui, il y a bien un rapport avec l’étape précédente) :).

Là encore j’utilise le primaire du groupe :

 

Il nous reste à configurer ProxySQL maintenant !

 

ProxySQL

Caractéristiques

  • Version du proxy : 1.4.4
  • Interface d’administration : 172.22.0.2:6032
  • Connexion au cluster : 172.22.0.2:3306

 

 

La configuration de ProxySQL peut se faire en ligne, ce qui est évidemment une très bonne chose.

Commençons par se connecter à l’interface d’administration sur le port 6032 avec l’utilisateur admin et le mot de passe… admin (!)

Données par défauts qui peuvent et qui doivent être changées dans la vraie vie.

 

Configurer les serveurs

Première étape, ajouter les nœuds du cluster au proxy :

 

Configurer les hostgroups

J’ai fais une présentation succincte des objets de ProxySQL 1.3 la dernière fois : Configurer ProxySQL pour MySQL Group Replication

La version 1.4 à quelques différences, la plus notable dans notre contexte est l’apparition de la table  mysql_group_replication_hostgroups :

 

La meilleure description trouvée est disponible dans l’article de mon collègue @LefredMySQL Group Replication: native support in ProxySQL

Je cite

 »

There are many new columns, let’s have a look at their meaning:

Column Name Description
writer_hostgroup the id of the hostgroup that will contain all the members that are writer
backup_writer_hostgroup if the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is
larger than the max_writers, the extra nodes are located in that backup writer group
reader_hostgroup the id of the hostgroup that will contain all the members in read_only
offline_hostgroup the id of the hostgroup that will contain the host not being online or not being part of the Group
active when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups
max_writers limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode
writer_is_also_reader boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup
max_transactions_behind if the value is greater than 0, it defines how much a node can be lagging in applying the transactions from the Group, see this post for more info

 »

Pas mieux 🙂

Notre configuration est la suivante :

  • writer_hostgroup = 2
  • backup_writer_hostgroup = 4
  • reader_hostgroup = 3
  • offline_hostgroup = 1
  • active = 1
  • max_writers = 1
  • writer_is_also_reader = 1
  • max_transactions_behind = 0

Ce qui nous donne :

 

Configuration de la supervision

Un peu plus haut on a créé un utilisateur de supervision dans le cluster.

C’est cet utilisateur que va utiliser ProxySQL pour être au courant de l’état des différents nœuds du cluster :

 

Création de l’utilisateur applicatif

L’application se connecte au serveur MySQL avec un utilisateur et un mot de passe (dans le meilleur des cas :D).

Cet utilisateur doit bien évidemment exister dans les différents serveurs qui composent le cluster, avec les droits MySQL qui vont bien.

Cet utilisateur doit également être renseigné dans ProxySQL :

 

Modifier le port d’écoute

Par défaut l’application va se connecter au cluster à travers ProxySQL en utilisant le port 6032 (dans notre cas: 172.22.0.2:6032)

Dans la vraie vie, les applications se connectent à MySQL bien souvent en utilisant le port MySQL par défaut, 3306.

ll est donc possible (pas obligatoire donc) de modifier le port de ProxySQL pour qu’il écoute sur 3306 :

 

Note 3: Pour une mystérieuse raison, ce dernier changement de configuration ne se charge pas à chaud (runtime). En clair je dois donc redémarrer ProxySQL pour que ce changement soit pris en compte.

 

 

La configuration de ProxySQL pour MySQL Group Replication est maintenant terminée \o/

J’en profite pour vous présenter une nouvelle table dans la version 1.4 : mysql_server_group_replication_log.

Elle est utile pour la supervision :

 

Playtime

Pour rappel, le workflow est le suivant:

  • L’application se connecte à ProxySQL (i.e. elle ne voit et ne connait que le proxy)
  • ProxySQL récupère les transactions et les redirigent sur le noeud primaire du cluster MySQL Group Replication.
  • En cas de crash/arrêt du Primaire,
    • MySQL Group Replication élit un nouveau primaire
    • ProxySQL identifie le nouveau primaire et dirige les transactions vers ce nouveau primaire

 

L’application doit donc être configurée pour se connecter à ProxySQL. Par exemple, si mon application est Drupal mon fichier settings.php ressemblera à l’extrait de code suivant :

  • Utilisateur : app_user
  • Mot de passe : app_pwd
  • Port : 3306 (ProxySQL)
  • Host : 172.22.0.2 (ProxySQL)

CQFD!

 

Simulons tout cela en ligne de commande !

Mon application est le client texte mysql. Pour simplifier mes commandes je crée au préalable un fichier de configuration pour le client mysql qui contient les informations suivantes :

Note 4 : Avoir un mot de passe en clair dans un fichier texte non chiffré n’est absolument pas recommandé.

 

Mes serveurs sont configurés avec la variable report_host renseignée (voir http://dasini.net/blog/2016/11/08/deployer-un-cluster-mysql-group-replication/).

Le primaire du cluster est encore mysql_node1 (pas encore de database failover depuis le début de l’article, mais on y arrive).

 

Maintenant testons le failover avec un exemple légèrement plus complexe.

Au préalable, créons la table test.poc au format InnoDB :

 

L’application test jouera les 2 requêtes suivante :

  • INSERT INTO test.poc (host) VALUES (@@report_host);
  • SELECT * FROM test.poc;

 

Grâce à ces 2 requêtes, le petit scripts ci-dessous et à un kill -9 opportun,  on va être en mesure de suivre les processus de routage (ProxySQL) et de failover (MySQL Group Replication).

Les transactions 1 & 2 (ids 2 et 9) sont jouées sur mysql_node 1.

A partir de la troisième transaction (ids 11 et 18), elles sont jouées sur le nouveau primaire mysql_node 3, car mysql_node 1 a crashé.

Terminons ce tuto en image.

 

MySQL Enterprise Monitor

Comme énoncé en introduction, une architecture de base de données doit de se reposer sur les 3 piliers : Monitoring / Backup process / High Availability.

Je vous présente de façon succincte, MySQL Enterprise Monitor (MEM) qui est l’outil de supervision de MySQL, disponible avec la version commerciale de MySQL (MySQL Enterprise Edition). Il permet la détection et l’alerte des problèmes, la supervision des serveurs, des backups… des différents types de réplications, y compris Group Replication.

Pour essayer les différents outils Enterprise, c’est par ici.

Ci-dessous quelques captures d’écrans des différents états du cluster supervisé par MySQL Enterprise Monitor (click to enlarge):

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

 

 

Le(s) mot(s) de la fin

Fin 😀

Vous connaissez déjà les technologies, MySQL Replication et MySQL semi-synchronous Replication.

MySQL Group Replication est un outil supplémentaire qui apporte notamment la notion de « Automatic Database Failover » qu’il manquait à MySQL.

Ces architectures s’utilisent la plupart du temps avec un proxy. ProxySQL est sans aucun doute aujourd’hui l’une des meilleures solutions pour MySQL.

 

Note 5: MySQL propose MySQL InnoDB Cluster, package comprenant MySQL Group Replication, MySQL Router et MySQL Shell.
Tuto : Tutoriel – Déployer MySQL 5.7 InnoDB Cluster

 

 

References

dasini.net

MySQL Group Replication

ProxySQL

 

Thanks for using MySQL!

 

2 Responses to “Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication”

  1. […] Ami lecteur, toi qui t’intéresse à MySQL Group Replication et ProxySQL, une version plus récente de cet article existe : Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication […]

  2. […] Lire cet article en français […]

Leave a Reply