dasini.net - Journal d\'un expert MySQL

30 mins avec les fonctions JSON de MySQL

juillet 23, 2018

Tags: CTE, JSON, NoSQL

Note: L’article suivant peut t’intéresser également: 30 mins avec JSON en MySQL.

Comme tu le sais, JSON (JavaScript Object Notation) est un populaire format d’échange de données. Depuis la version 5.7, MySQL supporte un type de données JSON natif (au format interne binaire pour des raisons d’efficacités), ainsi qu’un riche ensemble de fonctions qui te permettront de manipuler dans tout les sens tes documents JSON.

Soyons clair! Cet article n’est pas une revue exhaustive des différentes fonctions JSON implémentées dans MySQL (RTFM! 😉), mais plutôt une sélection arbitraire de certaines d’entre elles.

Note: Depuis MySQL 8 il est désormais possible de manipuler les documents JSON sans SQL (NoSQL) avec MySQL as a document store. (Cette fonctionnalités n’est pas couverte dans cet article).

Les exemples ci dessous sont réalisés sans trucage avec MySQL 8.0.11, téléchargeable ici.

JSON – Fonctions utilitaires

JSON_PRETTY

Améliorer la lisibilité avec JSON_PRETTY

Par défaut, l’affichage d’un document JSON dans MySQL ressemble à ceci :

SELECT doc FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
doc: {"_id": "564b3259666906a86ea90a99", "name": "Dj Reynolds Pub And Restaurant", "grades": [{"date": {"$date": 1409961600000}, "grade": "A", "score": 2}, {"date": {"$date": 1374451200000}, "grade": "A", "score": 11}, {"date": {"$date": 1343692800000}, "grade": "A", "score": 12}, {"date": {"$date": 1325116800000}, "grade": "A", "score": 12}], "address": {"coord": [-73.98513559999999, 40.7676919], "street": "West   57 Street", "zipcode": "10019", "building": "351"}, "borough": "Manhattan", "cuisine": "Irish", "restaurant_id": "30191841"}

SELECT doc FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

doc: {"_id": "564b3259666906a86ea90a99", "name": "Dj Reynolds Pub And Restaurant", "grades": [{"date": {"$date": 1409961600000}, "grade": "A", "score": 2}, {"date": {"$date": 1374451200000}, "grade": "A", "score": 11}, {"date": {"$date": 1343692800000}, "grade": "A", "score": 12}, {"date": {"$date": 1325116800000}, "grade": "A", "score": 12}], "address": {"coord": [-73.98513559999999, 40.7676919], "street": "West 57 Street", "zipcode": "10019", "building": "351"}, "borough": "Manhattan", "cuisine": "Irish", "restaurant_id": "30191841"}

Tu peux avoir un affichage plus agréable avec JSON_PRETTY :

SELECT JSON_PRETTY(doc) FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
JSON_PRETTY(doc): {
  "_id": "564b3259666906a86ea90a99",
  "name": "Dj Reynolds Pub And Restaurant",
  "grades": [
    {
      "date": {
        "$date": 1409961600000
      },
      "grade": "A",
      "score": 2
    },
    {
      "date": {
        "$date": 1374451200000
      },
      "grade": "A",
      "score": 11
    },
    {
      "date": {
        "$date": 1343692800000
      },
      "grade": "A",
      "score": 12
    },
    {
      "date": {
        "$date": 1325116800000
      },
      "grade": "A",
      "score": 12
    }
  ],
  "address": {
    "coord": [
      -73.98513559999999,
      40.7676919
    ],
    "street": "West   57 Street",
    "zipcode": "10019",
    "building": "351"
  },
  "borough": "Manhattan",
  "cuisine": "Irish",
  "restaurant_id": "30191841"
}

SELECT JSON_PRETTY(doc) FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

JSON_PRETTY(doc): {

"_id": "564b3259666906a86ea90a99",

"name": "Dj Reynolds Pub And Restaurant",

"grades": [

{

"date": {

"$date": 1409961600000

"grade": "A",

"score": 2

{

"date": {

"$date": 1374451200000

"grade": "A",

"score": 11

{

"date": {

"$date": 1343692800000

"grade": "A",

"score": 12

{

"date": {

"$date": 1325116800000

"grade": "A",

"score": 12

}

"address": {

"coord": [

-73.98513559999999,

40.7676919

"street": "West 57 Street",

"zipcode": "10019",

"building": "351"

"borough": "Manhattan",

"cuisine": "Irish",

"restaurant_id": "30191841"

}

JSON_STORAGE_SIZE

Renvoie le nombre d’octets utilisés pour stocker la représentation binaire d’un document JSON avec JSON_STORAGE_SIZE.

SELECT max(JSON_STORAGE_SIZE(doc)) FROM restaurants;
+-----------------------------+
| max(JSON_STORAGE_SIZE(doc)) |
+-----------------------------+
|                         916 |
+-----------------------------+

SELECT avg(JSON_STORAGE_SIZE(doc)) FROM restaurants;
+-----------------------------+
| avg(JSON_STORAGE_SIZE(doc)) |
+-----------------------------+
|                    537.2814 |
+-----------------------------+

SELECT min(JSON_STORAGE_SIZE(doc)) FROM restaurants;
+-----------------------------+
| min(JSON_STORAGE_SIZE(doc)) |
+-----------------------------+
|                         255 |
+-----------------------------+

SELECT max(JSON_STORAGE_SIZE(doc)) FROM restaurants;

+-----------------------------+

| max(JSON_STORAGE_SIZE(doc)) |

+-----------------------------+

| 916 |

+-----------------------------+

SELECT avg(JSON_STORAGE_SIZE(doc)) FROM restaurants;

+-----------------------------+

| avg(JSON_STORAGE_SIZE(doc)) |

+-----------------------------+

| 537.2814 |

+-----------------------------+

SELECT min(JSON_STORAGE_SIZE(doc)) FROM restaurants;

+-----------------------------+

| min(JSON_STORAGE_SIZE(doc)) |

+-----------------------------+

| 255 |

+-----------------------------+

Dans cette collection, le document le plus lourd fait 916 octets, le plus léger 255 et la taille moyenne de tout les documents est 537,2814.

Note: C’est l’espace utilisé pour stocker le document JSON tel qu’il a été inséré dans la colonne, avant toute mise à jour partielle qui aurait pu être effectuée par la suite.

Fonctions qui recherchent des valeurs JSON

JSON_EXTRACT (->) / JSON_UNQUOTE / ->> operator

JSON_EXTRACT (or ->) retourne des données d’un document JSON.

JSON_UNQUOTE supprime les guillemets des données JSON et renvoie le résultat sous la forme d’une chaîne de caractères utf8mb4.

->> l’opérateur JSON « unquote extract » qui est un raccourci pour JSON_UNQUOTE(JSON_EXTRACT())

SELECT JSON_EXTRACT(doc, "$.cuisine") FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
JSON_EXTRACT(doc, "$.cuisine"): "Irish"


SELECT doc->"$.cuisine" FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
doc->"$.cuisine": "Irish"

SELECT JSON_EXTRACT(doc, "$.cuisine") FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

JSON_EXTRACT(doc, "$.cuisine"): "Irish"

SELECT doc->"$.cuisine" FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

doc->"$.cuisine": "Irish"

Les deux requêtes ci-dessus sont similaires.

Pour avoir le même résultat sans les guillemets utilise ->> ou JSON_UNQUOTE(JSON_EXTRACT()) :

SELECT JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")) FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")): Irish


SELECT doc->>"$.cuisine" FROM restaurants LIMIT 1\G
doc->>"$.cuisine": Irish

SELECT JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")) FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")): Irish

SELECT doc->>"$.cuisine" FROM restaurants LIMIT 1\G

doc->>"$.cuisine": Irish

Les deux requêtes ci-dessus sont similaires.

JSON_CONTAINS

Recherche si la valeur de la clé correspond à une valeur spécifiée avec JSON_CONTAINS.

SELECT count(*) 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');
+----------+
| count(*) |
+----------+
|       24 |
+----------+


SELECT doc->>"$.name" 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');
+-----------------------------------------------+
| doc->>"$.name"                                |
+-----------------------------------------------+
| Belvedere Restaurant                          |
| Chez Macoule Restaurant                       |
| Paradise Venus Restaurant                     |
| Heavenly Fritaille Restaurant                 |
| Yolie'S Bar & Restaurant                      |
| Yo-Yo Fritaille                               |
| Kal Bakery & Restaurant                       |
| Bon Appetit Restaurant                        |
| Katou Fin Restaurant                          |
| Alhpa Restaurant                              |
| Lakay Buffet Restaurant                       |
| La Tranquilite Restaurant                     |
| La Caye Restaurant                            |
| Nous Les Amis Restaurant & Bakery             |
| Yoyo Fritaille                                |
| Fresh Crown Restaurant                        |
| Tonel Restaurant & Lounge                     |
| Grace Devine Pastry And Restaurant Restaurant |
| Viva Bubble Tea                               |
| Cafe Creole Restaurant N Bakery               |
| Delly'S Place Restaurant & Fritaille          |
| Creole Plate                                  |
| Chez Nous Restaurant & Fritaille              |
| Combite Creole                                |
+-----------------------------------------------+

SELECT count(*)

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');

+----------+

| count(*) |

+----------+

| 24 |

+----------+

SELECT doc->>"$.name"

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');

+-----------------------------------------------+

| doc->>"$.name" |

+-----------------------------------------------+

| Belvedere Restaurant |

| Chez Macoule Restaurant |

| Paradise Venus Restaurant |

| Heavenly Fritaille Restaurant |

| Yolie'S Bar & Restaurant |

| Yo-Yo Fritaille |

| Kal Bakery & Restaurant |

| Bon Appetit Restaurant |

| Katou Fin Restaurant |

| Alhpa Restaurant |

| Lakay Buffet Restaurant |

| La Tranquilite Restaurant |

| La Caye Restaurant |

| Nous Les Amis Restaurant & Bakery |

| Yoyo Fritaille |

| Fresh Crown Restaurant |

| Tonel Restaurant & Lounge |

| Grace Devine Pastry And Restaurant Restaurant |

| Viva Bubble Tea |

| Cafe Creole Restaurant N Bakery |

| Delly'S Place Restaurant & Fritaille |

| Creole Plate |

| Chez Nous Restaurant & Fritaille |

| Combite Creole |

+-----------------------------------------------+

JSON_CONTAINS_PATH

Indique si un document JSON contient des données dans l’un ou les chemins spécifiés avec JSON_CONTAINS_PATH.

Pour tester cette fonction, j’insère un document factice dans la collection restaurants :

INSERT INTO restaurants (doc) VALUES ('{"_id": "1234", "name": "Daz Restaurant", "cuisine": "West Indian", "restaurant_id": "4321"}');

1	INSERT INTO restaurants (doc) VALUES ('{"_id": "1234", "name": "Daz Restaurant", "cuisine": "West Indian", "restaurant_id": "4321"}');

Combien y a t’il de documents sans note (grades) ?

SELECT count(*), JSON_CONTAINS_PATH(doc, 'one', '$.grades') cp 
FROM restaurants 
GROUP BY cp;
+----------+------+
| count(*) | cp   |
+----------+------+
|        1 |    0 |
|    25359 |    1 |
+----------+------+

SELECT count(*), JSON_CONTAINS_PATH(doc, 'one', '$.grades') cp

FROM restaurants

GROUP BY cp;

+----------+------+

| count(*) | cp |

+----------+------+

| 1 | 0 |

| 25359 | 1 |

+----------+------+

Un seul ! Tu peux alors facilement vérifier la structure de ce document :

SELECT JSON_PRETTY(doc) 
FROM restaurants 
WHERE JSON_CONTAINS_PATH(doc, 'one', '$.grades') = 0\G
*************************** 1. row ***************************
JSON_PRETTY(doc): {
  "_id": "1234",
  "name": "Daz Restaurant",
  "cuisine": "West Indian",
  "restaurant_id": "4321"
}

SELECT JSON_PRETTY(doc)

FROM restaurants

WHERE JSON_CONTAINS_PATH(doc, 'one', '$.grades') = 0\G

*************************** 1. row ***************************

JSON_PRETTY(doc): {

"_id": "1234",

"name": "Daz Restaurant",

"cuisine": "West Indian",

"restaurant_id": "4321"

}

Un pont entre ces deux modèles

Pour paraphraser David Stokes (MySQL Community Manager) dans son livre MySQL and JSON – A practical Programming Guide.

The advantages of traditional relational data and schemaless data are both large. But in some cases, data in a schema needs to be schemaless, or schemaless-data needs to be in a schema.

Faire de tels transformations avec MySQL est extrêmement aisé !

Relationnel vers JSON

JSON_OBJECT

Évalue une liste de paires clé/valeur et renvoie un objet JSON contenant ces paires avec JSON_OBJECT.

Une requête SQL traditionnelle avec un jeu de résultats relationnel. En d’autres termes, le document JSON génère des données non-JSON :

SELECT doc->>"$.name" 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine') 
LIMIT 2;
+-------------------------+
| doc->>"$.name"          |
+-------------------------+
| Belvedere Restaurant    |
| Chez Macoule Restaurant |
+-------------------------+

SELECT doc->>"$.name"

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine')

LIMIT 2;

+-------------------------+

| doc->>"$.name" |

+-------------------------+

| Belvedere Restaurant |

| Chez Macoule Restaurant |

+-------------------------+

Ce jeu de résultats peut être converti au format JSON, plus précisément en un objet JSON :

SELECT JSON_OBJECT("Name", doc->>"$.name") 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine') 
LIMIT 2;
+-------------------------------------+
| JSON_OBJECT("Name", doc->>"$.name") |
+-------------------------------------+
| {"Name": "Belvedere Restaurant"}    |
| {"Name": "Chez Macoule Restaurant"} |
+-------------------------------------+

SELECT JSON_OBJECT("Name", doc->>"$.name")

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine')

LIMIT 2;

+-------------------------------------+

| JSON_OBJECT("Name", doc->>"$.name") |

+-------------------------------------+

| {"Name": "Belvedere Restaurant"} |

| {"Name": "Chez Macoule Restaurant"} |

+-------------------------------------+

Autre exemple :

SELECT Name, Population 
FROM City 
WHERE CountryCode='fra' 
ORDER BY Population DESC 
LIMIT 5;
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Paris     |    2125246 |
| Marseille |     798430 |
| Lyon      |     445452 |
| Toulouse  |     390350 |
| Nice      |     342738 |
+-----------+------------+


SELECT JSON_OBJECT("CityName",Name, "CityPop", Population) 
FROM City 
WHERE CountryCode='fra' 
ORDER BY Population DESC 
LIMIT 5;
+-----------------------------------------------------+
| JSON_OBJECT("CityName",Name, "CityPop", Population) |
+-----------------------------------------------------+
| {"CityPop": 2125246, "CityName": "Paris"}           |
| {"CityPop": 798430, "CityName": "Marseille"}        |
| {"CityPop": 445452, "CityName": "Lyon"}             |
| {"CityPop": 390350, "CityName": "Toulouse"}         |
| {"CityPop": 342738, "CityName": "Nice"}             |
+-----------------------------------------------------+

SELECT Name, Population

FROM City

WHERE CountryCode='fra'

ORDER BY Population DESC

LIMIT 5;

+-----------+------------+

| Name | Population |

+-----------+------------+

| Paris | 2125246 |

| Marseille | 798430 |

| Lyon | 445452 |

| Toulouse | 390350 |

| Nice | 342738 |

+-----------+------------+

SELECT JSON_OBJECT("CityName",Name, "CityPop", Population)

FROM City

WHERE CountryCode='fra'

ORDER BY Population DESC

LIMIT 5;

+-----------------------------------------------------+

| JSON_OBJECT("CityName",Name, "CityPop", Population) |

+-----------------------------------------------------+

| {"CityPop": 2125246, "CityName": "Paris"} |

| {"CityPop": 798430, "CityName": "Marseille"} |

| {"CityPop": 445452, "CityName": "Lyon"} |

| {"CityPop": 390350, "CityName": "Toulouse"} |

| {"CityPop": 342738, "CityName": "Nice"} |

+-----------------------------------------------------+

JSON_OBJECTAGG

Prend deux noms de colonnes ou expressions et renvoie un objet JSON contenant des paires clé/valeur avec JSON_OBJECTAGG.

Agréger des colonnes est très utile en SQL.

SELECT JSON_OBJECTAGG(Name, CountryCode) 
FROM City  
GROUP BY id 
ORDER BY RAND() 
LIMIT 5;
+-----------------------------------+
| JSON_OBJECTAGG(Name, CountryCode) |
+-----------------------------------+
| {"Reno": "USA"}                   |
| {"Hanam": "KOR"}                  |
| {"Laizhou": "CHN"}                |
| {"Yogyakarta": "IDN"}             |
| {"Tantoyuca": "MEX"}              |
+-----------------------------------+

SELECT JSON_OBJECTAGG(Name, CountryCode)

FROM City

GROUP BY id

ORDER BY RAND()

LIMIT 5;

+-----------------------------------+

| JSON_OBJECTAGG(Name, CountryCode) |

+-----------------------------------+

| {"Reno": "USA"} |

| {"Hanam": "KOR"} |

| {"Laizhou": "CHN"} |

| {"Yogyakarta": "IDN"} |

| {"Tantoyuca": "MEX"} |

+-----------------------------------+

Note:
- De manière générale, c’est plutôt une très mauvaise idée d’utiliser ORDER BY RAND() pour générer des enregistrements aléatoires, car ce n’est pas scalable (en clair, problèmes de performance avec de grosses tables).
- Il vaut mieux gérer l’aléatoire au niveau de l’application ou alors pré-calculer les valeurs aléatoires et les stocker dans la base.

JSON_ARRAY

Evalue une liste de valeurs et retourne un tableau JSON contenant ces valeurs avec JSON_ARRAY.

L’exemple qui suit est une requête Common Table Expression récursive aka recursive CTE (ou encore requête WITH) qui permet de parcourir une hiérarchie sans connaitre sa profondeur :

WITH RECURSIVE emp_ext (id, name, path) AS ( 
    SELECT id, name, CAST(id AS CHAR(200)) 
    FROM employees 
    WHERE manager_id IS NULL 
    UNION ALL 
    SELECT s.id, s.name, CONCAT(m.path, ",", s.id) 
    FROM emp_ext m 
        JOIN employees s ON m.id=s.manager_id 
) 
SELECT id,name, path FROM emp_ext ORDER BY path;
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

WITH RECURSIVE emp_ext (id, name, path) AS (

SELECT id, name, CAST(id AS CHAR(200))

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT s.id, s.name, CONCAT(m.path, ",", s.id)

FROM emp_ext m

JOIN employees s ON m.id=s.manager_id

)

SELECT id,name, path FROM emp_ext ORDER BY path;

+------+---------+-----------------+

| id | name | path |

+------+---------+-----------------+

| 333 | Yasmina | 333 |

| 198 | John | 333,198 |

| 29 | Pedro | 333,198,29 |

| 4610 | Sarah | 333,198,29,4610 |

| 72 | Pierre | 333,198,29,72 |

| 692 | Tarek | 333,692 |

| 123 | Adil | 333,692,123 |

+------+---------+-----------------+

Générer du JSON avec JSON_OBJECT & JSON_ARRAY :

WITH RECURSIVE emp_ext (id, name, path) AS ( 
    SELECT id, name, CAST(id AS CHAR(200)) 
    FROM employees 
    WHERE manager_id IS NULL 
    UNION ALL 
    SELECT s.id, s.name, CONCAT(m.path, ",", s.id) 
    FROM emp_ext m 
        JOIN employees s ON m.id=s.manager_id 
) 
SELECT JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path)) 
FROM emp_ext 
ORDER BY path;
+-------------------------------------------------------------+
| JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path)) |
+-------------------------------------------------------------+
| {"ID": 333, "Name": "Yasmina", "Path": ["333"]}             |
| {"ID": 198, "Name": "John", "Path": ["333,198"]}            |
| {"ID": 29, "Name": "Pedro", "Path": ["333,198,29"]}         |
| {"ID": 4610, "Name": "Sarah", "Path": ["333,198,29,4610"]}  |
| {"ID": 72, "Name": "Pierre", "Path": ["333,198,29,72"]}     |
| {"ID": 692, "Name": "Tarek", "Path": ["333,692"]}           |
| {"ID": 123, "Name": "Adil", "Path": ["333,692,123"]}        |
+-------------------------------------------------------------+

WITH RECURSIVE emp_ext (id, name, path) AS (

SELECT id, name, CAST(id AS CHAR(200))

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT s.id, s.name, CONCAT(m.path, ",", s.id)

FROM emp_ext m

JOIN employees s ON m.id=s.manager_id

)

SELECT JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path))

FROM emp_ext

ORDER BY path;

+-------------------------------------------------------------+

| JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path)) |

+-------------------------------------------------------------+

| {"ID": 333, "Name": "Yasmina", "Path": ["333"]} |

| {"ID": 198, "Name": "John", "Path": ["333,198"]} |

| {"ID": 29, "Name": "Pedro", "Path": ["333,198,29"]} |

| {"ID": 4610, "Name": "Sarah", "Path": ["333,198,29,4610"]} |

| {"ID": 72, "Name": "Pierre", "Path": ["333,198,29,72"]} |

| {"ID": 692, "Name": "Tarek", "Path": ["333,692"]} |

| {"ID": 123, "Name": "Adil", "Path": ["333,692,123"]} |

+-------------------------------------------------------------+

JSON_ARRAYAGG

Agréger un ensemble de résultats en un seul tableau JSON dont les éléments sont constitués des lignes avec JSON_ARRAYAGG.

A l’aide de cette autre fonction JSON d’agrégation voici différentes requêtes SQL qui génèrent du JSON :

SELECT CountryCode, JSON_ARRAYAGG(City.Name) 
FROM City 
    JOIN Country ON (City.CountryCode=Country.Code) 
WHERE Continent='Europe' 
GROUP BY 1 
LIMIT 5;
+-------------+--------------------------------------------------------------------------------------------------------------+
| CountryCode | JSON_ARRAYAGG(City.Name)                                                                                     |
+-------------+--------------------------------------------------------------------------------------------------------------+
| ALB         | ["Tirana"]                                                                                                   |
| AND         | ["Andorra la Vella"]                                                                                         |
| AUT         | ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"]                                              |
| BEL         | ["Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons", "Bruxelles [Brussel]"]  |
| BGR         | ["Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric", "Ruse"]      |
+-------------+--------------------------------------------------------------------------------------------------------------+

SELECT CountryCode, JSON_ARRAYAGG(City.Name)

FROM City

JOIN Country ON (City.CountryCode=Country.Code)

WHERE Continent='Europe'

GROUP BY 1

LIMIT 5;

+-------------+--------------------------------------------------------------------------------------------------------------+

| CountryCode | JSON_ARRAYAGG(City.Name) |

+-------------+--------------------------------------------------------------------------------------------------------------+

| ALB | ["Tirana"] |

| AND | ["Andorra la Vella"] |

| AUT | ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"] |

| BEL | ["Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons", "Bruxelles [Brussel]"] |

| BGR | ["Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric", "Ruse"] |

+-------------+--------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("CountryCode",CountryCode), JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name)) 
FROM City 
    JOIN Country ON (City.CountryCode=Country.Code) 
WHERE Continent='Europe' 
GROUP BY 1 
LIMIT 5;
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECT("CountryCode",CountryCode) | JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name))                                                                           |
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
| {"CountryCode": "ALB"}                 | {"CityName": ["Tirana"]}                                                                                                   |
| {"CountryCode": "AND"}                 | {"CityName": ["Andorra la Vella"]}                                                                                         |
| {"CountryCode": "AUT"}                 | {"CityName": ["Wien", "Graz", "Linz", "Salzburg", "Innsbruck", "Klagenfurt"]}                                              |
| {"CountryCode": "BEL"}                 | {"CityName": ["Schaerbeek", "Mons", "Namur", "Brugge", "Liège", "Antwerpen", "Charleroi", "Gent", "Bruxelles [Brussel]"]}  |
| {"CountryCode": "BGR"}                 | {"CityName": ["Burgas", "Šumen", "Dobric", "Sliven", "Pleven", "Stara Zagora", "Ruse", "Varna", "Plovdiv", "Sofija"]}      |
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("CountryCode",CountryCode), JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name))

FROM City

JOIN Country ON (City.CountryCode=Country.Code)

WHERE Continent='Europe'

GROUP BY 1

LIMIT 5;

+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

| JSON_OBJECT("CountryCode",CountryCode) | JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name)) |

+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

| {"CountryCode": "ALB"} | {"CityName": ["Tirana"]} |

| {"CountryCode": "AND"} | {"CityName": ["Andorra la Vella"]} |

| {"CountryCode": "AUT"} | {"CityName": ["Wien", "Graz", "Linz", "Salzburg", "Innsbruck", "Klagenfurt"]} |

| {"CountryCode": "BEL"} | {"CityName": ["Schaerbeek", "Mons", "Namur", "Brugge", "Liège", "Antwerpen", "Charleroi", "Gent", "Bruxelles [Brussel]"]} |

| {"CountryCode": "BGR"} | {"CityName": ["Burgas", "Šumen", "Dobric", "Sliven", "Pleven", "Stara Zagora", "Ruse", "Varna", "Plovdiv", "Sofija"]} |

+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name)) 
FROM City 
    JOIN Country ON (City.CountryCode=Country.Code) 
WHERE Continent='Europe' 
GROUP BY CountryCode 
LIMIT 5;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name))                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| {"Code": "ALB", "CityName": ["Tirana"]}                                                                                                   |
| {"Code": "AND", "CityName": ["Andorra la Vella"]}                                                                                         |
| {"Code": "AUT", "CityName": ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"]}                                              |
| {"Code": "BEL", "CityName": ["Bruxelles [Brussel]", "Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons"]}  |
| {"Code": "BGR", "CityName": ["Ruse", "Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric"]}      |
+-------------------------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name))

FROM City

JOIN Country ON (City.CountryCode=Country.Code)

WHERE Continent='Europe'

GROUP BY CountryCode

LIMIT 5;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name)) |

+-------------------------------------------------------------------------------------------------------------------------------------------+

| {"Code": "ALB", "CityName": ["Tirana"]} |

| {"Code": "AND", "CityName": ["Andorra la Vella"]} |

| {"Code": "AUT", "CityName": ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"]} |

| {"Code": "BEL", "CityName": ["Bruxelles [Brussel]", "Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons"]} |

| {"Code": "BGR", "CityName": ["Ruse", "Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric"]} |

+-------------------------------------------------------------------------------------------------------------------------------------------+

JSON vers Relationnel

Maintenant le processus inverse. Transformation des données JSON en données relationnelles.

JSON_TABLE

Extrait les données d’un document JSON et renvoies-les en tant que table relationnelle avec JSON_TABLE.

Conseil amical, je te recommande fortement de passer du temps dans la documentation de cette puissante et complète fonction, qui va te permettre de mapper des données JSON dans une table relationnelle temporaire, puis d’interroger cette dernière.

Assez de blabla, voici quelques exemples :

SELECT GNP 
FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP")) AS jst 
WHERE _id='FRA';
+---------+
| GNP     |
+---------+
| 1424285 |
+---------+

SELECT GNP

FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP")) AS jst

WHERE _id='FRA';

+---------+

| GNP |

+---------+

| 1424285 |

+---------+

SELECT GNP, Name, LifeExpectancy 
FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP", Name char(255) PATH "$.Name", LifeExpectancy int PATH "$.demographics.LifeExpectancy")) AS jst 
WHERE _id IN ('FRA', 'USA');
+---------+---------------+----------------+
| GNP     | Name          | LifeExpectancy |
+---------+---------------+----------------+
| 1424285 | France        |             79 |
| 8510700 | United States |             77 |
+---------+---------------+----------------+

SELECT GNP, Name, LifeExpectancy

FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP", Name char(255) PATH "$.Name", LifeExpectancy int PATH "$.demographics.LifeExpectancy")) AS jst

WHERE _id IN ('FRA', 'USA');

+---------+---------------+----------------+

| GNP | Name | LifeExpectancy |

+---------+---------------+----------------+

| 1424285 | France | 79 |

| 8510700 | United States | 77 |

+---------+---------------+----------------+

SELECT name AS "Creole Cuisine" 
FROM restaurant.restaurants, JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine")) AS jst 
WHERE cuisine='Creole';
+-----------------------------------------------+
| Creole Cuisine                                |
+-----------------------------------------------+
| Belvedere Restaurant                          |
| Chez Macoule Restaurant                       |
| Paradise Venus Restaurant                     |
| Heavenly Fritaille Restaurant                 |
| Yolie'S Bar & Restaurant                      |
| Yo-Yo Fritaille                               |
| Kal Bakery & Restaurant                       |
| Bon Appetit Restaurant                        |
| Katou Fin Restaurant                          |
| Alhpa Restaurant                              |
| Lakay Buffet Restaurant                       |
| La Tranquilite Restaurant                     |
| La Caye Restaurant                            |
| Nous Les Amis Restaurant & Bakery             |
| Yoyo Fritaille                                |
| Fresh Crown Restaurant                        |
| Tonel Restaurant & Lounge                     |
| Grace Devine Pastry And Restaurant Restaurant |
| Viva Bubble Tea                               |
| Cafe Creole Restaurant N Bakery               |
| Delly'S Place Restaurant & Fritaille          |
| Creole Plate                                  |
| Chez Nous Restaurant & Fritaille              |
| Combite Creole                                |
+-----------------------------------------------+

SELECT name AS "Creole Cuisine"

FROM restaurant.restaurants, JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine")) AS jst

WHERE cuisine='Creole';

+-----------------------------------------------+

| Creole Cuisine |

+-----------------------------------------------+

| Belvedere Restaurant |

| Chez Macoule Restaurant |

| Paradise Venus Restaurant |

| Heavenly Fritaille Restaurant |

| Yolie'S Bar & Restaurant |

| Yo-Yo Fritaille |

| Kal Bakery & Restaurant |

| Bon Appetit Restaurant |

| Katou Fin Restaurant |

| Alhpa Restaurant |

| Lakay Buffet Restaurant |

| La Tranquilite Restaurant |

| La Caye Restaurant |

| Nous Les Amis Restaurant & Bakery |

| Yoyo Fritaille |

| Fresh Crown Restaurant |

| Tonel Restaurant & Lounge |

| Grace Devine Pastry And Restaurant Restaurant |

| Viva Bubble Tea |

| Cafe Creole Restaurant N Bakery |

| Delly'S Place Restaurant & Fritaille |

| Creole Plate |

| Chez Nous Restaurant & Fritaille |

| Combite Creole |

+-----------------------------------------------+

JSON_TABLE – Nested Data

Parcours le chemin du document JSON et récupère les données imbriquées.

Par exemple, extraire toutes les notes (grades) des restaurants qui font de la cuisine Hawaiian :

SELECT name, cuisine, gradeID, grade 
FROM restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", NESTED PATH "$.grades[*]" COLUMNS (gradeID FOR ORDINALITY, grade char(20) PATH "$.grade"))) AS jst 
WHERE cuisine='Hawaiian';
+------------------+----------+---------+-------+
| name             | cuisine  | gradeID | grade |
+------------------+----------+---------+-------+
| Makana           | Hawaiian |       1 | C     |
| Makana           | Hawaiian |       2 | C     |
| Makana           | Hawaiian |       3 | A     |
| Makana           | Hawaiian |       4 | C     |
| Makana           | Hawaiian |       5 | A     |
| General Assembly | Hawaiian |       1 | A     |
| General Assembly | Hawaiian |       2 | A     |
| General Assembly | Hawaiian |       3 | A     |
| General Assembly | Hawaiian |       4 | A     |
| Onomea           | Hawaiian |       1 | A     |
| Onomea           | Hawaiian |       2 | A     |
+------------------+----------+---------+-------+

SELECT name, cuisine, gradeID, grade

FROM restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", NESTED PATH "$.grades[*]" COLUMNS (gradeID FOR ORDINALITY, grade char(20) PATH "$.grade"))) AS jst

WHERE cuisine='Hawaiian';

+------------------+----------+---------+-------+

+------------------+----------+---------+-------+

| Makana | Hawaiian | 1 | C |

| Makana | Hawaiian | 2 | C |

| Makana | Hawaiian | 3 | A |

| Makana | Hawaiian | 4 | C |

| Makana | Hawaiian | 5 | A |

| General Assembly | Hawaiian | 1 | A |

| General Assembly | Hawaiian | 2 | A |

| General Assembly | Hawaiian | 3 | A |

| General Assembly | Hawaiian | 4 | A |

| Onomea | Hawaiian | 1 | A |

| Onomea | Hawaiian | 2 | A |

+------------------+----------+---------+-------+

JSON_TABLE – Missing Data

Précise quelle action à accomplir en cas de données manquantes.

Comportement par défaut :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough")) AS jst  
LIMIT 2;
+--------------------------------+-------------+-----------+
| name                           | cuisine     | borough   |
+--------------------------------+-------------+-----------+
| Daz Restaurant                 | West Indian | NULL      |
| Dj Reynolds Pub And Restaurant | Irish       | Manhattan |
+--------------------------------+-------------+-----------+

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough")) AS jst

LIMIT 2;

+--------------------------------+-------------+-----------+

| name | cuisine | borough |

+--------------------------------+-------------+-----------+

| Daz Restaurant | West Indian | NULL |

| Dj Reynolds Pub And Restaurant | Irish | Manhattan |

+--------------------------------+-------------+-----------+

Renforce le comportement par défaut :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" NULL ON EMPTY)) AS jst 
LIMIT 2;
+--------------------------------+-------------+-----------+
| name                           | cuisine     | borough   |
+--------------------------------+-------------+-----------+
| Daz Restaurant                 | West Indian | NULL      |
| Dj Reynolds Pub And Restaurant | Irish       | Manhattan |
+--------------------------------+-------------+-----------+

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" NULL ON EMPTY)) AS jst

LIMIT 2;

+--------------------------------+-------------+-----------+

| name | cuisine | borough |

+--------------------------------+-------------+-----------+

| Daz Restaurant | West Indian | NULL |

| Dj Reynolds Pub And Restaurant | Irish | Manhattan |

+--------------------------------+-------------+-----------+

Déclenche une erreur :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" ERROR ON EMPTY)) AS jst 
LIMIT 2;
ERROR 3665 (22035): Missing value for JSON_TABLE column 'borough'

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" ERROR ON EMPTY)) AS jst

LIMIT 2;

ERROR 3665 (22035): Missing value for JSON_TABLE column 'borough'

Mettre une valeur par défaut :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" DEFAULT '"<UNKNOW>"' ON EMPTY)) AS jst 
LIMIT 2;
+--------------------------------+-------------+-----------+
| name                           | cuisine     | borough   |
+--------------------------------+-------------+-----------+
| Daz Restaurant                 | West Indian | <UNKNOW>  |
| Dj Reynolds Pub And Restaurant | Irish       | Manhattan |
+--------------------------------+-------------+-----------+

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" DEFAULT '"<UNKNOW>"' ON EMPTY)) AS jst

LIMIT 2;

+--------------------------------+-------------+-----------+

| name | cuisine | borough |

+--------------------------------+-------------+-----------+

| Daz Restaurant | West Indian | <UNKNOW> |

| Dj Reynolds Pub And Restaurant | Irish | Manhattan |

+--------------------------------+-------------+-----------+

Le(s) mot(s) de la fin

MySQL 8 et 5.7 possèdent un riche jeu de fonctions JSON. J’en ai présenté quelques unes mais rassures toi il t’en reste encore pas mal à découvrir, notamment pour créer, modifier,indexer… les documents.

A noter également que si le modèle relationnel ne convient pas à ton workload, MySQL 8 Document Store t’offre la possibilité de gérer tes collections à l’aide d’une API CRUD NoSQL. J’en parlerai plus en détail dans un prochain article.

Pour patienter je t’invite à lire : Top 10 reasons for NoSQL with MySQL.

Pour aller plus loin

Documentation

Articles

Autres ressources

Tu trouveras les bases de données utilisées dans cet article ici.
Le dump de la collection Restaurants ici.
Quelques livres qui peuvent être utile : ici.

Thanks for using MySQL!

Filed under:
json, MySQL, NoSQL by Olivier DASINI

Nouveaux livres MySQL

juillet 12, 2018

Tags: cluster, Document Store, JSON, livre, MySQL Cluster, NDB

Jadis, lorsque l’on voulait approfondir un sujet technique, il nous fallait à tout prix LE livre (celui là à littéralement changé ma vie, et ce n’est pas le seul: celui là, celui là, …). De nos jours, des milliards de ressources sont disponibles gratuitement sur internet, mais il faut bien l’avouer, la qualité est en générale plutôt mauvaise 🙁

Heureusement, il existe encore des talents qui donnent de leur temps pour écrire de nouveaux livres qui auront peut-être à leur tour un impact sur ces millions de femmes et d’hommes qui construisent les applications d’aujourd’hui et de demain en utilisant la base de données la plus populaire au monde, MySQL.

Permet moi de te présenter 3 nouveaux livres MySQL :

MySQL and JSON: A Practical Programming Guide

MySQL and JSON: A Practical Programming Guide
Discover how to use JavaScript Object Notation (JSON) with MySQL

Auteur : David Stokes (MySQL Community Manager)

ISBN-13 : 978-1260135442
ISBN-10 : 1260135446

Éditeur : Oracle Press

https://www.mhprofessional.com/9781260135442-usa-mysql-and-json-a-practical-programming-guide-group

Introducing the MySQL 8 Document Store

Introducing the MySQL 8 Document Store
Building schemaless database solutions

Auteur : Dr. Charles Bell (Development Manager)

eBook ISBN : 978-1-4842-2725-1
Softcover ISBN : 978-1-4842-2724-4
DOI : 10.1007/978-1-4842-2725-1

Éditeur : Apress

https://www.apress.com/fr/book/9781484227244

Pro MySQL NDB Cluster

Pro MySQL NDB Cluster
Master the MySQL Cluster Lifecycle

Auteurs : Jesper Wisborg Krogh(MySQL Senior Principal Technical Support Engineer), Mikiya Okuno(MySQL Technical Analyst)

eBook ISBN : 978-1-4842-2982-8
Softcover ISBN : 978-1-4842-2981-1
DOI : 10.1007/978-1-4842-2982-8

Éditeur : Apress

https://www.apress.com/gp/book/9781484229811

Je terminerai cet article par une citation de David Augustin de Brueys :

« Les livres sont comme des amis véritables qu’on retrouve toujours dans les bonnes occasions. »

MySQL Books

Mise à jour le 2018/08/06

Je recommande également la lecture de l’article de mon collègue Jesper‘s : MySQL Books.

Thanks for using (and reading) MySQL!

Filed under:
Divers, json, Livre, MySQL by Olivier DASINI

MySQL Document Store à Oracle Code Paris

juin 29, 2018

Tags: conférences, Document Store, open source, oracle

mise à jour le 13/07/2018: Ajout de la présentation

Oracle Code Paris 2018

Ce mardi 3 Juillet 2018, je serai présent à l’événement Oracle Code Paris au New Cap Event Center (3 Quai de Grenelle, 75015).

Je vais même faire plus, car je vais présenter MySQL 8.0 Document Store à 11:15 dans le Salon 8-9.

MySQL Document Store permet aux développeurs NoSQL de manipuler des documents JSON dans MySQL avec une API CRUD, c’est à dire sans avoir recours au SQL. En d’autres termes, SQL est devenu optionnel, dans MySQL !

Intriguant non ? 🙂 Pour en savoir plus, c’est ce mardi 3 juillet…

Je vous invite donc à venir nous rejoindre pour cet événement gratuit, permettant aux développeurs et autres d’échanger avec divers experts techniques et de découvrir les dernières technologies, pratiques et tendances du marché.

Présentation:

Infos & ~~inscriptions~~: http://bit.ly/OCodeParis

Filed under:
Conférence, Document Store, MySQL, Présentation by Olivier DASINI

MySQL Security – Série d’articles sur la sécurité d’une installation MySQL

avril 16, 2018

Tags: audit, Firewall, GDPR, RGPD, sécurité, security, TDE

J’ai publié, sur la version anglaise du site, une série modestement intitulée « MySQL Security » composée de 7 articles, dont le sujet principal est… la sécurité 🙂

Pour être plus précis, n’étant pas un expert en sécurité, cette série ne traite pas de LA sécurité dans son ensemble. Au contraire, je me suis concentré sur quelques-un des plugins et autres fonctionnalités de MySQL à connaître pour augmenter la sécurité globale de ton système de données.

On a donc:

Password Validation Plugin : Renforcer la robustesse des mots de passe.
Password Management : Gérer la politique de renouvellement des mots de passe.
User Account Locking : Configurer une stratégie de verrouillage des comptes utilisateur.
The Connection-Control Plugins : Atténuer les effets d’une attaque par force brute.
Enterprise Audit : Audit de la base de données afin de détecter une mauvaise utilisation et/ou de se conformer à la réglementation.
Enterprise Transparent Data Encryption (TDE) : Chiffrement des données pour protéger la confidentialité de vos clients. Transparent pour l’application.
Enterprise Firewall : Pare-feu temps réel base de données, qui bloque les activités non autorisées.

De plus, pour approfondir le sujet je t’encourage vivement à parcourir les liens suivants:

Thanks for using MySQL!

Follow me on twitter

Filed under:
MySQL, Sécurité by Olivier DASINI

Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication

janvier 9, 2018

Tags: group replication, ha, high availability, mem, monitor, Monitoring, ProxySQL, tuto

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 :
MySQL ProxySQL

MySQL 5.7 Group Replication : la seule solution native HA de MySQL, qui permet notamment de faire du vrai multi-master (avec MySQL NDB Cluster mais ceci est hors sujet).
ProxySQL 1.4 : modestement le meilleur proxy pour MySQL.

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 5.7.20 est téléchargé et installé, sur 3 hosts.
ProxySQL 1.4.4 est téléchargé et installé sur 1 host (le HA de ProxySQL n’est pas traité dans cet article).
Un cluster MySQL Group Replication de 3 nœuds est configuré et fonctionnel.

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 :

node1>
-- MySQL Server version number
SELECT left(version(),6);
+-------------------+
| left(version(),6) |
+-------------------+
| 5.7.20            |
+-------------------+

-- MySQL Group Replication plugin details
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'group%'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.1
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.7
         PLUGIN_AUTHOR: ORACLE
    PLUGIN_DESCRIPTION: Group Replication (1.0.0)
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: FORCE_PLUS_PERMANENT

-- MySQL Group Replication member status
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c2039a6-f152-11e7-90da-0242ac16001e
 MEMBER_HOST: mysql_node3
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c2039a8-f152-11e7-9132-0242ac160014
 MEMBER_HOST: mysql_node2
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a
 MEMBER_HOST: mysql_node1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

node1>

-- MySQL Server version number

SELECT left(version(),6);

+-------------------+

| left(version(),6) |

+-------------------+

| 5.7.20 |

+-------------------+

-- MySQL Group Replication plugin details

SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'group%'\G

*************************** 1. row ***************************

PLUGIN_NAME: group_replication

PLUGIN_VERSION: 1.0

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: GROUP REPLICATION

PLUGIN_TYPE_VERSION: 1.1

PLUGIN_LIBRARY: group_replication.so

PLUGIN_LIBRARY_VERSION: 1.7

PLUGIN_AUTHOR: ORACLE

PLUGIN_DESCRIPTION: Group Replication (1.0.0)

PLUGIN_LICENSE: PROPRIETARY

LOAD_OPTION: FORCE_PLUS_PERMANENT

-- MySQL Group Replication member status

SELECT * FROM performance_schema.replication_group_members\G

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 3c2039a6-f152-11e7-90da-0242ac16001e

MEMBER_HOST: mysql_node3

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

*************************** 2. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 3c2039a8-f152-11e7-9132-0242ac160014

MEMBER_HOST: mysql_node2

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

*************************** 3. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a

MEMBER_HOST: mysql_node1

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

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

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).

node1>
-- Is single primary mode activated?
SHOW VARIABLES LIKE 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+

-- Who is the primary node?
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a
 MEMBER_HOST: mysql_node1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

node1>

-- Is single primary mode activated?

SHOW VARIABLES LIKE 'group_replication_single_primary_mode';

+---------------------------------------+-------+

| Variable_name | Value |

+---------------------------------------+-------+

| group_replication_single_primary_mode | ON |

+---------------------------------------+-------+

-- Who is the primary node?

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

*************************** 1. row ***************************

MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a

MEMBER_HOST: mysql_node1

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MySQL Enterprise Monitor nous montre (click to enlarge):

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

USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)

RETURNS INT

DETERMINISTIC

RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)

RETURNS INT

DETERMINISTIC

RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))

RETURNS TEXT(10000)

DETERMINISTIC

RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))

RETURNS INT

DETERMINISTIC

BEGIN

DECLARE result BIGINT DEFAULT 0;

DECLARE colon_pos INT;

DECLARE next_dash_pos INT;

DECLARE next_colon_pos INT;

DECLARE next_comma_pos INT;

SET gtid_set = GTID_NORMALIZE(gtid_set);

SET colon_pos = LOCATE2(':', gtid_set, 1);

WHILE colon_pos != LENGTH(gtid_set) + 1 DO

SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);

SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);

SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);

IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN

SET result = result +

SUBSTR(gtid_set, next_dash_pos + 1,

LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -

SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;

ELSE

SET result = result + 1;

END IF;

SET colon_pos = next_colon_pos;

END WHILE;

RETURN result;

END$$

CREATE FUNCTION gr_applier_queue_length()

RETURNS INT

DETERMINISTIC

BEGIN

RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT

Received_transaction_set FROM performance_schema.replication_connection_status

WHERE Channel_name = 'group_replication_applier' ), (SELECT

@@global.GTID_EXECUTED) )));

END$$

CREATE FUNCTION gr_member_in_primary_partition()

RETURNS VARCHAR(3)

DETERMINISTIC

BEGIN

RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM

performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=

((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),

'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN

performance_schema.replication_group_member_stats USING(member_id));

END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT

sys.gr_member_in_primary_partition() as viable_candidate,

IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM

performance_schema.global_variables WHERE variable_name IN ('read_only',

'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,

sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

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

# Loading extra functions and views to sys schema on the cluster (using a primary node)

$ mysql -u root -p -h mysql_node1 < ./addition_to_sys.sql

# Loading extra functions and views to sys schema on the cluster (using a primary node)

$ mysql -u root -p -h mysql_node1 < ./addition_to_sys.sql

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

node1> 
-- Status of the primary node
SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

node1>

-- Status of the primary node

SELECT * FROM sys.gr_member_routing_candidate_status;

+------------------+-----------+---------------------+----------------------+

+------------------+-----------+---------------------+----------------------+

| YES | NO | 0 | 0 |

+------------------+-----------+---------------------+----------------------+

node2> 
-- Status of a secondary node
SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

node2>

-- Status of a secondary node

SELECT * FROM sys.gr_member_routing_candidate_status;

+------------------+-----------+---------------------+----------------------+

+------------------+-----------+---------------------+----------------------+

| YES | YES | 0 | 0 |

+------------------+-----------+---------------------+----------------------+

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 :

node1>
-- Create ProxySQL monitoring user inside the cluster
CREATE USER proxysqlmonitor@'%' IDENTIFIED BY 'Very-S3cr3t';

GRANT SELECT ON sys.* TO proxysqlmonitor@'%';

node1>

-- Create ProxySQL monitoring user inside the cluster

CREATE USER proxysqlmonitor@'%' IDENTIFIED BY 'Very-S3cr3t';

GRANT SELECT ON sys.* TO proxysqlmonitor@'%';

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

$ proxysql --version
ProxySQL version 1.4.4-139-ga51b040, codename Truls

$ service proxysql status
ProxySQL is running (58).

$ proxysql --version

ProxySQL version 1.4.4-139-ga51b040, codename Truls

$ service proxysql status

ProxySQL is running (58).

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.

$ mysql -u admin -p -P 6032 --protocol=tcp main

1	$ mysql -u admin -p -P 6032 --protocol=tcp main

Configurer les serveurs

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

-- Add 3 nodes of the cluster into the mysql_servers table
proxy> 
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'mysql_node1', 3306), (2, 'mysql_node2', 3306), (2, 'mysql_node3', 3306);

-- Add 3 nodes of the cluster into the mysql_servers table

proxy>

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'mysql_node1', 3306), (2, 'mysql_node2', 3306), (2, 'mysql_node3', 3306);

proxy> 
select * from mysql_servers;
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | mysql_node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | mysql_node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | mysql_node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

proxy>

select * from mysql_servers;

+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 2 | mysql_node1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| 2 | mysql_node2 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| 2 | mysql_node3 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

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 :

proxy> 
SHOW CREATE TABLE main.mysql_group_replication_hostgroups\G
*************************** 1. row ***************************ajouter les nœuds du cluster
       table: mysql_group_replication_hostgroups
Create Table: CREATE TABLE mysql_group_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
    offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,
    max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
    comment VARCHAR,
    UNIQUE (reader_hostgroup),
    UNIQUE (offline_hostgroup),
    UNIQUE (backup_writer_hostgroup))

proxy>

SHOW CREATE TABLE main.mysql_group_replication_hostgroups\G

*************************** 1. row ***************************ajouter les nœuds du cluster

table: mysql_group_replication_hostgroups

Create Table: CREATE TABLE mysql_group_replication_hostgroups (

writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,

backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,

reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),

offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),

active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,

max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,

writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,

max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,

comment VARCHAR,

UNIQUE (reader_hostgroup),

UNIQUE (offline_hostgroup),

UNIQUE (backup_writer_hostgroup))

La meilleure description trouvée est disponible dans l’article de mon collègue @Lefred : MySQL 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 :

proxy> 
INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) VALUES (2,4,3,1,1,1,1,0);

-- Save & load new configuration
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;

proxy>

INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) VALUES (2,4,3,1,1,1,1,0);

-- Save & load new configuration

SAVE MYSQL SERVERS TO DISK;

LOAD MYSQL SERVERS TO RUNTIME;

proxy> 
select * from mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
       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
                comment: NULL

proxy>

select * from mysql_group_replication_hostgroups\G

*************************** 1. row ***************************

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

comment: NULL

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 :

-- Set user name & password for monitoring module
SET mysql-monitor_username='proxysqlmonitor';
SET mysql-monitor_password='Very-S3cr3t';

-- Save & load new configuration
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;

-- Set user name & password for monitoring module

SET mysql-monitor_username='proxysqlmonitor';

SET mysql-monitor_password='Very-S3cr3t';

-- Save & load new configuration

SAVE MYSQL VARIABLES TO DISK;

LOAD MYSQL VARIABLES TO RUNTIME;

proxy> 
select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | mysql_node1 | ONLINE |
| 2            | mysql_node3 | ONLINE |
| 2            | mysql_node2 | ONLINE |
+--------------+-------------+--------+

proxy>

select hostgroup_id, hostname, status from runtime_mysql_servers;

+--------------+-------------+--------+

| hostgroup_id | hostname | status |

+--------------+-------------+--------+

| 2 | mysql_node1 | ONLINE |

| 2 | mysql_node3 | ONLINE |

| 2 | mysql_node2 | ONLINE |

+--------------+-------------+--------+

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 :

proxy> 
INSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('app_user', 'app_pwd, 1, 2, 200);

-- Save & load new configuration
SAVE MYSQL USERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;

proxy>

INSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('app_user', 'app_pwd, 1, 2, 200);

-- Save & load new configuration

SAVE MYSQL USERS TO DISK;

LOAD MYSQL USERS TO RUNTIME;

proxy> 
select * from mysql_users;
*************************** 1. row ***************************
              username: app_user
              password: app_pwd
                active: 1
               use_ssl: 0
     default_hostgroup: 2
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 200

proxy>

select * from mysql_users;

*************************** 1. row ***************************

username: app_user

password: app_pwd

active: 1

use_ssl: 0

default_hostgroup: 2

default_schema: NULL

schema_locked: 0

transaction_persistent: 1

fast_forward: 0

backend: 1

frontend: 1

max_connections: 200

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 :

proxy> 
SET mysql-interfaces='0.0.0.0:3306;/tmp/proxysql.sock';

SAVE MYSQL VARIABLES TO DISK;

proxy>

SET mysql-interfaces='0.0.0.0:3306;/tmp/proxysql.sock';

SAVE MYSQL VARIABLES TO DISK;

proxy> 
SHOW VARIABLES LIKE 'mysql-interfaces'\G
*************************** 1. row ***************************
Variable_name: mysql-interfaces
        Value: 0.0.0.0:3306;/tmp/proxysql.sock

proxy>

SHOW VARIABLES LIKE 'mysql-interfaces'\G

*************************** 1. row ***************************

Variable_name: mysql-interfaces

Value: 0.0.0.0:3306;/tmp/proxysql.sock

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.

$ service proxysql restart
Shutting down ProxySQL: DONE!
Starting ProxySQL: DONE!

$ service proxysql restart

Shutting down ProxySQL: DONE!

Starting ProxySQL: DONE!

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 :

proxy> 
select * from mysql_server_group_replication_log order by time_start_us desc limit 3\G
*************************** 1. row ***************************
           hostname: mysql_node3
               port: 3306
      time_start_us: 1515079109822616
    success_time_us: 1782
   viable_candidate: YES
          read_only: YES
transactions_behind: 0
              error: NULL
*************************** 2. row ***************************
           hostname: mysql_node2
               port: 3306
      time_start_us: 1515079109822292
    success_time_us: 1845
   viable_candidate: YES
          read_only: YES
transactions_behind: 0
              error: NULL
*************************** 3. row ***************************
           hostname: mysql_node1
               port: 3306
      time_start_us: 1515079109821971
    success_time_us: 1582
   viable_candidate: YES
          read_only: NO
transactions_behind: 0
              error: NULL

proxy>

select * from mysql_server_group_replication_log order by time_start_us desc limit 3\G

*************************** 1. row ***************************

hostname: mysql_node3

port: 3306

time_start_us: 1515079109822616

success_time_us: 1782

viable_candidate: YES

read_only: YES

transactions_behind: 0

error: NULL

*************************** 2. row ***************************

hostname: mysql_node2

port: 3306

time_start_us: 1515079109822292

success_time_us: 1845

viable_candidate: YES

read_only: YES

transactions_behind: 0

error: NULL

*************************** 3. row ***************************

hostname: mysql_node1

port: 3306

time_start_us: 1515079109821971

success_time_us: 1582

viable_candidate: YES

read_only: NO

transactions_behind: 0

error: NULL

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 :

...
$databases['default']['default'] = array (
  'database' => 'drupal',
  'username' => 'app_user',
  'password' => 'app_pwd',
  'prefix' => 'drupal_',
  'host' => '172.22.0.2',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);
...

...

$databases['default']['default'] = array (

'database' => 'drupal',

'username' => 'app_user',

'password' => 'app_pwd',

'prefix' => 'drupal_',

'host' => '172.22.0.2',

'port' => '3306',

'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',

'driver' => 'mysql',

);

...

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 :

$ cat /tmp/temp.cnf
[mysql]
user=app_user
password=app_pwd
protocol=tcp

$ cat /tmp/temp.cnf

[mysql]

user=app_user

password=app_pwd

protocol=tcp

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/).

$ for x in {1..5}; do mysql --defaults-file=/tmp/temp.cnf -h 172.22.0.2 -P 3306  -BNe"SELECT @@report_host;" ; done;
mysql_node1
mysql_node1
mysql_node1
mysql_node1
mysql_node1

$ for x in {1..5}; do mysql --defaults-file=/tmp/temp.cnf -h 172.22.0.2 -P 3306 -BNe"SELECT @@report_host;" ; done;

mysql_node1

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 :

proxy>
CREATE SCHEMA test;

CREATE TABLE test.poc (
id tinyint unsigned NOT NULL AUTO_INCREMENT,
host varchar(11),
time timestamp,
PRIMARY KEY (id)
) ENGINE=InnoDB;

proxy>

CREATE SCHEMA test;

CREATE TABLE test.poc (

id tinyint unsigned NOT NULL AUTO_INCREMENT,

host varchar(11),

time timestamp,

PRIMARY KEY (id)

) ENGINE=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).

$ while true; do
>   mysql --defaults-file=/tmp/temp.cnf -h 172.22.0.2 -P 3306 -BNe"INSERT INTO test.poc (host) VALUES (@@report_host); SELECT * FROM test.poc;";
>   echo;
>   sleep 4;
> done

2	mysql_node1	2018-01-04 16:42:31 <=> Inserted row #1

2	mysql_node1	2018-01-04 16:42:31
9	mysql_node1	2018-01-04 16:42:35 <=> Inserted row #2

2	mysql_node1	2018-01-04 16:42:31
9	mysql_node1	2018-01-04 16:42:35
11	mysql_node3	2018-01-04 16:42:43 <=> Inserted row #3 => Failover! New primary is mysql_node3

2	mysql_node1	2018-01-04 16:42:31
9	mysql_node1	2018-01-04 16:42:35
11	mysql_node3	2018-01-04 16:42:43
18	mysql_node3	2018-01-04 16:42:47 <=> Inserted row #4

$ while true; do

> mysql --defaults-file=/tmp/temp.cnf -h 172.22.0.2 -P 3306 -BNe"INSERT INTO test.poc (host) VALUES (@@report_host); SELECT * FROM test.poc;";

> echo;

> sleep 4;

> done

2 mysql_node1 2018-01-04 16:42:31 <=> Inserted row #1

2 mysql_node1 2018-01-04 16:42:31

9 mysql_node1 2018-01-04 16:42:35 <=> Inserted row #2

2 mysql_node1 2018-01-04 16:42:31

9 mysql_node1 2018-01-04 16:42:35

11 mysql_node3 2018-01-04 16:42:43 <=> Inserted row #3 => Failover! New primary is mysql_node3

2 mysql_node1 2018-01-04 16:42:31

9 mysql_node1 2018-01-04 16:42:35

11 mysql_node3 2018-01-04 16:42:43

18 mysql_node3 2018-01-04 16:42:47 <=> Inserted row #4

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):

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 innoDB Cluster

References

dasini.net

MySQL Group Replication

ProxySQL

Thanks for using MySQL!

Filed under:
Group Replication, Haute Disponibilité, Monitoring, MySQL, ProxySQL, tuto by Olivier DASINI

Où télécharger MySQL ?

juillet 24, 2017

Tags: Docker, download, github

Read this post in English

Lorsque l’on démarre un nouveau projet, il est en général conseillé de partir sur la version la plus récente de MySQL, histoire de profiter des toutes dernières fonctionnalités mais aussi (surtout ?) d’être certain d’être à jour au niveau des patchs de sécurité.

Powered by MySQL
Cet article centralise les différentes URLs pour télécharger la base de données Open Source la plus populaire au monde.

MySQL Community Edition

Si tu cherches le dernier binaire compilé, les paquets client et/ou serveur, ou tout simplement les sources à compiler toi même, en 64 ou 32 bits, pour l’OS de ton choix (Linux, Windows, Mac, Solaris, FreeBSD) :

Toutes les plateformes : https://dev.mysql.com/downloads/mysql/
Windows (la suite complète) : https://dev.mysql.com/downloads/windows/

Dépôts Linux

Sur ta distribution Linux, le MySQL embarqué est bien souvent très ancien (ou pire encore, tu peux te retrouver, à ton insu, avec autre chose que MySQL) .

La solution ? Installer le dépôt MySQL qui permet une installation aussi simple que pratique de la dernière GA, mais aussi des autres produits de l’écosystème :

Yum Repository : https://dev.mysql.com/downloads/repo/yum/
APT Repository : https://dev.mysql.com/downloads/repo/apt/
SUSE Repository : https://dev.mysql.com/downloads/repo/suse/

Au passage, je vous suggère de lire l’article d’Ivan Ma : Installation of MySQL.

Autres

Image Docker, créée, maintenue et supportée par MySQL :

Docker : https://hub.docker.com/r/mysql/mysql-server/

Dépôt GitHub créé, maintenu et supporté par MySQL :

GitHub : https://github.com/mysql/mysql-server

MySQL NDB Cluster

MySQL NDB Cluster, la base de données distribuée temps réel est disponible dans les dépôts ci-dessus, ainsi que dans ces 2 URLs dédiées :

NDB Cluster : https://dev.mysql.com/downloads/cluster/
Docker (NDB cluster) : https://hub.docker.com/r/mysql/mysql-cluster/

MySQL Enterprise Edition

Si tu es client Oracle MySQL connecte toi à ton compte My Oracle Support :

MOS : http://support.oracle.com

L’Oracle Software Delivery Cloud (e-delivery) permet de tester les produits MySQL Enterprise :

Edelivery : http://edelivery.oracle.com

MySQL Enterprise Edition est également disponible sur le Cloud, exclusivement sur Oracle Cloud :

MySQL Cloud Service : https://cloud.oracle.com/mysql

Anciennes versions

Parfois on n’a pas le choix, une version précise est impérative. Tu la trouveras dans les archives :

MySQL Server : https://downloads.mysql.com/archives/community/
MySQL NDB Cluster : https://downloads.mysql.com/archives/cluster/
Archive produits MySQL : https://downloads.mysql.com/archives/

Thanks for using MySQL!

Announcing MySQL Server 5.7.19, 5.6.37, and 5.5.57

Filed under:
Astuce, MySQL by Olivier DASINI

Topo sur les premières versions publique de MySQL

juillet 19, 2017

Read this blog post in English

j’ai régulièrement l’opportunité de rencontrer les utilisateurs des produits MySQL, et je suis toujours un peu surpris de voir des applications critiques qui tournent sur des versions pas vraiment récente (pour employer un euphémisme) 🙂

La bonne nouvelle est que manifestement les anciennes versions de MySQL sont suffisamment stables et performantes pour faire tourner du business moderne. Cependant, et ce même si je comprend bien qu’il est parfois pertinent de figer toutes les couches d’une architecture, il est souvent dommage de ne pas profiter des dernières améliorations d’un point de vue, performance, stabilité, sécurité et bien entendu des nouvelles fonctionnalités de la dernière GA:

La notion de temps étant relative, je te propose un petit jeu de mise en perspective des dates des premières versions publique (non GA), une sorte de blind-test MySQL…

MySQL 3.22 | 1998

Voiture la plus vendue en France : Renault Clio II (phase 1)
Meilleure vente de Singles en France : Garou, Daniel Lavoie et Patrick Fiori, Belle
Evénement : 12 juillet 1998, la France bat le Brésil lors de la finale de la coupe du monde de football 3 buts à 0

Sources:
http://dev.cs.ovgu.de/db/mysql/News-3.22.x.html
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=18
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_1998
https://fr.wikipedia.org/wiki/1998_en_France

MySQL 3.23 | 1999

Voiture la plus vendue en France : Renault Clio II (phase 1)
Meilleure vente de Singles en France : Lou Bega, Mambo No. 5
Evénement : 11 août 1999, une éclipse totale de Soleil se produit sur le Nord de la France

Sources:
http://mysql.localhost.net.ar/doc/refman/4.1/en/news-3-23-x.html
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=18
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_1999
https://fr.wikipedia.org/wiki/1999_en_France

MySQL 4.0 | 2001

Voiture la plus vendue en France : Peugeot 206 (phase 1)
Meilleure vente de Singles en France : Star Academy 1, La musique
Evénement : 27 juin, fin de la conscription. L’armée française est désormais entièrement professionnelle

Sources:
http://mysql.localhost.net.ar/doc/refman/4.1/en/news-4-0-x.html
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=19
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2001
https://fr.wikipedia.org/wiki/2001_en_France

MySQL 4.1 | 2003

Voiture la plus vendue en France : Renault Clio II (phase 2)
Meilleure vente de Singles en France : DJ BoBo, Chihuahua
Evénement : 30 mai, dernier vol du Concorde d’Air France entre Paris et New York

Sources:
http://mysql.localhost.net.ar/doc/refman/4.1/en/news-4-1-x.html
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=20
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2003
https://fr.wikipedia.org/wiki/2003_en_France

MySQL 5.0 | 2003

Voiture la plus vendue en France : Renault Clio II (phase 2)
Meilleure vente de Singles en France : DJ BoBo, Chihuahua
Evénement : 30 mai, dernier vol du Concorde d’Air France entre Paris et New York

Sources:
http://ftp.nchu.edu.tw/MySQL/doc/refman/5.0/en/news-5-0-x.html
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=20
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2003
https://fr.wikipedia.org/wiki/2003_en_France

MySQL 5.1 | 2005

Voiture la plus vendue en France : Renault Clio III (phase 1)
Meilleure vente de Singles en France : Ilona Mitrecey, Un monde parfait
Evénement : 31 mars, lancement de la TNT (14 chaînes gratuites).

Sources:
http://ftp.nchu.edu.tw/MySQL/doc/refman/5.1/en/news-5-1-x.html
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=22
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2005
https://fr.wikipedia.org/wiki/2005_en_France

MySQL 5.5 | 2009

Voiture la plus vendue en France : Peugeot 207
Meilleure vente de Singles en France : Helmut Fritz, Ça m’énerve
Evénement : 15 avril, entrée en vigueur des nouvelles plaques d’immatriculations sur les véhicules neufs. La nouvelle numérotation est attribuée à vie pour chaque véhicule et comporte deux lettres, un tiret, trois chiffres, un tiret et deux lettres. Un numéro de département au choix ainsi que le logo de la région correspondante est apposée sur la droite

Sources:
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=23
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2009
https://fr.wikipedia.org/wiki/2009_en_France

MySQL 5.6 | 2011

Voiture la plus vendue en France : Renault Clio III (phase 2)
Meilleure vente de Singles en France : Israel Kamakawiwo’ole : Over the Rainbow
Evénement : 14 mai : arrestation à New-York de Dominique Strauss-Kahn, directeur général du FMI, dans le cadre d’une accusation de crimes sexuels à l’encontre de Nafissatou Diallo

Sources:
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=24
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2011
https://fr.wikipedia.org/wiki/2011_en_France

MySQL 5.7 | 2013

Voiture la plus vendue en France : Renault Clio IV
Meilleure vente de Singles en France : Daft Punk, Get Lucky
Evénement : 9 février, la découverte de viande de cheval camouflée dans des préparations surgelées réputées pur bœuf renforce les soupçons de tromperie sur la traçabilité agroalimentaire à l’échelle de la communauté économique européenne

Sources:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=25
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2013
https://fr.wikipedia.org/wiki/2013_en_France

MySQL 8.0 | 2016

Voiture la plus vendue en France : Renault Clio IV (phase 2)
Meilleure vente de Singles en France : M Pokora, Cette Année la
Evénement : 1er janvier, entrée en vigueur du nouveau découpage des régions

Sources:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
http://www.auto-moto.com/occasion/souvenirs-souvenirs/voiture-la-plus-vendue-par-annee-les-statistiques-de-1947-a-nos-jours-55637.html#item=26
https://fr.wikipedia.org/wiki/Liste_des_titres_musicaux_num%C3%A9ro_un_en_France_en_2016
https://fr.wikipedia.org/wiki/2016_en_France

Sakila in Norway

Thanks for using MySQL!

Filed under:
Astuce, MySQL by Olivier DASINI

PHP Tour 2017 – Slides MySQL InnoDB Cluster

mai 26, 2017

Olivier DASINI aka @freshdaz La dernière édition du PHP Tour s’est déroulée les 18 et 19 mai 2017 à Nantes. Ce que j’en garde : un très bon cru, de bien belles rencontres, de bonnes bières ainsi qu’une excellente organisation (merci l’AFUP).

J’ai également eu l’opportunité de présenter MySQL InnoDB Cluster, la nouvelle solution native de haute disponibilité de MySQL :

MySQL InnoDB Cluster – A complete High Availability solution for MySQL from Olivier DASINI

Vidéo de la conférence : https://youtu.be/Y48cbFqd5QA

Les photos sont disponibles sur le groupe Flickr du PHP Tour 2017 Nantes.

Les vidéos des conférences sont disponibles sur le site de l’AFUP.

Thanks for using MySQL!

Filed under:
Conférence, Group Replication, Haute Disponibilité, InnoDB Cluster, PHP, Présentation by Olivier DASINI

Tutoriel – Déployer MySQL innoDB Cluster

mai 11, 2017

Tags: group replication, Haute disponibilité, InnoDB, InnoDB Cluster, router, shell, tuto

Dans les épisodes précédents on a vu comment déployer « manuellement » MySQL Group Replication, comprendre et tester MySQL InnoDB Cluster ainsi que comment gérer aisément un cluster Group Replication déja déployé avec MySQL Shell.

Aujourd’hui, dans la série Haute Disponibilité avec MySQL on va voir comment déployer et gérer un cluster from scratch , sous la forme d’un tutoriel, grâce à la solution tout en un : MySQL InnoDB Cluster.

Note:
L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server (plugin GRoup Replication) avec pour moteur de stockage InnoDB, solution à ne pas confondre avec MySQL NDB Cluster (moteur de stockage NDB).

Le contexte

3 instances MySQL autonomes, <spoil altert> qui vont grâce au plugin MySQL Group Replication se transformer en une base de données distribuée.</spoil altert>

Instance MySQL 1 : 192.168.1.22; Nœud numéro 1 du cluster
Instance MySQL 2 : 192.168.1.50; Nœud numéro 2 du cluster
Instance MySQL 3 : 192.168.1.48; Nœud numéro 3 du cluster

1 instance applicative : 192.168.1.11; MySQL Router + mon application.

MySQL Shell est installé sur toutes les instances.

En ce qui concerne les versions des logiciels:

—
Update 08-08-2018

Note: TOUJOURS prendre la dernière version du Router et du Shell. C’est actuellement la branche 8.0.x qui est bien évidemment compatible avec MySQL 5.7 InnoDB Cluster.

ex: MySQL Router 8.0.12 + MySQL Shell 8.0.12 avec MySQL 5.7.23
—

Avec un schéma sa donne (à peu près) ça :

MySQL InnoDB Cluster PoC Architecture

Vérifier la configuration des instances

La première étape consiste à s’assurer que les instances MySQL sont correctement configurées pour l’utilisation de MySQL Group Replication, la couche haute disponibilité de notre architecture. A noter qu’il est préférable de provisionner ses instances déjà correctement configurées (comme détaillé dans cet article) pour MySQL Group Replication.

La vérification de la configuration se fait grâce à MySQL Shell et la méthode checkInstanceConfiguration() :

$ mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

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

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.



mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')
Please provide the password for 'root@192.168.1.22:3306': 
Validating instance...

The instance '192.168.1.22:3306' is not valid for Cluster usage.

The following issues were encountered:

 - Some configuration options need to be fixed.

+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable or restart the server |
| enforce_gtid_consistency         | OFF           | ON             | Restart the server                               |
| gtid_mode                        | OFF           | ON             | Restart the server                               |
| log_bin                          | 0             | 1              | Restart the server                               |
| log_slave_updates                | 0             | ON             | Restart the server                               |
| master_info_repository           | FILE          | TABLE          | Restart the server                               |
| relay_log_info_repository        | FILE          | TABLE          | Restart the server                               |
| transaction_write_set_extraction | OFF           | XXHASH64       | Restart the server                               |
+----------------------------------+---------------+----------------+--------------------------------------------------+


Please fix these issues , restart the serverand try again.

{
    "config_errors": [
        {
            "action": "server_update", 
            "current": "CRC32", 
            "option": "binlog_checksum", 
            "required": "NONE"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "log_bin", 
            "required": "1"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "log_slave_updates", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "FILE", 
            "option": "master_info_repository", 
            "required": "TABLE"
        },
        {
            "action": "restart", 
            "current": "FILE", 
            "option": "relay_log_info_repository", 
            "required": "TABLE"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "transaction_write_set_extraction", 
            "required": "XXHASH64"
        }
    ], 
    "errors": [], 
    "restart_required": true, 
    "status": "error"
}

$ mysqlsh

Welcome to MySQL Shell 1.0.9

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')

Please provide the password for 'root@192.168.1.22:3306':

Validating instance...

The instance '192.168.1.22:3306' is not valid for Cluster usage.

The following issues were encountered:

- Some configuration options need to be fixed.

+----------------------------------+---------------+----------------+--------------------------------------------------+

+----------------------------------+---------------+----------------+--------------------------------------------------+

| enforce_gtid_consistency | OFF | ON | Restart the server |

| gtid_mode | OFF | ON | Restart the server |

| log_bin | 0 | 1 | Restart the server |

| log_slave_updates | 0 | ON | Restart the server |

+----------------------------------+---------------+----------------+--------------------------------------------------+

Please fix these issues , restart the serverand try again.

{

"config_errors": [

{

"action": "server_update",

"current": "CRC32",

"option": "binlog_checksum",

"required": "NONE"

{

"action": "restart",

"current": "OFF",

"option": "enforce_gtid_consistency",

"required": "ON"

{

"action": "restart",

"current": "OFF",

"option": "gtid_mode",

"required": "ON"

{

"action": "restart",

"current": "0",

"option": "log_bin",

"required": "1"

{

"action": "restart",

"current": "0",

"option": "log_slave_updates",

"required": "ON"

{

"action": "restart",

"current": "FILE",

"option": "master_info_repository",

"required": "TABLE"

{

"action": "restart",

"current": "FILE",

"option": "relay_log_info_repository",

"required": "TABLE"

{

"action": "restart",

"current": "OFF",

"option": "transaction_write_set_extraction",

"required": "XXHASH64"

}

"errors": [],

"restart_required": true,

"status": "error"

}

Dans mon cas, avec l’installation par défaut sous Ubuntu, niveau configuration j’ai tout à faire 🙂

La méthode renvoie un document JSON (pratique pour l’automatisation) avec la liste des tâches à effectuer pour être conforme… Configurons donc !

J’ai deux solutions :

1/ je prépare mes instances « manuellement » (cette tâche peut bien évidemment s’automatiser) comme expliqué dans l’article comment configurer un groupe.
2/ je me connecte à chaque instance en local, et j’utilise la méthode : configureLocalInstance()

Soyons fou ! allons y pour la méthode 2 :

mysql-js> dba.configureLocalInstance('root@192.168.1.22:3306')
Dba.configureLocalInstance: This function only works with local instances (RuntimeError)

1 2	mysql-js> dba.configureLocalInstance('root@192.168.1.22:3306') Dba.configureLocalInstance: This function only works with local instances (RuntimeError)

Ouppss!!! dba.configureLocalInstance ne fonctionne qu’en local, c’est-à-dire, si je suis connecté sur la machine hôte de l’instance MySQL (ce qui est une bonne chose). Du coup après m’être connecté à l’hôte 192.168.1.22 :

sudo mysqlsh
Welcome to MySQL Shell 1.0.9
...

mysql-js> dba.configureLocalInstance('root@localhost:3306')
Please provide the password for 'root@localhost:3306': 

Detecting the configuration file...
Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf
Do you want to modify this file? [Y|n]: Y
Validating instance...

The configuration has been updated but it is required to restart the server.

{
    "config_errors": [
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "log_bin", 
            "required": "1"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "log_slave_updates", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "FILE", 
            "option": "master_info_repository", 
            "required": "TABLE"
        },
        {
            "action": "restart", 
            "current": "FILE", 
            "option": "relay_log_info_repository", 
            "required": "TABLE"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "transaction_write_set_extraction", 
            "required": "XXHASH64"
        }
    ], 
    "errors": [], 
    "restart_required": true, 
    "status": "error"
}

sudo mysqlsh

Welcome to MySQL Shell 1.0.9

...

mysql-js> dba.configureLocalInstance('root@localhost:3306')

Please provide the password for 'root@localhost:3306':

Detecting the configuration file...

Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf

Do you want to modify this file? [Y|n]: Y

Validating instance...

The configuration has been updated but it is required to restart the server.

{

"config_errors": [

{

"action": "restart",

"current": "OFF",

"option": "enforce_gtid_consistency",

"required": "ON"

{

"action": "restart",

"current": "OFF",

"option": "gtid_mode",

"required": "ON"

{

"action": "restart",

"current": "0",

"option": "log_bin",

"required": "1"

{

"action": "restart",

"current": "0",

"option": "log_slave_updates",

"required": "ON"

{

"action": "restart",

"current": "FILE",

"option": "master_info_repository",

"required": "TABLE"

{

"action": "restart",

"current": "FILE",

"option": "relay_log_info_repository",

"required": "TABLE"

{

"action": "restart",

"current": "OFF",

"option": "transaction_write_set_extraction",

"required": "XXHASH64"

}

"errors": [],

"restart_required": true,

"status": "error"

}

Note: Assure toi d’avoir les droits nécessaires pour mettre à jour le fichier de configuration de MySQL.

Les informations ajoutées dans le fichier de configuration se trouvent en fin de fichier :

# cat /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
... <data previously in the file> ...

log_slave_updates = ON
server_id = 2323701546
relay_log_info_repository = TABLE
master_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
binlog_format = ROW
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port = 3306
binlog_checksum = NONE
enforce_gtid_consistency = ON
log_bin
gtid_mode = ON

# cat /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

... <data previously in the file> ...

log_slave_updates = ON

server_id = 2323701546

relay_log_info_repository = TABLE

master_info_repository = TABLE

transaction_write_set_extraction = XXHASH64

binlog_format = ROW

disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

report_port = 3306

binlog_checksum = NONE

enforce_gtid_consistency = ON

log_bin

gtid_mode = ON

192.168.1.22 est configurée. Après redémarrage de l’instance MySQL, la sortie de checkInstanceConfiguration est beaucoup moins anxiogène :

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')
Please provide the password for 'root@192.168.1.22:3306': 
Validating instance...

The instance '192.168.1.22:3306' is valid for Cluster usage
{
    "status": "ok"
}

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')

Please provide the password for 'root@192.168.1.22:3306':

Validating instance...

The instance '192.168.1.22:3306' is valid for Cluster usage

{

"status": "ok"

}

OK ! Le membre est prêt pour faire parti d’un groupe.

La même procédure doit être appliquée sur les autres instances MySQL.

…

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Validating instance...

The instance '192.168.1.50:3306' is valid for Cluster usage
{
    "status": "ok"
}
mysql-js> dba.checkInstanceConfiguration('root@192.168.1.48:3306')
Please provide the password for 'root@192.168.1.48:3306': 
Validating instance...

The instance '192.168.1.48:3306' is valid for Cluster usage
{
    "status": "ok"
}

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.50:3306')

Please provide the password for 'root@192.168.1.50:3306':

Validating instance...

The instance '192.168.1.50:3306' is valid for Cluster usage

{

"status": "ok"

}

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.48:3306')

Please provide the password for 'root@192.168.1.48:3306':

Validating instance...

The instance '192.168.1.48:3306' is valid for Cluster usage

{

"status": "ok"

}

Créer le cluster

Une fois les 3 instances correctement configurées, l’étape suivante consiste à créer le cluster avec createCluster. Cette méthode va être jouée sur le premier membre, l’instance MySQL sur 192.168.1.22, elle va permettre de bootstrapper le cluster:

mysql-js> \c root@192.168.1.22
Creating a Session to 'root@192.168.1.22'
Enter password: 
Classic Session successfully established. No default schema selected.


mysql-js> var cluster = dba.createCluster('pocCluster', {ipWhitelist: "192.168.1.0/16"});
A new InnoDB cluster will be created on instance 'root@192.168.1.22:3306'.

Creating InnoDB cluster 'pocCluster' on 'root@192.168.1.22:3306'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

mysql-js> \c root@192.168.1.22

Creating a Session to 'root@192.168.1.22'

Enter password:

Classic Session successfully established. No default schema selected.

mysql-js> var cluster = dba.createCluster('pocCluster', {ipWhitelist: "192.168.1.0/16"});

A new InnoDB cluster will be created on instance 'root@192.168.1.22:3306'.

Creating InnoDB cluster 'pocCluster' on 'root@192.168.1.22:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

createCluster() prend comme paramètre le nom du cluster (pocCluster). Je peux lui passer également quelques information optionnelles comme la whitelist.

On peut vérifier l’état du nœud dans le cluster avec status() :

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Notes : Assure toi que ton DNS (ou /etc/hosts) est correctement configuré, sinon tu vas avoir des soucis de connections…

L’ajouts des nœuds suivant se fait avec addInstance(), il est néanmoins conseillé d’exécuter checkInstanceState() au préalable pour s’assurer de la compatibilité des GTID sets :

Nœud 2

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new", 
    "state": "ok"
}


mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.0/16"})
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@192.168.1.50:3306': 
Adding instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully added to the cluster.


mysql-js> cluster.status();
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

Please provide the password for 'root@192.168.1.50:3306':

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.0/16"})

A new instance will be added to the InnoDB cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@192.168.1.50:3306':

Adding instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully added to the cluster.

mysql-js> cluster.status();

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Au cas où l’instance ajoutée contient plus de transactions que le groupe checkInstanceState le fait savoir :

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is invalid for the cluster.
The instance contains additional transactions in relation to the cluster.

{
    "reason": "diverged", 
    "state": "error"
}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

Please provide the password for 'root@192.168.1.50:3306':

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is invalid for the cluster.

The instance contains additional transactions in relation to the cluster.

{

"reason": "diverged",

"state": "error"

}

En fonction du contexte, il faut alors soit restaurer une sauvegarde d’un membre du cluster sur l’instance problématique (celle qui diverge) ou alors si tu sais ce que tu fais, une synchronisation des GTIDs est toujours possible, voir un reset master.

Nœud 3

mysql-js> cluster.checkInstanceState('root@192.168.1.48:3306')
Please provide the password for 'root@192.168.1.48:3306': 
Analyzing the instance replication state...

The instance '192.168.1.48:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new", 
    "state": "ok"
}


mysql-js> cluster.addInstance("root@192.168.1.48:3306", {ipWhitelist: "192.168.1.0/16"})
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@192.168.1.48:3306': 
Adding instance to the cluster ...

The instance 'root@192.168.1.48:3306' was successfully added to the cluster.


mysql-js> cluster.status();
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.checkInstanceState('root@192.168.1.48:3306')

Please provide the password for 'root@192.168.1.48:3306':

Analyzing the instance replication state...

The instance '192.168.1.48:3306' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

mysql-js> cluster.addInstance("root@192.168.1.48:3306", {ipWhitelist: "192.168.1.0/16"})

A new instance will be added to the InnoDB cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@192.168.1.48:3306':

Adding instance to the cluster ...

The instance 'root@192.168.1.48:3306' was successfully added to the cluster.

mysql-js> cluster.status();

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Et voilà!

Cluster MySQL Group Replication de 3 nœuds déployé grâce à MySQL Shell !

Nœud 1 : 192.168.1.22 : Primaire (lecture/écriture)
Nœud 2 : 192.168.1.50 : Secondaire (lecture seule)
Nœud 3 : 192.168.1.48 : Secondaire (lecture seule)

Pour rendre la configuration persistante ie l’écrire dans le fichier de configuration, il faut exécuter, après que le noeud soit configuré, la méthode dba.configureLocalInstance() :

daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/mysql.conf.d/mysqld.cnf
0


daz@192.168.1.50:~$ sudo mysqlsh --uri=root@localhost
Creating a Session to 'root@localhost'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.9
...


mysql-js> dba.configureLocalInstance("root@localhost:3306")  
Please provide the password for 'root@localhost:3306': 

Detecting the configuration file...
Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf
Do you want to modify this file? [Y|n]: Y
Validating instance...

The instance 'localhost:3306' is valid for Cluster usage
You can now use it in an InnoDB Cluster.

{
    "status": "ok"
}



daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/my.cnf
32

daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/mysql.conf.d/mysqld.cnf

daz@192.168.1.50:~$ sudo mysqlsh --uri=root@localhost

Creating a Session to 'root@localhost'

Enter password:

Classic Session successfully established. No default schema selected.

Welcome to MySQL Shell 1.0.9

...

mysql-js> dba.configureLocalInstance("root@localhost:3306")

Please provide the password for 'root@localhost:3306':

Detecting the configuration file...

Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf

Do you want to modify this file? [Y|n]: Y

Validating instance...

The instance 'localhost:3306' is valid for Cluster usage

You can now use it in an InnoDB Cluster.

{

"status": "ok"

}

daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/my.cnf

A noter que cette opération ne peut se faire qu’en local.

La suite ?

Configuration de MySQL Router

Les recommandations de MySQL sont d’installer MySQL Router sur la machine hôte de l’application, je vais donc l’installer sur la machine 192.168.1.11.

Note: Si tu ne peux (veux) pas mettre MySQL Router sur l’application, il va alors te falloir gérer le HA du Router. Plusieurs solutions sont envisageables comme :

Pacemaker
Keepalived

Bootstrap MySQL Router

daz@192.168.1.11 ~ $ mysqlrouter --bootstrap root@192.168.1.22:3306 --directory RouterPoC
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at /home/daz/RouterPoC...
MySQL Router  has now been configured for the InnoDB cluster 'pocCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'pocCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'pocCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

daz@192.168.1.11 ~ $ mysqlrouter --bootstrap root@192.168.1.22:3306 --directory RouterPoC

Please enter MySQL password for root:

Bootstrapping MySQL Router instance at /home/daz/RouterPoC...

MySQL Router has now been configured for the InnoDB cluster 'pocCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'pocCluster':

- Read/Write Connections: localhost:6446

- Read/Only Connections: localhost:6447

X protocol connections to cluster 'pocCluster':

- Read/Write Connections: localhost:64460

- Read/Only Connections: localhost:64470

4 ports TCP ont été configurés, dont 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 fichier de configuration du Router contient quelques informations importantes, tel que le(s) port(s) à utiliser par l’application :

daz@192.168.1.11 ~ $ cat ~/RouterPoC/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/home/daz/RouterPoC/log
runtime_folder=/home/daz/RouterPoC/run
data_folder=/home/daz/RouterPoC/data
keyring_path=/home/daz/RouterPoC/data/keyring
master_key_path=/home/daz/RouterPoC/mysqlrouter.key

[logger]
level = INFO

[metadata_cache:pocCluster]
router_id=3
bootstrap_server_addresses=mysql://192.168.1.22:3306,mysql://192.168.1.50:3306,mysql://192.168.1.48:3306
user=mysql_router3_qeteek4wi41s
metadata_cluster=pocCluster
ttl=300

[routing:pocCluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://pocCluster/default?role=PRIMARY
mode=read-write
protocol=classic

[routing:pocCluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://pocCluster/default?role=SECONDARY
mode=read-only
protocol=classic

[routing:pocCluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://pocCluster/default?role=PRIMARY
mode=read-write
protocol=x

[routing:pocCluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://pocCluster/default?role=SECONDARY
mode=read-only
protocol=x

daz@192.168.1.11 ~ $ cat ~/RouterPoC/mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap

[DEFAULT]

logging_folder=/home/daz/RouterPoC/log

runtime_folder=/home/daz/RouterPoC/run

data_folder=/home/daz/RouterPoC/data

keyring_path=/home/daz/RouterPoC/data/keyring

master_key_path=/home/daz/RouterPoC/mysqlrouter.key

[logger]

level = INFO

[metadata_cache:pocCluster]

router_id=3

bootstrap_server_addresses=mysql://192.168.1.22:3306,mysql://192.168.1.50:3306,mysql://192.168.1.48:3306

user=mysql_router3_qeteek4wi41s

metadata_cluster=pocCluster

ttl=300

[routing:pocCluster_default_rw]

bind_address=0.0.0.0

bind_port=6446

destinations=metadata-cache://pocCluster/default?role=PRIMARY

mode=read-write

protocol=classic

[routing:pocCluster_default_ro]

bind_address=0.0.0.0

bind_port=6447

destinations=metadata-cache://pocCluster/default?role=SECONDARY

mode=read-only

protocol=classic

[routing:pocCluster_default_x_rw]

bind_address=0.0.0.0

bind_port=64460

destinations=metadata-cache://pocCluster/default?role=PRIMARY

mode=read-write

protocol=x

[routing:pocCluster_default_x_ro]

bind_address=0.0.0.0

bind_port=64470

destinations=metadata-cache://pocCluster/default?role=SECONDARY

mode=read-only

protocol=x

Il est évidemment possible de modifier ce fichier. Par exemple, souvent les applications se connectent au port 3306, ça peut donc avoir du sens de modifier le port du noeud primaire en le passant de 6446 à 3306 :

daz@192.168.1.11 ~ $ grep -A6 pocCluster_default_rw ~/RouterPoC/mysqlrouter.conf
[routing:pocCluster_default_rw]
bind_address=0.0.0.0
bind_port=3306
destinations=metadata-cache://pocCluster/default?role=PRIMARY
mode=read-write
protocol=classic

daz@192.168.1.11 ~ $ grep -A6 pocCluster_default_rw ~/RouterPoC/mysqlrouter.conf

[routing:pocCluster_default_rw]

bind_address=0.0.0.0

bind_port=3306

destinations=metadata-cache://pocCluster/default?role=PRIMARY

mode=read-write

protocol=classic

Ton application va donc (continuer à) se connecter au port 3306, sauf que maintenant ce n’est pas 1 instance, mais bel et bien 3 instances qui sont en backend, et ceci en toute transparence.

Ensuite, il faut démarrer MySQL Router avec le script start.sh

daz@192.168.1.11 ~ $ ~/RouterPoC/start.sh

1	daz@192.168.1.11 ~ $ ~/RouterPoC/start.sh

L’arrêt du Router se fait avec le script stop.sh (mais tu l’avais deviné)

daz@192.168.1.11 ~ $ ~/RouterPoC/stop.sh

1	daz@192.168.1.11 ~ $ ~/RouterPoC/stop.sh

Gestion des nœuds

Quelques commandes qui vont te simplifier la vie…

Récupérer les méta-données d’un cluster

Les méta-données du cluster sont stockées sur les membres dans le schéma mysql_innodb_cluster_metadata :

mysql> SHOW SCHEMAS;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0,00 sec)


mysql> SHOW TABLES IN mysql_innodb_cluster_metadata;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| routers                                 |
| schema_version                          |
+-----------------------------------------+
6 rows in set (0,01 sec)


mysql> SELECT cluster_name FROM mysql_innodb_cluster_metadata.clusters;
+--------------+
| cluster_name |
+--------------+
| pocCluster   |
+--------------+

mysql> SHOW SCHEMAS;

+-------------------------------+

| Database |

+-------------------------------+

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

+-------------------------------+

5 rows in set (0,00 sec)

mysql> SHOW TABLES IN mysql_innodb_cluster_metadata;

+-----------------------------------------+

| Tables_in_mysql_innodb_cluster_metadata |

+-----------------------------------------+

| clusters |

| hosts |

| instances |

| replicasets |

| routers |

| schema_version |

+-----------------------------------------+

6 rows in set (0,01 sec)

mysql> SELECT cluster_name FROM mysql_innodb_cluster_metadata.clusters;

+--------------+

| cluster_name |

+--------------+

| pocCluster |

+--------------+

Pour récupérer ces informations dans une nouvelle session il faut utiliser la méthode getCluster :

mysql-js> cluster.status()
ReferenceError: cluster is not defined


mysql-js> var cluster = dba.getCluster('pocCluster')


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

ReferenceError: cluster is not defined

mysql-js> var cluster = dba.getCluster('pocCluster')

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Failover

Le basculement niveau base de données (changement de primaire) est automatiquement géré par les membres du cluster entre eux.

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Crash du noeud primaire (192.168.1.22)…

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Nouveau primaire élu par le groupe : 192.168.1.48.

Et 192.168.1.22 est porté disparu (MIA).

Un rejoinInstance() est nécessaire pour remettre le membre dans le cluster. Il aura un rôle de secondaire (voir paragraphe suivant Remettre un membre dans le groupe).

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Remettre un membre dans le groupe

Un membre avec son instance MySQL qui tourne et qui est correctement configuré peut être remis (après un redémarrage de l’instance par exemple) dans le groupe avec la commande rejoinInstance() :

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }
}


mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.
The instance is fully recoverable.

{
    "reason": "recoverable", 
    "state": "ok"
}


mysql-js> cluster.rejoinInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.0/16"})
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Please provide the password for 'root@192.168.1.50:3306': 
Rejoining instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully rejoined on the cluster.

The instance '192.168.1.50:3306' was successfully added to the MySQL Cluster.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

Please provide the password for 'root@192.168.1.50:3306':

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.

The instance is fully recoverable.

{

"reason": "recoverable",

"state": "ok"

}

mysql-js> cluster.rejoinInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.0/16"})

Rejoining the instance to the InnoDB cluster. Depending on the original

problem that made the instance unavailable, the rejoin operation might not be

successful and further manual steps will be needed to fix the underlying

problem.

Please monitor the output of the rejoin operation and take necessary action if

the instance cannot rejoin.

Please provide the password for 'root@192.168.1.50:3306':

Rejoining instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully rejoined on the cluster.

The instance '192.168.1.50:3306' was successfully added to the MySQL Cluster.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Supprimer une instance du groupe

Sans grande surprise, c’est la commande removeInstance

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}


mysql-js> cluster.removeInstance("root@192.168.1.50:3306")
The instance will be removed from the InnoDB cluster. Depending on the 
instance being the Seed or not, the Metadata session might become invalid. 
If so, please start a new session to the Metadata Storage R/W instance.

The instance 'root@192.168.1.50:3306' was successfully removed from the cluster.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

mysql-js> cluster.removeInstance("root@192.168.1.50:3306")

The instance will be removed from the InnoDB cluster. Depending on the

instance being the Seed or not, the Metadata session might become invalid.

If so, please start a new session to the Metadata Storage R/W instance.

The instance 'root@192.168.1.50:3306' was successfully removed from the cluster.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

L’instance n’est alors plus listée dans les méta-données :

mysql-js> \sql
Switching to SQL mode... Commands end with ;


mysql-sql> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;
+-------------------+
| instance_name     |
+-------------------+
| 192.168.1.22:3306 |
| 192.168.1.48:3306 |
+-------------------+
2 rows in set (0.00 sec)

mysql-js> \sql

Switching to SQL mode... Commands end with ;

mysql-sql> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;

+-------------------+

| instance_name |

+-------------------+

| 192.168.1.22:3306 |

| 192.168.1.48:3306 |

+-------------------+

2 rows in set (0.00 sec)

Pour la remettre dans le groupe, il faut donc rejouer le processus de l’ajout d’instance vu plus haut :

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.
The instance is fully recoverable.

{
    "reason": "recoverable", 
    "state": "ok"
}


mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.0/16"})
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@192.168.1.50:3306': 
Adding instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully added to the cluster.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

Please provide the password for 'root@192.168.1.50:3306':

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.

The instance is fully recoverable.

{

"reason": "recoverable",

"state": "ok"

}

mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.0/16"})

A new instance will be added to the InnoDB cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'root@192.168.1.50:3306':

Adding instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully added to the cluster.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Perte du quorum

Si le cluster perd plus de la moitié de ses membres il se retrouve dans un état assez désagréable, network partitioning, en clair il faut une intervention externe au groupe pour permettre aux membres restant de continuer à faire leur boulot de serveur MySQL.

Dans notre cas, avec 3 instances, il faut en perdre 2 d’un coup :

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Perte des nœuds (crash) 192.168.1.22 & 192.168.1.50… (Mayday, Mayday, Mayday!!!)

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "NO_QUORUM", 
        "statusText": "Cluster has no quorum as visible from 'localhost:3306' and cannot process write transactions. 2 members are not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "UNREACHABLE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "UNREACHABLE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "NO_QUORUM",

"statusText": "Cluster has no quorum as visible from 'localhost:3306' and cannot process write transactions. 2 members are not active",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "UNREACHABLE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "UNREACHABLE"

}

Le failover automatique ne peut pas s’enclencher, le membre survivant (192.168.1.48) est en lecture seule. Il faut donc intervenir :

$ mysqlsh --uri=root@192.168.1.48
Creating a Session to 'root@192.168.1.48'
...

mysql-js> var cluster = dba.getCluster("pocCluster")
WARNING: The session is on a Read Only instance.
         Write operations on the InnoDB cluster will not be allowed


mysql-js> cluster.forceQuorumUsingPartitionOf('192.168.1.48:3306')
Restoring replicaset 'default' from loss of quorum, by using the partition composed of [192.168.1.48:3306]

Please provide the password for 'root@192.168.1.48:3306': 
Restoring the InnoDB cluster ...

The InnoDB cluster was successfully restored using the partition from the instance 'root@192.168.1.48:3306'.

WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 2 members are not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }
}

$ mysqlsh --uri=root@192.168.1.48

Creating a Session to 'root@192.168.1.48'

...

mysql-js> var cluster = dba.getCluster("pocCluster")

WARNING: The session is on a Read Only instance.

Write operations on the InnoDB cluster will not be allowed

mysql-js> cluster.forceQuorumUsingPartitionOf('192.168.1.48:3306')

Restoring replicaset 'default' from loss of quorum, by using the partition composed of [192.168.1.48:3306]

Please provide the password for 'root@192.168.1.48:3306':

Restoring the InnoDB cluster ...

The InnoDB cluster was successfully restored using the partition from the instance 'root@192.168.1.48:3306'.

WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

}

Evidemment, sauf si tu es joueur 🙂 , il faut éviter de rester trop longtemps dans cet état.

Une fois les instances en condition, il faut utiliser rejoinInstance() pour remettre les membres dans le cluster, en tant que secondaire (voir paragraphe Remettre un membre dans le groupe).

Repartir après un arrêt total du cluster

La perte du quorum est une chose, mais il y a pire, perdre tout les nœuds…

En cas d’arrêt total du cluster i.e. toutes les instances sont éteintes, il faut utiliser, une fois les instances MySQL de nouveau démarrées rebootClusterFromCompleteOutage() :

$ mysqlsh --uri=root@192.168.1.48
Creating a Session to 'root@mysql_node1'
...

mysql-js> var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')
Reconfiguring the cluster 'pocCluster' from complete outage...

The instance '192.168.1.22:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y

The instance '192.168.1.50:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y


The cluster was successfully rebooted.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

$ mysqlsh --uri=root@192.168.1.48

Creating a Session to 'root@mysql_node1'

...

mysql-js> var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')

Reconfiguring the cluster 'pocCluster' from complete outage...

The instance '192.168.1.22:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y|N]: y

The instance '192.168.1.50:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y|N]: y

The cluster was successfully rebooted.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Le membre sur lequel la commande est exécutée est le nouveau primaire.

Dans la même thématique :

Thanks for using MySQL!

Filed under:
Group Replication, Haute Disponibilité, InnoDB Cluster, MySQL, tuto by Olivier DASINI

Slides du meetup « Mise en bouche PHP »

mai 3, 2017

Mardi soir, j’ai eu le plaisir d’échanger avec la communauté PHP parisienne et de présenter MySQL InnoDB Cluster lors du meetup « Mise en bouche PHP » organisé par l’AFUP Paris et sponsorisé par Oracle MySQL.

Voici les slides

MySQL InnoDB Cluster – Meetup Oracle MySQL / AFUP Paris from Olivier DASINI

Prochain rendez-vous, le 18 mai pour le PHP Tour 2017 à Nantes.

Thanks for using MySQL!

Filed under:
Conférence, Group Replication, Haute Disponibilité, InnoDB Cluster, PHP, Présentation by Olivier DASINI