{"id":1746,"date":"2017-01-11T12:06:52","date_gmt":"2017-01-11T11:06:52","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1746"},"modified":"2024-12-10T15:51:12","modified_gmt":"2024-12-10T14:51:12","slug":"configurer-proxysql-pour-mysql-group-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/","title":{"rendered":"Configurer ProxySQL pour MySQL Group Replication"},"content":{"rendered":"<p>Ami lecteur, toi qui t\u2019int\u00e9resse \u00e0 MySQL Group Replication et ProxySQL, une version plus r\u00e9cente de cet article existe : <a href=\"http:\/\/dasini.net\/blog\/2018\/01\/09\/configurer-proxysql-1-4-pour-mysql-5-7-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication<\/a><\/p>\n<p><em><small>Cet article s&rsquo;inspire de <a href=\"http:\/\/lefred.be\/content\/ha-with-mysql-group-replication-and-proxysql\/\" target=\"_blank\" rel=\"noopener\">HA with MySQL Group Replication and ProxySQL<\/a> de <a href=\"http:\/\/lefred.be\/\" target=\"_blank\" rel=\"noopener\">Fr\u00e9d\u00e9ric Descamps<\/a> aka\u00a0<a class=\"ProfileHeaderCard-screennameLink u-linkComplex js-nav\" href=\"https:\/\/twitter.com\/lefred\" target=\"_blank\" rel=\"noopener\">@<span class=\"u-linkComplex-target\">lefred<\/span><\/a><\/small><\/em><\/p>\n<p>Dans un pr\u00e9c\u00e9dent article je vous ai pr\u00e9sent\u00e9 comment<a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\"> d\u00e9ployer un cluster MySQL Group Replication<\/a>, la nouvelle solution de <a href=\"https:\/\/www.mysql.com\/products\/enterprise\/high_availability.html\" target=\"_blank\" rel=\"noopener\">haute disponibilit\u00e9<\/a> de MySQL.<\/p>\n<p>Ce type d&rsquo;architecture est souvent utilis\u00e9 avec un composant qui se place entre l&rsquo;application et le cluster,composant g\u00e9n\u00e9ralement appel\u00e9 <strong>proxy<\/strong> (quelque chose) ou <strong>router<\/strong> quelque chose. Dans cet article, je vais vous pr\u00e9senter le meilleur (selon moi) <a href=\"http:\/\/www.proxysql.com\/compare\" target=\"_blank\" rel=\"noopener\">proxy open source<\/a> du moment :\u00a0<a href=\"http:\/\/www.proxysql.com\/\" target=\"_blank\" rel=\"noopener\">ProxySQL<\/a>\u00a0(<strong>1.3.2<\/strong>\u00a0: la version GA \u00e0 la date d&rsquo;\u00e9criture).<\/p>\n<p>Le but de cet article est de cr\u00e9er un PoC <strong>Solution HA Base de Donn\u00e9es Open Source<\/strong> : <strong>MySQL Group Replication<\/strong> et <strong>ProxySQL<\/strong>.<\/p>\n<p>L&rsquo;article \u00e9tant suffisamment long, je ne vais couvrir ni l&rsquo;installation des n\u0153uds MySQL (5.7.17), ni le d\u00e9ploiement du cluster. Mais comme je suis sympa, voici les ressources n\u00e9cessaires pour accomplir ces diff\u00e9rentes t\u00e2ches:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.mysql.com\/downloads\/\" target=\"_blank\" rel=\"noopener\">T\u00e9l\u00e9charger\u00a0MySQL 5.7.17+<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/installing.html\" target=\"_blank\" rel=\"noopener\">Doc &amp; installation de MySQL<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">D\u00e9ployer un cluster MySQL Group Replication de 3 n\u0153uds<\/a><\/li>\n<\/ul>\n<p><em><span style=\"text-decoration: underline;\">Avertissement<\/span>!<\/em><br \/>\n<em> Ce qui suit est effectu\u00e9 par un professionnel&#8230; sur son ordi portable \ud83d\ude42\u00a0Toutes les instances sont en local, <del>car je suis fain\u00e9ant<\/del> par commodit\u00e9.<\/em><br \/>\n<em> Attention, \u00e0 ne\u00a0<strong>pas<\/strong> reproduire tel quel en production.\u00a0<\/em><\/p>\n<h2>MySQL Group Replication<\/h2>\n<p>MySQL Group Replication se pr\u00e9sente comme un plugin embarqu\u00e9 dans MySQL \u00e0 partir de MySQL 5.7.17.<\/p>\n<p><span style=\"text-decoration: underline;\">Caract\u00e9ristiques<\/span><\/p>\n<ul>\n<li>Version du serveur : 5.7.17<\/li>\n<li>Version du plugin : 1.0<\/li>\n<li>N\u0153ud 1 : 127.0.0.1 : 14418<\/li>\n<li>N\u0153ud 2 : 127.0.0.1 : 14419<\/li>\n<li>N\u0153ud 3 : 127.0.0.1 : 14420<\/li>\n<\/ul>\n<p>A partir d&rsquo;ici, on a donc un cluster MySQL Group Replication de 3 n\u0153uds, install\u00e9, d\u00e9ploy\u00e9 et qui fonctionne :<\/p>\n<pre class=\"lang:mysql decode:true\">node3 [14420]&gt;\nSELECT left(version(),6);\n+-------------------+\n| left(version(),6) |\n+-------------------+\n| 5.7.17            |\n+-------------------+\n\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014418-1111-1111-1111-111111111111\nMEMBER_HOST: localhost\nMEMBER_PORT: 14418\nMEMBER_STATE: ONLINE\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014419-2222-2222-2222-222222222222\nMEMBER_HOST: localhost\nMEMBER_PORT: 14419\nMEMBER_STATE: ONLINE\n*************************** 3. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014420-3333-3333-3333-333333333333\nMEMBER_HOST: localhost\nMEMBER_PORT: 14420\nMEMBER_STATE: ONLINE<\/pre>\n<p>Le cluster est en mode single primary, c&rsquo;est \u00e0 dire qu&rsquo;un seul n\u0153ud est disponible en \u00e9criture (\u00e0 la fois).<\/p>\n<pre class=\"lang:mysql decode:true\">node3 [14420]&gt;\nSHOW VARIABLES LIKE 'group_replication_single_primary_mode';\n+---------------------------------------+-------+\n| Variable_name                         | Value |\n+---------------------------------------+-------+\n| group_replication_single_primary_mode | ON    |\n+---------------------------------------+-------+\n\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE\nFROM performance_schema.replication_group_members\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nMEMBER_ID: 00014418-1111-1111-1111-111111111111\nMEMBER_HOST: localhost\nMEMBER_PORT: 14418\nMEMBER_STATE: ONLINE<\/pre>\n<p>Pour pouvoir automatiser le routage des requ\u00eates, mais aussi permettre le failover avec ProxySQL nous allons\u00a0enrichir le sch\u00e9ma <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/sys-schema.html\" target=\"_blank\" rel=\"noopener\"><em>sys<\/em><\/a> de MySQL 5.7.17 avec le code ci-dessous (<a href=\"https:\/\/github.com\/freshdaz\/proxysql_groupreplication_checker\/blob\/master\/Group_Replication_helper_functions_and_views.sql\" target=\"_blank\" rel=\"noopener\">disponible ici \u00e9galement<\/a>) :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Group_Replication_helper_functions_and_views.sql\">USE sys;\n\nSET GLOBAL log_bin_trust_function_creators=ON;\n\nDELIMITER $\n\nCREATE FUNCTION IFZERO(a INT, b INT)\n  RETURNS INT\nRETURN IF(a = 0, b, a)$\n\nCREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)\n  RETURNS INT\nRETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$\n\nCREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))\n  RETURNS TEXT(10000)\nRETURN GTID_SUBTRACT(g, '')$\n\nCREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))\n  RETURNS INT\n  BEGIN\n    DECLARE result BIGINT DEFAULT 0;\n    DECLARE colon_pos INT;\n    DECLARE next_dash_pos INT;\n    DECLARE next_colon_pos INT;\n    DECLARE next_comma_pos INT;\n    SET gtid_set = GTID_NORMALIZE(gtid_set);\n    SET colon_pos = LOCATE2(':', gtid_set, 1);\n    WHILE colon_pos != LENGTH(gtid_set) + 1 DO\n      SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);\n      SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);\n      SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);\n      IF next_dash_pos &lt; next_colon_pos AND next_dash_pos &lt; next_comma_pos THEN\n        SET result = result +\n          SUBSTR(gtid_set, next_dash_pos + 1,\n            LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -\n          SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;\n      ELSE\n        SET result = result + 1;\n      END IF;\n      SET colon_pos = next_colon_pos;\n    END WHILE;\n    RETURN result;\nEND$\n\nCREATE FUNCTION gr_applier_queue_length()\n  RETURNS INT\n  BEGIN\n    RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT\nReceived_transaction_set FROM performance_schema.replication_connection_status\nWHERE Channel_name = 'group_replication_applier' ), (SELECT\n@@global.GTID_EXECUTED) )));\nEND$\n\nCREATE FUNCTION gr_member_in_primary_partition()\n  RETURNS VARCHAR(3)\n  BEGIN\n    RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM\nperformance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') &gt;=\n((SELECT COUNT(*) FROM performance_schema.replication_group_members)\/2) = 0),\n'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN\nperformance_schema.replication_group_member_stats USING(member_id));\nEND$\n\nCREATE VIEW gr_member_routing_candidate_status AS SELECT\nsys.gr_member_in_primary_partition() as viable_candidate,\nIF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM\nperformance_schema.global_variables WHERE variable_name IN ('read_only',\n'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,\nsys.gr_applier_queue_length() as transactions_behind$\n\nDELIMITER ;\n\nSET GLOBAL log_bin_trust_function_creators=OFF;\n<\/pre>\n<p>Vous pouvez retrouver ce bout de code (version originale) et bien plus encore dans l&rsquo;article du Product Manager de MySQL Group Replication, <a href=\"https:\/\/twitter.com\/mattalord\" target=\"_blank\" rel=\"noopener\">Matt Lord<\/a> : <a href=\"http:\/\/mysqlhighavailability.com\/mysql-group-replication-a-quick-start-guide\/\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication: A Quick Start Guide<\/a><\/p>\n<p><em><span style=\"text-decoration: underline;\">Important<br \/>\n<\/span><\/em><em>Dans la version originale du script il faut passer la commande <strong>SET GLOBAL log_bin_trust_function_creators=ON;<\/strong> sur TOUT les n\u0153uds du cluster, avant de jouer le script qui contient le code ci-dessus :<\/em><\/p>\n<pre class=\"lang:mysql decode:true\">node[2|3] (secondaries)&gt;\nSET GLOBAL log_bin_trust_function_creators=ON;\n<\/pre>\n<pre class=\"lang:mysql decode:true\">node[1](primary)&gt;\nSET GLOBAL log_bin_trust_function_creators=ON;\nsource Group_Replication_helper_functions_and_views.sql\nSET GLOBAL log_bin_trust_function_creators=OFF;<\/pre>\n<pre class=\"lang:mysql decode:true\">node[2|3] (secondaries)&gt;\nSET GLOBAL log_bin_trust_function_creators=OFF;<\/pre>\n<h2>ProxySQL<\/h2>\n<p><span style=\"text-decoration: underline;\">Caract\u00e9ristiques<\/span><\/p>\n<ul>\n<li>Version du proxy\u00a0: 1.3.2.0<\/li>\n<\/ul>\n<p>Dans le cadre de cet article je ne vais aborder que les fonctionnalit\u00e9s qui relative au routage et failover de ProxySQL. Je vous invite cependant \u00e0 lire la <a href=\"https:\/\/github.com\/sysown\/proxysql\/wiki\" target=\"_blank\" rel=\"noopener\">documentation<\/a>.<\/p>\n<p>Les grandes \u00e9tapes pour la pr\u00e9paration de notre environnement sont :<\/p>\n<p><span style=\"text-decoration: underline;\">T\u00e9l\u00e9chargement<\/span><\/p>\n<pre class=\"lang:sh decode:true\">$\nwget https:\/\/github.com\/sysown\/proxysql\/releases\/download\/v1.3.2\/proxysql_1.3.2-ubuntu16_amd64.deb<\/pre>\n<p><span style=\"text-decoration: underline;\">Installation<\/span><\/p>\n<pre class=\"lang:sh decode:true\">$\ndpkg -i proxysql_1.3.2-ubuntu16_amd64.deb<\/pre>\n<p><span style=\"text-decoration: underline;\">D\u00e9marrage du service<\/span><\/p>\n<pre class=\"lang:sh decode:true\">$\nservice proxysql start<\/pre>\n<p>Et voil\u00e0 le travail!<\/p>\n<p>ProxySQL est install\u00e9 et fonctionnel :<\/p>\n<pre class=\"lang:sh decode:true\">$ service proxysql status\n? proxysql.service - LSB: High Performance Advanced Proxy for MySQL\nLoaded: loaded (\/etc\/init.d\/proxysql; bad; vendor preset: enabled)\nActive: active (running) since Mon 2017-01-02 11:03:20 CET; 4h 50min ago\n...<\/pre>\n<p>Faisons un point. A ce stade on a :<\/p>\n<ul>\n<li>d\u00e9ploy\u00e9 un cluster <strong>MySQL Group Replication<\/strong> de 3 n\u0153uds<\/li>\n<li>ajout\u00e9 des fonctions et des vues dans <strong>sys schema<\/strong> (Group_Replication_helper_functions_and_views.sql)<\/li>\n<li>install\u00e9 <strong>ProxySQL<\/strong><\/li>\n<\/ul>\n<p>Il nous reste pas mal de choses int\u00e9ressantes \u00e0 voir. Cependant, avant de passer \u00e0 la suite regardons \u00e0 quoi ressemble notre architecture\u00a0cible :<\/p>\n<p><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_Group_Replication_and_ProxySQL.png?resize=364%2C511\" alt=\"\" width=\"364\" height=\"511\" \/><\/p>\n<p>L&rsquo;application se connecte \u00e0 ProxySQL qui redirige les requ\u00eates sur les bons n\u0153uds. Les fl\u00e8ches rouges repr\u00e9sentent les \u00e9critures et les vertes les lectures.<\/p>\n<p>La suite ? on va donc maintenant rendre ProxySQL conscient de MySQL Group Replication.<br \/>\nPour se faire on va se connecter \u00e0 ProxySQL en mode administrateur (utilisateur admin) :<\/p>\n<pre class=\"lang:sh decode:true\">$\nmysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Proxy&gt; '\n\nmysql: [Warning] Using a password on the command line interface can be insecure.\nWelcome to the MySQL monitor. Commands end with ; or \\g.\nYour MySQL connection id is 2\nServer version: 5.5.30 (ProxySQL Admin Module)\n\nCopyright (c) 2000, 2016, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.<\/pre>\n<p>La syntaxe est celle de MySQL \\o\/<\/p>\n<p>Un DBA se sent un peu comme \u00e0 la maison et c&rsquo;est plut\u00f4t agr\u00e9able.<\/p>\n<p>Voyons un peu ce qu&rsquo;il y a sous la surface :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSHOW SCHEMAS;\n+-----+---------+-------------------------------+\n| seq | name    | file                          |\n+-----+---------+-------------------------------+\n| 0   | main    |                               |\n| 2   | disk    | \/var\/lib\/proxysql\/proxysql.db |\n| 3   | stats   |                               |\n| 4   | monitor |                               |\n+-----+---------+-------------------------------+<\/pre>\n<p>ProxySQL contient 4 sch\u00e9mas (base de donn\u00e9es). En voici une description succincte:<\/p>\n<ul>\n<li><em>main<\/em> : la configuration courante<\/li>\n<li><em>disk<\/em> : configuration persistante (stock\u00e9e sur le disque)<\/li>\n<li><em>stats<\/em> : statistiques li\u00e9es \u00e0 l&rsquo;utilisation du proxy<\/li>\n<li><em>monitor<\/em> : donn\u00e9es collect\u00e9e en vue de la supervision<\/li>\n<\/ul>\n<p>Succinct, comme convenu!<br \/>\nSuffisant n\u00e9anmoins dans le cadre de cet article, car nous allons principalement utiliser les tables du sch\u00e9ma <em>main<\/em>. tu veux en savoir plus ? <a href=\"https:\/\/github.com\/sysown\/proxysql\/wiki\" target=\"_blank\" rel=\"noopener\">RTFM<\/a> !<\/p>\n<h3>Configuration des groupes dans ProxySQL<\/h3>\n<p>ProxySQL utilise la notion de <em>Hostgroup<\/em> pour regrouper de mani\u00e8re logique des serveurs par type. Ils\u00a0sont d\u00e9finit dans la table <em>main.mysql_servers<\/em>\u00a0:<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSHOW CREATE TABLE main.mysql_servers\\G\n*************************** 1. row ***************************\ntable: mysql_servers\nCreate Table: CREATE TABLE mysql_servers (\nhostgroup_id INT NOT NULL DEFAULT 0,\nhostname VARCHAR NOT NULL,\nport INT NOT NULL DEFAULT 3306,\nstatus VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',\nweight INT CHECK (weight &gt;= 0) NOT NULL DEFAULT 1,\ncompression INT CHECK (compression &gt;=0 AND compression &lt;= 102400) NOT NULL DEFAULT 0,\nmax_connections INT CHECK (max_connections &gt;=0) NOT NULL DEFAULT 1000,\nmax_replication_lag INT CHECK (max_replication_lag &gt;= 0 AND max_replication_lag &lt;= 126144000) NOT NULL DEFAULT 0,\nuse_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,\nmax_latency_ms INT UNSIGNED CHECK (max_latency_ms&gt;=0) NOT NULL DEFAULT 0,\ncomment VARCHAR NOT NULL DEFAULT '',\nPRIMARY KEY (hostgroup_id, hostname, port) )<\/pre>\n<p>Comme vu sur le sch\u00e9ma, on va s\u00e9parer les lectures des \u00e9critures.\u00a0Pour se faire on va \u00a0donc cr\u00e9er 2 groupes, l&rsquo;un d\u00e9di\u00e9 aux \u00e9critures et l&rsquo;autres&#8230; aux lectures :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\n-- Hostgroup 1 &lt;=&gt; Writes\nINSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14418);\nINSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14419);\nINSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14420);\n\n-- Hostgroup 2 &lt;=&gt; Reads\nINSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14418);\nINSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14419);\nINSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14420);\n\n-- Load &amp; save config\nLOAD MYSQL SERVERS TO RUNTIME;\nSAVE MYSQL SERVERS TO DISK;\n\n-- Check\nSELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;\n+--------------+-----------+-------+--------+\n| hostgroup_id | hostname  | port  | status |\n+--------------+-----------+-------+--------+\n| 1            | 127.0.0.1 | 14418 | ONLINE |\n| 1            | 127.0.0.1 | 14419 | ONLINE |\n| 1            | 127.0.0.1 | 14420 | ONLINE |\n| 2            | 127.0.0.1 | 14418 | ONLINE |\n| 2            | 127.0.0.1 | 14419 | ONLINE |\n| 2            | 127.0.0.1 | 14420 | ONLINE |\n+--------------+-----------+-------+--------+\n<\/pre>\n<h3>Supervision<\/h3>\n<p>ProxySQL comprend un module de supervision (monitoring) qui a pour\u00a0utilisateur et mot de passe par d\u00e9faut, respectivement <em>monitor<\/em> et <em>monitor<\/em> (oui 2 fois monitor).\u00a0Cet utilisateur doit bien \u00e9videmment exister au niveau des membres du cluster et il n&rsquo;a besoin que du privil\u00e8ge MySQL <strong>USAGE<\/strong>, afin de\u00a0<em>pinger<\/em> et v\u00e9rifier la variable <em>read_only<\/em>.<br \/>\nLe privil\u00e8ge <strong>REPLICATION CLIENT<\/strong> est cependant n\u00e9cessaire s&rsquo;il doit superviser aussi le retard de r\u00e9plication.<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\n-- Set user name &amp; password for monitoring module\nSET mysql-monitor_username='monitoring';\nSET mysql-monitor_password='Very-S3cr3t';\n\n-- Load &amp; save config\nLOAD MYSQL VARIABLES TO RUNTIME;\nSAVE MYSQL VARIABLES TO DISK;<\/pre>\n<h3>Scheduler<\/h3>\n<p>Le <em>scheduler<\/em> permet d&rsquo;ex\u00e9cuter des scripts externes \u00e0 ProxySQL. Nous avons\u00a0besoin d&rsquo;un script pour superviser les membres du cluster pour le routage des requ\u00eates et le failover.\u00a0Ce script est disponible sur mon repo GitHub : <a href=\"https:\/\/github.com\/freshdaz\/proxysql_groupreplication_checker\/blob\/master\/proxysql_groupreplication_checker.sh\" target=\"_blank\" rel=\"noopener\">proxysql_groupreplication_checker.sh<\/a>\u00a0et\u00a0\u00a0doit \u00eatre plac\u00e9 dans \u00ab\u00a0<strong>\/var\/lib\/proxysql\/<\/strong>\u00a0\u00bb<\/p>\n<p>Petit rappel: ce script n&rsquo;est fournit que pour l&rsquo;exemple. Il <span style=\"text-decoration: underline;\">ne doit pas \u00eatre utiliser tel quel\u00a0en production<\/span>.<\/p>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span><\/em><br \/>\n<em> Ce script est l\u00e9g\u00e8rement modifi\u00e9 par rapport \u00e0 la version originale que Lefred propose : <a href=\"https:\/\/github.com\/lefred\/proxysql_groupreplication_checker\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/lefred\/proxysql_groupreplication_checker<\/a> pour pouvoir fonctionner dans le cadre de ce PoC avec\u00a0ProxySQL 1.3.2.<\/em><\/p>\n<p>Les jobs \u00e0 ex\u00e9cuter sont stock\u00e9s dans la table scheduler du schema main :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSHOW CREATE TABLE main.scheduler\\G\n*************************** 1. row ***************************\ntable: scheduler\nCreate Table: CREATE TABLE scheduler (\nid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\nactive INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,\ninterval_ms INTEGER CHECK (interval_ms&gt;=100 AND interval_ms&lt;=100000000) NOT NULL,\nfilename VARCHAR NOT NULL,\narg1 VARCHAR,\narg2 VARCHAR,\narg3 VARCHAR,\narg4 VARCHAR,\narg5 VARCHAR,\ncomment VARCHAR NOT NULL DEFAULT '')<\/pre>\n<p>Quelques infos pour la route :<\/p>\n<ul>\n<li><em>arg1<\/em> identifiant d&rsquo;hostgroup pour les \u00e9critures<\/li>\n<li><em>arg2<\/em> identifiant d&rsquo;hostgroup pour les lectures<\/li>\n<li><em>arg3<\/em> nombre d&rsquo;\u00e9crivain actif simultan\u00e9ment<\/li>\n<li><em>arg4<\/em> bool\u00e9en qui permet de choisir si le membre actif pour les \u00e9critures est aussi candidat pour les lectures<\/li>\n<li><em>arg5<\/em> fichier de log<\/li>\n<\/ul>\n<p>Param\u00e9trons le scheduler :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nINSERT INTO main.scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES \n(1,'10000','\/var\/lib\/proxysql\/proxysql_groupreplication_checker.sh','1','2','1','0','\/var\/lib\/proxysql\/proxysql_groupreplication_checker.log');\n\n-- Load &amp; save config\nSAVE SCHEDULER TO DISK;\nLOAD SCHEDULER TO RUNTIME;\n\nSELECT * FROM main.scheduler\\G\n*************************** 1. row ***************************\nid: 1\nactive: 1\ninterval_ms: 10000\nfilename: \/var\/lib\/proxysql\/proxysql_groupreplication_checker.sh\narg1: 1\narg2: 2\narg3: 1\narg4: 0\narg5: \/var\/lib\/proxysql\/proxysql_groupreplication_checker.log\ncomment:<\/pre>\n<p>Et on peut constater que le script \u00e0 modifi\u00e9 le statut des membres du cluster :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers WHERE hostgroup_id ;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |\n| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14419 | ONLINE       | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<p>En clair, du point de vue de l&rsquo;application, qui se connecte au proxy :<\/p>\n<ul>\n<li>14418 aka node 1 (primaire) : accessible en \u00e9criture \/ inaccessible en lecture<\/li>\n<li>14419 aka node 2 (secondaire) : inaccessible en \u00e9criture \/ accessible en lecture<\/li>\n<li>14420 aka node 3 (secondaire) : inaccessible en \u00e9criture \/ accessible en lecture<\/li>\n<\/ul>\n<p>On est bien !<\/p>\n<h3>Test du failover<\/h3>\n<p>A ce stade on a tout ce qu&rsquo;il faut pour tester le comportement du proxy en cas de perte du primaire. Chiche?<\/p>\n<p>V\u00e9rification du statut des membres \u00e0 l&rsquo;aide de la supervision ProxySQL (perspective du proxy) :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |\n| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14419 | ONLINE       | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<p>V\u00e9rification de l&rsquo;identit\u00e9 du n\u0153ud primaire (perspective du cluster) :<\/p>\n<pre class=\"lang:mysql decode:true\">node2 [14419]&gt;\n-- Find the primary node\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE\nFROM performance_schema.replication_group_members\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nMEMBER_ID: 00014418-1111-1111-1111-111111111111\nMEMBER_HOST: localhost\nMEMBER_PORT: 14418\nMEMBER_STATE: ONLINE<\/pre>\n<p>V\u00e9rification du statut des membres en contactant les n\u0153uds directement (perspective client) :<\/p>\n<pre class=\"lang:sh decode:true\">$\nfor port in 14418 14419 14420; do \n  mysql -P$port --protocol=TCP -BNe\"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');\" 2&gt; \/dev\/null;\n  echo;\ndone;\n\nport 14418\nread_only OFF\nsuper_read_only OFF\n\nport 14419\nread_only ON\nsuper_read_only ON\n\nport 14420\nread_only ON\nsuper_read_only ON<\/pre>\n<p>Il y a manifestement un consensus pour dire que :<\/p>\n<ul>\n<li>Membre\u00a01, port 11418 est le n\u0153ud primaire, en mode lecture\/\u00e9criture (mais lecture seule pour ProxySQL)<\/li>\n<li>Membre 2 (port 14419) &amp; 3 (port 14420) sont des n\u0153uds secondaires, en mode lecture seule.<\/li>\n<\/ul>\n<p><span style=\"text-decoration: underline;\"><em>Note<\/em><\/span><br \/>\n<em>En fonction de votre configuration un utilisateur et un mot de passe peuvent \u00eatre requis<\/em><\/p>\n<p>La premi\u00e8re action de notre test consiste en l&rsquo; arr\u00eat du n\u0153ud primaire (node 1, port 14418) :<\/p>\n<pre class=\"lang:mysql decode:true\">node1 [14418]&gt; SHUTDOWN;\nQuery OK, 0 rows affected (0,00 sec)<\/pre>\n<p>MySQL Group Replication va donc \u00e9lire un nouveau membre primaire :<\/p>\n<pre class=\"lang:mysql decode:true\">node2 [14419]&gt;\n-- Find the primary node\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE\nFROM performance_schema.replication_group_members\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nMEMBER_ID: 00014419-2222-2222-2222-222222222222\nMEMBER_HOST: localhost\nMEMBER_PORT: 14419\nMEMBER_STATE: ONLINE<\/pre>\n<p>Le n\u0153ud 2 (port 14419) est donc le nouveau membre\u00a0primaire du cluster.<\/p>\n<p>Une autre vue de ce nouveau statut :<\/p>\n<pre class=\"lang:sh decode:true\">$\nfor port in 14418 14419 14420; do\n\u00a0 mysql -P$port --protocol=TCP -BNe\"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');\" 2&gt; \/dev\/null;\n\u00a0 echo;\ndone;\n\n\nport 14419\nread_only OFF\nsuper_read_only OFF\n\nport 14420\nread_only ON\nsuper_read_only ON<\/pre>\n<p>En ce qui concerne la vision ProxySQL :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14419 | ONLINE       | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<p>Confirmation, avec ce nouvel \u00e9tat :<\/p>\n<ul>\n<li>14418 aka node 1 (down) :\u00a0inaccessible<\/li>\n<li>14419 aka node 2 (primaire) : accessible en \u00e9criture \/ inaccessible en lecture<\/li>\n<li>14420 aka node 3 (secondaire) : inaccessible en \u00e9criture \/ accessible en lecture<\/li>\n<\/ul>\n<p>Que c&rsquo;est beau l&rsquo;informatique&#8230; quand \u00e7a fonctionne.<\/p>\n<p>Que se passe t&rsquo;il si le membre 1 revient ?<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14419 | ONLINE       | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | ONLINE       | 0                   |\n| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<p>Le proxy nous montre un nouvel \u00e9tat, une fois le n\u0153ud 1 de retour dans le cluster :<\/p>\n<ul>\n<li>14418 aka node 1 (secondaire) :\u00a0inaccessible en \u00e9criture \/ accessible en lecture<\/li>\n<li>14419 aka node 2 (primaire) : accessible en \u00e9criture \/ inaccessible en lecture<\/li>\n<li>14420 aka node 3 (secondaire) : inaccessible en \u00e9criture \/ accessible en lecture<\/li>\n<\/ul>\n<p>Pour la suite de l&rsquo;artcile, le membre\u00a01 (14418) est \u00e0 nouveau le n\u0153ud\u00a0primaire.<\/p>\n<p>Ci dessous extraits de quelques \u00e9tats du proxy durant la phase de failover :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | ONLINE       | 0                   |\n| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<p>## few seconds&#8230;<\/p>\n<pre class=\"lang:mysql decode:true\">SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |\n| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<p>## few seconds&#8230;<\/p>\n<pre class=\"lang:mysql decode:true\">SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;\n+--------------+-----------+-------+--------------+---------------------+\n| hostgroup_id | hostname  | port  | status       | max_replication_lag |\n+--------------+-----------+-------+--------------+---------------------+\n| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |\n| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |\n| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |\n| 2            | 127.0.0.1 | 14419 | ONLINE       | 0                   |\n| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |\n+--------------+-----------+-------+--------------+---------------------+<\/pre>\n<h3>R\u00e8gles de routage<\/h3>\n<p>La table <em>mysql_query_rules<\/em>\u00a0du schema main va contenir les r\u00e8gles de routages. C&rsquo;est donc\u00a0l\u00e0 que nous allons pouvoir s\u00e9parer les requ\u00eates d&rsquo;\u00e9criture des requ\u00eates de lecture\u00a0:<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt; \nSHOW CREATE TABLE main.mysql_query_rules\\G\n*************************** 1. row ***************************\ntable: mysql_query_rules\nCreate Table: CREATE TABLE mysql_query_rules (\nrule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\nactive INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,\nusername VARCHAR,\nschemaname VARCHAR,\nflagIN INT NOT NULL DEFAULT 0,\nclient_addr VARCHAR,\nproxy_addr VARCHAR,\nproxy_port INT,\ndigest VARCHAR,\nmatch_digest VARCHAR,\nmatch_pattern VARCHAR,\nnegate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,\nflagOUT INT,\nreplace_pattern VARCHAR,\ndestination_hostgroup INT DEFAULT NULL,\ncache_ttl INT CHECK(cache_ttl &gt; 0),\nreconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,\ntimeout INT UNSIGNED,\nretries INT CHECK (retries&gt;=0 AND retries &lt;=1000),\ndelay INT UNSIGNED,\nmirror_flagOUT INT UNSIGNED,\nmirror_hostgroup INT UNSIGNED,\nerror_msg VARCHAR,\nlog INT CHECK (log IN (0,1)),\napply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,\ncomment VARCHAR)<\/pre>\n<p>Pour ce PoC nos r\u00e8gles seront simples et d\u00e9finies \u00e0 base de regex :<\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Groupe lecture<\/span> : requ\u00eates commen\u00e7ant par le mot cl\u00e9 SELECT (<strong>^SELECT<\/strong>) vont dans le <em>hostgroup 2<\/em>.<\/li>\n<li><span style=\"text-decoration: underline;\">Groupe \u00e9criture<\/span> : toutes les autres requ\u00eates (DDL, DML,&#8230;), ainsi que les SELECT contenant la clause FOR UPDATE (<strong>^SELECT.*FOR UPDATE$<\/strong>) vont dans le <em>hostgroup 1<\/em>.<\/li>\n<\/ul>\n<p>Si une requ\u00eate ne correspond \u00e0 aucune des r\u00e8gles, elle sera dirig\u00e9e vers le <em>hostgroup<\/em> par d\u00e9faut (champ<em> default_hostgroup<\/em> de la table <em>mysql_users<\/em>) de l&rsquo;utilisateur qui ex\u00e9cute la requ\u00eate.<\/p>\n<p>Regardons la structure de la table <em>mysql_users<\/em> :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSHOW CREATE TABLE main.mysql_users\\G\n*************************** 1. row ***************************\ntable: mysql_users\nCreate Table: CREATE TABLE mysql_users (\nusername VARCHAR NOT NULL,\npassword VARCHAR,\nactive INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,\nuse_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,\ndefault_hostgroup INT NOT NULL DEFAULT 0,\ndefault_schema VARCHAR,\nschema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,\ntransaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,\nfast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,\nbackend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,\nfrontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,\nmax_connections INT CHECK (max_connections &gt;=0) NOT NULL DEFAULT 10000,\nPRIMARY KEY (username, backend),\nUNIQUE (username, frontend))<\/pre>\n<p>Cr\u00e9ons l&rsquo;utilisateur MySQL utilis\u00e9 pour se connecter au backend (app_user \/ app_pwd) :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSELECT username, password, active, default_hostgroup FROM main.mysql_users;\nEmpty set (0,00 sec)\n\nINSERT INTO main.mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'app_pwd', 1);\n\nSELECT username, password, active, default_hostgroup FROM main.mysql_users;\n+----------+----------+--------+-------------------+\n| username | password | active | default_hostgroup |\n+----------+----------+--------+-------------------+\n| app_user | app_pwd  | 1      | 1                 |\n+----------+----------+--------+-------------------+\n\n-- Load &amp; save config\nLOAD MYSQL USERS TO RUNTIME;\nSAVE MYSQL USERS TO DISK;<\/pre>\n<p>Maintenant nous pouvons renseigner la table <em>mysql_query_rules<\/em> avec nos 2 r\u00e8gles de routages (\u00e9criture et lecture) :<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\n-- Rule for writes : hostgroup 1\nINSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, \"^SELECT .* FOR UPDATE\", 1, 1);\n\n-- Rule for reads : hostgroup 2\nINSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, \"^SELECT \", 2, 1);\n\n-- Load &amp; save config\nLOAD MYSQL QUERY RULES TO RUNTIME;\nSAVE MYSQL QUERY RULES TO DISK;\n\n-- Check\nSELECT rule_id, active, match_pattern, destination_hostgroup FROM main.mysql_query_rules;\n+---------+--------+-----------------------+-----------------------+\n| rule_id | active | match_pattern         | destination_hostgroup |\n+---------+--------+-----------------------+-----------------------+\n| 1       | 1      | ^SELECT .* FOR UPDATE | 1                     |\n| 2       | 1      | ^SELECT               | 2                     |\n+---------+--------+-----------------------+-----------------------+<\/pre>\n<p>Et voil\u00e0!<\/p>\n<p>Il est temps de passer aux choses s\u00e9rieuses.<\/p>\n<h2>Playtime<\/h2>\n<p>Toutes les briques de notre archi sont assembl\u00e9es et configur\u00e9es, c&rsquo;est donc l&rsquo;heure du test final.<\/p>\n<p>Notre appli, simul\u00e9e par un script bash, va se connecter au proxy (ProxySQL) pour interroger la base de donn\u00e9es distribu\u00e9e (MySQL Group Replication).<\/p>\n<p>Au fait on fait comment pour se connecter au proxy ?<\/p>\n<pre class=\"lang:mysql decode:true\">Proxy&gt;\nSHOW VARIABLES LIKE 'mysql-interfaces'\\G\n*************************** 1. row ***************************\nVariable_name: mysql-interfaces\nValue: 0.0.0.0:6033;\/tmp\/proxysql.sock<\/pre>\n<p>Pour se connecter \u00e0 la base de donn\u00e9es, par l&rsquo;interm\u00e9diaire du proxy,\u00a0l&rsquo;application peut soit utiliser le port <strong>6033<\/strong>, soit le socket <strong>\/tmp\/proxysql.sock<\/strong>\u00a0:<\/p>\n<pre class=\"lang:sh decode:true\">$\nfor x in {1..10}; do \n  mysql -u app_user -h127.0.0.1 -P6033 -BNe\"SELECT @@port;\" ; \ndone;\n\n14420\n14420\n14420\n14420\n14419\n14419\n14420\n14419\n14419\n14419<\/pre>\n<p>Comme pr\u00e9vu, les requ\u00eates de lecture se connectent sur l&rsquo;un des 2 n\u0153uds secondaires (node 2 : port 14419 || node 3: port 14420).<\/p>\n<p>Allons plus loin dans le test et cr\u00e9ons la table\u00a0<em>test.poc<\/em>\u00a0:<\/p>\n<pre class=\"lang:mysql decode:true\">app [6033]&gt;\nCREATE TABLE test.poc (\nid mediumint(9) unsigned NOT NULL AUTO_INCREMENT,\nport mediumint unsigned,\ntime timestamp,\nPRIMARY KEY (`id`)\n) ENGINE=InnoDB;\nQuery OK, 0 rows affected (0,08 sec)<\/pre>\n<p>L&rsquo;application test jouera les 2 requ\u00eates suivante :<\/p>\n<ul>\n<li><em>INSERT INTO test.poc (port) VALUES (@@port);<\/em><\/li>\n<li><em>SELECT * FROM test.poc;<\/em><\/li>\n<\/ul>\n<p>Gr\u00e2ce \u00e0 ces 2 requ\u00eates et le petit scripts ci-dessous, on va \u00eatre en mesure de suivre les processus de routage et de failover.<br \/>\nEn clair :<\/p>\n<ul>\n<li>\u00e9criture sur le primaire<\/li>\n<li>lectures sur le(s) secondaire(s)<\/li>\n<li>crash du master<\/li>\n<li>failover automatique et transparent<\/li>\n<\/ul>\n<pre class=\"lang:sh decode:true\">$\nwhile true; do\n  mysql -u app_user -h127.0.0.1 -P6033 -BNe\"INSERT INTO test.poc (port) VALUES (@@port); SELECT *, @@port FROM test.poc;\";\n  echo;\n  sleep 4;\ndone\n\n3 14418 2017-01-04 16:51:05 14419 &lt;=&gt; Row 1\n\n3 14418 2017-01-04 16:51:05 14419\n10 14418 2017-01-04 16:51:09 14419 &lt;=&gt; Row 2\n\n3 14418 2017-01-04 16:51:05 14420\n10 14418 2017-01-04 16:51:09 14420\n17 14418 2017-01-04 16:51:13 14420 &lt;=&gt; Row 3\n\n3 14418 2017-01-04 16:51:05 14420\n10 14418 2017-01-04 16:51:09 14420\n17 14418 2017-01-04 16:51:13 14420\n18 14419 2017-01-04 16:51:21 14420 &lt;=&gt; Row 4 =&gt; Failover, new primary node (14419)\n\n3 14418 2017-01-04 16:51:05 14420\n10 14418 2017-01-04 16:51:09 14420\n17 14418 2017-01-04 16:51:13 14420\n18 14419 2017-01-04 16:51:21 14420\n25 14419 2017-01-04 16:51:25 14420 &lt;=&gt; Row 5<\/pre>\n<p>Revoyons la sc\u00e8ne au ralenti&#8230;<\/p>\n<ul>\n<li>Row 1 : Ecriture sur 14418, Lecture sur 14419<\/li>\n<li>Row 2 : Ecriture sur 14418, Lecture sur 14419<\/li>\n<li>Row 3 : Ecriture sur 14418, Lecture sur 14420<\/li>\n<li>Row 4 : Ecriture sur 14419, Lecture sur 14420\n<ul>\n<li>Le membre 14418 est stopp\u00e9, failover automatique de MySQL Group Replication et ProxySQL redirige les \u00e9critures sur 14419<\/li>\n<\/ul>\n<\/li>\n<li>Row 5 : Ecriture sur 14419, Lecture sur 14420<\/li>\n<\/ul>\n<p>Trop cool !<\/p>\n<h2>Le mot de la fin<\/h2>\n<p>R\u00e9capitulons: pour ce PoC, \u00a0une architecture\u00a0ProxySQL 1.3 avec MySQL Group Replication s&rsquo;est mont\u00e9e\u00a0en plusieurs \u00e9tapes :<\/p>\n<ol>\n<li>D\u00e9ployement d&rsquo;un cluster MySQL Group Replication (3 ou 5 n\u0153uds en g\u00e9n\u00e9ral)<\/li>\n<li>Enrichissement de\u00a0<em>sys schema<\/em> afin g\u00e9rer le routage et le failover<\/li>\n<li>Installation et param\u00e9trage de ProxySQL<\/li>\n<li>Ajout du job\u00a0de routage et de failover<\/li>\n<\/ol>\n<p>L&rsquo;\u00e9tape 2 ne sera plus n\u00e9cessaire \u00e0 terme, sys schema sera nativement compatible avec Goup Replication dans une prochaine version de MySQL.<\/p>\n<p>En ce qui concerne les \u00e9tapes 3 et 4, elles vont \u00eatre simplifi\u00e9es car\u00a0<a href=\"https:\/\/github.com\/sysown\/proxysql\/tree\/v1.4.0-GR\" target=\"_blank\" rel=\"noopener\">ProxySQL 1.4<\/a>\u00a0qui est en cours de d\u00e9veloppement, est nativement compatible Group Replication.<\/p>\n<p>Et surtout n&rsquo;oubliez pas que\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-innodb-cluster\/en\/mysql-innodb-cluster.html\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster<\/a>\u00a0est la solution maison <strong>tout en un<\/strong>, qui simplifie grandement le d\u00e9ploiement de ce type d&rsquo;architecture. Pour rappel InnoDB Cluster c&rsquo;est\u00a0:<\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-shell.html\" target=\"_blank\" rel=\"noopener\">MySQL Shell<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-router\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL Router<\/a> <strong>2.1<\/strong><\/li>\n<\/ul>\n<p>Vous pouvez d\u2019ores et d\u00e9j\u00e0 tester la derni\u00e8re pr\u00e9-version (labs preview) de MySQL InnoDB Cluster:\u00a0<a href=\"http:\/\/labs.mysql.com\/\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster 5.7.17 Preview 2<\/a><br \/>\nC&rsquo;est inutile mais je vais le rappeler quand m\u00eame : il n&rsquo;est pas recommand\u00e9 d&rsquo;utiliser les binaires du Labs en production.<\/p>\n<h2>R\u00e9f\u00e9rences<\/h2>\n<h3>MySQL Group Replication<\/h3>\n<ul>\n<li><a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">D\u00e9ployer un cluster MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL pour MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">FAQ Webinar MySQL Group Replication<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener\">Documentation de MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/mysqlhighavailability.com\/mysql-group-replication-a-quick-start-guide\/\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication: A Quick Start Guide<\/a><\/li>\n<\/ul>\n<h3>ProxySQL<\/h3>\n<ul>\n<li><a href=\"http:\/\/www.proxysql.com\/\" target=\"_blank\" rel=\"noopener\">ProxySQL<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/sysown\/proxysql\/wiki\" target=\"_blank\" rel=\"noopener\">Documentation de ProxySQL<\/a>n<\/li>\n<li><a href=\"http:\/\/www.proxysql.com\/compare\" target=\"_blank\" rel=\"noopener\">Comparatif\u00a0de solutions proxy<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/sysown\/proxysql\/tree\/v1.4.0-GR\" target=\"_blank\" rel=\"noopener\">ProxySQL 1.44<\/a><\/li>\n<\/ul>\n<h3>MySQL InnoDB Cluster<\/h3>\n<ul>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/\" target=\"_blank\" rel=\"noopener\">Tester MySQL InnoDB Cluster<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/04\/10\/adopte-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Adopte un\u2026 cluster MySQL Group Replication<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-innodb-cluster\/en\/mysql-innodb-cluster.html\" target=\"_blank\" rel=\"noopener\">Documentation de MySQL InnoDB Cluster<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/mysql-innodb-cluster-whats-new-in-the-5-7-17-preview-release\/\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster \u2013 What\u2019s New in the 5.7.17 Preview Release<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/mysql-innodb-cluster-a-hands-on-tutorial\/\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster \u2013 A Hands on Tutorial<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/mysql-innodb-cluster-navigating-the-cluster\/\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster \u2013 Navigating the Cluster<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/mysql-innodb-cluster-setting-up-innodb-cluster-on-windows\/\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster \u2013 Setting up InnoDB Cluster on Windows<\/a><\/li>\n<\/ul>\n<p>Thanks for using MySQL!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dans un pr\u00e9c\u00e9dent article je vous ai pr\u00e9sent\u00e9 comment d\u00e9ployer un cluster MySQL Group Replication, la nouvelle solution de haute disponibilit\u00e9 de MySQL.<\/p>\n<p>Ce type d&rsquo;architecture est souvent utilis\u00e9 avec un composant qui se place entre l&rsquo;application et le cluster,composant g\u00e9n\u00e9ralement appel\u00e9 proxy (quelque chose) ou router quelque chose. Dans cet article, je vais vous pr\u00e9senter le meilleur (selon moi) proxy open source du moment :\u00a0ProxySQL\u00a0(1.3.2\u00a0: la version GA \u00e0 la date d&rsquo;\u00e9criture).<\/p>\n<p>Le but de cet article est de cr\u00e9er un PoC Solution HA Base de Donn\u00e9es Open Source : MySQL Group Replication et ProxySQL.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[282,288,8,294,337],"tags":[286,292,330,298,296,300,341],"class_list":["post-1746","post","type-post","status-publish","format-standard","hentry","category-group-replication","category-innodb-cluster","category-mysql","category-proxysql","category-tuto","tag-group-replication","tag-ha","tag-high-availability","tag-innodb-cluster","tag-proxy","tag-proxysql","tag-tuto"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-sa","jetpack-related-posts":[{"id":2035,"url":"https:\/\/dasini.net\/blog\/2018\/01\/09\/configurer-proxysql-1-4-pour-mysql-5-7-group-replication\/","url_meta":{"origin":1746,"position":0},"title":"Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication","author":"Olivier DASINI","date":"9 janvier 2018","format":false,"excerpt":"Toute architecture de base de donn\u00e9es se doit de se reposer sur 3 piliers, la supervision (monitoring) , la sauvegarde\/restauration et la haute disponibilit\u00e9. Mon premier article de l'ann\u00e9e 2018\u00a0 concerne l'un des meilleurs combos\u00a0du moment, en mati\u00e8re de haute disponibilit\u00e9 niveau base de donn\u00e9es : - MySQL 5.7 Group\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"MySQL","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/mysql_300x161.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1800,"url":"https:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/","url_meta":{"origin":1746,"position":1},"title":"FAQ Webinar MySQL Group Replication","author":"Olivier DASINI","date":"3 mars 2017","format":false,"excerpt":"Le 1er mars dernier, j'ai pr\u00e9sent\u00e9 lors d'un webinar, la technologie de haute disponibilit\u00e9 MySQL Group Replication. On a explos\u00e9 notre record d'affluence et j'ai \u00e9t\u00e9 inond\u00e9 de questions, preuve s'il en faut de votre int\u00e9r\u00eat, toujours plus important, pour la base de donn\u00e9es Open Source la plus populaire au\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1678,"url":"https:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/","url_meta":{"origin":1746,"position":2},"title":"D\u00e9ployer un cluster MySQL Group Replication","author":"Olivier DASINI","date":"8 novembre 2016","format":false,"excerpt":"Historiquement, les solutions de haute disponibilit\u00e9 (HA) avec MySQL tournent autour de la fonctionnalit\u00e9 native\u00a0MySQL Replication: replication asynchrone ou semi-synchrone. Ces modes de \u00a0replication sont tr\u00e8s largement utilis\u00e9s pour le besoins critiques d'enterprises \"at scale\" comme Facebook, Twitter, Booking.com, Uber... Aujourd'hui MySQL propose une nouvelle fonctionnalit\u00e9 native de haute disponibilit\u00e9\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/Group_Replication_3_nodes.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1855,"url":"https:\/\/dasini.net\/blog\/2017\/04\/10\/adopte-un-cluster-mysql-group-replication\/","url_meta":{"origin":1746,"position":3},"title":"Adopte un&#8230; cluster MySQL Group Replication","author":"Olivier DASINI","date":"10 avril 2017","format":false,"excerpt":"Au menu d'aujourd'hui : comment passer de l'administration \"manuelle\" de votre solution HA MySQL Group Replication \u00e0 une administration plus simple, plus fun mais surtout facilement automatisable avec le pack MySQL InnoDB Cluster. En clair, on va voir comment utiliser MySQL Shell pour l'administration et l'orchestration du cluster et MySQL\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"MySQL InnoDB Cluster Overview","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_cluster_overview.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1832,"url":"https:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/","url_meta":{"origin":1746,"position":4},"title":"Tester MySQL InnoDB Cluster","author":"Olivier DASINI","date":"13 mars 2017","format":false,"excerpt":"MySQL InnoDB Cluster est la (future) solution out-of-the-box HA de MySQL (\u00e0 ne pas confondre avec MySQL NDB Cluster). Ce produit est compos\u00e9 de 3 \u00e9l\u00e9ments : MySQL Group Replication Plugin de r\u00e9plication multi-ma\u00eetre, avec r\u00e9solution de conflits et basculement (failover) automatique. MySQL Router Middleware l\u00e9ger et performant qui fournit\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"MySQL InnoDB Cluster Architecture","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_Cluster_Architecture.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1654,"url":"https:\/\/dasini.net\/blog\/2016\/10\/05\/mysql-a-oracle-openworld-2016\/","url_meta":{"origin":1746,"position":5},"title":"MySQL \u00e0 Oracle OpenWorld 2016","author":"Olivier DASINI","date":"5 octobre 2016","format":false,"excerpt":"R\u00e9sum\u00e9 des annonces MySQL \u00e0 Oracle OpenWorld: MySQL dans le cloud, MySQL Group Replication, MySQL InnoDB Cluster, MySQL 8,...","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/Oracle_MySQL_Cloud_Service.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1746","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1746"}],"version-history":[{"count":43,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1746\/revisions"}],"predecessor-version":[{"id":7245,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1746\/revisions\/7245"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1746"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1746"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1746"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}