{"id":1855,"date":"2017-04-10T09:29:45","date_gmt":"2017-04-10T08:29:45","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1855"},"modified":"2018-01-11T20:11:48","modified_gmt":"2018-01-11T19:11:48","slug":"adopte-un-cluster-mysql-group-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2017\/04\/10\/adopte-un-cluster-mysql-group-replication\/","title":{"rendered":"Adopte un&#8230; cluster MySQL Group Replication"},"content":{"rendered":"<p>Autant le dire tout de suite, rien avoir avec un site internet de rencontre en ligne! \ud83d\ude42<br \/>\nC&rsquo;est bel et bien un nouvel article dans la s\u00e9rie, <strong>Haute Disponibilit\u00e9 avec MySQL<\/strong>.<\/p>\n<p>Au menu d&rsquo;aujourd&rsquo;hui : comment passer de l&rsquo;administration \u00ab\u00a0manuelle\u00a0\u00bb de votre solution HA\u00a0<a href=\"https:\/\/dev.mysql.com\/downloads\/mysql\/\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication<\/a>\u00a0\u00e0 une administration plus simple, plus fun mais surtout facilement automatisable avec le pack <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-innodb-cluster-userguide.html\" target=\"_blank\" rel=\"noopener\">MySQL InnoDB Cluster<\/a>. En clair, on va voir comment utiliser <a href=\"https:\/\/dev.mysql.com\/downloads\/shell\/\" target=\"_blank\" rel=\"noopener\">MySQL Shell<\/a> pour l&rsquo;administration et l&rsquo;orchestration du cluster et <a href=\"https:\/\/dev.mysql.com\/downloads\/router\/\" target=\"_blank\" rel=\"noopener\">MySQL Router<\/a> pour rediriger automatiquement les transactions de l&rsquo;application vers le noeud primaire du cluster.<\/p>\n<p>Quelques pr\u00e9-requis sont n\u00e9cessaire pour optimiser ta compr\u00e9hension\u00a0de cet article, je te conseille donc la lecture pr\u00e9alable des articles suivants:<\/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\/03\/03\/faq-webinar-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">FAQ Webinar MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/\" target=\"_blank\" rel=\"noopener\">Tester MySQL InnoDB Cluster<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL pour MySQL Group Replication<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><em>Note:\u00a0<\/em><br \/>\n<em>L&rsquo;article traite de MySQL InnoDB Cluster, HA natif de MySQL Server, solution \u00e0 ne pas confondre avec <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-cluster.html\" target=\"_blank\" rel=\"noopener\">MySQL NDB Cluster<\/a>.<\/em><\/p>\n<p>&nbsp;<\/p>\n<h2>Le contexte<\/h2>\n<p>Pour ce PoC, j&rsquo;ai un cluster MySQL Group Replication de 3 n\u0153uds, fonctionnel, en mode \u00ab\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication-single-primary-mode.html\" target=\"_blank\" rel=\"noopener\">Single Primary<\/a>\u00a0\u00bb (d\u00e9ploy\u00e9 avec Docker Compose):<\/p>\n<ul>\n<li>Instance 1 : mysql_node1 (172.19.0.2)<\/li>\n<li>Instance 2 : mysql_node2 (172.19.0.4)<\/li>\n<li>Instance 3 : mysql_node3 (172.19.0.3)<\/li>\n<\/ul>\n<pre class=\"lang:sh decode:true\">$ docker inspect mysql_node1 | grep IPAddress | tail -1\r\n                    \"IPAddress\": \"172.19.0.2\",\r\n$ docker inspect mysql_node2 | grep IPAddress | tail -1\r\n                    \"IPAddress\": \"172.19.0.4\",\r\n$ docker inspect mysql_node3 | grep IPAddress | tail -1\r\n                    \"IPAddress\": \"172.19.0.3\",\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>MySQL <strong>Router<\/strong> et mon application (simul\u00e9e avec le client texte mysql) sont sur la machine host (par commodit\u00e9). C&rsquo;est \u00e9galement le cas de MySQL <strong>Shell<\/strong>.<\/p>\n<p>En ce qui concerne les versions des softs:<\/p>\n<ul>\n<li>MySQL Server 5.7.17<\/li>\n<li>MySQL Router 2.1.2 rc<\/li>\n<li>MySQL Shell\u00a01.0.8-rc<\/li>\n<\/ul>\n<p>Docker 1.12.6 &amp; Docker-compose 1.11.2. Docker est hors du cadre\u00a0de cet article, mais tu trouveras \u00e0 la fin de cet article le fichier <em>docker-compose.yml<\/em> utilis\u00e9.<\/p>\n<p>&nbsp;<\/p>\n<p>Ah oui, j&rsquo;ai failli oublier :<\/p>\n<p><strong>TL;DR<\/strong><br \/>\nTu as un cluster <strong>MySQL Group Replication<\/strong> configur\u00e9\/administr\u00e9 manuellement et qui tourne. Tu peux l&rsquo;administrer \/ le configurer avec<strong> MySQL Shell<\/strong> et g\u00e9rer le routage des requ\u00eates applicatives avec <strong>MySQL Router<\/strong>, ces 3 composants forment\u00a0<strong>MySQL InnoDB Cluster<\/strong>.<\/p>\n<p><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_cluster_overview.png?resize=450%2C565\" alt=\"MySQL InnoDB Cluster Overview\" width=\"450\" height=\"565\" \/><\/p>\n<p>&nbsp;<\/p>\n<h2>MySQL Group Replication<\/h2>\n<p>Les \u00e9tapes de d\u00e9ploiement du cluster Group Replication ont d\u00e9j\u00e0 \u00e9t\u00e9 trait\u00e9es\u00a0<a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\" target=\"_blank\" rel=\"noopener\">ici<\/a>.<\/p>\n<p>Voici mes 3 instances MySQL 5.7<\/p>\n<pre class=\"lang:sh decode:true\">$ docker ps\r\nCONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS                     NAMES\r\nf4c4aa2d3726        mysql:5.7           \"docker-entrypoint.sh\"   About a minute ago   Up About a minute   0.0.0.0:14002-&gt;3306\/tcp   mysql_node2\r\n2304f0e44d4c        mysql:5.7           \"docker-entrypoint.sh\"   About a minute ago   Up About a minute   0.0.0.0:14003-&gt;3306\/tcp   mysql_node3\r\nfb6ae3c76a06        mysql:5.7           \"docker-entrypoint.sh\"   About a minute ago   Up About a minute   0.0.0.0:14001-&gt;3306\/tcp   mysql_node1\r\n<\/pre>\n<p>MySQL 5.7.17 plus pr\u00e9cis\u00e9ment.<\/p>\n<pre class=\"lang:sh decode:true\"> $ docker exec -it mysql_node1 mysql -uroot -p -e\"SELECT version();\"\r\nEnter password: \r\n+------------+\r\n| version()  |\r\n+------------+\r\n| 5.7.17-log |\r\n+------------+<\/pre>\n<p>&nbsp;<\/p>\n<p>A quoi ressemble mon cluster Group Replication ?<\/p>\n<p>Je peux avoir la description de l&rsquo;architecture avec la table <em>performance_schema.<strong>replication_group_members<\/strong><\/em> :<\/p>\n<pre class=\"lang:mysql decode:true\">docker exec -it mysql_node1 mysql -uroot -p -e\"SELECT * FROM performance_schema.replication_group_members\\G\" \r\nEnter password: \r\n*************************** 1. row ***************************\r\nCHANNEL_NAME: group_replication_applier\r\n   MEMBER_ID: d300be14-1797-11e7-a22e-0242ac130002\r\n MEMBER_HOST: mysql_node1\r\n MEMBER_PORT: 3306\r\nMEMBER_STATE: ONLINE\r\n*************************** 2. row ***************************\r\nCHANNEL_NAME: group_replication_applier\r\n   MEMBER_ID: d3100569-1797-11e7-a278-0242ac130004\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: d321b2db-1797-11e7-a16f-0242ac130003\r\n MEMBER_HOST: mysql_node3\r\n MEMBER_PORT: 3306\r\nMEMBER_STATE: ONLINE<\/pre>\n<p>L&rsquo;identification du noeud primaire peut se faire de la mani\u00e8re suivante :<\/p>\n<pre class=\"lang:mysql decode:true\">$ docker exec -it mysql_node1 mysql -uroot -p -e\"SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE) WHERE VARIABLE_NAME='group_replication_primary_member'\\G\" \r\nEnter password: \r\n*************************** 1. row ***************************\r\n   MEMBER_ID: 8b5bad71-1720-11e7-94f0-0242ac130002\r\n MEMBER_HOST: mysql_node1\r\nMEMBER_STATE: ONLINE<\/pre>\n<p>Le\u00a0noeud mysql_node1 est donc en mode lecture \u00e9criture aka le noeud primaire (cette info nous sera utile pour la suite) et les 2 autres en lecture seule (<em>super read only<\/em> activ\u00e9):<\/p>\n<pre class=\"lang:mysql decode:true\">$ docker exec -it mysql_node2 mysql -uroot -p -e\"CREATE SCHEMA gr_test\"\r\nEnter password: \r\nERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement<\/pre>\n<pre class=\"lang:mysql decode:true\">$ docker exec -it mysql_node1 mysql -uroot -p -e\"CREATE SCHEMA gr_test;\"\r\nEnter password: \r\n\r\n$ docker exec -it mysql_node2 mysql -uroot -p -e\"SHOW SCHEMAS;\"\r\nEnter password: \r\n+--------------------+\r\n| Database           |\r\n+--------------------+\r\n| information_schema |\r\n| gr_test            |\r\n| mysql              |\r\n| performance_schema |\r\n| sys                |\r\n+--------------------+<\/pre>\n<p>&nbsp;<\/p>\n<p>On a donc un cluster MySQL Group Replication avec 3 n\u0153uds online.<br \/>\nLe membre mysql_node1 est (pour le moment) le primaire, mysql_node2 et mysql_node3 sont les secondaires.<\/p>\n<p>&nbsp;<\/p>\n<p>L&rsquo;\u00e9tape suivant consistera \u00e0 g\u00e9rer le cluster avec MySQL Shell.<\/p>\n<p>&nbsp;<\/p>\n<h2>MySQL Shell, interface pour g\u00e9rer son cluster<\/h2>\n<p>On va se connecter avec le client MySQL Shell au noeud primaire :<\/p>\n<pre class=\"lang:sh decode:true\">$ # Connect to the primary node : mysql_node1\r\n$ mysqlsh --uri=root@mysql_node1\r\nCreating a Session to 'root@mysql_node1'super read only\r\nEnter password: \r\nClassic Session successfully established. No default schema selected.\r\nWelcome to MySQL Shell 1.0.8-rc\r\n\r\nCopyright (c) 2016, 2017, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType '\\help', '\\h' or '\\?' for help, type '\\quit' or '\\q' to exit.\r\n\r\nCurrently in JavaScript mode. Use \\sql to switch to SQL mode and execute queries.\r\nmysql-js&gt;<\/pre>\n<p>Ensuite, je \u00ab\u00a0cr\u00e9e\u00a0\u00bb mon cluster, en fait je vais rendre persistante les informations relatives \u00e0 l&rsquo;architecture du groupe dans mon cluster (plus d&rsquo;info sur ce sujet plus bas).<\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; var cluster=dba.createCluster('pocCluster', {adoptFromGR: true, ipWhitelist:'172.19.0.0\/16'})\r\nA new InnoDB cluster will be created on instance 'root@mysql_node1:3306'.\r\n\r\nCreating InnoDB cluster 'pocCluster' on 'root@mysql_node1:3306'...\r\nAdding Seed Instance...\r\n\r\nCluster successfully created. Use Cluster.addInstance() to add MySQL instances.\r\nAt least 3 instances are needed for the cluster to be able to withstand up to\r\none server failure.<\/pre>\n<p>La m\u00e9thode\u00a0<strong><em>createCluster()<\/em><\/strong> prends comme param\u00e8tres, le nom du cluster (<em>pocCluster<\/em>) ainsi que des param\u00e8tres optionnels comme <strong><em><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication-ip-address-whitelisting.html\" target=\"_blank\" rel=\"noopener\">ipWhitelist<\/a><\/em><\/strong> (172.19.0.0\/16)&#8230;<\/p>\n<p>Pour plus de d\u00e9tails\u00a0connecte toi \u00e0 MySQL Shell (<em>mysqlsh<\/em>) et tape :\u00a0<strong><em>dba.help(&lsquo;createCluster&rsquo;)<\/em><\/strong><\/p>\n<pre class=\"lang:sh decode:true\">$ mysqlsh\r\n*Welcome to MySQL Shell 1.0.8-rc\r\n\r\nCopyright (c) 2016, 2017, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType '\\help', '\\h' or '\\?' for help, type '\\quit' or '\\q' to exit.\r\n\r\nCurrently in JavaScript mode. Use \\sql to switch to SQL mode and execute queries.\r\n\r\nmysql-js&gt; dba.help('createCluster')<\/pre>\n<p>&nbsp;<\/p>\n<p>V\u00e9rifions l&rsquo;\u00e9tat du cluster<\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; \/* Check cluster status *\/\r\nmysql-js&gt; cluster.status()\r\n{\r\n    \"clusterName\": \"pocCluster\", \r\n    \"defaultReplicaSet\": {\r\n        \"name\": \"default\", \r\n        \"primary\": \"mysql_node1:3306\", \r\n        \"status\": \"OK\", \r\n        \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n        \"topology\": {\r\n            \"mysql_node1:3306\": {\r\n                \"address\": \"mysql_node1:3306\", \r\n                \"mode\": \"R\/W\", \r\n                \"readReplicas\": {}, \r\n                \"role\": \"HA\", \r\n                \"status\": \"ONLINE\"\r\n            }, \r\n            \"mysql_node2:3306\": {\r\n                \"address\": \"mysql_node2:3306\", \r\n                \"mode\": \"R\/O\", \r\n                \"readReplicas\": {}, \r\n                \"role\": \"HA\", \r\n                \"status\": \"ONLINE\"\r\n            }, \r\n            \"mysql_node3:3306\": {\r\n                \"address\": \"mysql_node3:3306\", \r\n                \"mode\": \"R\/O\", \r\n                \"readReplicas\": {}, \r\n                \"role\": \"HA\", \r\n                \"status\": \"ONLINE\"\r\n            }\r\n        }\r\n    }\r\n}<\/pre>\n<p>MySQL Shell nous confirme ce que nous savions d\u00e9j\u00e0:<\/p>\n<p>je m&rsquo;auto cite; un <em>grand<\/em> (1m89) DBA \u00e0 dit un jour :<\/p>\n<p>\u00ab\u00a0On a donc un cluster MySQL Group Replication d\u00e9ploy\u00e9 avec 3 n\u0153uds online. Le membre mysql_node1 est (pour le moment) le primaire, mysql_node2 et mysql_node3 sont les secondaires.\u00a0\u00bb<\/p>\n<p>&nbsp;<\/p>\n<p>En zoomant dans les entrailles du groupe, on constate que la m\u00e9thode <em>createCluster()<\/em> a \u00e9crit des donn\u00e9es dans le cluster :<\/p>\n<pre class=\"lang:sh decode:true\">$ docker exec -it mysql_node1 mysql -uroot -p -e\"SHOW SCHEMAS; SHOW TABLES IN mysql_innodb_cluster_metadata;\"\r\nEnter password: \r\n+-------------------------------+\r\n| Database                      |\r\n+-------------------------------+\r\n| information_schema            |\r\n| gr_test                       |\r\n| mysql                         |\r\n| mysql_innodb_cluster_metadata |\r\n| performance_schema            |\r\n| sys                           |\r\n+-------------------------------+\r\n\r\n+-----------------------------------------+\r\n| Tables_in_mysql_innodb_cluster_metadata |\r\n+-----------------------------------------+\r\n| clusters                                |\r\n| hosts                                   |\r\n| instances                               |\r\n| replicasets                             |\r\n| routers                                 |\r\n| schema_version                          |\r\n+-----------------------------------------+<\/pre>\n<p>Le sch\u00e9ma\u00a0<strong><em>mysql_innodb_cluster_metadata<\/em><\/strong> a donc \u00e9t\u00e9 cr\u00e9\u00e9 pour contenir les informations relatives au cluster.<\/p>\n<p>Le nom des tables est assez explicite :<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>hosts<\/strong><\/span><\/p>\n<pre class=\"lang:sh decode:true \">$ docker exec -it mysql_node1 mysql -uroot -p -e\" SELECT * FROM mysql_innodb_cluster_metadata.hosts\\G\"\r\nEnter password: \r\n*************************** 1. row ***************************\r\n           host_id: 6\r\n         host_name: mysql_node1\r\n        ip_address: \r\n public_ip_address: NULL\r\n          location: \r\n        attributes: NULL\r\nadmin_user_account: NULL\r\n*************************** 2. row ***************************\r\n           host_id: 13\r\n         host_name: mysql_node2\r\n        ip_address: \r\n public_ip_address: NULL\r\n          location: \r\n        attributes: NULL\r\nadmin_user_account: NULL\r\n*************************** 3. row ***************************\r\n           host_id: 20\r\n         host_name: mysql_node3\r\n        ip_address: \r\n public_ip_address: NULL\r\n          location: \r\n        attributes: NULL\r\nadmin_user_account: NULL<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>clusters<\/strong><\/span><\/p>\n<pre class=\"lang:mysql decode:true\"> $ docker exec -it mysql_node1 mysql -uroot -p -e\" SELECT * FROM mysql_innodb_cluster_metadata.clusters\\G\"\r\nEnter password: \r\n*************************** 1. row ***************************\r\n         cluster_id: 6\r\n       cluster_name: pocCluster\r\n default_replicaset: 6\r\n        description: Default Cluster\r\nmysql_user_accounts: NULL\r\n            options: {\"adminType\": \"local\"}\r\n         attributes: {\"default\": true}<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>replicasets<\/strong><\/span><\/p>\n<pre class=\"lang:mysql decode:true \">$ docker exec -it mysql_node1 mysql -uroot -p -e\" SELECT * FROM mysql_innodb_cluster_metadata.replicasets\\G\"\r\nEnter password: \r\n*************************** 1. row ***************************\r\n  replicaset_id: 6\r\n     cluster_id: 6\r\nreplicaset_type: gr\r\n  topology_type: pm\r\nreplicaset_name: default\r\n         active: 1\r\n     attributes: {\"adopted\": \"true\", \"group_replication_group_name\": \"4e0f05b7-d9d0-11e6-87cf-002710cccc64\"}\r\n    description: NULL<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>instances<\/strong><\/span><\/p>\n<pre class=\"lang:mysql decode:true \">$ docker exec -it mysql_node1 mysql -uroot -p -e\" SELECT * FROM mysql_innodb_cluster_metadata.instances\\G\"\r\nEnter password: \r\n*************************** 1. row ***************************\r\n      instance_id: 6\r\n          host_id: 6\r\n    replicaset_id: 6\r\nmysql_server_uuid: d300be14-1797-11e7-a22e-0242ac130002\r\n    instance_name: mysql_node1:3306\r\n             role: HA\r\n           weight: NULL\r\n        addresses: {\"mysqlX\": \"mysql_node1:33060\", \"grLocal\": \"mysql_node1:4999\", \"mysqlClassic\": \"mysql_node1:3306\"}\r\n       attributes: NULL\r\n    version_token: NULL\r\n      description: NULL\r\n*************************** 2. row ***************************\r\n      instance_id: 13\r\n          host_id: 13\r\n    replicaset_id: 6\r\nmysql_server_uuid: d3100569-1797-11e7-a278-0242ac130004\r\n    instance_name: mysql_node2:3306\r\n             role: HA\r\n           weight: NULL\r\n        addresses: {\"mysqlX\": \"mysql_node2:33060\", \"grLocal\": \"mysql_node2:4999\", \"mysqlClassic\": \"mysql_node2:3306\"}\r\n       attributes: NULL\r\n    version_token: NULL\r\n      description: NULL\r\n*************************** 3. row ***************************\r\n      instance_id: 20\r\n          host_id: 20\r\n    replicaset_id: 6\r\nmysql_server_uuid: d321b2db-1797-11e7-a16f-0242ac130003\r\n    instance_name: mysql_node3:3306\r\n             role: HA\r\n           weight: NULL\r\n        addresses: {\"mysqlX\": \"mysql_node3:33060\", \"grLocal\": \"mysql_node3:4999\", \"mysqlClassic\": \"mysql_node3:3306\"}\r\n       attributes: NULL\r\n    version_token: NULL\r\n      description: NULL<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h2>D\u00e9ploiement de MySQL Router<\/h2>\n<p>Le d\u00e9ploiement du router est trivial, il faut pour commencer le <em>bootstrapper<\/em>\u00a0au cluster, c&rsquo;est \u00e0 dire le lier au cluster en le connectant aux m\u00e9ta-donn\u00e9es :<\/p>\n<pre class=\"lang:sh decode:true\">$ mysqlrouter --bootstrap root@mysql_node1:3306 --directory routerDocker --name routerDocker\r\n[sudo] password for daz: \r\nPlease enter MySQL password for root: \r\n\r\nBootstrapping MySQL Router instance at \/home\/daz\/routerDocker...\r\nMySQL Router 'routerDocker' has now been configured for the InnoDB cluster 'pocCluster'.\r\n\r\nThe following connection information can be used to connect to the cluster.\r\n\r\nClassic MySQL protocol connections to cluster 'pocCluster':\r\n- Read\/Write Connections: localhost:6446\r\n- Read\/Only Connections: localhost:6447\r\n\r\nX protocol connections to cluster 'pocCluster':\r\n- Read\/Write Connections: localhost:64460\r\n- Read\/Only Connections: localhost:64470<\/pre>\n<p>Les param\u00e8tres <em>directory<\/em> et <em>name<\/em> sont optionnels.<\/p>\n<p>&nbsp;<\/p>\n<p>Lancer MySQL Router :<\/p>\n<pre class=\"lang:sh decode:true \">$ mysqlrouter -c ~\/routerDocker\/mysqlrouter.conf &amp;\r\n<\/pre>\n<p>L\u2019application doit se connecter (par d\u00e9faut) au port <strong>6446<\/strong> (\u00e9critures et lectures vers le noeud primaire). En cas de besoin de read scalability, les lectures peuvent \u00eatre dirig\u00e9es vers le port 6447.<\/p>\n<p>&nbsp;<\/p>\n<p>Inspectons de nouveau les m\u00e9ta donn\u00e9es :<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>routers<\/strong><\/span><\/p>\n<pre class=\"lang:sh decode:true\">$ docker exec -it mysql_node1 mysql -uroot -p -e\" SELECT * FROM mysql_innodb_cluster_metadata.routers\\G\"\r\nEnter password: \r\n*************************** 1. row ***************************\r\n  router_id: 6\r\nrouter_name: routerDocker\r\n    host_id: 27\r\n attributes: NULL<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>hosts<\/strong><\/span><\/p>\n<pre class=\"lang:mysql decode:true \">$ docker exec -it mysql_node1 mysql -uroot -p -e\" SELECT * FROM mysql_innodb_cluster_metadata.hosts\\G\"\r\nEnter password: \r\n*************************** 1. row ***************************\r\n           host_id: 6\r\n         host_name: mysql_node1\r\n        ip_address: \r\n public_ip_address: NULL\r\n          location: \r\n        attributes: NULL\r\nadmin_user_account: NULL\r\n*************************** 2. row ***************************\r\n           host_id: 13\r\n         host_name: mysql_node2\r\n        ip_address: \r\n public_ip_address: NULL\r\n          location: \r\n        attributes: NULL\r\nadmin_user_account: NULL\r\n*************************** 3. row ***************************\r\n           host_id: 20\r\n         host_name: mysql_node3\r\n        ip_address: \r\n public_ip_address: NULL\r\n          location: \r\n        attributes: NULL\r\nadmin_user_account: NULL\r\n*************************** 4. row ***************************\r\n           host_id: 27\r\n         host_name: \r\n        ip_address: NULL\r\n public_ip_address: NULL\r\n          location: \r\n        attributes: {\"registeredFrom\": \"mysql-router\"}\r\nadmin_user_account: NULL<\/pre>\n<p>&nbsp;<\/p>\n<p>Voil\u00e0, mon cluster Group Replication param\u00e9tr\u00e9 \u00ab\u00a0\u00e0 la main\u00a0\u00bb fait maintenant partie int\u00e9grante de mon InnoDB Cluster, je peux donc l&rsquo;administrer avec MySQL Shell et je peux vous assurer que c&rsquo;est vraiment pratique.<\/p>\n<p>Mais ceci est une autre histoire et fera l&rsquo;objet d&rsquo;un autre article \ud83d\ude42<\/p>\n<p>&nbsp;<\/p>\n<h2>Annexe<\/h2>\n<p>Le fichier <strong>docker-compose<\/strong> est le suivant :<\/p>\n<pre class=\"lang:ini decode:true\">version: '2'\r\nservices:\r\n  node1:\r\n    container_name: mysql_node1\r\n    image: \"mysql:5.7\"\r\n    volumes:\r\n      - ~\/Documents\/Docker\/confdir\/mysql1:\/etc\/mysql\/conf.d\r\n    ports:\r\n      - \"14001:3306\"\r\n    environment:\r\n      - MYSQL_ROOT_PASSWORD=root\r\n    networks:\r\n      app_net:\r\n        ipv4_address: 172.19.0.2\r\n  node2:\r\n    container_name: mysql_node2\r\n    image: \"mysql:5.7\"\r\n    volumes:\r\n      - ~\/Documents\/Docker\/confdir\/mysql2:\/etc\/mysql\/conf.d\r\n    ports:\r\n      - \"14002:3306\"\r\n    environment:\r\n      - MYSQL_ROOT_PASSWORD=root\r\n    networks:\r\n      app_net:\r\n        ipv4_address: 172.19.0.4\r\n  node3:\r\n    container_name: mysql_node3\r\n    image: \"mysql:5.7\"\r\n    volumes:\r\n      - ~\/Documents\/Docker\/confdir\/mysql3:\/etc\/mysql\/conf.d\r\n    ports:\r\n      - \"14003:3306\"\r\n    environment:\r\n      - MYSQL_ROOT_PASSWORD=root\r\n    networks:\r\n      app_net:\r\n        ipv4_address: 172.19.0.3\r\n\r\nnetworks:\r\n  app_net:\r\n    driver: bridge\r\n    ipam:\r\n      driver: default\r\n      config:\r\n      -\r\n        subnet: 172.19.0.0\/24<\/pre>\n<p>&nbsp;<\/p>\n<p>Thanks for using MySQL!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Au menu d&rsquo;aujourd&rsquo;hui : comment passer de l&rsquo;administration \u00ab\u00a0manuelle\u00a0\u00bb de votre solution HA MySQL Group Replication \u00e0 une administration plus simple, plus fun mais surtout facilement automatisable avec le pack MySQL InnoDB Cluster. En clair, on va voir comment utiliser MySQL Shell pour l&rsquo;administration et l&rsquo;orchestration du cluster et MySQL Router pour rediriger automatiquement les transactions de l&rsquo;application vers le noeud primaire du cluster.<\/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,288,8,337],"tags":[320,286,79,298,302,318,341],"class_list":["post-1855","post","type-post","status-publish","format-standard","hentry","category-group-replication","category-haute-disponibilite","category-innodb-cluster","category-mysql","category-tuto","tag-docker","tag-group-replication","tag-haute-disponibilite","tag-innodb-cluster","tag-router","tag-shell","tag-tuto"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-tV","jetpack-related-posts":[{"id":4381,"url":"https:\/\/dasini.net\/blog\/2021\/02\/24\/webinar-la-haute-disponibilite-avec-mysql\/","url_meta":{"origin":1855,"position":0},"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":[]},{"id":1832,"url":"https:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/","url_meta":{"origin":1855,"position":1},"title":"Tester MySQL InnoDB Cluster","author":"Olivier DASINI","date":"13 mars 2017","format":false,"excerpt":"MySQL InnoDB Cluster est la (future) solution out-of-the-box HA de MySQL (\u00e0 ne pas confondre avec MySQL NDB Cluster). Ce produit est compos\u00e9 de 3 \u00e9l\u00e9ments : MySQL Group Replication Plugin de r\u00e9plication multi-ma\u00eetre, avec r\u00e9solution de conflits et basculement (failover) automatique. MySQL Router Middleware l\u00e9ger et performant qui fournit\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"MySQL InnoDB Cluster Architecture","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_Cluster_Architecture.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":3291,"url":"https:\/\/dasini.net\/blog\/2019\/07\/16\/deployer-1-cluster-mysql-avec-2-commandes\/","url_meta":{"origin":1855,"position":2},"title":"D\u00e9ployer 1 cluster MySQL avec 2 commandes","author":"Olivier DASINI","date":"16 juillet 2019","format":false,"excerpt":"Dans cette courte vid\u00e9o, je vais te montrer comment cr\u00e9er un cluster MySQL de 3 n\u0153uds avec simplement 2 commandes. https:\/\/www.youtube.com\/watch?v=YZzGsDjlWvk","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\/img.youtube.com\/vi\/YZzGsDjlWvk\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1936,"url":"https:\/\/dasini.net\/blog\/2017\/05\/26\/php-tour-2017-slides-mysql-innodb-cluster\/","url_meta":{"origin":1855,"position":3},"title":"PHP Tour 2017 &#8211; Slides  MySQL InnoDB Cluster","author":"Olivier DASINI","date":"26 mai 2017","format":false,"excerpt":"La derni\u00e8re \u00e9dition du PHP Tour s'est d\u00e9roul\u00e9e les 18 et 19 mai 2017 \u00e0 Nantes. Ce que j'en garde : un tr\u00e8s bon cru, de bien belles rencontres, de bonnes bi\u00e8res ainsi qu'une excellente organisation (merci l'AFUP). J'ai \u00e9galement eu l'opportunit\u00e9 de pr\u00e9senter MySQL InnoDB Cluster, la nouvelle solution\u2026","rel":"","context":"Dans &quot;Conf\u00e9rence&quot;","block_context":{"text":"Conf\u00e9rence","link":"https:\/\/dasini.net\/blog\/category\/conference\/"},"img":{"alt_text":"Olivier DASINI aka @freshdaz","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/Olivier_Dasini_conf_2017.jpg?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":1855,"position":4},"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":4389,"url":"https:\/\/dasini.net\/blog\/2021\/03\/02\/slides-et-video-du-webinar-la-haute-disponibilite-avec-mysql\/","url_meta":{"origin":1855,"position":5},"title":"Slides et vid\u00e9o du webinar  La haute disponibilit\u00e9 avec MySQL","author":"Olivier DASINI","date":"2 mars 2021","format":false,"excerpt":"Le 25 f\u00e9vrier dernier, a eu lieu le webinar: La haute disponibilit\u00e9 avec MySQL. MERCI \u00e0 tous pour votre pr\u00e9sence ? La pr\u00e9sentation est disponible ici, sur mon compte slideshare:","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\/img.youtube.com\/vi\/S41Z-QMupHE\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1855","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=1855"}],"version-history":[{"count":20,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1855\/revisions"}],"predecessor-version":[{"id":2092,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1855\/revisions\/2092"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1855"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1855"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1855"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}