Configurer ProxySQL pour MySQL Group Replication

janvier 11, 2017

Cet article s’inspire de HA with MySQL Group Replication and ProxySQL de Frédéric Descamps aka @lefred

 

Dans un précédent article je vous ai présenté comment déployer un cluster MySQL Group Replication, la nouvelle solution de haute disponibilité de MySQL.

Ce type d’architecture est souvent utilisé avec un composant qui se place entre l’application et le cluster,composant généralement appelé proxy (quelque chose) ou router quelque chose. Dans cet article, je vais vous présenter le meilleur (selon moi) proxy open source du moment : ProxySQL (1.3.2 : la version GA à la date d’écriture).

Le but de cet article est de créer un PoC Solution HA Base de Données Open Source : MySQL Group Replication et ProxySQL.

 

L’article étant suffisamment long, je ne vais couvrir ni l’installation des nœuds MySQL (5.7.17), ni le déploiement du cluster. Mais comme je suis sympa, voici les ressources nécessaires pour accomplir ces différentes tâches:

 

Avertissement!
Ce qui suit est effectué par un professionnel… sur son ordi portable 🙂 Toutes les instances sont en local, car je suis fainéant par commodité.
Attention, à ne pas reproduire tel quel en production. 

 

MySQL Group Replication

MySQL Group Replication se présente comme un plugin embarqué dans MySQL à partir de MySQL 5.7.17.

Caractéristiques

  • Version du serveur : 5.7.17
  • Version du plugin : 1.0
  • Nœud 1 : 127.0.0.1 : 14418
  • Nœud 2 : 127.0.0.1 : 14419
  • Nœud 3 : 127.0.0.1 : 14420

 

A partir d’ici, on a donc un cluster MySQL Group Replication de 3 nœuds, installé, déployé et qui fonctionne :

 

Le cluster est en mode single primary, c’est à dire qu’un seul nœud est disponible en écriture (à la fois).

 

Pour pouvoir automatiser le routage des requêtes, mais aussi permettre le failover avec ProxySQL nous allons enrichir le schéma sys de MySQL 5.7.17 avec le code ci-dessous (disponible ici également) :

Vous pouvez retrouver ce bout de code (version originale) et bien plus encore dans l’article du Product Manager de MySQL Group Replication, Matt Lord : MySQL Group Replication: A Quick Start Guide

Important
Dans la version originale du script il faut passer la commande SET GLOBAL log_bin_trust_function_creators=ON; sur TOUT les nœuds du cluster, avant de jouer le script qui contient le code ci-dessus :

 

 

ProxySQL

Caractéristiques

  • Version du proxy : 1.3.2.0

 

Dans le cadre de cet article je ne vais aborder que les fonctionnalités qui relative au routage et failover de ProxySQL. Je vous invite cependant à lire la documentation.

Les grandes étapes pour la préparation de notre environnement sont :

Téléchargement

Installation

Démarrage du service

 

Et voilà le travail!

ProxySQL est installé et fonctionnel :

 

Faisons un point. A ce stade on a :

  • déployé un cluster MySQL Group Replication de 3 nœuds
  • ajouté des fonctions et des vues dans sys schema (Group_Replication_helper_functions_and_views.sql)
  • installé ProxySQL

Il nous reste pas mal de choses intéressantes à voir. Cependant, avant de passer à la suite regardons à quoi ressemble notre architecture cible :

 

L’application se connecte à ProxySQL qui redirige les requêtes sur les bons nœuds. Les flèches rouges représentent les écritures et les vertes les lectures.

La suite ? on va donc maintenant rendre ProxySQL conscient de MySQL Group Replication.
Pour se faire on va se connecter à ProxySQL en mode administrateur (utilisateur admin) :

La syntaxe est celle de MySQL \o/

Un DBA se sent un peu comme à la maison et c’est plutôt agréable.

Voyons un peu ce qu’il y a sous la surface :

ProxySQL contient 4 schémas (base de données). En voici une description succincte:

  • main : la configuration courante
  • disk : configuration persistante (stockée sur le disque)
  • stats : statistiques liées à l’utilisation du proxy
  • monitor : données collectée en vue de la supervision

Succinct, comme convenu!
Suffisant néanmoins dans le cadre de cet article, car nous allons principalement utiliser les tables du schéma main. tu veux en savoir plus ? RTFM !

 

Configuration des groupes dans ProxySQL

ProxySQL utilise la notion de Hostgroup pour regrouper de manière logique des serveurs par type. Ils sont définit dans la table main.mysql_servers :

 

Comme vu sur le schéma, on va séparer les lectures des écritures. Pour se faire on va  donc créer 2 groupes, l’un dédié aux écritures et l’autres… aux lectures :

 

Supervision

ProxySQL comprend un module de supervision (monitoring) qui a pour utilisateur et mot de passe par défaut, respectivement monitor et monitor (oui 2 fois monitor). Cet utilisateur doit bien évidemment exister au niveau des membres du cluster et il n’a besoin que du privilège MySQL USAGE, afin de pinger et vérifier la variable read_only.
Le privilège REPLICATION CLIENT est cependant nécessaire s’il doit superviser aussi le retard de réplication.

 

Scheduler

Le scheduler permet d’exécuter des scripts externes à ProxySQL. Nous avons besoin d’un script pour superviser les membres du cluster pour le routage des requêtes et le failover. Ce script est disponible sur mon repo GitHub : proxysql_groupreplication_checker.sh et  doit être placé dans « /var/lib/proxysql/ »

Petit rappel: ce script n’est fournit que pour l’exemple. Il ne doit pas être utiliser tel quel en production.

 

Note
Ce script est légèrement modifié par rapport à la version originale que Lefred propose : https://github.com/lefred/proxysql_groupreplication_checker pour pouvoir fonctionner dans le cadre de ce PoC avec ProxySQL 1.3.2.

 

Les jobs à exécuter sont stockés dans la table scheduler du schema main :

Quelques infos pour la route :

  • arg1 identifiant d’hostgroup pour les écritures
  • arg2 identifiant d’hostgroup pour les lectures
  • arg3 nombre d’écrivain actif simultanément
  • arg4 booléen qui permet de choisir si le membre actif pour les écritures est aussi candidat pour les lectures
  • arg5 fichier de log

 

Paramétrons le scheduler :

Et on peut constater que le script à modifié le statut des membres du cluster :

En clair, du point de vue de l’application, qui se connecte au proxy :

  • 14418 aka node 1 (primaire) : accessible en écriture / inaccessible en lecture
  • 14419 aka node 2 (secondaire) : inaccessible en écriture / accessible en lecture
  • 14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture

On est bien !

 

Test du failover

A ce stade on a tout ce qu’il faut pour tester le comportement du proxy en cas de perte du primaire. Chiche?

Vérification du statut des membres à l’aide de la supervision ProxySQL (perspective du proxy) :

Vérification de l’identité du nœud primaire (perspective du cluster) :

Vérification du statut des membres en contactant les nœuds directement (perspective client) :

Il y a manifestement un consensus pour dire que :

  • Membre 1, port 11418 est le nœud primaire, en mode lecture/écriture (mais lecture seule pour ProxySQL)
  • Membre 2 (port 14419) & 3 (port 14420) sont des nœuds secondaires, en mode lecture seule.

Note
En fonction de votre configuration un utilisateur et un mot de passe peuvent être requis

 

La première action de notre test consiste en l’ arrêt du nœud primaire (node 1, port 14418) :

MySQL Group Replication va donc élire un nouveau membre primaire :

Le nœud 2 (port 14419) est donc le nouveau membre primaire du cluster.

Une autre vue de ce nouveau statut :

En ce qui concerne la vision ProxySQL :

Confirmation, avec ce nouvel état :

  • 14418 aka node 1 (down) : inaccessible
  • 14419 aka node 2 (primaire) : accessible en écriture / inaccessible en lecture
  • 14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture

Que c’est beau l’informatique… quand ça fonctionne.

Que se passe t’il si le membre 1 revient ?

Le proxy nous montre un nouvel état, une fois le nœud 1 de retour dans le cluster :

  • 14418 aka node 1 (secondaire) : inaccessible en écriture / accessible en lecture
  • 14419 aka node 2 (primaire) : accessible en écriture / inaccessible en lecture
  • 14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture

Pour la suite de l’artcile, le membre 1 (14418) est à nouveau le nœud primaire.

 

Ci dessous extraits de quelques états du proxy durant la phase de failover :

## few seconds…

## few seconds…

 

Règles de routage

La table mysql_query_rules du schema main va contenir les règles de routages. C’est donc là que nous allons pouvoir séparer les requêtes d’écriture des requêtes de lecture :

Pour ce PoC nos règles seront simples et définies à base de regex :

  • Groupe lecture : requêtes commençant par le mot clé SELECT (^SELECT) vont dans le hostgroup 2.
  • Groupe écriture : toutes les autres requêtes (DDL, DML,…), ainsi que les SELECT contenant la clause FOR UPDATE (^SELECT.*FOR UPDATE$) vont dans le hostgroup 1.

Si une requête ne correspond à aucune des règles, elle sera dirigée vers le hostgroup par défaut (champ default_hostgroup de la table mysql_users) de l’utilisateur qui exécute la requête.

Regardons la structure de la table mysql_users :

 

Créons l’utilisateur MySQL utilisé pour se connecter au backend (app_user / app_pwd) :

Maintenant nous pouvons renseigner la table mysql_query_rules avec nos 2 règles de routages (écriture et lecture) :

Et voilà!

Il est temps de passer aux choses sérieuses.

 

Playtime

Toutes les briques de notre archi sont assemblées et configurées, c’est donc l’heure du test final.

Notre appli, simulée par un script bash, va se connecter au proxy (ProxySQL) pour interroger la base de données distribuée (MySQL Group Replication).

Au fait on fait comment pour se connecter au proxy ?

Pour se connecter à la base de données, par l’intermédiaire du proxy, l’application peut soit utiliser le port 6033, soit le socket /tmp/proxysql.sock :

Comme prévu, les requêtes de lecture se connectent sur l’un des 2 nœuds secondaires (node 2 : port 14419 || node 3: port 14420).

Allons plus loin dans le test et créons la table test.poc :

 

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

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

 

Grâce à ces 2 requêtes et le petit scripts ci-dessous, on va être en mesure de suivre les processus de routage et de failover.
En clair :

  • écriture sur le primaire
  • lectures sur le(s) secondaire(s)
  • crash du master
  • failover automatique et transparent

Revoyons la scène au ralenti…

  • Row 1 : Ecriture sur 14418, Lecture sur 14419
  • Row 2 : Ecriture sur 14418, Lecture sur 14419
  • Row 3 : Ecriture sur 14418, Lecture sur 14420
  • Row 4 : Ecriture sur 14419, Lecture sur 14420
    • Le membre 14418 est stoppé, failover automatique de MySQL Group Replication et ProxySQL redirige les écritures sur 14419
  • Row 5 : Ecriture sur 14419, Lecture sur 14420

 

Trop cool !

 

Le mot de la fin

Récapitulons: pour ce PoC,  une architecture ProxySQL 1.3 avec MySQL Group Replication s’est montée en plusieurs étapes :

  1. Déployement d’un cluster MySQL Group Replication (3 ou 5 nœuds en général)
  2. Enrichissement de sys schema afin gérer le routage et le failover
  3. Installation et paramétrage de ProxySQL
  4. Ajout du job de routage et de failover

L’étape 2 ne sera plus nécessaire à terme, sys schema sera nativement compatible avec Goup Replication dans une prochaine version de MySQL.

En ce qui concerne les étapes 3 et 4, elles vont être simplifiées car ProxySQL 1.4 qui est en cours de développement, est nativement compatible Group Replication.

Et surtout n’oubliez pas que MySQL InnoDB Cluster est la solution maison tout en un, qui simplifie grandement le déploiement de ce type d’architecture. Pour rappel InnoDB Cluster c’est :

Vous pouvez d’ores et déjà tester la dernière pré-version (labs preview) de MySQL InnoDB Cluster: MySQL InnoDB Cluster 5.7.17 Preview 2
C’est inutile mais je vais le rappeler quand même : il n’est pas recommandé d’utiliser les binaires du Labs en production.

 

Références

MySQL Group Replication

 

ProxySQL

 

MySQL InnoDB Cluster

 

Thanks for using MySQL!

 

Leave a Reply