Mes confs MySQL pour avril et mai 2017

avril 24, 2017

J’aurai le plaisir de vous rencontrer (et accessoirement de parler de MySQL) les 25 avril, 2 et 18 mai.
 

MySQL The world's most popular open source database

MySQL Day Paris

Toutes l’info MySQL par l’équipe MySQL France

Info et inscription

 

Mise en bouche PHP – Paris

Meetup organisé par l’antenne AFUP Paris

Info et inscription

 

PHP tour 2017 – Nantes

La prochaine étape du cycle de conférences itinérant, réunissant toutes les communautés PHP se déroule à Nantes

Info et inscription

 

 

0

Adopte un… cluster MySQL Group Replication

avril 10, 2017

Autant le dire tout de suite, rien avoir avec un site internet de rencontre en ligne! 🙂
C’est bel et bien un nouvel article dans la série, Haute Disponibilité avec MySQL.

Au menu d’aujourd’hui : comment passer de l’administration « manuelle » de votre solution HA MySQL Group Replication à une administration plus simple, plus fun mais surtout facilement automatisable avec le pack MySQL InnoDB Cluster. En clair, on va voir comment utiliser MySQL Shell pour l’administration et l’orchestration du cluster et MySQL Router pour rediriger automatiquement les transactions de l’application vers le noeud primaire du cluster.

Quelques pré-requis sont nécessaire pour optimiser ta compréhension de cet article, je te conseille donc la lecture préalable des articles suivants:

 

Note: 
L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server, solution à ne pas confondre avec MySQL NDB Cluster.

 

Le contexte

Pour ce PoC, j’ai un cluster MySQL Group Replication de 3 nœuds, fonctionnel, en mode « Single Primary » (déployé avec Docker Compose):

  • Instance 1 : mysql_node1 (172.19.0.2)
  • Instance 2 : mysql_node2 (172.19.0.4)
  • Instance 3 : mysql_node3 (172.19.0.3)

 

MySQL Router et mon application (simulée avec le client texte mysql) sont sur la machine host (par commodité). C’est également le cas de MySQL Shell.

En ce qui concerne les versions des softs:

  • MySQL Server 5.7.17
  • MySQL Router 2.1.2 rc
  • MySQL Shell 1.0.8-rc

Docker 1.12.6 & Docker-compose 1.11.2. Docker est hors du cadre de cet article, mais tu trouveras à la fin de cet article le fichier docker-compose.yml utilisé.

 

Ah oui, j’ai failli oublier :

TL;DR
Tu as un cluster MySQL Group Replication configuré/administré manuellement et qui tourne. Tu peux l’administrer / le configurer avec MySQL Shell et gérer le routage des requêtes applicatives avec MySQL Router, ces 3 composants forment MySQL InnoDB Cluster.

MySQL InnoDB Cluster Overview

 

MySQL Group Replication

Les étapes de déploiement du cluster Group Replication ont déjà été traitées ici.

Voici mes 3 instances MySQL 5.7

MySQL 5.7.17 plus précisément.

 

A quoi ressemble mon cluster Group Replication ?

Je peux avoir la description de l’architecture avec la table performance_schema.replication_group_members :

L’identification du noeud primaire peut se faire de la manière suivante :

Le noeud mysql_node1 est donc en mode lecture écriture aka le noeud primaire (cette info nous sera utile pour la suite) et les 2 autres en lecture seule (super read only activé):

 

On a donc un cluster MySQL Group Replication avec 3 nœuds online.
Le membre mysql_node1 est (pour le moment) le primaire, mysql_node2 et mysql_node3 sont les secondaires.

 

L’étape suivant consistera à gérer le cluster avec MySQL Shell.

 

MySQL Shell, interface pour gérer son cluster

On va se connecter avec le client MySQL Shell au noeud primaire :

Ensuite, je « crée » mon cluster, en fait je vais rendre persistante les informations relatives à l’architecture du groupe dans mon cluster (plus d’info sur ce sujet plus bas).

La méthode createCluster() prends comme paramètres, le nom du cluster (pocCluster) ainsi que des paramètres optionnels comme ipWhitelist (172.19.0.0/16)…

Pour plus de détails connecte toi à MySQL Shell (mysqlsh) et tape : dba.help(‘createCluster’)

 

Vérifions l’état du cluster

MySQL Shell nous confirme ce que nous savions déjà:

je m’auto cite; un grand (1m89) DBA à dit un jour :

« On a donc un cluster MySQL Group Replication déployé avec 3 nœuds online. Le membre mysql_node1 est (pour le moment) le primaire, mysql_node2 et mysql_node3 sont les secondaires. »

 

En zoomant dans les entrailles du groupe, on constate que la méthode createCluster() a écrit des données dans le cluster :

Le schéma mysql_innodb_cluster_metadata a donc été créé pour contenir les informations relatives au cluster.

Le nom des tables est assez explicite :

hosts

 

clusters

 

replicasets

 

instances

 

 

Déploiement de MySQL Router

Le déploiement du router est trivial, il faut pour commencer le bootstrapper au cluster, c’est à dire le lier au cluster en le connectant aux méta-données :

Les paramètres directory et name sont optionnels.

 

Lancer MySQL Router :

L’application doit se connecter (par défaut) au port 6446 (écritures et lectures vers le noeud primaire). En cas de besoin de read scalability, les lectures peuvent être dirigées vers le port 6447.

 

Inspectons de nouveau les méta données :

routers

 

hosts

 

Voilà, mon cluster Group Replication paramétré « à la main » fait maintenant partie intégrante de mon InnoDB Cluster, je peux donc l’administrer avec MySQL Shell et je peux vous assurer que c’est vraiment pratique.

Mais ceci est une autre histoire et fera l’objet d’un autre article 🙂

 

Annexe

Le fichier docker-compose est le suivant :

 

Thanks for using MySQL!

0

Tester MySQL InnoDB Cluster

mars 13, 2017

MySQL InnoDB Cluster est la (future) solution out-of-the-box HA de MySQL (à ne pas confondre avec MySQL NDB Cluster). Ce produit est composé de 3 éléments :

  • MySQL Group Replication
    • Plugin de réplication multi-maître, avec résolution de conflits et basculement (failover) automatique.
  • MySQL Router
    • Middleware légé et performant qui fournit un routage transparent entre l’application et le cluster.
  • MySQL Shell
    • Client interactif Javascript, Python et SQL qui permet d’administrer le cluster.

MySQL InnoDB Cluster Architecture
MySQL Group Replication est GA et peut donc être utilisé tel quel hors MySQL InnoDB Cluster (voir Déployer un cluster MySQL Group Replication).

Ce n’est par contre pas encore le cas pour les 2 autres composants, MySQL Shell et MySQL Router qui sont en Release Candidate (RC), il n’est donc pas recommandé à ce jour de les utiliser dans un environnement de production.

 

Note: 
L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server, solution à ne pas confondre avec MySQL NDB Cluster.

 

Installer MySQL InnoDB Cluster

Dans le cadre de cet article, les versions utilisées sont:

  • MySQL Server : 5.7.17
  • MySQL Shell : 1.0.8-rc
  • MySQL Router : 2.1.2 rc

Pour utiliser MySQL InnoDB Cluster, il faut simplement installer ces 3 composants :

 

Déployer les instances de test

MySQL Shell permet de déployer simplement des instances MySQL de test (sandbox).

Connexion avec MySQL Shell :

 

Déployer la 1ère instance MySQL qui fera partie de notre cluster :

  • Host : localhost
  • Port : 3310

Il suffit de rentrer le mot de passe root, puis l’instance est crée dans ~/mysql-sandboxes :

 

Créons 2 autres instances pour le cluster:

  • Host : localhost
  • Ports : 3320 & 3330

On a donc 3 instances MySQL dans notre sandbox.

 

A Noter que si vous avez déjà un cluster MySQL Group Replication actif, MySQL InnoDB Cluster est capable de l’adopter. Ceci fera l’objet d’un prochain article.

 

Gérer les instances

D’autres méthodes existent pour gérer les instances:

  • Stop
    • dba.stopSandboxInstance()
  • Start
    • dba.startSandboxInstance()
  • Kill  : permet de simuler le crash d’un nœud
    • dba.killSandboxInstance()
  • Delete : suppression totale de l’instance de la sandbox
    • dba.deleteSandboxInstance()

 

Exemple – Arrêt et suppression d’une instance

 

L’aide est disponible dans MySQL Shell avec dba.help()

 

Vérifier la configuration des instances

Un moyen simple de savoir si les instances ont la configuration requise pour faire partie du cluster est d’utiliser : dba.checkInstanceConfiguration()

 

Créer le cluster

On a donc 3 instances MySQL, en standalone, configurées et prêtes à se transformer en une base de données distribuée.

Je vais donc me connecter à une de mes instances :

  • User : root
  • Host : localhost
  • Ports : 3310

 

Puis commencer la création effective de mon instance MySQL InnoDB Cluster, nommée testcluster

Je me retrouve pour le moment avec un cluster d’1 nœud. Certes, pas encore hautement disponible, mais c’est un début 🙂

La méthode status() me le confirme:

 

Avant de lui rajouter des petits copains, on va vérifier que toutes les instances ont la même liste de transactions exécutées:

Parfait !

 

Ajouter les autres nœuds

addInstance(), la bien nommée :

Ajout de localhost:3320

Ajout de localhost:3330

 

L’architecture de notre cluster est maintenant:

localhost:3310 est le primaire il est donc le seul à accepter les écritures. Les 2 autres membres ne sont accessibles qu’en lecture.

C’est le comportement par défaut de MySQL Group Replication est donc de MySQL InnoDB Cluster.  Pour avoir un cluster en multi-master, il faut le préciser lors de la création du cluster (dba.createCluster()).

 

Les informations révélées par les différentes commandes exécutée jusqu’ici, sont persistante. Elles sont en stockées dans les nœuds du cluster, dans le schéma mysql_innodb_cluster_metadata :

 

Déployer MySQL Router

MySQL Router étant déjà installé, on va le configurer pour l’interfacer avec notre cluster:

4 ports TCP ont été configurés, 2 pour les connexions MySQL traditionnelles:

  • 6446 : lectures / écritures pour le noeud primaire
  • 6447 : lectures seules pour les nœuds secondaires (Round-Robin)

Et le pendant pour les connexions avec le protocole X (64460 & 64470), pour une utilisation NoSQL Document Store de MySQL.

 

Le bootstrap à généré un fichier de configuration pour MySQL Router

 

Utiliser MySQL Router

Evidemment il faut le démarrer

 

Un petit coup d’œil dans les logs:

 

Voila MySQL InnoDB Cluster configuré et prêt à être testé !

L’application doit se connecter au port 6446 (écritures et lectures vers le noeud primaire). Les lectures peuvent également être dirigées vers le port 6447.

 

Tests

Port de lecture

=> affiche : 3320, 3330, 3320, 3330, …

 

Port d’écriture

=> affiche : 3310, 3310, 3310, …

 

Failover automatique

Grâce à la méthode dba.killSandboxInstance() on peut simuler un crash du serveur primaire et ainsi voir à l’oeuvre le failover automatique du cluster.

Les 2 sessions qui suivent s’exécutent en parallèle:

Session 1

 

Session 2

=> La session 1 va afficher 3310 puis 3320 après le basculement de la base de données (database failover).

 

 

En complément je vous invite à lire :

et regarder la video de lefred : MySQL InnoDB Cluster: MySQL Shell starter guide

 

Documentation

 

Thanks for using MySQL!

2

FAQ Webinar MySQL Group Replication

mars 3, 2017

Mise-à-jour: 7 Mars 2017

Le 1er mars dernier, j’ai présenté lors d’un webinar, la technologie de haute disponibilité MySQL Group Replication. On a explosé notre record d’affluence et j’ai été inondé de questions, preuve s’il en faut de votre intérêt, toujours plus important, pour la base de données Open Source la plus populaire au monde.

Je n’ai malheureusement pas été en mesure de répondre à toutes les questions, ce qui me permet aujourd’hui de les centraliser dans cette FAQ. Cette dernière pourra être mise à jour de temps à autres, en fonctions des questions que je récupérerai du terrain.

En complément de cette FAQ

 


  • Sur quels OS peut on utiliser MySQL Group Replication ?

MySQL Group Replication est un plugin du serveur MySQL. Il est donc présent sur toutes les plateformes où MySQL 5.7 est disponible :

  • Linux
  • Windows
  • Solaris
  • OSX
  • FreeBSD

Liste complete

 

  • Est ce que la feature de multi-master nécessitera un update du driver MySQL ou cela sera vu comme un master « standard » d’un point de vu client ? 

L’architecture classique MySQL Group Replication est la suivante :

Client + MySQL Router      |      MySQL Group Replication

Client      |      ProxySQL      |      MySQL Group Replication

C’est du moins le cas en mode Single Primary car il faut pouvoir identifier le nœud primaire.

Cette architecture est valable également en mode multi-master (ou multi-Primaire), cependant dans ce cas, vu que tout les membres sont master, il suffit simplement que le driver contiennent la liste des membres.

 

  • Est-ce que l’on a une contrainte sur le nombre de nœuds minimum ?

Pas de contraintes par « design ».  Cependant, pour qu’un system distribué puisse fournir du failover automatique, il faut minimum 3 nœuds. Cela permet d’éviter le split brain.

A noter qu’il est possible de mettre en place un cluster MySQL Group Replication avec seulement 2 nœuds. Mais en cas d’arrêt non prévu (crash) d’un des membres, l’autre membre n’acceptera pas de nouvelles requêtes. On se trouve alors dans un cas de Network Partitioning,  et la partition doit être débloquée selon la procedure inscrite dans la doc.

Par contre si l’un des 2 membres est arrêté « gracieusement » (STOP GROUP_REPLICATION;), l’autre continue de fonctionner normalement.

 

  • Dans le cas d’une utilisation multi-master, est-ce que l’on peut ajouter/retirer des nœuds dynamiquement ?

Oui, dans les 2 modes (Single Primary ou multi-master) il est possible d’ajouter et/ou de retirer des nœuds dynamiquement.

 

  • Au sein d’un cluster MySQL Group Replication, l’élection d’un nœud master R/W est-il automatique ou manuel ?
  • Dans le mode Single Primary, lorsque le primaire tombe, comment est choisi le nouveau nœud primaire ?

Le process d’election est automatique et transparent pour le client. Alors pas mal de process se passent « behind the scene » !

Il faut notamment que les autres membres identifient le fait que le nœud ne fasse plus partie du cluster et puis ensuite enclencher le process d’élection du nouveau nœud primaire (le prochain dans la liste ordonnée en fonction de l’UUID de instances du cluster) et aussi désactiver le mode read-only.

Identifier le nœud primaire : https://dev.mysql.com/doc/refman/5.7/en/group-replication-find-primary.html

Exemples:

L’information qui permet de savoir quel nœud est primaire est disponible dans la table performance_schema.global_status :

En la joignant avec la table performance_schema.replication_group_members ont obtient un peu plus d’infos:

 

  • Est-ce que l’on a des limites en terme de taille de base de données pour MySQL Group Replication ?

Le moteur de stockage utilisé avec MySQL Group Replication est InnoDB. Les limites sont donc celles imposées par InnoDB.

 

  • Est-ce que l’on peut utiliser MySQL Router avec une replication asynchrone classique ?

MySQL Router 2.0, peut être utilisé avec MySQL Replication : https://dev.mysql.com/doc/mysql-router/2.0/en/

Le plugin Connection Routing fournit 2 modes de « routage »:

  • Le mode Read-Only : route les requêtes en mode round-robin sur une liste d’instances MySQL.
    => Cela a du sens pour router les requêtes de lectures sur les slaves.
  • Le mode Read-Write : route les requêtes sur le premier serveur de la liste, si celui si n’est plus accessible, les requêtes sont alors routées sur le suivant de la liste et ainsi de suite jusqu’au dernier.
    => Perso, je ne suis pas trop fan de ce mode pour MySQL Replication.

 

  • Sur une configuration multi-master, est il toujours conseillé de mettre en place un offset sur les ID pour chaque node pour éviter les conflits ?

Avec la réplication classique (asynchrone) de MySQL,  pour une architecture multi-master en actif/actif (que je ne conseille toujours pas), il est nécessaire de mettre en place différents offsets pour prévenir les conflits liés aux clés primaire en auto increment (details).

Dans le cas de MySQL Group Replication, ce problème se pose également en mode multi-master. C’est pour cela qu’il est géré automatiquement par le cluster. Il est cependant possible de modifier les paramètres manuellement (details).

 

  • La modification d’une table est-elle automatiquement répliquée ?

Toutes les modifications certifiées d’un nœud vont être répliquées sur les autres. Cela concerne évidemment les DDL.

A noter que certaines précautions doivent être prisent lorsque vous exécutez des DDL en mode multi-master (details).

 

  • Avec 3 nœuds, si on perd 1 nœud et que les 2 autres ne se voient plus, pas de quorum donc plus d’écriture ?

Avec une architecture MySQL Group Replication de 3 nœuds, l’arrêt non prévu (crash) d’1 membre génère la recomposition automatique du cluster avec 2 nœuds ie aucune intervention n’est nécessaire.

Par contre vous vous trouvez dans une situation « inconfortable » et il faut donc re-provisionner un 3ème nœuds ASAP. Parce qu’avec ce cluster de 2 membres, en cas de crash d’un des 2 ou en cas de split-brain, la recomposition automatique du cluster ne peut plus se faire.

On se trouve alors dans le cas du Network Partitioning (les membres n’acceptent plus d’écriture), et la partition doit être débloquée « manuellement ».

 

  • A quel format le log binaire doit être positionné ?

Seul le format ROW est valide (binlog-format=ROW)

 

  • Peut on contrôler les délais de réplication ?

Le Flow-control permet de maintenir les membres du groupe raisonnablement proches les uns des autres.

A noter que d’autres approches pour contrôler les délais de réplication seront disponibles dans le futur.

Je vous conseille la lecture de cet article qui traite du sujet des performances de MySQL group Replication.

 

  • Pourquoi 9 nœuds maximum ?

Selon les résultats de nos tests, jusqu’à 9 membres les performances restent très satisfaisantes. De plus ce nombre assure une très bonne disponibilité car le cluster peut alors gérer automatiquement jusqu’à 4 pertes de nœuds simultanément !

A noter qu’il est toujours possible de rajouter des slaves au cluster MySQL Group Replication.

 

  • Est-il possible d’avoir une configuration multi OS par exemple un membre sur Linux et un autre membre sur du Windows ?

Tout à fait !

Je peux par exemple avoir l’architecture MySQL Group Replication suivante :

Nœud A sur Linux
Nœud B sur Windows
Nœud C sur OSX

 


Quelques liens utiles

 

 

Thanks for using MySQL!

1

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!

 

1

Retour sur le MySQL Day Paris 2016

novembre 24, 2016

Oracle MySQL Day Paris

Chose promise, chose due 🙂

Voici les présentations de l’Oracle MySQL Day Paris du 22 novembre 2016.

State Of The Dolphin

MySQL Day Paris 2016 – State Of The Dolphin from Olivier DASINI

 

MySQL High Availability  – InnoDB Cluster and NDB Cluster

MySQL Day Paris 2016 – MySQL HA: InnoDB Cluster and NDB Cluster from Olivier DASINI

 

MySQL as a Document Store

MySQL Day Paris 2016 – MySQL as a Document Store from Olivier DASINI

 

Introducing Oracle MySQL Cloud Service

MySQL Day Paris 2016 – Introducing Oracle MySQL Cloud Service from Olivier DASINI

 

MySQL Enterprise Edition – Achieve the Highest Levels of Security

MySQL Day Paris 2016 – MySQL Enterprise Edition from Olivier DASINI

 

MySQL Day Paris
Thanks for using MySQL!

 

0

Déployer un cluster MySQL Group Replication

novembre 8, 2016

Mise-à-jour: 13 janvier 2017

Historiquement, les solutions de haute disponibilité (HA) avec MySQL tournent autour de la fonctionnalité native MySQL Replication: replication asynchrone ou semi-synchrone. Ces modes de  replication sont très largement utilisés pour le besoins critiques d’enterprises « at scale » comme Facebook, Twitter, Booking.com, Uber

Aujourd’hui MySQL propose une nouvelle fonctionnalité native de haute disponibilité :

MySQL Group Replication.

MySQL Group Replication

MySQL Group Replication est un plugin pour MySQL 5.7+ qui fournit nativement de la réplication virtuellement synchrone avec détection/résolution de conflit et cohérence garantie. Ce plugin est disponible sur tous les systèmes d’exploitation supportés par MySQL (Linux, Windows, Solaris, OSX, …)

C’est la fonctionnalité idéale pour les architectures multi-maîtres. Il n’est alors plus nécessaire de gérer le failover de la base de données (à l’aide d’outils comme: mysqlfailover; mysqlrpladmin; MHA).

A noter que le plugin MySQL Group replication est en ce moment en Release Candidate (RC) ce qui veut dire qu’il n’est pas encore conseillé pour la production.  C’est néanmoins le bon moment pour le tester (binaires téléchargeable ici). Le statut RC est la dernière étape avant la GA, et selon mon petit doigt ce n’est plus qu’une question de semaines avant la sortie officielle en GA (qui a dit MySQL 5.7.17 ???).

Le plugin MySQL Group Replication est GA et embarqué dans MySQL à partir de la version 5.7.17. Pour télécharger MySQL rendez-vous sur la page : http://www.mysql.com/downloads/ 

 

Déployer un cluster de 3 nœuds

La version du plugin lors de l’écriture de cet article est 0.9 disponible avec MySQL 5.7.15 dans le lab de MySQL : http://labs.mysql.com/
Cette article présume que MySQL 5.7.15 est déja installé (installer MySQL).

Article mis-à-jour avec la version 1.0 du plugin, qui est la version GA embarquée dans MySQL 5.7.17.

Cette article présume qu’une version de MySQL égale ou supérieure à 5.7.17 est déja installé (installer MySQL).

Caractéristiques

  • Version du serveur : 5.7.17
  • Version du plugin : 1.0
  • Nœud 1 : 192.168.1.11 : 14115
  • Nœud 2 : 192.168.1.9 : 3301
  • Nœud 3 : 192.168.1.48 : 5709

 

Configurer les instances MySQL

Comme toutes technologies, il y a des contraintes et quelques limitations. MySQL Group Replication nécessite :

  • MySQL 5.7
  • Tables avec le moteur de stockage InnoDB
  • Tables avec une clé primaire
  • Seul le protocole IPV4 est supporté
  • Les DDL en parallèle de DDL/DML exécutés par différents nœuds sur le même objet sont proscrits.

Liste complète : Requirements and Limitations

 

Au niveau configuration des serveurs il faut:

 

 

Il faut également avoir une valeur de la variable server_id différente pour chacun des 3 nœuds:

  • Nœud 1 : server_id=11
  • Nœud 2 : server_id=9
  • Nœud 3 : server_id=48

 

Pour synthétiser, dans le fichiers de configuration (my.cnf / my.ini) du nœud 1, il faut avoir (en plus des infos classiques):

Pareil pour les 2 autres, à la valeur de server_id près.

Configuration spécifique à MySQL Group Replication

Le cluster doit avoir un identifiant unique au format UUID:

  • le UUID doit être valide
  • il doit être définit sur chacune des machines du groupe

La fonction MySQL UUID() permet de générer un… uuid:

 

 

Note.

Pour avoir une architecture multi-master ie pouvoir écrire sur toutes les intances à la fois il faut group_replication_single_primary_mode = OFF sur tous les nœuds

 

Activation du plugin MySQL Group Replication

A partir de la version 5.7.17,  MySQL embarque le plugin Group Replication qui se nomme : group_replication.so.

Le plugin n’est évidemment pas activé par défaut, cependant vous avez 2 solutions pour l’activer :

  • dans le fichier de configuration comme indiqué précédemment (nécessite un redémarrage de l’instance MySQL)

  • à la volée, avec la commande INSTALL PLUGIN si l’instance est correctement configurée

Un fois activé, les informations relatives au plugin sont disponible avec (à faire sur tout les nœuds) :

Le fichier group_replication.so se trouve dans le répertoire à plugin – plugin_dir:

/usr/local/mysql/lib/plugin/ pour ma configuration.

 

Synthétisons encore!

Dans le fichiers de configuration (my.cnf / my.ini) du nœud 1, il faut ajouter:

 

Et donc pour les 2 nœuds restant, il faut ajuster:

Nœud 2 :

 

Nœud 3 :

 

Note.

Mon environnement étant « exotique » j’ai du configurer les 2 variables qui suivent, pour que le process de restauration fonctionne. 

  • Nœud 1 : report_port = 14115 / report_host = « 192.168.1.11 »
  • Nœud 2 : report_port = 3301 / report_host = « 192.168.1.9 »
  • Nœud 3 : report_port = 5709 / report_host = « 192.168.1.48 »

Cependant il est plus que recommandé de configurer correctement nom d’hôte (/etc/hosts; /etc/hostname;)  et DNS.

 

 

Optionnel : Pour empêcher l’utilisation (malencontreuse) d’autres moteurs de stockage autre qu’InnoDB (non-transactionnel donc), il peut être utile d’ajouter à la conf:

 

Le fichier de configuration étant modifié, il faut redémarrer les instances MySQL pour que la nouvelle configuration prenne effet.

A noter quand même que la plupart des variables relatives à Group Replication  sont changeable à chaud avec la commande SET GLOBAL <commande>.

 

Utilisateur de restauration

Cet utilisateur est nécessaire pour le processus de récupération automatique (recovery) lorsqu’un serveur (r)entre dans le groupe.

A créer et à configurer sur tout les nœuds du groupe. Il permettra d’établir une connexion replica/source entre les membres du groupe en cas de process de recovery.

 

On est pas mal là!

Avant d’aller plus loin, une bonne habitude est de vérifier que les serveurs sont correctement configurés.

 

Vérification de la configuration

Configuration serveur

La commande SHOW GLOBAL VARIABLES permet de voir la valeur des variables serveur.

 

Configuration restauration

 

Configuration Group Replication

Cela correspond à ce qui a été renseigné un peu plus haut.

Je vous conseille de toujours vérifier les configurations, sur tout les nœuds. Ca permet de gagner du temps et de sauvegarder l’énergie par la suite… « Certified wise DBA »

 

 

Déployer le cluster

Le moment tant attendu arrive…

Pour la suite de cet article, je présume que les instances ont les mêmes données, donc le cas présent, pas de données.

Dans le cas contraire il suffit simplement de faire une sauvegarde complète d’une des instances et de la restaurer sur les 2 autres (Backup and Recovery).

 

Amorcer le cluster

L’amorçage (bootstrap) consiste à créer un groupe d’un seul nœud (faut bien commencer !). Ce dernier va par la suite être en mesure de recevoir d’autres membres. La procédure de bootstrap ne doit donc se faire que sur un seul membre, le tout premier nœud du groupe.

Le nœud 1 est donc désigné volontaire pour l’amorçage.

Revoyons la scène au ralenti…

  • j’initie : group_replication_bootstrap_group = ON
  • je démarre : START GROUP_REPLICATION
  • je termine l’initialisation : group_replication_bootstrap_group = OFF

 

C’est vraiment important d’exécuter group_replication_bootstrap_group = ON seulement sur le 1er nœud du cluster, sous peine de se retrouver avec des groupes indépendant (split-brain artificiel).

 

Récupérer des infos sur le cluster c’est facile, avec les tables:

  • performance_schema.replication_group_members
  • performance_schema.replication_connection_status

 

 

Le groupe contient bien un seul membre, 192.168.1.11 aka nœud 1 et il est ONLINE.

Jusqu’ici tout va bien!

 

Ajouter des nœuds

Un cluster composé d’un seul membre, c’est un bon début. Mais ce n’est évidemment pas suffisant pour avoir de la haute disponibilité avec notre base de données MySQL

Ajout du nœud 2

Trop facile !!!

La supervision montre que le cluster est donc maintenant composé de 2 membres, ONLINE!

 

On ne vas pas s’arrêter en si bon chemin

 

Ajout du nœud 3

Cap ou pas cap ?

Le process est évidemment le même.

 

Les 3 nœuds sont bien actifs et fonctionnels.

 

Alors ? Heureux ?

Il est possible d’avoir jusqu’à 9 membres, donc tu peux y aller Marcel!!!

 

Identifier le nœud primaire

Rapide récap

Je viens de créer un cluster de 3 nœuds en utilisant le plugin HA natif MySQL Group Replication.

Sur ces 3 nœuds, 2 sont en mode lecture seule (mode: super_read_only) et le troisième en mode lecture/écriture, c’est le nœud primaire.

Un intérêt de cette architecture HA est qu’elle est finalement très proche d’une architecture de réplication classique master/slaves. La grosse différence, et c’est ce qui fait sa puissance, est qu’avec MySQL Group Replication, il n’est plus utile de gérer le failover base de données. Est ça, ce n’est pas rien !

 

L’information qui permet de savoir quel nœud est primaire est disponible dans la table performance_schema.global_status :

 

En la joignant avec la table performance_schema.replication_group_members ont obtient un peu plus d’infos:

Dons cette architecture, le nœud 00014115-1111-1111-1111-111111111111 aka 192.168.1.11 aka nœud 1 est le primaire.

 

Note.

En mode multi-maître, ils sont tous primaire

 

Le corollaire immédiat de cette information est que dans cette configuration un seul nœud accepte les écritures. Cependant il est possible de lire sur tous les nœuds.
Sur le nœud 2 -nœud NON primaire

Comme prévu les écritures sont impossibles.

 

 

Sur le nœud 1 – le nœud primaire

Le schéma (database) gr_test est créé sur le nœud 1. La transaction est répliquée automatiquement sur les autres nœuds.

 

 

Arrêt du nœud primaire

Si le nœud primaire n’est plus en ligne (arrêt du serveur pour cause de maintenance ou crash) l’un des 2 autres nœuds devient alors primaire.

 

Les autres membres du cluster sont au courant de la disparition de leur pote.

Le groupe ne se compose alors plus que de 2 membres : 192.168.1.9 & 192.168.1.48

Et l’un des deux devient primaire.

 

Le nœud 2 est passé primaire (automatic database failover)
Je peux donc écrire sur le nœud 2 :