Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication

janvier 9, 2018

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

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

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 https://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 5.7 InnoDB Cluster

References

dasini.net

MySQL Group Replication

ProxySQL

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

2 Responses to “Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication”

Configurer ProxySQL pour MySQL Group Replication | dasini.net - Journal d'un expert MySQL

janvier 9th, 2018 at 12:16

[…] 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 […]
Setting up ProxySQL 1.4 with MySQL 5.7 Group Replication | dasini.net - Journal d'un expert MySQL

janvier 22nd, 2018 at 10:59

[…] Lire cet article en français […]

dasini.net – Journal d'un expert MySQL