Configurer ProxySQL pour MySQL Group Replication
Ami lecteur, toi qui t’intéresse à MySQL Group Replication et ProxySQL, une version plus récente de cet article existe : Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication
Cet article s’inspire de HA with MySQL Group Replication and ProxySQL de Frédéric Descamps aka @lefred
Dans un précédent article je vous ai présenté comment déployer un cluster MySQL Group Replication, la nouvelle solution de haute disponibilité de MySQL.
Ce type d’architecture est souvent utilisé avec un composant qui se place entre l’application et le cluster,composant généralement appelé proxy (quelque chose) ou router quelque chose. Dans cet article, je vais vous présenter le meilleur (selon moi) proxy open source du moment : ProxySQL (1.3.2 : la version GA à la date d’écriture).
Le but de cet article est de créer un PoC Solution HA Base de Données Open Source : MySQL Group Replication et ProxySQL.
L’article étant suffisamment long, je ne vais couvrir ni l’installation des nœuds MySQL (5.7.17), ni le déploiement du cluster. Mais comme je suis sympa, voici les ressources nécessaires pour accomplir ces différentes tâches:
- Télécharger MySQL 5.7.17+
- Doc & installation de MySQL
- Déployer un cluster MySQL Group Replication de 3 nœuds
Avertissement!
Ce qui suit est effectué par un professionnel… sur son ordi portable 🙂 Toutes les instances sont en local, car je suis fainéant par commodité.
Attention, à ne pas reproduire tel quel en production.
MySQL Group Replication
MySQL Group Replication se présente comme un plugin embarqué dans MySQL à partir de MySQL 5.7.17.
Caractéristiques
- Version du serveur : 5.7.17
- Version du plugin : 1.0
- Nœud 1 : 127.0.0.1 : 14418
- Nœud 2 : 127.0.0.1 : 14419
- Nœud 3 : 127.0.0.1 : 14420
A partir d’ici, on a donc un cluster MySQL Group Replication de 3 nœuds, installé, déployé et qui fonctionne :
node3 [14420]> SELECT left(version(),6); +-------------------+ | left(version(),6) | +-------------------+ | 5.7.17 | +-------------------+ SELECT * FROM performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 00014418-1111-1111-1111-111111111111 MEMBER_HOST: localhost MEMBER_PORT: 14418 MEMBER_STATE: ONLINE *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 00014419-2222-2222-2222-222222222222 MEMBER_HOST: localhost MEMBER_PORT: 14419 MEMBER_STATE: ONLINE *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 00014420-3333-3333-3333-333333333333 MEMBER_HOST: localhost MEMBER_PORT: 14420 MEMBER_STATE: ONLINE
Le cluster est en mode single primary, c’est à dire qu’un seul nœud est disponible en écriture (à la fois).
node3 [14420]> SHOW VARIABLES LIKE 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ 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: 00014418-1111-1111-1111-111111111111 MEMBER_HOST: localhost MEMBER_PORT: 14418 MEMBER_STATE: ONLINE
Pour pouvoir automatiser le routage des requêtes, mais aussi permettre le failover avec ProxySQL nous allons enrichir le schéma sys de MySQL 5.7.17 avec le code ci-dessous (disponible ici également) :
USE sys;
SET GLOBAL log_bin_trust_function_creators=ON;
DELIMITER $
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
RETURN IF(a = 0, b, a)$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
RETURN GTID_SUBTRACT(g, '')$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
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
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)
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$
DELIMITER ;
SET GLOBAL log_bin_trust_function_creators=OFF;
Vous pouvez retrouver ce bout de code (version originale) et bien plus encore dans l’article du Product Manager de MySQL Group Replication, Matt Lord : MySQL Group Replication: A Quick Start Guide
Important
Dans la version originale du script il faut passer la commande SET GLOBAL log_bin_trust_function_creators=ON; sur TOUT les nœuds du cluster, avant de jouer le script qui contient le code ci-dessus :
node[2|3] (secondaries)> SET GLOBAL log_bin_trust_function_creators=ON;
node[1](primary)> SET GLOBAL log_bin_trust_function_creators=ON; source Group_Replication_helper_functions_and_views.sql SET GLOBAL log_bin_trust_function_creators=OFF;
node[2|3] (secondaries)> SET GLOBAL log_bin_trust_function_creators=OFF;
ProxySQL
Caractéristiques
- Version du proxy : 1.3.2.0
Dans le cadre de cet article je ne vais aborder que les fonctionnalités qui relative au routage et failover de ProxySQL. Je vous invite cependant à lire la documentation.
Les grandes étapes pour la préparation de notre environnement sont :
Téléchargement
$ wget https://github.com/sysown/proxysql/releases/download/v1.3.2/proxysql_1.3.2-ubuntu16_amd64.deb
Installation
$ dpkg -i proxysql_1.3.2-ubuntu16_amd64.deb
Démarrage du service
$ service proxysql start
Et voilà le travail!
ProxySQL est installé et fonctionnel :
$ service proxysql status ? proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled) Active: active (running) since Mon 2017-01-02 11:03:20 CET; 4h 50min ago ...
Faisons un point. A ce stade on a :
- déployé un cluster MySQL Group Replication de 3 nœuds
- ajouté des fonctions et des vues dans sys schema (Group_Replication_helper_functions_and_views.sql)
- installé ProxySQL
Il nous reste pas mal de choses intéressantes à voir. Cependant, avant de passer à la suite regardons à quoi ressemble notre architecture cible :
L’application se connecte à ProxySQL qui redirige les requêtes sur les bons nœuds. Les flèches rouges représentent les écritures et les vertes les lectures.
La suite ? on va donc maintenant rendre ProxySQL conscient de MySQL Group Replication.
Pour se faire on va se connecter à ProxySQL en mode administrateur (utilisateur admin) :
$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Proxy> ' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2016, 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;' or '\h' for help. Type '\c' to clear the current input statement.
La syntaxe est celle de MySQL \o/
Un DBA se sent un peu comme à la maison et c’est plutôt agréable.
Voyons un peu ce qu’il y a sous la surface :
Proxy> SHOW SCHEMAS; +-----+---------+-------------------------------+ | seq | name | file | +-----+---------+-------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | +-----+---------+-------------------------------+
ProxySQL contient 4 schémas (base de données). En voici une description succincte:
- main : la configuration courante
- disk : configuration persistante (stockée sur le disque)
- stats : statistiques liées à l’utilisation du proxy
- monitor : données collectée en vue de la supervision
Succinct, comme convenu!
Suffisant néanmoins dans le cadre de cet article, car nous allons principalement utiliser les tables du schéma main. tu veux en savoir plus ? RTFM !
Configuration des groupes dans ProxySQL
ProxySQL utilise la notion de Hostgroup pour regrouper de manière logique des serveurs par type. Ils sont définit dans la table main.mysql_servers :
Proxy>
SHOW CREATE TABLE main.mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
Comme vu sur le schéma, on va séparer les lectures des écritures. Pour se faire on va donc créer 2 groupes, l’un dédié aux écritures et l’autres… aux lectures :
Proxy> -- Hostgroup 1 <=> Writes INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14418); INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14419); INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14420); -- Hostgroup 2 <=> Reads INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14418); INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14419); INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14420); -- Load & save config LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; -- Check SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers; +--------------+-----------+-------+--------+ | hostgroup_id | hostname | port | status | +--------------+-----------+-------+--------+ | 1 | 127.0.0.1 | 14418 | ONLINE | | 1 | 127.0.0.1 | 14419 | ONLINE | | 1 | 127.0.0.1 | 14420 | ONLINE | | 2 | 127.0.0.1 | 14418 | ONLINE | | 2 | 127.0.0.1 | 14419 | ONLINE | | 2 | 127.0.0.1 | 14420 | ONLINE | +--------------+-----------+-------+--------+
Supervision
ProxySQL comprend un module de supervision (monitoring) qui a pour utilisateur et mot de passe par défaut, respectivement monitor et monitor (oui 2 fois monitor). Cet utilisateur doit bien évidemment exister au niveau des membres du cluster et il n’a besoin que du privilège MySQL USAGE, afin de pinger et vérifier la variable read_only.
Le privilège REPLICATION CLIENT est cependant nécessaire s’il doit superviser aussi le retard de réplication.
Proxy> -- Set user name & password for monitoring module SET mysql-monitor_username='monitoring'; SET mysql-monitor_password='Very-S3cr3t'; -- Load & save config LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
Scheduler
Le scheduler permet d’exécuter des scripts externes à ProxySQL. Nous avons besoin d’un script pour superviser les membres du cluster pour le routage des requêtes et le failover. Ce script est disponible sur mon repo GitHub : proxysql_groupreplication_checker.sh et doit être placé dans « /var/lib/proxysql/ »
Petit rappel: ce script n’est fournit que pour l’exemple. Il ne doit pas être utiliser tel quel en production.
Note
Ce script est légèrement modifié par rapport à la version originale que Lefred propose : https://github.com/lefred/proxysql_groupreplication_checker pour pouvoir fonctionner dans le cadre de ce PoC avec ProxySQL 1.3.2.
Les jobs à exécuter sont stockés dans la table scheduler du schema main :
Proxy> SHOW CREATE TABLE main.scheduler\G *************************** 1. row *************************** table: scheduler Create Table: CREATE TABLE scheduler ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL, filename VARCHAR NOT NULL, arg1 VARCHAR, arg2 VARCHAR, arg3 VARCHAR, arg4 VARCHAR, arg5 VARCHAR, comment VARCHAR NOT NULL DEFAULT '')
Quelques infos pour la route :
- arg1 identifiant d’hostgroup pour les écritures
- arg2 identifiant d’hostgroup pour les lectures
- arg3 nombre d’écrivain actif simultanément
- arg4 booléen qui permet de choisir si le membre actif pour les écritures est aussi candidat pour les lectures
- arg5 fichier de log
Paramétrons le scheduler :
Proxy> INSERT INTO main.scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log'); -- Load & save config SAVE SCHEDULER TO DISK; LOAD SCHEDULER TO RUNTIME; SELECT * FROM main.scheduler\G *************************** 1. row *************************** id: 1 active: 1 interval_ms: 10000 filename: /var/lib/proxysql/proxysql_groupreplication_checker.sh arg1: 1 arg2: 2 arg3: 1 arg4: 0 arg5: /var/lib/proxysql/proxysql_groupreplication_checker.log comment:
Et on peut constater que le script à modifié le statut des membres du cluster :
Proxy> SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers WHERE hostgroup_id ; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | ONLINE | 0 | | 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14419 | ONLINE | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
En clair, du point de vue de l’application, qui se connecte au proxy :
- 14418 aka node 1 (primaire) : accessible en écriture / inaccessible en lecture
- 14419 aka node 2 (secondaire) : inaccessible en écriture / accessible en lecture
- 14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture
On est bien !
Test du failover
A ce stade on a tout ce qu’il faut pour tester le comportement du proxy en cas de perte du primaire. Chiche?
Vérification du statut des membres à l’aide de la supervision ProxySQL (perspective du proxy) :
Proxy> SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | ONLINE | 0 | | 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14419 | ONLINE | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
Vérification de l’identité du nœud primaire (perspective du cluster) :
node2 [14419]> -- Find 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: 00014418-1111-1111-1111-111111111111 MEMBER_HOST: localhost MEMBER_PORT: 14418 MEMBER_STATE: ONLINE
Vérification du statut des membres en contactant les nœuds directement (perspective client) :
$
for port in 14418 14419 14420; do
mysql -P$port --protocol=TCP -BNe"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');" 2> /dev/null;
echo;
done;
port 14418
read_only OFF
super_read_only OFF
port 14419
read_only ON
super_read_only ON
port 14420
read_only ON
super_read_only ON
Il y a manifestement un consensus pour dire que :
- Membre 1, port 11418 est le nœud primaire, en mode lecture/écriture (mais lecture seule pour ProxySQL)
- Membre 2 (port 14419) & 3 (port 14420) sont des nœuds secondaires, en mode lecture seule.
Note
En fonction de votre configuration un utilisateur et un mot de passe peuvent être requis
La première action de notre test consiste en l’ arrêt du nœud primaire (node 1, port 14418) :
node1 [14418]> SHUTDOWN; Query OK, 0 rows affected (0,00 sec)
MySQL Group Replication va donc élire un nouveau membre primaire :
node2 [14419]> -- Find 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: 00014419-2222-2222-2222-222222222222 MEMBER_HOST: localhost MEMBER_PORT: 14419 MEMBER_STATE: ONLINE
Le nœud 2 (port 14419) est donc le nouveau membre primaire du cluster.
Une autre vue de ce nouveau statut :
$
for port in 14418 14419 14420; do
mysql -P$port --protocol=TCP -BNe"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');" 2> /dev/null;
echo;
done;
port 14419
read_only OFF
super_read_only OFF
port 14420
read_only ON
super_read_only ON
En ce qui concerne la vision ProxySQL :
Proxy> SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14419 | ONLINE | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
Confirmation, avec ce nouvel état :
- 14418 aka node 1 (down) : inaccessible
- 14419 aka node 2 (primaire) : accessible en écriture / inaccessible en lecture
- 14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture
Que c’est beau l’informatique… quand ça fonctionne.
Que se passe t’il si le membre 1 revient ?
Proxy> SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14419 | ONLINE | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | ONLINE | 0 | | 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
Le proxy nous montre un nouvel état, une fois le nœud 1 de retour dans le cluster :
- 14418 aka node 1 (secondaire) : inaccessible en écriture / accessible en lecture
- 14419 aka node 2 (primaire) : accessible en écriture / inaccessible en lecture
- 14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture
Pour la suite de l’artcile, le membre 1 (14418) est à nouveau le nœud primaire.
Ci dessous extraits de quelques états du proxy durant la phase de failover :
Proxy> SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | ONLINE | 0 | | 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
## few seconds…
SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | ONLINE | 0 | | 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
## few seconds…
SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers; +--------------+-----------+-------+--------------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------+-------+--------------+---------------------+ | 1 | 127.0.0.1 | 14418 | ONLINE | 0 | | 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 | | 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 | | 2 | 127.0.0.1 | 14419 | ONLINE | 0 | | 2 | 127.0.0.1 | 14420 | ONLINE | 0 | +--------------+-----------+-------+--------------+---------------------+
Règles de routage
La table mysql_query_rules du schema main va contenir les règles de routages. C’est donc là que nous allons pouvoir séparer les requêtes d’écriture des requêtes de lecture :
Proxy> SHOW CREATE TABLE main.mysql_query_rules\G *************************** 1. row *************************** table: mysql_query_rules Create Table: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, username VARCHAR, schemaname VARCHAR, flagIN INT NOT NULL DEFAULT 0, client_addr VARCHAR, proxy_addr VARCHAR, proxy_port INT, digest VARCHAR, match_digest VARCHAR, match_pattern VARCHAR, negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0, flagOUT INT, replace_pattern VARCHAR, destination_hostgroup INT DEFAULT NULL, cache_ttl INT CHECK(cache_ttl > 0), reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL, timeout INT UNSIGNED, retries INT CHECK (retries>=0 AND retries <=1000), delay INT UNSIGNED, mirror_flagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, log INT CHECK (log IN (0,1)), apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0, comment VARCHAR)
Pour ce PoC nos règles seront simples et définies à base de regex :
- Groupe lecture : requêtes commençant par le mot clé SELECT (^SELECT) vont dans le hostgroup 2.
- Groupe écriture : toutes les autres requêtes (DDL, DML,…), ainsi que les SELECT contenant la clause FOR UPDATE (^SELECT.*FOR UPDATE$) vont dans le hostgroup 1.
Si une requête ne correspond à aucune des règles, elle sera dirigée vers le hostgroup par défaut (champ default_hostgroup de la table mysql_users) de l’utilisateur qui exécute la requête.
Regardons la structure de la table mysql_users :
Proxy> SHOW CREATE TABLE main.mysql_users\G *************************** 1. row *************************** table: mysql_users Create Table: CREATE TABLE mysql_users ( username VARCHAR NOT NULL, password VARCHAR, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0, default_hostgroup INT NOT NULL DEFAULT 0, default_schema VARCHAR, schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0, transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0, fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0, backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1, frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1, max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000, PRIMARY KEY (username, backend), UNIQUE (username, frontend))
Créons l’utilisateur MySQL utilisé pour se connecter au backend (app_user / app_pwd) :
Proxy>
SELECT username, password, active, default_hostgroup FROM main.mysql_users;
Empty set (0,00 sec)
INSERT INTO main.mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'app_pwd', 1);
SELECT username, password, active, default_hostgroup FROM main.mysql_users;
+----------+----------+--------+-------------------+
| username | password | active | default_hostgroup |
+----------+----------+--------+-------------------+
| app_user | app_pwd | 1 | 1 |
+----------+----------+--------+-------------------+
-- Load & save config
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
Maintenant nous pouvons renseigner la table mysql_query_rules avec nos 2 règles de routages (écriture et lecture) :
Proxy> -- Rule for writes : hostgroup 1 INSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, "^SELECT .* FOR UPDATE", 1, 1); -- Rule for reads : hostgroup 2 INSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, "^SELECT ", 2, 1); -- Load & save config LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; -- Check SELECT rule_id, active, match_pattern, destination_hostgroup FROM main.mysql_query_rules; +---------+--------+-----------------------+-----------------------+ | rule_id | active | match_pattern | destination_hostgroup | +---------+--------+-----------------------+-----------------------+ | 1 | 1 | ^SELECT .* FOR UPDATE | 1 | | 2 | 1 | ^SELECT | 2 | +---------+--------+-----------------------+-----------------------+
Et voilà!
Il est temps de passer aux choses sérieuses.
Playtime
Toutes les briques de notre archi sont assemblées et configurées, c’est donc l’heure du test final.
Notre appli, simulée par un script bash, va se connecter au proxy (ProxySQL) pour interroger la base de données distribuée (MySQL Group Replication).
Au fait on fait comment pour se connecter au proxy ?
Proxy> SHOW VARIABLES LIKE 'mysql-interfaces'\G *************************** 1. row *************************** Variable_name: mysql-interfaces Value: 0.0.0.0:6033;/tmp/proxysql.sock
Pour se connecter à la base de données, par l’intermédiaire du proxy, l’application peut soit utiliser le port 6033, soit le socket /tmp/proxysql.sock :
$
for x in {1..10}; do
mysql -u app_user -h127.0.0.1 -P6033 -BNe"SELECT @@port;" ;
done;
14420
14420
14420
14420
14419
14419
14420
14419
14419
14419
Comme prévu, les requêtes de lecture se connectent sur l’un des 2 nœuds secondaires (node 2 : port 14419 || node 3: port 14420).
Allons plus loin dans le test et créons la table test.poc :
app [6033]> CREATE TABLE test.poc ( id mediumint(9) unsigned NOT NULL AUTO_INCREMENT, port mediumint unsigned, time timestamp, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Query OK, 0 rows affected (0,08 sec)
L’application test jouera les 2 requêtes suivante :
- INSERT INTO test.poc (port) VALUES (@@port);
- SELECT * FROM test.poc;
Grâce à ces 2 requêtes et le petit scripts ci-dessous, on va être en mesure de suivre les processus de routage et de failover.
En clair :
- écriture sur le primaire
- lectures sur le(s) secondaire(s)
- crash du master
- failover automatique et transparent
$ while true; do mysql -u app_user -h127.0.0.1 -P6033 -BNe"INSERT INTO test.poc (port) VALUES (@@port); SELECT *, @@port FROM test.poc;"; echo; sleep 4; done 3 14418 2017-01-04 16:51:05 14419 <=> Row 1 3 14418 2017-01-04 16:51:05 14419 10 14418 2017-01-04 16:51:09 14419 <=> Row 2 3 14418 2017-01-04 16:51:05 14420 10 14418 2017-01-04 16:51:09 14420 17 14418 2017-01-04 16:51:13 14420 <=> Row 3 3 14418 2017-01-04 16:51:05 14420 10 14418 2017-01-04 16:51:09 14420 17 14418 2017-01-04 16:51:13 14420 18 14419 2017-01-04 16:51:21 14420 <=> Row 4 => Failover, new primary node (14419) 3 14418 2017-01-04 16:51:05 14420 10 14418 2017-01-04 16:51:09 14420 17 14418 2017-01-04 16:51:13 14420 18 14419 2017-01-04 16:51:21 14420 25 14419 2017-01-04 16:51:25 14420 <=> Row 5
Revoyons la scène au ralenti…
- Row 1 : Ecriture sur 14418, Lecture sur 14419
- Row 2 : Ecriture sur 14418, Lecture sur 14419
- Row 3 : Ecriture sur 14418, Lecture sur 14420
- Row 4 : Ecriture sur 14419, Lecture sur 14420
- Le membre 14418 est stoppé, failover automatique de MySQL Group Replication et ProxySQL redirige les écritures sur 14419
- Row 5 : Ecriture sur 14419, Lecture sur 14420
Trop cool !
Le mot de la fin
Récapitulons: pour ce PoC, une architecture ProxySQL 1.3 avec MySQL Group Replication s’est montée en plusieurs étapes :
- Déployement d’un cluster MySQL Group Replication (3 ou 5 nœuds en général)
- Enrichissement de sys schema afin gérer le routage et le failover
- Installation et paramétrage de ProxySQL
- Ajout du job de routage et de failover
L’étape 2 ne sera plus nécessaire à terme, sys schema sera nativement compatible avec Goup Replication dans une prochaine version de MySQL.
En ce qui concerne les étapes 3 et 4, elles vont être simplifiées car ProxySQL 1.4 qui est en cours de développement, est nativement compatible Group Replication.
Et surtout n’oubliez pas que MySQL InnoDB Cluster est la solution maison tout en un, qui simplifie grandement le déploiement de ce type d’architecture. Pour rappel InnoDB Cluster c’est :
Vous pouvez d’ores et déjà tester la dernière pré-version (labs preview) de MySQL InnoDB Cluster: MySQL InnoDB Cluster 5.7.17 Preview 2
C’est inutile mais je vais le rappeler quand même : il n’est pas recommandé d’utiliser les binaires du Labs en production.
Références
MySQL Group Replication
- Déployer un cluster MySQL Group Replication
- Configurer ProxySQL pour MySQL Group Replication
- FAQ Webinar MySQL Group Replication
- Documentation de MySQL Group Replication
- MySQL Group Replication: A Quick Start Guide
ProxySQL
MySQL InnoDB Cluster
- Tester MySQL InnoDB Cluster
- Adopte un… cluster MySQL Group Replication
- Documentation de MySQL InnoDB Cluster
- MySQL InnoDB Cluster – What’s New in the 5.7.17 Preview Release
- MySQL InnoDB Cluster – A Hands on Tutorial
- MySQL InnoDB Cluster – Navigating the Cluster
- MySQL InnoDB Cluster – Setting up InnoDB Cluster on Windows
Thanks for using MySQL!
Architecte Solution Cloud chez Oracle
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] Configurer ProxySQL pour MySQL Group Replication […]
[…] 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 […]
[…] Configurer ProxySQL pour MySQL Group Replication […]
[…] is compatible with MySQL Group Replication since version 1.3 see (in French) : Configurer ProxySQL pour MySQL Group Replication. However, version 1.4 have a native MySQL Group Replication support. It is therefore easier to use […]