Configurer ProxySQL pour MySQL Group Replication

janvier 11, 2017

Tags: group replication, ha, high availability, InnoDB Cluster, proxy, ProxySQL, tuto

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:

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

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

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;

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;

1 2	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[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;

1 2	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

1 2	$ 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

1 2	$ dpkg -i proxysql_1.3.2-ubuntu16_amd64.deb

Démarrage du service

$
service proxysql start

1 2	$ 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
...

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

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)

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 |                               |
+-----+---------+-------------------------------+

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

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 |
+--------------+-----------+-------+--------+

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;

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

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

| 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;

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

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:

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                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers WHERE hostgroup_id ;

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

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

| 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                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;

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

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

| 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

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

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)

1 2	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

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

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                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers;

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

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

| 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                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 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                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 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                   |
+--------------+-----------+-------+--------------+---------------------+

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 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                   |
+--------------+-----------+-------+--------------+---------------------+

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

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

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

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;

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;

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

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

| 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                     |
+---------+--------+-----------------------+-----------------------+

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;

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

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

| 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

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

for x in {1..10}; do

mysql -u app_user -h127.0.0.1 -P6033 -BNe"SELECT @@port;" ;

done;

14420

14419

14420

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)

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

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

ProxySQL

MySQL InnoDB Cluster

Thanks for using MySQL!

Olivier DASINI

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
—–

4 Responses to “Configurer ProxySQL pour MySQL Group Replication”

FAQ Webinar MySQL Group Replication | dasini.net - Journal d'un expert MySQL

mars 7th, 2017 at 16:53

[…] Configurer ProxySQL pour MySQL Group Replication […]
Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication | dasini.net - Journal d'un expert MySQL

janvier 9th, 2018 at 9:53

[…] 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 […]
Adopte un… cluster MySQL Group Replication | dasini.net - Journal d'un expert MySQL

janvier 11th, 2018 at 20:13

[…] Configurer ProxySQL pour MySQL Group Replication […]
Setting up ProxySQL 1.4 with MySQL 5.7 Group Replication | dasini.net - Journal d'un expert MySQL

août 21st, 2018 at 9:57

[…] 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 […]

dasini.net – Journal d'un expert MySQL