{"id":2035,"date":"2018-01-09T09:53:26","date_gmt":"2018-01-09T08:53:26","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2035"},"modified":"2024-12-10T15:49:34","modified_gmt":"2024-12-10T14:49:34","slug":"configurer-proxysql-1-4-pour-mysql-5-7-group-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/01\/09\/configurer-proxysql-1-4-pour-mysql-5-7-group-replication\/","title":{"rendered":"Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication"},"content":{"rendered":"<p><em><a href=\"http:\/\/dasini.net\/blog\/2018\/01\/09\/setting-up-proxysql-1-4-with-mysql-5-7-group-replication\/\" target=\"_blank\" rel=\"noopener\">Read this post in English<\/a><\/em><\/p>\n<p>Toute architecture de base de donn\u00e9es se doit de se reposer sur <span style=\"text-decoration: underline;\">3 piliers<\/span>, la <strong>supervision<\/strong> (monitoring) , la <strong>sauvegarde\/restauration<\/strong> et la <strong>haute disponibilit\u00e9<\/strong>. Mon premier article de l&rsquo;ann\u00e9e 2018\u00a0 concerne l&rsquo;un des meilleurs combos\u00a0du moment, en mati\u00e8re de <a href=\"https:\/\/www.mysql.com\/fr\/why-mysql\/white-papers\/mysql-guide-to-high-availability-solutions\/\" target=\"_blank\" rel=\"noopener\"><span style=\"text-decoration: underline;\">haute disponibilit\u00e9<\/span> niveau base de donn\u00e9es<\/a> :<br \/>\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignleft\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/mysql_300x161.jpg?resize=167%2C89\" alt=\"MySQL\" width=\"167\" height=\"89\" \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/proxysql-321x157.jpg?resize=184%2C90\" alt=\"ProxySQL\" width=\"184\" height=\"90\" \/><\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener\">MySQL 5.7 Group Replication<\/a>\u00a0: la seule solution native HA de MySQL, qui permet notamment de faire du vrai multi-master (avec <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-cluster.html\" target=\"_blank\" rel=\"noopener\">MySQL NDB Cluster<\/a> mais ceci est hors sujet).<\/li>\n<li><a href=\"http:\/\/www.proxysql.com\/\" target=\"_blank\" rel=\"noopener\">ProxySQL 1.4<\/a>\u00a0: modestement le <a href=\"http:\/\/www.proxysql.com\/compare\" target=\"_blank\" rel=\"noopener\">meilleur proxy<\/a> pour MySQL.<\/li>\n<\/ul>\n<p><em><span style=\"text-decoration: underline;\">Note 1<\/span>: la r\u00e9plication native (<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication.html\" target=\"_blank\" rel=\"noopener\">MySQL replication<\/a>) reste \u00e9videmment une alternative de premier plan pour les besoins de haute dispos. D&rsquo;ailleurs ProxySQL la g\u00e8re \u00e9galement nativement.<\/em><\/p>\n<p><em><span style=\"text-decoration: underline;\">Note 2<\/span>: ProxySQL a pl\u00e9thore de <a href=\"http:\/\/www.proxysql.com\/#features\" target=\"_blank\" rel=\"noopener\">fonctionnalit\u00e9s<\/a>, toutefois le but de cet article est son utilisation dans un contexte MySQL Group Replication.<\/em><\/p>\n<p>ProxySQL est compatible avec MySQL Group Replication depuis la version 1.3 voir :\u00a0<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>. Cependant, la version <strong>1.4<\/strong>\u00a0supporte\u00a0nativement MySQL Group Replication. Il est donc plus facile d&rsquo;utiliser ensemble ces 2 technologies populaires, et c&rsquo;est ce que nous allons voir imm\u00e9diatement.<\/p>\n<p>Dans cet article je vais faire les hypoth\u00e8ses suivantes :<\/p>\n<ul>\n<li>MySQL <strong>5.7.20<\/strong> est <a href=\"http:\/\/dasini.net\/blog\/2017\/07\/24\/ou-telecharger-mysql\/\" target=\"_blank\" rel=\"noopener\">t\u00e9l\u00e9charg\u00e9<\/a> et <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/installing.html\" target=\"_blank\" rel=\"noopener\">install\u00e9<\/a>, sur 3 hosts.<\/li>\n<li>ProxySQL <strong>1.4.4<\/strong> est <a href=\"https:\/\/github.com\/sysown\/proxysql\/releases\/tag\/v1.4.4\" target=\"_blank\" rel=\"noopener\">t\u00e9l\u00e9charg\u00e9<\/a> et <a href=\"https:\/\/github.com\/sysown\/proxysql\/wiki\" target=\"_blank\" rel=\"noopener\">install\u00e9<\/a> sur 1 host (le HA de ProxySQL n&rsquo;est pas trait\u00e9 dans cet article).<\/li>\n<li>Un cluster MySQL Group Replication de <strong>3<\/strong> n\u0153uds est <a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">configur\u00e9 et fonctionnel<\/a>.<\/li>\n<\/ul>\n<h2>MySQL Group Replication<\/h2>\n<p><span style=\"text-decoration: underline;\">Caract\u00e9ristiques<\/span><\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Version du serveur<\/span> : 5.7.20<\/li>\n<li><span style=\"text-decoration: underline;\">Version du plugin<\/span> : 1.0<\/li>\n<li><span style=\"text-decoration: underline;\">N\u0153ud 1<\/span> : <strong>mysql_node1<\/strong>, 172.22.0.10 : 3306<\/li>\n<li><span style=\"text-decoration: underline;\">N\u0153ud 2<\/span> : <strong>mysql_node2<\/strong>, 172.22.0.20 : 3306<\/li>\n<li><span style=\"text-decoration: underline;\">N\u0153ud 3<\/span> : <strong>mysql_node3<\/strong>, 172.22.0.30 : 3306<\/li>\n<\/ul>\n<p>Ces caract\u00e9ristiques repr\u00e9sentent donc un cluster MySQL Group Replication de 3\u00a0n\u0153uds, install\u00e9, d\u00e9ploy\u00e9 et qui fonctionne :<\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt;\n-- MySQL Server version number\nSELECT left(version(),6);\n+-------------------+\n| left(version(),6) |\n+-------------------+\n| 5.7.20            |\n+-------------------+\n\n-- MySQL Group Replication plugin details\nSELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'group%'\\G\n*************************** 1. row ***************************\n           PLUGIN_NAME: group_replication\n        PLUGIN_VERSION: 1.0\n         PLUGIN_STATUS: ACTIVE\n           PLUGIN_TYPE: GROUP REPLICATION\n   PLUGIN_TYPE_VERSION: 1.1\n        PLUGIN_LIBRARY: group_replication.so\nPLUGIN_LIBRARY_VERSION: 1.7\n         PLUGIN_AUTHOR: ORACLE\n    PLUGIN_DESCRIPTION: Group Replication (1.0.0)\n        PLUGIN_LICENSE: PROPRIETARY\n           LOAD_OPTION: FORCE_PLUS_PERMANENT\n\n-- MySQL Group Replication member status\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\n   MEMBER_ID: 3c2039a6-f152-11e7-90da-0242ac16001e\n MEMBER_HOST: mysql_node3\n MEMBER_PORT: 3306\nMEMBER_STATE: ONLINE\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_applier\n   MEMBER_ID: 3c2039a8-f152-11e7-9132-0242ac160014\n MEMBER_HOST: mysql_node2\n MEMBER_PORT: 3306\nMEMBER_STATE: ONLINE\n*************************** 3. row ***************************\nCHANNEL_NAME: group_replication_applier\n   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a\n MEMBER_HOST: mysql_node1\n MEMBER_PORT: 3306\nMEMBER_STATE: ONLINE\n<\/pre>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/monitor.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Enterprise Monitor<\/strong><\/a> nous donne une vue graphique du cluster et de son \u00e9tat (click to enlarge) :<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online.png?resize=468%2C270\" alt=\"MySQL\" width=\"468\" height=\"270\" \/><\/a><\/p>\n<p>Le cluster est en mode <strong>single primary<\/strong>, c&rsquo;est \u00e0 dire qu&rsquo;un seul n\u0153ud est disponible en lecture &amp; \u00e9criture \u00e0 la fois (alors que les 2 autres n\u0153uds ne sont accessibles qu&rsquo;en lecture seule).<\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt;\n-- Is single primary mode activated?\nSHOW VARIABLES LIKE 'group_replication_single_primary_mode';\n+---------------------------------------+-------+\n| Variable_name                         | Value |\n+---------------------------------------+-------+\n| group_replication_single_primary_mode | ON    |\n+---------------------------------------+-------+\n\n-- Who is 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 ***************************\n   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a\n MEMBER_HOST: mysql_node1\n MEMBER_PORT: 3306\nMEMBER_STATE: ONLINE\n<\/pre>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/monitor.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Enterprise Monitor<\/strong><\/a> nous montre (click to enlarge):<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_status.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_status.png?resize=622%2C149\" alt=\"MySQL\" width=\"622\" height=\"149\" \/><\/a><\/p>\n<p>Je vais enrichir le\u00a0sch\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 avec le script:\u00a0<a href=\"https:\/\/gist.github.com\/lefred\/77ddbde301c72535381ae7af9f968322\" target=\"_blank\" rel=\"noopener\">addition_to_sys.sql<\/a><\/p>\n<pre class=\"lang:mysql decode:true\" title=\"addition_to_sys.sql: MySQL Group Replication extra functions and views to sys schema\">USE sys;\n\nDELIMITER $\n\nCREATE FUNCTION IFZERO(a INT, b INT)\nRETURNS INT\nDETERMINISTIC\nRETURN IF(a = 0, b, a)$\n\nCREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)\nRETURNS INT\nDETERMINISTIC\nRETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$\n\nCREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))\nRETURNS TEXT(10000)\nDETERMINISTIC\nRETURN GTID_SUBTRACT(g, '')$\n\nCREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))\nRETURNS INT\nDETERMINISTIC\nBEGIN\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()\nRETURNS INT\nDETERMINISTIC\nBEGIN\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()\nRETURNS VARCHAR(3)\nDETERMINISTIC\nBEGIN\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, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$\n\nDELIMITER ;<\/pre>\n<p>Je charge donc les fonctions et les vues dans le noeud <strong>primaire<\/strong> (mysql_node1) du cluster :<\/p>\n<pre class=\"lang:mysql decode:true\"># Loading extra functions and views to sys schema on the cluster (using a primary node)\n\n$ mysql -u root -p -h mysql_node1 &lt; .\/addition_to_sys.sql\n<\/pre>\n<p>Ce script va permettre \u00e0 ProxySQL de superviser l&rsquo;\u00e9tat des n\u0153uds du cluster.<br \/>\ne.g.<\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt; \n-- Status of the primary node\nSELECT * FROM sys.gr_member_routing_candidate_status;\n+------------------+-----------+---------------------+----------------------+\n| viable_candidate | read_only | transactions_behind | transactions_to_cert |\n+------------------+-----------+---------------------+----------------------+\n| YES              | NO        |                   0 |                    0 |\n+------------------+-----------+---------------------+----------------------+<\/pre>\n<pre class=\"lang:mysql decode:true \">node2&gt; \n-- Status of a secondary node\nSELECT * FROM sys.gr_member_routing_candidate_status;\n+------------------+-----------+---------------------+----------------------+\n| viable_candidate | read_only | transactions_behind | transactions_to_cert |\n+------------------+-----------+---------------------+----------------------+\n| YES              | YES       |                   0 |                    0 |\n+------------------+-----------+---------------------+----------------------+<\/pre>\n<p>La derni\u00e8re \u00e9tape de configuration cot\u00e9 cluster, consiste en la cr\u00e9ation des utilisateurs de supervision qui vont \u00eatre utilis\u00e9s par ProxySQL (oui, il y a bien un rapport avec l&rsquo;\u00e9tape pr\u00e9c\u00e9dente) :).<\/p>\n<p>L\u00e0 encore j&rsquo;utilise le primaire du groupe :<\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt;\n-- Create ProxySQL monitoring user inside the cluster\nCREATE USER proxysqlmonitor@'%' IDENTIFIED BY 'Very-S3cr3t';\n\nGRANT SELECT ON sys.* TO proxysqlmonitor@'%';\n<\/pre>\n<p>Il nous reste \u00e0 configurer ProxySQL maintenant !<\/p>\n<h2>ProxySQL<\/h2>\n<p><span style=\"text-decoration: underline;\">Caract\u00e9ristiques<\/span><\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Version du proxy<\/span>\u00a0: 1.4.4<\/li>\n<li><span style=\"text-decoration: underline;\">Interface d&rsquo;administration<\/span>\u00a0: 172.22.0.2:<strong>6032<\/strong><\/li>\n<li><span style=\"text-decoration: underline;\">Connexion au cluster<\/span>\u00a0: 172.22.0.2:<strong>3306<\/strong><\/li>\n<\/ul>\n<pre class=\"lang:sh decode:true\">$ proxysql --version\nProxySQL version 1.4.4-139-ga51b040, codename Truls\n\n$ service proxysql status\nProxySQL is running (58).\n<\/pre>\n<p>La configuration de ProxySQL peut se faire en ligne, ce qui est \u00e9videmment une tr\u00e8s bonne chose.<\/p>\n<p>Commen\u00e7ons par se connecter \u00e0 l&rsquo;interface d&rsquo;administration sur le port 6032 avec l&rsquo;utilisateur admin et le mot de passe&#8230; admin (!)<\/p>\n<p>Donn\u00e9es par d\u00e9fauts qui peuvent et qui doivent \u00eatre chang\u00e9es dans la vraie vie.<\/p>\n<pre class=\"lang:sh decode:true\">$ mysql -u admin -p -P 6032 --protocol=tcp main<\/pre>\n<h3>Configurer les serveurs<\/h3>\n<p>Premi\u00e8re \u00e9tape, ajouter les n\u0153uds du cluster au proxy :<\/p>\n<pre class=\"lang:mysql decode:true\">-- Add 3 nodes of the cluster into the mysql_servers table\nproxy&gt; \nINSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'mysql_node1', 3306), (2, 'mysql_node2', 3306), (2, 'mysql_node3', 3306);\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \nselect * from mysql_servers;\n+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\n| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |\n+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\n| 2            | mysql_node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |\n| 2            | mysql_node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |\n| 2            | mysql_node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |\n+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\n<\/pre>\n<h3>Configurer les hostgroups<\/h3>\n<p>J&rsquo;ai fais une pr\u00e9sentation succincte des objets de ProxySQL 1.3 la derni\u00e8re fois :\u00a0<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><\/p>\n<p>La version 1.4 \u00e0 quelques diff\u00e9rences, la plus notable dans notre contexte est l&rsquo;apparition de la table\u00a0\u00a0<em><strong>mysql_group_replication_hostgroups<\/strong><\/em> :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \nSHOW CREATE TABLE main.mysql_group_replication_hostgroups\\G\n*************************** 1. row ***************************ajouter les n\u0153uds du cluster\n       table: mysql_group_replication_hostgroups\nCreate Table: CREATE TABLE mysql_group_replication_hostgroups (\n    writer_hostgroup INT CHECK (writer_hostgroup&gt;=0) NOT NULL PRIMARY KEY,\n    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup&gt;=0 AND backup_writer_hostgroup&lt;&gt;writer_hostgroup) NOT NULL,\n    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup&lt;&gt;writer_hostgroup AND backup_writer_hostgroup&lt;&gt;reader_hostgroup AND reader_hostgroup&gt;0),\n    offline_hostgroup INT NOT NULL CHECK (offline_hostgroup&lt;&gt;writer_hostgroup AND offline_hostgroup&lt;&gt;reader_hostgroup AND backup_writer_hostgroup&lt;&gt;offline_hostgroup AND offline_hostgroup&gt;=0),\n    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,\n    max_writers INT NOT NULL CHECK (max_writers &gt;= 0) DEFAULT 1,\n    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,\n    max_transactions_behind INT CHECK (max_transactions_behind&gt;=0) NOT NULL DEFAULT 0,\n    comment VARCHAR,\n    UNIQUE (reader_hostgroup),\n    UNIQUE (offline_hostgroup),\n    UNIQUE (backup_writer_hostgroup))<\/pre>\n<p>La meilleure description trouv\u00e9e est disponible dans l\u2019article de mon coll\u00e8gue <a href=\"https:\/\/twitter.com\/lefred\" target=\"_blank\" rel=\"noopener\">@Lefred<\/a> :\u00a0<a href=\"http:\/\/lefred.be\/content\/mysql-group-replication-native-support-in-proxysql\/\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication: native support in ProxySQL<\/a><\/p>\n<p>Je cite<\/p>\n<p>\u00a0\u00bb<\/p>\n<p>There are many new columns, let\u2019s have a look at their meaning:<\/p>\n<table>\n<tbody>\n<tr>\n<th>Column Name<\/th>\n<th>Description<\/th>\n<\/tr>\n<tr>\n<td>writer_hostgroup<\/td>\n<td>the id of the hostgroup that will contain all the members that are writer<\/td>\n<\/tr>\n<tr>\n<td>backup_writer_hostgroup<\/td>\n<td>if the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is<br \/>\nlarger than the max_writers, the extra nodes are located in that backup writer group<\/td>\n<\/tr>\n<tr>\n<td>reader_hostgroup<\/td>\n<td>the id of the hostgroup that will contain all the members in read_only<\/td>\n<\/tr>\n<tr>\n<td>offline_hostgroup<\/td>\n<td>the id of the hostgroup that will contain the host not being online or not being part of the Group<\/td>\n<\/tr>\n<tr>\n<td>active<\/td>\n<td>when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups<\/td>\n<\/tr>\n<tr>\n<td>max_writers<\/td>\n<td>limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode<\/td>\n<\/tr>\n<tr>\n<td>writer_is_also_reader<\/td>\n<td>boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup<\/td>\n<\/tr>\n<tr>\n<td>max_transactions_behind<\/td>\n<td>if the value is greater than 0, it defines how much a node can be lagging in applying the transactions from the Group,\u00a0<a href=\"http:\/\/lefred.be\/content\/mysql-group-replication-synchronous-or-asynchronous-replication\/\" target=\"_blank\" rel=\"noopener\">see this post for more info<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u00a0\u00bb<\/p>\n<p>Pas mieux \ud83d\ude42<\/p>\n<p>Notre configuration est la suivante :<\/p>\n<ul>\n<li>writer_hostgroup = 2<\/li>\n<li>backup_writer_hostgroup = 4<\/li>\n<li>reader_hostgroup = 3<\/li>\n<li>offline_hostgroup = 1<\/li>\n<li>active = 1<\/li>\n<li>max_writers = 1<\/li>\n<li>writer_is_also_reader = 1<\/li>\n<li>max_transactions_behind = 0<\/li>\n<\/ul>\n<p>Ce qui nous donne :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \nINSERT 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);\n\n-- Save &amp; load new configuration\nSAVE MYSQL SERVERS TO DISK;\nLOAD MYSQL SERVERS TO RUNTIME;\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \nselect * from mysql_group_replication_hostgroups\\G\n*************************** 1. row ***************************\n       writer_hostgroup: 2\nbackup_writer_hostgroup: 4\n       reader_hostgroup: 3\n      offline_hostgroup: 1\n                 active: 1\n            max_writers: 1\n  writer_is_also_reader: 1\nmax_transactions_behind: 0\n                comment: NULL<\/pre>\n<h3>Configuration de la supervision<\/h3>\n<p>Un peu plus haut on a cr\u00e9\u00e9 un utilisateur de supervision dans le cluster.<\/p>\n<p>C&rsquo;est cet utilisateur que va utiliser ProxySQL pour \u00eatre au courant de l&rsquo;\u00e9tat des diff\u00e9rents n\u0153uds du cluster :<\/p>\n<pre class=\"lang:mysql decode:true\">-- Set user name &amp; password for monitoring module\nSET mysql-monitor_username='proxysqlmonitor';\nSET mysql-monitor_password='Very-S3cr3t';\n\n-- Save &amp; load new configuration\nSAVE MYSQL VARIABLES TO DISK;\nLOAD MYSQL VARIABLES TO RUNTIME;<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \nselect hostgroup_id, hostname, status  from runtime_mysql_servers;\n+--------------+-------------+--------+\n| hostgroup_id | hostname    | status |\n+--------------+-------------+--------+\n| 2            | mysql_node1 | ONLINE |\n| 2            | mysql_node3 | ONLINE |\n| 2            | mysql_node2 | ONLINE |\n+--------------+-------------+--------+<\/pre>\n<h3>Cr\u00e9ation de l&rsquo;utilisateur applicatif<\/h3>\n<p>L&rsquo;application se connecte au serveur MySQL avec un utilisateur et un mot de passe (dans le meilleur des cas :D).<\/p>\n<p>Cet utilisateur doit bien \u00e9videmment exister dans les diff\u00e9rents serveurs qui composent le cluster, avec les droits MySQL qui vont bien.<\/p>\n<p>Cet utilisateur doit \u00e9galement \u00eatre renseign\u00e9 dans ProxySQL :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \nINSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('app_user', 'app_pwd, 1, 2, 200);\n\n-- Save &amp; load new configuration\nSAVE MYSQL USERS TO DISK;\nLOAD MYSQL USERS TO RUNTIME;\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \nselect * from mysql_users;\n*************************** 1. row ***************************\n              username: app_user\n              password: app_pwd\n                active: 1\n               use_ssl: 0\n     default_hostgroup: 2\n        default_schema: NULL\n         schema_locked: 0\ntransaction_persistent: 1\n          fast_forward: 0\n               backend: 1\n              frontend: 1\n       max_connections: 200<\/pre>\n<h3>Modifier le port d\u2019\u00e9coute<\/h3>\n<p>Par d\u00e9faut l&rsquo;application va se connecter au cluster \u00e0 travers ProxySQL en utilisant le port <strong>6032<\/strong> (dans notre cas:\u00a0172.22.0.2:6032)<\/p>\n<p>Dans la vraie vie, les applications se connectent \u00e0 MySQL bien souvent en utilisant le port MySQL par d\u00e9faut, <strong>3306<\/strong>.<\/p>\n<p>ll est donc possible (pas obligatoire donc) de modifier le port de ProxySQL pour qu&rsquo;il \u00e9coute sur 3306 :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \nSET mysql-interfaces='0.0.0.0:3306;\/tmp\/proxysql.sock';\n\nSAVE MYSQL VARIABLES TO DISK;\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \nSHOW VARIABLES LIKE 'mysql-interfaces'\\G\n*************************** 1. row ***************************\nVariable_name: mysql-interfaces\n        Value: 0.0.0.0:3306;\/tmp\/proxysql.sock<\/pre>\n<p><em><span style=\"text-decoration: underline;\">Note 3<\/span>: Pour une myst\u00e9rieuse raison, ce dernier changement de configuration ne se charge pas \u00e0 chaud (runtime). En clair je dois donc red\u00e9marrer ProxySQL pour que ce changement soit pris en compte.<\/em><\/p>\n<pre class=\"lang:sh decode:true\">$ service proxysql restart\nShutting down ProxySQL: DONE!\nStarting ProxySQL: DONE!\n<\/pre>\n<p>La configuration de ProxySQL pour MySQL Group Replication est maintenant termin\u00e9e \\o\/<\/p>\n<p>J&rsquo;en profite pour vous pr\u00e9senter une nouvelle table dans la version 1.4 :\u00a0<em><strong>mysql_server_group_replication_log<\/strong><\/em>.<\/p>\n<p>Elle est utile pour la supervision :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \nselect * from mysql_server_group_replication_log order by time_start_us desc limit 3\\G\n*************************** 1. row ***************************\n           hostname: mysql_node3\n               port: 3306\n      time_start_us: 1515079109822616\n    success_time_us: 1782\n   viable_candidate: YES\n          read_only: YES\ntransactions_behind: 0\n              error: NULL\n*************************** 2. row ***************************\n           hostname: mysql_node2\n               port: 3306\n      time_start_us: 1515079109822292\n    success_time_us: 1845\n   viable_candidate: YES\n          read_only: YES\ntransactions_behind: 0\n              error: NULL\n*************************** 3. row ***************************\n           hostname: mysql_node1\n               port: 3306\n      time_start_us: 1515079109821971\n    success_time_us: 1582\n   viable_candidate: YES\n          read_only: NO\ntransactions_behind: 0\n              error: NULL\n<\/pre>\n<h2>Playtime<\/h2>\n<p>Pour rappel, le workflow est le suivant:<\/p>\n<ul>\n<li>L&rsquo;application se connecte \u00e0 ProxySQL (i.e. elle ne voit et ne connait que le proxy)<\/li>\n<li>ProxySQL r\u00e9cup\u00e8re les transactions et les redirigent sur le noeud primaire du cluster MySQL Group Replication.<\/li>\n<li>En cas de crash\/arr\u00eat du Primaire,\n<ul>\n<li>MySQL Group Replication \u00e9lit un nouveau primaire<\/li>\n<li>ProxySQL identifie le nouveau primaire\u00a0et dirige les transactions vers ce nouveau primaire<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>L&rsquo;application doit donc \u00eatre configur\u00e9e pour se connecter \u00e0 ProxySQL. Par exemple, si mon application est <a href=\"https:\/\/www.drupal.org\/\" target=\"_blank\" rel=\"noopener\">Drupal<\/a> mon fichier <em>settings.php<\/em> ressemblera \u00e0 l&rsquo;extrait de code suivant :<\/p>\n<pre class=\"lang:php decode:true\" title=\"Extract from setting.php (Drupal)\">...\n$databases['default']['default'] = array (\n  'database' =&gt; 'drupal',\n  'username' =&gt; 'app_user',\n  'password' =&gt; 'app_pwd',\n  'prefix' =&gt; 'drupal_',\n  'host' =&gt; '172.22.0.2',\n  'port' =&gt; '3306',\n  'namespace' =&gt; 'Drupal\\\\Core\\\\Database\\\\Driver\\\\mysql',\n  'driver' =&gt; 'mysql',\n);\n...<\/pre>\n<ul>\n<li>Utilisateur : app_user<\/li>\n<li>Mot de passe : app_pwd<\/li>\n<li>Port : 3306 (ProxySQL)<\/li>\n<li>Host : 172.22.0.2 (ProxySQL)<\/li>\n<\/ul>\n<p>CQFD!<\/p>\n<p>Simulons tout cela en ligne de commande !<\/p>\n<p>Mon application est le client texte\u00a0<em><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql.html\" target=\"_blank\" rel=\"noopener\"><strong>mysql<\/strong><\/a><\/em>. Pour simplifier mes commandes je cr\u00e9e au pr\u00e9alable un fichier de configuration pour le client <em>mysql<\/em> qui contient les informations suivantes :<\/p>\n<pre class=\"lang:sh decode:true\">$ cat \/tmp\/temp.cnf\n[mysql]\nuser=app_user\npassword=app_pwd\nprotocol=tcp\n<\/pre>\n<p><span style=\"text-decoration: underline;\">Note 4<\/span><em> : Avoir un mot de passe en clair dans un fichier texte non chiffr\u00e9 n&rsquo;est absolument pas recommand\u00e9.<\/em><\/p>\n<p>Mes serveurs sont configur\u00e9s avec la variable <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-slave.html#option_mysqld_report-host\" target=\"_blank\" rel=\"noopener\">report_host<\/a> renseign\u00e9e (voir <a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/<\/a>).<\/p>\n<pre class=\"lang:sh decode:true \">$ for x in {1..5}; do mysql --defaults-file=\/tmp\/temp.cnf -h 172.22.0.2 -P 3306  -BNe\"SELECT @@report_host;\" ; done;\nmysql_node1\nmysql_node1\nmysql_node1\nmysql_node1\nmysql_node1\n<\/pre>\n<p>Le primaire du cluster est encore mysql_node1 (pas encore de <em>database failover<\/em> depuis le d\u00e9but de l&rsquo;article, mais on y arrive).<\/p>\n<p>Maintenant testons le failover avec un exemple l\u00e9g\u00e8rement plus complexe.<\/p>\n<p>Au pr\u00e9alable, cr\u00e9ons la table <em>test.poc<\/em> au format <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-storage-engine.html\" target=\"_blank\" rel=\"noopener\">InnoDB<\/a> :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt;\nCREATE SCHEMA test;\n\nCREATE TABLE test.poc (\nid tinyint unsigned NOT NULL AUTO_INCREMENT,\nhost varchar(11),\ntime timestamp,\nPRIMARY KEY (id)\n) ENGINE=InnoDB;<\/pre>\n<p>L\u2019application test jouera les 2 requ\u00eates suivante :<\/p>\n<ul>\n<li><em>INSERT INTO test.poc (host) VALUES (@@report_host);<\/em><\/li>\n<li><em>SELECT * FROM test.poc;<\/em><\/li>\n<\/ul>\n<p>Gr\u00e2ce \u00e0 ces 2 requ\u00eates, le petit scripts ci-dessous et\u00a0\u00e0 un kill -9 opportun,\u00a0 on va \u00eatre en mesure de suivre les processus de routage (ProxySQL) et de failover (MySQL Group Replication).<\/p>\n<pre class=\"lang:sh decode:true\">$ while true; do\n&gt;   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;\";\n&gt;   echo;\n&gt;   sleep 4;\n&gt; done\n\n2\tmysql_node1\t2018-01-04 16:42:31 &lt;=&gt; Inserted row #1\n\n2\tmysql_node1\t2018-01-04 16:42:31\n9\tmysql_node1\t2018-01-04 16:42:35 &lt;=&gt; Inserted row #2\n\n2\tmysql_node1\t2018-01-04 16:42:31\n9\tmysql_node1\t2018-01-04 16:42:35\n11\tmysql_node3\t2018-01-04 16:42:43 &lt;=&gt; Inserted row #3 =&gt; Failover! New primary is mysql_node3\n\n2\tmysql_node1\t2018-01-04 16:42:31\n9\tmysql_node1\t2018-01-04 16:42:35\n11\tmysql_node3\t2018-01-04 16:42:43\n18\tmysql_node3\t2018-01-04 16:42:47 &lt;=&gt; Inserted row #4\n\n<\/pre>\n<p>Les transactions 1 &amp; 2 (ids 2 et 9) sont jou\u00e9es sur <em>mysql_node 1<\/em>.<\/p>\n<p>A partir de la troisi\u00e8me transaction (ids 11 et 18), elles sont jou\u00e9es sur le nouveau primaire <em>mysql_node 3<\/em>, car <em>mysql_node 1<\/em>\u00a0a crash\u00e9.<\/p>\n<p>Terminons ce tuto en image.<\/p>\n<h2>MySQL Enterprise Monitor<\/h2>\n<p>Comme \u00e9nonc\u00e9 en introduction, une architecture de base de donn\u00e9es doit de se reposer sur les 3 piliers : <strong>Monitoring<\/strong> \/ <strong>Backup process<\/strong> \/ <strong>High Availability<\/strong>.<\/p>\n<p>Je vous pr\u00e9sente de fa\u00e7on succincte,\u00a0<a href=\"https:\/\/www.mysql.com\/products\/enterprise\/monitor.html\" target=\"_blank\" rel=\"noopener\">MySQL Enterprise Monitor<\/a> (MEM) qui est l&rsquo;outil de supervision de MySQL, disponible avec la version commerciale de MySQL (<a href=\"https:\/\/www.mysql.com\/fr\/why-mysql\/presentations\/mysql-enterprise-edition\/\" target=\"_blank\" rel=\"noopener\">MySQL Enterprise Edition<\/a>). Il permet la d\u00e9tection et l&rsquo;alerte des probl\u00e8mes, la supervision des serveurs, des backups&#8230; des diff\u00e9rents types de r\u00e9plications, y compris Group Replication.<\/p>\n<p>Pour essayer les diff\u00e9rents outils Enterprise, c&rsquo;est <a href=\"https:\/\/www.mysql.com\/fr\/trials\/\" target=\"_blank\" rel=\"noopener\">par ici<\/a>.<\/p>\n<p>Ci-dessous quelques captures d&rsquo;\u00e9crans des diff\u00e9rents \u00e9tats du cluster supervis\u00e9 par MySQL Enterprise Monitor (click to enlarge):<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online.png?resize=679%2C392\" alt=\"MySQL\" width=\"679\" height=\"392\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_group_only.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_group_only.png?resize=499%2C541\" alt=\"MySQL\" width=\"499\" height=\"541\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_status.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_status.png?resize=738%2C177\" alt=\"MySQL\" width=\"738\" height=\"177\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_replication_config.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_online_replication_config.png?resize=691%2C340\" alt=\"MySQL\" width=\"691\" height=\"340\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_down.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_down.png?resize=499%2C608\" alt=\"MySQL\" width=\"499\" height=\"608\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_down_events.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_down_events.png?resize=622%2C149\" alt=\"MySQL\" width=\"622\" height=\"149\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_down_details.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_down_details.png?resize=622%2C149\" alt=\"MySQL\" width=\"622\" height=\"149\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_events.png\" target=\"_blank\" rel=\"noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/demo_MEM_GR_3_nodes_1_node_events.png?resize=738%2C108\" alt=\"MySQL\" width=\"738\" height=\"108\" \/><\/a><\/p>\n<h2>Le(s) mot(s) de la fin<\/h2>\n<p>Fin \ud83d\ude00<\/p>\n<p>Vous connaissez d\u00e9j\u00e0 les technologies, <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Replication<\/strong><\/a> et <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-semisync.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL semi-synchronous Replication<\/strong><\/a>.<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Group Replication<\/strong><\/a> est un outil suppl\u00e9mentaire qui apporte notamment la notion de \u00ab\u00a0<strong>Automatic Database Failover<\/strong>\u00a0\u00bb qu&rsquo;il manquait \u00e0 MySQL.<\/p>\n<p>Ces architectures s&rsquo;utilisent la plupart du temps avec un proxy. <a href=\"http:\/\/www.proxysql.com\/\" target=\"_blank\" rel=\"noopener\">ProxySQL<\/a> est sans aucun doute aujourd&rsquo;hui l&rsquo;une des meilleures solutions pour MySQL.<\/p>\n<p><em><span style=\"text-decoration: underline;\">Note 5<\/span>: MySQL propose <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-innodb-cluster-userguide.html\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster<\/a>, package comprenant MySQL Group Replication, <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-router\/2.1\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL Router<\/a> et <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell-excerpt\/5.7\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL Shell<\/a>.<br \/>\nTuto :\u00a0<a href=\"http:\/\/dasini.net\/blog\/2018\/08\/21\/tutoriel-deployer-mysql-5-7-innodb-cluster\/\" target=\"_blank\" rel=\"noopener\">Tutoriel \u2013 D\u00e9ployer MySQL 5.7 InnoDB Cluster<\/a><\/em><\/p>\n<h2>References<\/h2>\n<p><strong>dasini.net<\/strong><\/p>\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\/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=\"http:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL 1.3 pour MySQL Group Replication<\/a><\/li>\n<li><em><a href=\"http:\/\/dasini.net\/blog\/2018\/08\/21\/tutoriel-deployer-mysql-5-7-innodb-cluster\/\" target=\"_blank\" rel=\"noopener\">Tutoriel \u2013 D\u00e9ployer MySQL 5.7 InnoDB Cluster<\/a><\/em><\/li>\n<\/ul>\n<p><strong>MySQL Group Replication<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/mysqlhighavailability.com\/group-replication-features-backported-to-mysql-5-7\/\" target=\"_blank\" rel=\"noopener\">Group Replication Features backported to MySQL 5.7<\/a><\/li>\n<li><a href=\"https:\/\/mysqlhighavailability.com\/group-replication-prioritise-member-for-the-primary-member-election\/\" target=\"_blank\" rel=\"noopener\">Group Replication: Prioritise member for the Primary Member Election<\/a><\/li>\n<li><a href=\"https:\/\/mysqlhighavailability.com\/group-replication-support-savepoint\/\" target=\"_blank\" rel=\"noopener\">Group Replication support SAVEPOINT<\/a><\/li>\n<li><a href=\"https:\/\/mysqlhighavailability.com\/group-replication-extending-group-replication-performance_schema-tables\/\" target=\"_blank\" rel=\"noopener\">Group Replication \u2013 Extending Group Replication performance_schema tables<\/a><\/li>\n<li><a href=\"https:\/\/mysqlhighavailability.com\/category\/replication\/group-replication\/\" target=\"_blank\" rel=\"noopener\">MySQL High Availability blog<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener\">Doc<\/a><\/li>\n<li><a href=\"http:\/\/lefred.be\/?s=group+replication\" target=\"_blank\" rel=\"noopener\">lefred&rsquo;s blog<\/a><\/li>\n<\/ul>\n<p><strong>ProxySQL<\/strong><\/p>\n<ul>\n<li><a href=\"http:\/\/lefred.be\/content\/mysql-group-replication-native-support-in-proxysql\/\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication: native support in ProxySQL<\/a><\/li>\n<li><a href=\"http:\/\/www.proxysql.com\/blog\/how-to-run-multiple-proxysql-instances\" target=\"_blank\" rel=\"noopener\">http:\/\/www.proxysql.com\/blog\/how-to-run-multiple-proxysql-instances<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/sysown\/proxysql\/wiki\" target=\"_blank\" rel=\"noopener\">Doc<\/a><\/li>\n<\/ul>\n<p>Thanks for using MySQL!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&rsquo;ann\u00e9e 2018\u00a0 concerne l&rsquo;un des meilleurs combos\u00a0du moment, en mati\u00e8re de haute disponibilit\u00e9 niveau base de donn\u00e9es :<br \/>\n &#8211; MySQL 5.7 Group Replication\u00a0: 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).<br \/>\n &#8211; ProxySQL 1.4\u00a0: modestement le meilleur proxy pour MySQL.<\/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,316,359,8,294,337],"tags":[286,292,330,332,334,361,300,341],"class_list":["post-2035","post","type-post","status-publish","format-standard","hentry","category-group-replication","category-haute-disponibilite","category-monitoring-fr","category-mysql","category-proxysql","category-tuto","tag-group-replication","tag-ha","tag-high-availability","tag-mem","tag-monitor","tag-monitoring","tag-proxysql","tag-tuto"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-wP","jetpack-related-posts":[{"id":1800,"url":"https:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/","url_meta":{"origin":2035,"position":0},"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":1634,"url":"https:\/\/dasini.net\/blog\/2016\/08\/29\/meetup-mysql-group-replication-mysql-as-a-document-store\/","url_meta":{"origin":2035,"position":1},"title":"Meetup &#8211; MySQL Group Replication &#038; MySQL as a Document Store","author":"Olivier DASINI","date":"29 ao\u00fbt 2016","format":false,"excerpt":"Oracle MySQL, Openska et Executive MBA Epitech ont le plaisir de vous inviter le mardi 6 septembre pour le premier meetup MySQL de la rentr\u00e9e. Au programme: MySQL Group Replication & MySQL as a Document Store","rel":"","context":"Dans &quot;Conf\u00e9rence&quot;","block_context":{"text":"Conf\u00e9rence","link":"https:\/\/dasini.net\/blog\/category\/conference\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/mysqlhighavailability.com\/wp-content\/uploads\/2014\/09\/1.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1746,"url":"https:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/","url_meta":{"origin":2035,"position":2},"title":"Configurer ProxySQL pour MySQL Group Replication","author":"Olivier DASINI","date":"11 janvier 2017","format":false,"excerpt":"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. Ce type d'architecture est souvent utilis\u00e9 avec un composant qui se place entre l'application et le cluster,composant g\u00e9n\u00e9ralement appel\u00e9 proxy (quelque chose) ou router quelque chose. Dans\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\/MySQL_Group_Replication_and_ProxySQL.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":2035,"position":3},"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":[]},{"id":1678,"url":"https:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/","url_meta":{"origin":2035,"position":4},"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":4381,"url":"https:\/\/dasini.net\/blog\/2021\/02\/24\/webinar-la-haute-disponibilite-avec-mysql\/","url_meta":{"origin":2035,"position":5},"title":"Webinar \u2013  La haute disponibilit\u00e9 avec MySQL","author":"Olivier DASINI","date":"24 f\u00e9vrier 2021","format":false,"excerpt":"MySQL propose plusieurs solutions pour vous aider \u00e0 b\u00e2tir une architecture hautement disponible. On retrouve InnoDB Cluster avec Group Replication, la r\u00e9plication asynchrone en utilisant InnoDB ReplicaSet ou encore la r\u00e9plication semi-synchrone, et enfin NDB Cluster. Dans cette session, nous examinerons ces diff\u00e9rents sc\u00e9narios, les \u00e9cueils \u00e0 \u00e9viter et les\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2035","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=2035"}],"version-history":[{"count":51,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2035\/revisions"}],"predecessor-version":[{"id":7243,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2035\/revisions\/7243"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=2035"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=2035"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=2035"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}