{"id":2090,"date":"2018-01-09T09:53:26","date_gmt":"2018-01-09T08:53:26","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2090"},"modified":"2018-08-21T09:57:13","modified_gmt":"2018-08-21T08:57:13","slug":"setting-up-proxysql-1-4-with-mysql-5-7-group-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/01\/09\/setting-up-proxysql-1-4-with-mysql-5-7-group-replication\/","title":{"rendered":"Setting up ProxySQL 1.4 with MySQL 5.7 Group Replication"},"content":{"rendered":"<p><em><a href=\"http:\/\/dasini.net\/blog\/2018\/01\/09\/configurer-proxysql-1-4-pour-mysql-5-7-group-replication\/\" target=\"_blank\" rel=\"noopener\">Lire cet article en fran\u00e7ais<\/a><\/em><\/p>\n<p>There are 3\u00a0pillars for a database architecture:<\/p>\n<ul>\n<li><strong>Monitoring<\/strong><\/li>\n<li><strong>Backup \/ Restore process<\/strong><\/li>\n<li><strong>High Availability<\/strong><\/li>\n<\/ul>\n<p>This blog post is about database High Availability; more precisely about one of the best combo of the moment :<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<p>&nbsp;<\/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: the only native HA solution for MySQL, it&rsquo;s a\u00a0Single\/Multi-master update everywhere replication plugin for MySQL with built-in automatic distributed recovery, conflict detection and group membership.<\/li>\n<li><a href=\"http:\/\/www.proxysql.com\/\" target=\"_blank\" rel=\"noopener\">ProxySQL 1.4<\/a>\u00a0: probably the\u00a0<a href=\"http:\/\/www.proxysql.com\/compare\" target=\"_blank\" rel=\"noopener\">best proxy<\/a> for MySQL.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span>\u00a0: <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication.html\" target=\"_blank\" rel=\"noopener\">MySQL replication<\/a> is indeed more than a serious alternative for HA. By the way ProxySQL natively handle it.<\/em><\/p>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span>\u00a0: ProxySQL have a plethora of <a href=\"http:\/\/www.proxysql.com\/#features\" target=\"_blank\" rel=\"noopener\">features<\/a>, <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">however the purpose of this article is its use in a MySQL Group Replication context.<\/span><\/span>.<\/em><\/p>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span>\u00a0:<\/em>\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-cluster.html\" target=\"_blank\" rel=\"noopener\">MySQL NDB Cluster<\/a>\u00a0is also a HA solution adapted for the distributed computing environment. This is out of the scope of this article.<\/p>\n<p>&nbsp;<\/p>\n<p>ProxySQL is compatible with MySQL Group Replication since version 1.3 see (in French) :\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>. However, version <strong>1.4<\/strong> have a native MySQL Group Replication support. <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">It is therefore easier to use these two popular technologies together.<\/span><\/span><\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">This is what we will see now.<\/span><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">In this article I will make the following assumptions :<\/span><\/span><\/p>\n<ul>\n<li>MySQL <strong>5.7.20<\/strong> is <a href=\"http:\/\/dasini.net\/blog\/2017\/07\/24\/how-to-get-mysql\/\" target=\"_blank\" rel=\"noopener\">downloaded<\/a> and <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/installing.html\" target=\"_blank\" rel=\"noopener\">installed<\/a>, on 3 hosts.<\/li>\n<li>ProxySQL <strong>1.4.4<\/strong> is <a href=\"https:\/\/github.com\/sysown\/proxysql\/releases\/tag\/v1.4.4\" target=\"_blank\" rel=\"noopener\">downloaded<\/a> and <a href=\"https:\/\/github.com\/sysown\/proxysql\/wiki\" target=\"_blank\" rel=\"noopener\">installed<\/a>\u00a0 on 1 host (ProxySQL\u00a0HA <span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">is not covered in this article<\/span><\/span>).<\/li>\n<li>A 3 nodes MySQL Group Replication cluster is <a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">up and running<\/a>.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2>MySQL Group Replication<\/h2>\n<p><span style=\"text-decoration: underline;\">Specifications<br \/>\n<\/span><\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Server version<\/span> : 5.7.20<\/li>\n<li><span style=\"text-decoration: underline;\">Plugin version<\/span> : 1.0<\/li>\n<li><span style=\"text-decoration: underline;\">Node 1<\/span> : <strong>mysql_node1<\/strong>, 172.22.0.10 : 3306<\/li>\n<li><span style=\"text-decoration: underline;\">Node 2<\/span> : <strong>mysql_node2<\/strong>, 172.22.0.20 : 3306<\/li>\n<li><span style=\"text-decoration: underline;\">Node 3<\/span> : <strong>mysql_node3<\/strong>, 172.22.0.30 : 3306<\/li>\n<\/ul>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">These characteristics therefore represent a 3-nodes MySQL Group Replication cluster, up and running:<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt;\r\n-- MySQL Server version number\r\nSELECT left(version(),6);\r\n+-------------------+\r\n| left(version(),6) |\r\n+-------------------+\r\n| 5.7.20            |\r\n+-------------------+\r\n\r\n-- MySQL Group Replication plugin details\r\nSELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'group%'\\G\r\n*************************** 1. row ***************************\r\n           PLUGIN_NAME: group_replication\r\n        PLUGIN_VERSION: 1.0\r\n         PLUGIN_STATUS: ACTIVE\r\n           PLUGIN_TYPE: GROUP REPLICATION\r\n   PLUGIN_TYPE_VERSION: 1.1\r\n        PLUGIN_LIBRARY: group_replication.so\r\nPLUGIN_LIBRARY_VERSION: 1.7\r\n         PLUGIN_AUTHOR: ORACLE\r\n    PLUGIN_DESCRIPTION: Group Replication (1.0.0)\r\n        PLUGIN_LICENSE: PROPRIETARY\r\n           LOAD_OPTION: FORCE_PLUS_PERMANENT\r\n\r\n-- MySQL Group Replication member status\r\nSELECT * FROM performance_schema.replication_group_members\\G\r\n*************************** 1. row ***************************\r\nCHANNEL_NAME: group_replication_applier\r\n   MEMBER_ID: 3c2039a6-f152-11e7-90da-0242ac16001e\r\n MEMBER_HOST: mysql_node3\r\n MEMBER_PORT: 3306\r\nMEMBER_STATE: ONLINE\r\n*************************** 2. row ***************************\r\nCHANNEL_NAME: group_replication_applier\r\n   MEMBER_ID: 3c2039a8-f152-11e7-9132-0242ac160014\r\n MEMBER_HOST: mysql_node2\r\n MEMBER_PORT: 3306\r\nMEMBER_STATE: ONLINE\r\n*************************** 3. row ***************************\r\nCHANNEL_NAME: group_replication_applier\r\n   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a\r\n MEMBER_HOST: mysql_node1\r\n MEMBER_PORT: 3306\r\nMEMBER_STATE: ONLINE\r\n<\/pre>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/monitor.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Enterprise Monitor<\/strong><\/a> <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">gives us a graphical view of the cluster and its state:<\/span> (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>&nbsp;<\/p>\n<p>The cluster is in <strong>single primary<\/strong> mode, ie <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">only one node is available for reading &amp; writing at a time (while the other 2 nodes are\u00a0<strong>read-only<\/strong>).<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt;\r\n-- Is single primary mode activated?\r\nSHOW VARIABLES LIKE 'group_replication_single_primary_mode';\r\n+---------------------------------------+-------+\r\n| Variable_name                         | Value |\r\n+---------------------------------------+-------+\r\n| group_replication_single_primary_mode | ON    |\r\n+---------------------------------------+-------+\r\n\r\n-- Who is the primary node?\r\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE\r\nFROM performance_schema.replication_group_members\r\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\r\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\r\n*************************** 1. row ***************************\r\n   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a\r\n MEMBER_HOST: mysql_node1\r\n MEMBER_PORT: 3306\r\nMEMBER_STATE: ONLINE\r\n<\/pre>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/monitor.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Enterprise Monitor<\/strong><\/a> shows us (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>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">I am going to extend the MySQL 5.7<\/span><\/span> <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/sys-schema.html\" target=\"_blank\" rel=\"noopener\"><em>sys<\/em><\/a> <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">schema with the following script<\/span><\/span> :\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;\r\n\r\nDELIMITER $$\r\n\r\nCREATE FUNCTION IFZERO(a INT, b INT)\r\nRETURNS INT\r\nDETERMINISTIC\r\nRETURN IF(a = 0, b, a)$$\r\n\r\nCREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)\r\nRETURNS INT\r\nDETERMINISTIC\r\nRETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$\r\n\r\nCREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))\r\nRETURNS TEXT(10000)\r\nDETERMINISTIC\r\nRETURN GTID_SUBTRACT(g, '')$$\r\n\r\nCREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))\r\nRETURNS INT\r\nDETERMINISTIC\r\nBEGIN\r\n  DECLARE result BIGINT DEFAULT 0;\r\n  DECLARE colon_pos INT;\r\n  DECLARE next_dash_pos INT;\r\n  DECLARE next_colon_pos INT;\r\n  DECLARE next_comma_pos INT;\r\n  SET gtid_set = GTID_NORMALIZE(gtid_set);\r\n  SET colon_pos = LOCATE2(':', gtid_set, 1);\r\n  WHILE colon_pos != LENGTH(gtid_set) + 1 DO\r\n     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);\r\n     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);\r\n     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);\r\n     IF next_dash_pos &lt; next_colon_pos AND next_dash_pos &lt; next_comma_pos THEN\r\n       SET result = result +\r\n         SUBSTR(gtid_set, next_dash_pos + 1,\r\n                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -\r\n         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;\r\n     ELSE\r\n       SET result = result + 1;\r\n     END IF;\r\n     SET colon_pos = next_colon_pos;\r\n  END WHILE;\r\n  RETURN result;\r\nEND$$\r\n\r\nCREATE FUNCTION gr_applier_queue_length()\r\nRETURNS INT\r\nDETERMINISTIC\r\nBEGIN\r\n  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT\r\nReceived_transaction_set FROM performance_schema.replication_connection_status\r\nWHERE Channel_name = 'group_replication_applier' ), (SELECT\r\n@@global.GTID_EXECUTED) )));\r\nEND$$\r\n\r\nCREATE FUNCTION gr_member_in_primary_partition()\r\nRETURNS VARCHAR(3)\r\nDETERMINISTIC\r\nBEGIN\r\n  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM\r\nperformance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') &gt;=\r\n((SELECT COUNT(*) FROM performance_schema.replication_group_members)\/2) = 0),\r\n'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN\r\nperformance_schema.replication_group_member_stats USING(member_id));\r\nEND$$\r\n\r\nCREATE VIEW gr_member_routing_candidate_status AS SELECT\r\nsys.gr_member_in_primary_partition() as viable_candidate,\r\nIF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM\r\nperformance_schema.global_variables WHERE variable_name IN ('read_only',\r\n'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,\r\nsys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$\r\n\r\nDELIMITER ;<\/pre>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">So I load the functions and views into the cluster <strong>primary<\/strong> node (<em>mysql_node1<\/em>) :<\/span><\/p>\n<pre class=\"lang:mysql decode:true\"># Loading extra functions and views to sys schema on the cluster (using a primary node)\r\n\r\n$ mysql -u root -p -h mysql_node1 &lt; .\/addition_to_sys.sql\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">This script will allow ProxySQL to monitor the state of the cluster nodes.<\/span><\/p>\n<p>e.g.<\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt; \r\n-- Status of the primary node\r\nSELECT * FROM sys.gr_member_routing_candidate_status;\r\n+------------------+-----------+---------------------+----------------------+\r\n| viable_candidate | read_only | transactions_behind | transactions_to_cert |\r\n+------------------+-----------+---------------------+----------------------+\r\n| YES              | NO        |                   0 |                    0 |\r\n+------------------+-----------+---------------------+----------------------+<\/pre>\n<pre class=\"lang:mysql decode:true \">node2&gt; \r\n-- Status of a secondary node\r\nSELECT * FROM sys.gr_member_routing_candidate_status;\r\n+------------------+-----------+---------------------+----------------------+\r\n| viable_candidate | read_only | transactions_behind | transactions_to_cert |\r\n+------------------+-----------+---------------------+----------------------+\r\n| YES              | YES       |                   0 |                    0 |\r\n+------------------+-----------+---------------------+----------------------+<\/pre>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">On the cluster side, the last configuration step consists of creating the supervision users that will be used by ProxySQL (yes, there is a relationship with the previous step) :).<\/span><\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Here again I use the <strong>primary<\/strong> of the group :<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">node1&gt;\r\n-- Create ProxySQL monitoring user inside the cluster\r\nCREATE USER proxysqlmonitor@'%' IDENTIFIED BY 'Very-S3cr3t';\r\n\r\nGRANT SELECT ON sys.* TO proxysqlmonitor@'%';\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Let&rsquo;s configure ProxySQL now !<\/p>\n<p>&nbsp;<\/p>\n<h2>ProxySQL<\/h2>\n<p><span style=\"text-decoration: underline;\">Specifications<\/span><\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Proxy version<\/span> : 1.4.4<\/li>\n<li><span style=\"text-decoration: underline;\">Admin Interface<\/span> : 172.22.0.2:<strong>6032<\/strong><\/li>\n<li><span style=\"text-decoration: underline;\">Cluster connection<\/span> : 172.22.0.2:<strong>3306<\/strong><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<pre class=\"lang:sh decode:true\">$ proxysql --version\r\nProxySQL version 1.4.4-139-ga51b040, codename Truls\r\n\r\n$ service proxysql status\r\nProxySQL is running (58).\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>ProxySQL configuration can be done online, which is obviously a very good thing.<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">Let&rsquo;s start by logging into the admin interface on the default port 6032 with the default admin user and password&#8230; : admin (!) &#8211; <em><span class=\"\">Defaults that can and must be changed in real life.<\/span><\/em><\/span><\/p>\n<pre class=\"lang:sh decode:true\">$ mysql -u admin -p -P 6032 --protocol=tcp main<\/pre>\n<p>&nbsp;<\/p>\n<h3>Servers configuration<\/h3>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">First step, add the cluster nodes to the proxy :<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">-- Add 3 nodes of the cluster into the mysql_servers table\r\nproxy&gt; \r\nINSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'mysql_node1', 3306), (2, 'mysql_node2', 3306), (2, 'mysql_node3', 3306);\r\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \r\nselect * from mysql_servers;\r\n+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\r\n| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |\r\n+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\r\n| 2            | mysql_node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |\r\n| 2            | mysql_node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |\r\n| 2            | mysql_node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |\r\n+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h3>Hostgroups configuration<\/h3>\n<p>I have done a brief presentation of ProxySQL 1.3 objects last time :\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><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Version 1.4 has some differences, the most notable in our context is the new table : <\/span><\/span><em><strong>mysql_group_replication_hostgroups<\/strong><\/em> :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \r\nSHOW CREATE TABLE main.mysql_group_replication_hostgroups\\G\r\n*************************** 1. row ***************************ajouter les n\u0153uds du cluster\r\n       table: mysql_group_replication_hostgroups\r\nCreate Table: CREATE TABLE mysql_group_replication_hostgroups (\r\n    writer_hostgroup INT CHECK (writer_hostgroup&gt;=0) NOT NULL PRIMARY KEY,\r\n    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup&gt;=0 AND backup_writer_hostgroup&lt;&gt;writer_hostgroup) NOT NULL,\r\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),\r\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),\r\n    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,\r\n    max_writers INT NOT NULL CHECK (max_writers &gt;= 0) DEFAULT 1,\r\n    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,\r\n    max_transactions_behind INT CHECK (max_transactions_behind&gt;=0) NOT NULL DEFAULT 0,\r\n    comment VARCHAR,\r\n    UNIQUE (reader_hostgroup),\r\n    UNIQUE (offline_hostgroup),\r\n    UNIQUE (backup_writer_hostgroup))<\/pre>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">The best description I have found is available in my brilliant colleague&rsquo;s blog post <\/span>:\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>I quote<\/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>by\u00a0<span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"> <\/span><a href=\"https:\/\/twitter.com\/lefred\" target=\"_blank\" rel=\"noopener\">@Lefred<\/a> \ud83d\ude42<\/p>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Our configuration is as follows :<\/span><\/span><\/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><span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Which gives us :<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \r\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);\r\n\r\n-- Save &amp; load new configuration\r\nSAVE MYSQL SERVERS TO DISK;\r\nLOAD MYSQL SERVERS TO RUNTIME;\r\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \r\nselect * from mysql_group_replication_hostgroups\\G\r\n*************************** 1. row ***************************\r\n       writer_hostgroup: 2\r\nbackup_writer_hostgroup: 4\r\n       reader_hostgroup: 3\r\n      offline_hostgroup: 1\r\n                 active: 1\r\n            max_writers: 1\r\n  writer_is_also_reader: 1\r\nmax_transactions_behind: 0\r\n                comment: NULL<\/pre>\n<p>&nbsp;<\/p>\n<h3>Supervision configuration<\/h3>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">A little further up we created a supervision user in the cluster (remember?).<\/span><\/span><\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">It is this user that will use ProxySQL to be aware of the state of the various nodes of the cluster :<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">-- Set user name &amp; password for monitoring module\r\nSET mysql-monitor_username='proxysqlmonitor';\r\nSET mysql-monitor_password='Very-S3cr3t';\r\n\r\n-- Save &amp; load new configuration\r\nSAVE MYSQL VARIABLES TO DISK;\r\nLOAD MYSQL VARIABLES TO RUNTIME;<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \r\nselect hostgroup_id, hostname, status  from runtime_mysql_servers;\r\n+--------------+-------------+--------+\r\n| hostgroup_id | hostname    | status |\r\n+--------------+-------------+--------+\r\n| 2            | mysql_node1 | ONLINE |\r\n| 2            | mysql_node3 | ONLINE |\r\n| 2            | mysql_node2 | ONLINE |\r\n+--------------+-------------+--------+<\/pre>\n<p>&nbsp;<\/p>\n<h3>Application user creation<\/h3>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">The application connects to the MySQL server with a user and password (in the best case: D).<\/span><\/span><\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">This user must obviously exist in the different MySQL instances that form the cluster, with the correct MySQL privileges.<\/span><\/span><\/p>\n<p>This user must also be created in ProxySQL :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \r\nINSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('app_user', 'app_pwd, 1, 2, 200);\r\n\r\n-- Save &amp; load new configuration\r\nSAVE MYSQL USERS TO DISK;\r\nLOAD MYSQL USERS TO RUNTIME;\r\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \r\nselect * from mysql_users;\r\n*************************** 1. row ***************************\r\n              username: app_user\r\n              password: app_pwd\r\n                active: 1\r\n               use_ssl: 0\r\n     default_hostgroup: 2\r\n        default_schema: NULL\r\n         schema_locked: 0\r\ntransaction_persistent: 1\r\n          fast_forward: 0\r\n               backend: 1\r\n              frontend: 1\r\n       max_connections: 200<\/pre>\n<p>&nbsp;<\/p>\n<h3>Change the listening port<\/h3>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">By default the application will connect to the cluster through ProxySQL using port <strong>6032<\/strong> (in our case: 172.22.0.2:6032)<\/span><\/span><\/p>\n<div id=\"tts_button\"><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">In real life, applications usually connect to MySQL using its default port, <strong>3306<\/strong>.<\/span><\/span><\/div>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">It is therefore possible (not required) to modify the ProxySQL port to listen on 3306 :<\/span><\/span><\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \r\nSET mysql-interfaces='0.0.0.0:3306;\/tmp\/proxysql.sock';\r\n\r\nSAVE MYSQL VARIABLES TO DISK;\r\n<\/pre>\n<pre class=\"lang:mysql decode:true \">proxy&gt; \r\nSHOW VARIABLES LIKE 'mysql-interfaces'\\G\r\n*************************** 1. row ***************************\r\nVariable_name: mysql-interfaces\r\n        Value: 0.0.0.0:3306;\/tmp\/proxysql.sock<\/pre>\n<p>&nbsp;<\/p>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span>\u00a0:\u00a0<span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">For a mysterious reason, this last configuration change is not online.<\/span> <span class=\"\">In other words I must restart ProxySQL for this change to be taken into account.<\/span><\/span><\/em><\/p>\n<pre class=\"lang:sh decode:true\">$ service proxysql restart\r\nShutting down ProxySQL: DONE!\r\nStarting ProxySQL: DONE!\r\n<\/pre>\n<p>&nbsp;<\/p>\n<div id=\"tts_button\"><\/div>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">The ProxySQL configuration for MySQL Group Replication is now complete<\/span><\/span> \\o\/<\/p>\n<p>I&rsquo;m taking this opportunity to introduce a new table in version 1.4 :\u00a0<em><strong>mysql_server_group_replication_log<\/strong><\/em>.<\/p>\n<p>Useful for monitoring :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt; \r\nselect * from mysql_server_group_replication_log order by time_start_us desc limit 3\\G\r\n*************************** 1. row ***************************\r\n           hostname: mysql_node3\r\n               port: 3306\r\n      time_start_us: 1515079109822616\r\n    success_time_us: 1782\r\n   viable_candidate: YES\r\n          read_only: YES\r\ntransactions_behind: 0\r\n              error: NULL\r\n*************************** 2. row ***************************\r\n           hostname: mysql_node2\r\n               port: 3306\r\n      time_start_us: 1515079109822292\r\n    success_time_us: 1845\r\n   viable_candidate: YES\r\n          read_only: YES\r\ntransactions_behind: 0\r\n              error: NULL\r\n*************************** 3. row ***************************\r\n           hostname: mysql_node1\r\n               port: 3306\r\n      time_start_us: 1515079109821971\r\n    success_time_us: 1582\r\n   viable_candidate: YES\r\n          read_only: NO\r\ntransactions_behind: 0\r\n              error: NULL\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h2>Playtime<\/h2>\n<p>As a reminder, workflow is as follows :<\/p>\n<ul>\n<li><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">The app connects to ProxySQL (i.e. it only sees and knows the proxy)<\/span><\/li>\n<li><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">ProxySQL retrieves transactions and redirects them to the primary node of the MySQL Group Replication cluster.<\/span><\/li>\n<li>In case of primary node crash \/ shutdown,\n<ul>\n<li>MySQL Group Replication elects a new primary<\/li>\n<li>ProxySQL identifies the new primary and sends transactions to this new primary<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">The application must therefore be configured to connect to ProxySQL. For example, if my app is <\/span><a href=\"https:\/\/www.drupal.org\/\" target=\"_blank\" rel=\"noopener\">Drupal<\/a>\u00a0 <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">then my <em>settings.php<\/em> file will look like the following code snippet :<\/span><\/p>\n<pre class=\"lang:php decode:true\" title=\"Extract from setting.php (Drupal)\">...\r\n$databases['default']['default'] = array (\r\n  'database' =&gt; 'drupal',\r\n  'username' =&gt; 'app_user',\r\n  'password' =&gt; 'app_pwd',\r\n  'prefix' =&gt; 'drupal_',\r\n  'host' =&gt; '172.22.0.2',\r\n  'port' =&gt; '3306',\r\n  'namespace' =&gt; 'Drupal\\\\Core\\\\Database\\\\Driver\\\\mysql',\r\n  'driver' =&gt; 'mysql',\r\n);\r\n...<\/pre>\n<ul>\n<li>User : app_user<\/li>\n<li>Password : app_pwd<\/li>\n<li>Port : 3306 (ProxySQL)<\/li>\n<li>Host : 172.22.0.2 (ProxySQL)<\/li>\n<\/ul>\n<p>QED!<\/p>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Let&rsquo;s simulate all this in the command line!<\/span><\/span><\/p>\n<p>My app is the <em><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql.html\" target=\"_blank\" rel=\"noopener\"><strong>mysql<\/strong><\/a><\/em> command-line tool.\u00a0<span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">To simplify my commands, I first create a configuration file for the mysql client that contains the following information (btw it&rsquo;s not recommended to have passwords in clear in a text file)\u00a0<\/span><\/span> :<\/p>\n<pre class=\"lang:sh decode:true\">$ cat \/tmp\/temp.cnf\r\n[mysql]\r\nuser=app_user\r\npassword=app_pwd\r\nprotocol=tcp\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">My servers are configured with the variable<\/span><\/span> <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> filled (see <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;\r\nmysql_node1\r\nmysql_node1\r\nmysql_node1\r\nmysql_node1\r\nmysql_node1\r\n<\/pre>\n<p>The cluster primary node is still mysql_node1 (no <em>database failover<\/em> yet since the beginning of this article, but we get there).<\/p>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Now let&rsquo;s test the failover with a slightly more complex example.<\/span><\/span><\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">First, let&rsquo;s create the <\/span><\/span><em>test.poc<\/em> table in <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-storage-engine.html\" target=\"_blank\" rel=\"noopener\">InnoDB<\/a> format :<\/p>\n<pre class=\"lang:mysql decode:true\">proxy&gt;\r\nCREATE SCHEMA test;\r\n\r\nCREATE TABLE test.poc (\r\nid tinyint unsigned NOT NULL AUTO_INCREMENT,\r\nhost varchar(11),\r\ntime timestamp,\r\nPRIMARY KEY (id)\r\n) ENGINE=InnoDB;<\/pre>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">The test application will run the following 2 queries :<\/span><\/span><\/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>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">With these 2 queries, the little scripts below and a timely kill -9, we will be able to follow the routing process (ProxySQL) and the database failover (MySQL Group Replication).<\/span><\/span><\/p>\n<pre class=\"lang:sh decode:true\">$ while true; do\r\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;\";\r\n&gt;   echo;\r\n&gt;   sleep 4;\r\n&gt; done\r\n\r\n2\tmysql_node1\t2018-01-04 16:42:31 &lt;=&gt; Inserted row #1\r\n\r\n2\tmysql_node1\t2018-01-04 16:42:31\r\n9\tmysql_node1\t2018-01-04 16:42:35 &lt;=&gt; Inserted row #2\r\n\r\n2\tmysql_node1\t2018-01-04 16:42:31\r\n9\tmysql_node1\t2018-01-04 16:42:35\r\n11\tmysql_node3\t2018-01-04 16:42:43 &lt;=&gt; Inserted row #3 =&gt; Failover! New primary is mysql_node3\r\n\r\n2\tmysql_node1\t2018-01-04 16:42:31\r\n9\tmysql_node1\t2018-01-04 16:42:35\r\n11\tmysql_node3\t2018-01-04 16:42:43\r\n18\tmysql_node3\t2018-01-04 16:42:47 &lt;=&gt; Inserted row #4\r\n\r\n<\/pre>\n<p>Transactions 1 &amp; 2 (ids 2 et 9) are run on <em>mysql_node 1<\/em>.<\/p>\n<p>From the third transaction (ids 11 et 18), they are run on the new primary <em>mysql_node 3<\/em>, because <em>mysql_node 1<\/em> crashed.<\/p>\n<p>&nbsp;<\/p>\n<p><span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Let&rsquo;s finish this tutorial with pictures.<\/span><\/span><\/p>\n<p>&nbsp;<\/p>\n<h2>MySQL Enterprise Monitor<\/h2>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\">As stated in the introduction, a database architecture should rely on the 3 pillars <\/span> : <strong>Monitoring<\/strong> \/ <strong>Backup process<\/strong> \/ <strong>High Availability<\/strong>.<\/p>\n<p>Below a brief introduction of <a href=\"https:\/\/www.mysql.com\/products\/enterprise\/monitor.html\" target=\"_blank\" rel=\"noopener\">MySQL Enterprise Monitor<\/a> (MEM) <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">which is the MySQL monitoring tool, available with the commercial editions of MySQL <\/span><\/span>(<a href=\"https:\/\/www.mysql.com\/fr\/why-mysql\/presentations\/mysql-enterprise-edition\/\" target=\"_blank\" rel=\"noopener\">MySQL Enterprise Edition<\/a>). MEM enables monitoring of MySQL instances and their hosts, notification of potential issues and problems, and advice on how to correct issues. You can also monitor and troubleshoot all types of MySQL Replication including MySQL Group Replication.<\/p>\n<p>If you want to try our\u00a0 Enterprise tools, please\u00a0<a href=\"https:\/\/www.mysql.com\/fr\/trials\/\" target=\"_blank\" rel=\"noopener\">click here<\/a>.<\/p>\n<p><span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">Below are some screenshots of the various states of the cluster supervised by MySQL<\/span><\/span> 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<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h2>The final word(s)<\/h2>\n<p><span id=\"result_box\" class=\"short_text\" lang=\"en\" tabindex=\"-1\"><span class=\"\">You already know<\/span><\/span>, <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication.html\" target=\"_blank\" rel=\"noopener\"><strong>MySQL Replication<\/strong><\/a> and <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> <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">is an additional tool that brings in especially the concept of built-in <\/span><\/span>\u00ab\u00a0<strong>Automatic Database Failover<\/strong>\u00a0\u00bb <span id=\"result_box\" class=\"\" lang=\"en\" tabindex=\"-1\"><span class=\"\">that was missing in MySQL<\/span><\/span>.<\/p>\n<p>These architectures are used most of the time with a proxy. <a href=\"http:\/\/www.proxysql.com\/\" target=\"_blank\" rel=\"noopener\">ProxySQL<\/a> is without a doubt one of the best companion for MySQL today.<\/p>\n<p>&nbsp;<\/p>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span>\u00a0: MySQL provides\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-innodb-cluster-userguide.html\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster<\/a>, which combines MySQL technologies (\u00a0MySQL Group Replication, <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-router\/2.1\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL Router<\/a> and <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell-excerpt\/5.7\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL Shell<\/a>) to enable you to create highly available clusters of MySQL server instances.<br \/>\nTutorial :\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 (French)<\/a><\/em><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/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\/2017\/05\/11\/tutoriel-deployer-mysql-innodb-cluster\/\" target=\"_blank\" rel=\"noopener\">Tutoriel \u2013 D\u00e9ployer MySQL 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>&nbsp;<\/p>\n<p>Thanks for using MySQL!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are 3\u00a0pillars for a database architecture: Monitoring, Backup \/ Restore process, High Availability<br \/>\nThis blog post is about database High Availability; more precisely about one of the best combo of the moment :<br \/>\nMySQL 5.7 Group Replication\u00a0: the only native HA solution for MySQL, it&rsquo;s a\u00a0Single\/Multi-master update everywhere replication plugin for MySQL with built-in automatic distributed recovery, conflict detection and group membership.<br \/>\nProxySQL 1.4\u00a0: probably the\u00a0best proxy for 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":[351,355,357,203,353,339],"tags":[343,345,363,347,349],"class_list":["post-2090","post","type-post","status-publish","format-standard","hentry","category-group-replication-en","category-high-availability","category-monitoring","category-mysql-en","category-proxysql-en","category-tuto-en","tag-group-replication-en","tag-high-availability-en","tag-monitoring-en","tag-proxysql-en","tag-tuto-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-xI","jetpack-related-posts":[{"id":4188,"url":"https:\/\/dasini.net\/blog\/2021\/01\/26\/replicate-from-mysql-5-7-to-mysql-database-service\/","url_meta":{"origin":2090,"position":0},"title":"Replicate from MySQL 5.7 to MySQL Database Service","author":"Olivier DASINI","date":"26 janvier 2021","format":false,"excerpt":"MySQL Replication is a very common topology, widely used in various architecture. People use it, among others, for High Availability, Read Scalability or Geographic Redundancy. Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture. Let\u2019s say you are running\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4194,"url":"https:\/\/dasini.net\/blog\/2021\/01\/19\/mysql-in-azure\/","url_meta":{"origin":2090,"position":1},"title":"MySQL in Azure","author":"Anastasia Papachristopoulou","date":"19 janvier 2021","format":false,"excerpt":"In this article, we are going to see how to set up a MySQL Database instance on Microsoft Azure Cloud.","rel":"","context":"Dans &quot;Azure&quot;","block_context":{"text":"Azure","link":"https:\/\/dasini.net\/blog\/category\/azure\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3193,"url":"https:\/\/dasini.net\/blog\/2019\/07\/09\/mysql-innodb-cluster-recovering-and-provisioning-with-mysqldump\/","url_meta":{"origin":2090,"position":2},"title":"MySQL InnoDB Cluster &#8211;  Recovering and provisioning with mysqldump","author":"Olivier DASINI","date":"9 juillet 2019","format":false,"excerpt":"As the administrator of a cluster, among other tasks, you should be able to restore failed nodes and grow (or shrink) your cluster by adding (or removing) new nodes. In MySQL, as a backup tool (and if your amount of data is not too big), you can use mysqldump a\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4662,"url":"https:\/\/dasini.net\/blog\/2021\/08\/03\/discovering-mysql-database-service-episode-1-introduction\/","url_meta":{"origin":2090,"position":3},"title":"Discovering MySQL Database Service &#8211; Episode 1 &#8211; Introduction","author":"Olivier DASINI","date":"3 ao\u00fbt 2021","format":false,"excerpt":"This is the first episode of \u201cDiscovering MySQL Database Service\u201c, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services. Like any series, in this episode I\u2019m going to give you some context and set\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"MySQL Database Service","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":3463,"url":"https:\/\/dasini.net\/blog\/2019\/09\/19\/mysql-8-0-17-new-features-summary\/","url_meta":{"origin":2090,"position":4},"title":"MySQL 8.0.17 &#8211; New Features Summary","author":"Olivier DASINI","date":"19 septembre 2019","format":false,"excerpt":"This presentation is a summary of the MySQL 8.0.17 new features.","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3219,"url":"https:\/\/dasini.net\/blog\/2019\/07\/11\/mysql-innodb-cluster-recovering-and-provisioning-with-mysql-enterprise-backup\/","url_meta":{"origin":2090,"position":5},"title":"MySQL InnoDB Cluster &#8211; Recovering and provisioning with MySQL Enterprise Backup","author":"Olivier DASINI","date":"11 juillet 2019","format":false,"excerpt":"Like I stated in my previous article - MySQL InnoDB Cluster - Recovering and provisioning with mysqldump : \"As the administrator of a cluster, among others tasks, you should be able to restore failed nodes and to add (or remove) new nodes\". Well, I still agree with myself :) MySQL\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"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\/2090","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=2090"}],"version-history":[{"count":17,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2090\/revisions"}],"predecessor-version":[{"id":2627,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2090\/revisions\/2627"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=2090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=2090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=2090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}