{"id":1832,"date":"2017-03-13T13:16:31","date_gmt":"2017-03-13T12:16:31","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1832"},"modified":"2024-12-10T15:50:22","modified_gmt":"2024-12-10T14:50:22","slug":"tester-mysql-innodb-cluster","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/","title":{"rendered":"Tester MySQL InnoDB Cluster"},"content":{"rendered":"<p><strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-innodb-cluster-userguide.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL InnoDB Cluster<\/a><\/strong> est la (future) solution out-of-the-box HA de MySQL (\u00e0 ne pas confondre avec <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-cluster.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL NDB Cluster<\/a>). Ce produit est compos\u00e9 de 3 \u00e9l\u00e9ments :<\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/downloads\/mysql\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>MySQL Group Replication<\/strong><\/a>\n<ul>\n<li>Plugin de r\u00e9plication multi-ma\u00eetre, avec r\u00e9solution de conflits et basculement (failover) automatique.<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/downloads\/router\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>MySQL Router<\/strong><\/a>\n<ul>\n<li>Middleware l\u00e9ger et performant qui fournit un routage transparent entre l&rsquo;application et le cluster.<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/downloads\/shell\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>MySQL Shell<\/strong><\/a>\n<ul>\n<li>Client interactif Javascript, Python et SQL qui permet d&rsquo;administrer le cluster.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\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_Architecture.png?resize=355%2C399\" alt=\"MySQL InnoDB Cluster Architecture\" width=\"355\" height=\"399\" \/><br \/>\nMySQL Group Replication est GA et peut donc \u00eatre utilis\u00e9 tel quel hors MySQL InnoDB Cluster (voir\u00a0<a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener noreferrer\">D\u00e9ployer un cluster MySQL Group Replication<\/a>).<\/p>\n<p>Ce n&rsquo;est par contre pas encore le cas pour les 2 autres composants, MySQL Shell et MySQL Router qui sont en Release Candidate (RC), il n&rsquo;est donc pas recommand\u00e9 \u00e0 ce jour de les utiliser dans un environnement de production.<\/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 noreferrer\">MySQL NDB Cluster<\/a>.<\/em><\/p>\n<h2>Installer MySQL InnoDB Cluster<\/h2>\n<p>Dans le cadre de cet article, les versions utilis\u00e9es sont:<\/p>\n<ul>\n<li>MySQL Server : <strong>5.7.17<\/strong><\/li>\n<li>MySQL Shell :<strong>\u00a01.0.8-rc<\/strong><\/li>\n<li>MySQL Router :\u00a0<strong>2.1.2 rc<\/strong><\/li>\n<\/ul>\n<p>Pour utiliser MySQL InnoDB Cluster, il faut simplement installer ces 3 composants :<\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/installing.html\" target=\"_blank\" rel=\"noopener noreferrer\">Installer MySQL Server<\/a>\u00a0(5.7.17+)<\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-router\/2.1\/en\/mysql-router-installation.html\" target=\"_blank\" rel=\"noopener noreferrer\">Installer MySQL Router<\/a>\u00a0(2.1.2+)<\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-shell.html\" target=\"_blank\" rel=\"noopener noreferrer\">Installer MySQL Shell<\/a>\u00a0(1.0.8.+)<\/li>\n<\/ul>\n<h2>D\u00e9ployer les instances de test<\/h2>\n<p>MySQL Shell permet de d\u00e9ployer simplement des instances MySQL de test (sandbox).<\/p>\n<p>Connexion avec\u00a0MySQL Shell :<\/p>\n<pre class=\"lang:sh decode:true\">$ mysqlsh\nWelcome to MySQL Shell 1.0.8-rc\n\nCopyright (c) 2016, 2017, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType '\\help', '\\h' or '\\?' for help, type '\\quit' or '\\q' to exit.\n\nCurrently in JavaScript mode. Use \\sql to switch to SQL mode and execute queries.\nmysql-js&gt;<\/pre>\n<p>D\u00e9ployer la 1\u00e8re instance MySQL qui fera partie de notre cluster :<\/p>\n<ul>\n<li><em>Host : localhost<\/em><\/li>\n<li><em>Port : 3310<\/em><\/li>\n<\/ul>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; dba.deploySandboxInstance(3310)\nA new MySQL sandbox instance will be created on this host in \n\/home\/daz\/mysql-sandboxes\/3310\n\nPlease enter a MySQL root password for the new instance: \nDeploying new MySQL instance...\n\nInstance localhost:3310 successfully deployed and started.\nUse shell.connect('root@localhost:3310'); to connect to the instance.<\/pre>\n<p>Il suffit de rentrer le mot de passe <em>root<\/em>, puis l&rsquo;instance est cr\u00e9e dans <em>~\/mysql-sandboxes<\/em> :<\/p>\n<pre class=\"lang:sh decode:true\">$ ls ~\/mysql-sandboxes\/3310\/\n3310.pid my.cnf mysqld mysqld.sock mysqld.sock.lock mysql-files mysqlx.sock mysqlx.sock.lock sandboxdata start.sh stop.sh<\/pre>\n<p>Cr\u00e9ons 2 autres instances\u00a0pour le cluster:<\/p>\n<ul>\n<li><em>Host : localhost<\/em><\/li>\n<li><em>Ports : 3320 &amp; 3330<\/em><\/li>\n<\/ul>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; dba.deploySandboxInstance(3320)\nA new MySQL sandbox instance will be created on this host in \n\/home\/daz\/mysql-sandboxes\/3320\n\nPlease enter a MySQL root password for the new instance: \nDeploying new MySQL instance...\n\nInstance localhost:3320 successfully deployed and started.\nUse shell.connect('root@localhost:3320'); to connect to the instance.\n\nmysql-js&gt; dba.deploySandboxInstance(3330)\nA new MySQL sandbox instance will be created on this host in \n\/home\/daz\/mysql-sandboxes\/3330\n\nPlease enter a MySQL root password for the new instance: \nDeploying new MySQL instance...\n\nInstance localhost:3330 successfully deployed and started.\nUse shell.connect('root@localhost:3330'); to connect to the instance.<\/pre>\n<p>On a donc 3 instances MySQL dans notre sandbox.<\/p>\n<p>A Noter que si vous avez d\u00e9j\u00e0 un cluster MySQL Group Replication actif, MySQL InnoDB Cluster est capable de l&rsquo;adopter. Ceci fera l&rsquo;objet d&rsquo;un prochain article.<\/p>\n<h2>G\u00e9rer les instances<\/h2>\n<p>D&rsquo;autres m\u00e9thodes\u00a0existent pour g\u00e9rer les instances:<\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Stop<\/span>\n<ul>\n<li><strong>dba.stopSandboxInstance()<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><span style=\"text-decoration: underline;\">Start<\/span>\n<ul>\n<li><strong>dba.startSandboxInstance()<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><span style=\"text-decoration: underline;\">Kill<\/span> \u00a0: permet de simuler le crash d&rsquo;un n\u0153ud\n<ul>\n<li><strong>dba.killSandboxInstance()<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><span style=\"text-decoration: underline;\">Delete<\/span> : suppression totale de l&rsquo;instance de la sandbox\n<ul>\n<li><strong>dba.deleteSandboxInstance()<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><span style=\"text-decoration: underline;\"><strong>Exemple &#8211;\u00a0<\/strong><strong>Arr\u00eat et suppression d&rsquo;une instance<\/strong><\/span><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; dba.stopSandboxInstance(3320)\nThe MySQL sandbox instance on this host in \n\/...\/mysql-sandboxes\/3320 will be stopped\n\nPlease enter the MySQL root password for the instance 'localhost:3320':\n\nStopping MySQL instance...\n\nInstance localhost:3320 successfully stopped.\n\nmysql-js&gt; dba.deleteSandboxInstance(3320);\nThe MySQL sandbox instance on this host in \n\/...\/mysql-sandboxes\/3320 will be deleted\n\n\nDeleting MySQL instance...\n\nInstance localhost:3320 successfully deleted.<\/pre>\n<p>L&rsquo;aide est disponible dans MySQL Shell avec\u00a0<strong>dba.help()<\/strong><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; dba.help()\n\nThe global variable 'dba' is used to access the MySQL AdminAPI functionality\nand perform DBA operations. It is used for managing MySQL InnoDB clusters.\n\nThe following properties are currently supported.\n\n- verbose Enables verbose mode on the Dba operations.\n\n\nThe following functions are currently supported.\n\n- checkInstanceConfiguration\n- configureLocalInstance Validates and configures an instance for\ncluster usage.\n- createCluster Creates a MySQL InnoDB cluster.\n- deleteSandboxInstance Deletes an existing MySQL Server instance on\nlocalhost.\n- deploySandboxInstance Creates a new MySQL Server instance on\nlocalhost.\n- dropMetadataSchema Drops the Metadata Schema.\n- getCluster Retrieves a cluster from the Metadata Store.\n- help Provides help about this class and it's\nmembers\n- killSandboxInstance Kills a running MySQL Server instance on\nlocalhost.\n- rebootClusterFromCompleteOutage Reboots a cluster from complete outage.\n- resetSession Sets the session object to be used on the\nDba operations.\n- startSandboxInstance Starts an existing MySQL Server instance on\nlocalhost.\n- stopSandboxInstance Stops a running MySQL Server instance on\nlocalhost.\n\nFor more help on a specific function use: dba.help('&lt;functionName&gt;')\n\ne.g. dba.help('deploySandboxInstance')<\/pre>\n<h2>V\u00e9rifier la configuration des instances<\/h2>\n<p>Un moyen simple de savoir si les instances ont la configuration requise pour faire partie du cluster est d&rsquo;utiliser :\u00a0<strong>dba.checkInstanceConfiguration()<\/strong><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; dba.checkInstanceConfiguration('root@localhost:3310')\nPlease provide the password for 'root@localhost:3310': \nValidating instance...\n\nThe instance 'localhost:3310' is valid for Cluster usage\n{\n\"status\": \"ok\"\n}\nmysql-js&gt; dba.checkInstanceConfiguration('root@localhost:3320')\nPlease provide the password for 'root@localhost:3320': \nValidating instance...\n\nThe instance 'localhost:3320' is valid for Cluster usage\n{\n\"status\": \"ok\"\n}\nmysql-js&gt; dba.checkInstanceConfiguration('root@localhost:3330')\nPlease provide the password for 'root@localhost:3330': \nValidating instance...\n\nThe instance 'localhost:3330' is valid for Cluster usage\n{\n\"status\": \"ok\"\n}<\/pre>\n<h2>Cr\u00e9er le cluster<\/h2>\n<p>On a donc 3 instances MySQL, en <em>standalone<\/em>, configur\u00e9es et pr\u00eates \u00e0 se transformer en\u00a0une base de donn\u00e9es distribu\u00e9e.<\/p>\n<p>Je vais donc me connecter \u00e0 une de mes instances :<\/p>\n<ul>\n<li><em>User : root<\/em><\/li>\n<li><em>Host : localhost<\/em><\/li>\n<li><em>Ports : 3310<\/em><\/li>\n<\/ul>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; \\c root@localhost:3310\nCreating a Session to 'root@localhost:3310'\nEnter password: \nClassic Session successfully established. No default schema selected.<\/pre>\n<p>Puis commencer la cr\u00e9ation effective de mon instance MySQL InnoDB Cluster, nomm\u00e9e\u00a0<em>testcluster<\/em><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; var cluster=dba.createCluster('testcluster')\nA new InnoDB cluster will be created on instance 'root@localhost:3310'.\n\nCreating InnoDB cluster 'testcluster' on 'root@localhost:3310'...\nAdding Seed Instance...\n\nCluster successfully created. Use Cluster.addInstance() to add MySQL instances.\nAt least 3 instances are needed for the cluster to be able to withstand up to\none server failure.<\/pre>\n<p>Je me retrouve pour le moment avec un cluster d&rsquo;1 n\u0153ud. Certes, pas encore hautement disponible, mais c&rsquo;est un d\u00e9but \ud83d\ude42<\/p>\n<p>La m\u00e9thode <strong>status()<\/strong> me le confirme:<\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; cluster.status()\n{\n    \"clusterName\": \"testcluster\", \n    \"defaultReplicaSet\": {\n        \"name\": \"default\", \n        \"primary\": \"localhost:3310\", \n        \"status\": \"OK_NO_TOLERANCE\", \n        \"statusText\": \"Cluster is NOT tolerant to any failures.\", \n        \"topology\": {\n            \"localhost:3310\": {\n                \"address\": \"localhost:3310\", \n                \"mode\": \"R\/W\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }\n        }\n    }\n}<\/pre>\n<p>Avant de lui rajouter des petits copains, on va v\u00e9rifier que toutes les instances ont la m\u00eame liste de transactions ex\u00e9cut\u00e9es:<\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; cluster.checkInstanceState('root@localhost:3310')\nPlease provide the password for 'root@localhost:3310': \nAnalyzing the instance replication state...\n\nThe instance 'localhost:3310' is valid for the cluster.\nThe instance is fully recoverable.\n\n{\n\"reason\": \"recoverable\", \n\"state\": \"ok\"\n}\n\nmysql-js&gt; cluster.checkInstanceState('root@localhost:3320')\nPlease provide the password for 'root@localhost:3320': \nAnalyzing the instance replication state...\n\nThe instance 'localhost:3320' is valid for the cluster.\nThe instance is new to Group Replication.\n\n{\n\"reason\": \"new\", \n\"state\": \"ok\"\n}\n\nmysql-js&gt; cluster.checkInstanceState('root@localhost:3330')\nPlease provide the password for 'root@localhost:3330': \nAnalyzing the instance replication state...\n\nThe instance 'localhost:3330' is valid for the cluster.\nThe instance is new to Group Replication.\n\n{\n\"reason\": \"new\", \n\"state\": \"ok\"\n}<\/pre>\n<p>Parfait !<\/p>\n<h2>Ajouter les autres n\u0153uds<\/h2>\n<p><strong>addInstance()<\/strong>, la bien nomm\u00e9e :<\/p>\n<p>Ajout de <em>localhost:3320<\/em><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; cluster.addInstance('root@localhost:3320')\nA new instance will be added to the InnoDB cluster. Depending on the amount of\ndata on the cluster this might take from a few seconds to several hours.\n\nPlease provide the password for 'root@localhost:3320': \nAdding instance to the cluster ...\n\nThe instance 'root@localhost:3320' was successfully added to the cluster.<\/pre>\n<p>Ajout de <em>localhost:3330<\/em><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; cluster.addInstance('root@localhost:3330')\nA new instance will be added to the InnoDB cluster. Depending on the amount of\ndata on the cluster this might take from a few seconds to several hours.\n\nPlease provide the password for 'root@localhost:3330': \nAdding instance to the cluster ...\n\nThe instance 'root@localhost:3330' was successfully added to the cluster.<\/pre>\n<p>L&rsquo;architecture de notre cluster est maintenant:<\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; cluster.status()\n{\n    \"clusterName\": \"testcluster\", \n    \"defaultReplicaSet\": {\n        \"name\": \"default\", \n        \"primary\": \"localhost:3310\", \n        \"status\": \"OK\", \n        \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \n        \"topology\": {\n            \"localhost:3310\": {\n                \"address\": \"localhost:3310\", \n                \"mode\": \"R\/W\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }, \n            \"localhost:3320\": {\n                \"address\": \"localhost:3320\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }, \n            \"localhost:3330\": {\n                \"address\": \"localhost:3330\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }\n        }\n    }\n}<\/pre>\n<p>localhost:3310 est le primaire il est donc le seul \u00e0 accepter les \u00e9critures. Les 2 autres membres ne sont accessibles qu&rsquo;en lecture.<\/p>\n<p>C&rsquo;est le <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication-single-primary-mode.html\" target=\"_blank\" rel=\"noopener noreferrer\">comportement par d\u00e9faut<\/a> de MySQL Group Replication est donc de MySQL InnoDB Cluster. \u00a0Pour avoir un cluster en<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication-multi-primary-mode.html\" target=\"_blank\" rel=\"noopener noreferrer\"> multi-master<\/a>, il faut le pr\u00e9ciser lors de la cr\u00e9ation du cluster (<strong>dba.createCluster()<\/strong>).<\/p>\n<p>Les informations r\u00e9v\u00e9l\u00e9es par les diff\u00e9rentes commandes ex\u00e9cut\u00e9e jusqu&rsquo;ici, sont persistante. Elles sont en stock\u00e9es dans les n\u0153uds du cluster, dans le sch\u00e9ma <strong>mysql_innodb_cluster_metadata<\/strong> :<\/p>\n<pre class=\"lang:mysql decode:true\">$ mysql --protocol=tcp -uroot -p -P3310\n\nmysql&gt; show schemas;\n+-------------------------------+\n| Database                      |\n+-------------------------------+\n| information_schema            |\n| mysql                         |\n| mysql_innodb_cluster_metadata |\n| performance_schema            |\n| sys                           |\n+-------------------------------+\n5 rows in set (0,00 sec)\n\n\nmysql&gt; show tables in mysql_innodb_cluster_metadata;\n+-----------------------------------------+\n| Tables_in_mysql_innodb_cluster_metadata |\n+-----------------------------------------+\n| clusters                                |\n| hosts                                   |\n| instances                               |\n| replicasets                             |\n| routers                                 |\n| schema_version                          |\n+-----------------------------------------+\n6 rows in set (0,00 sec)<\/pre>\n<h2>D\u00e9ployer MySQL Router<\/h2>\n<p>MySQL Router \u00e9tant d\u00e9j\u00e0 install\u00e9, on va le configurer pour l&rsquo;interfacer avec notre cluster:<\/p>\n<pre class=\"lang:sh decode:true\">$ mysqlrouter --bootstrap root@localhost:3310 --directory routerSandbox\nPlease enter MySQL password for root:\n\nBootstrapping system MySQL Router instance...\nMySQL Router has now been configured for the InnoDB cluster 'testcluster'.\n\nThe following connection information can be used to connect to the cluster.\n\nClassic MySQL protocol connections to cluster 'testcluster':\n- Read\/Write Connections: localhost:6446\n- Read\/Only Connections: localhost:6447\n\nX protocol connections to cluster 'testcluster':\n- Read\/Write Connections: localhost:64460\n- Read\/Only Connections: localhost:64470<\/pre>\n<p>4 ports TCP ont \u00e9t\u00e9 configur\u00e9s, 2 pour les connexions MySQL traditionnelles:<\/p>\n<ul>\n<li><strong>6446<\/strong> : lectures \/ \u00e9critures pour le noeud primaire<\/li>\n<li><strong>6447<\/strong> : lectures seules pour les n\u0153uds secondaires (Round-Robin)<\/li>\n<\/ul>\n<p>Et le pendant pour les connexions avec le protocole X (64460 &amp; 64470), pour une utilisation <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/document-store.html\" target=\"_blank\" rel=\"noopener noreferrer\">NoSQL Document Store<\/a> de MySQL.<\/p>\n<p>Le bootstrap \u00e0 g\u00e9n\u00e9r\u00e9 un fichier de configuration pour MySQL Router<\/p>\n<pre class=\"lang:vim decode:true\">$ view ~\/routerSandbox\/mysqlrouter.conf\n\n# File automatically generated during MySQL Router bootstrap\n[DEFAULT]\nname=routerSandbox\nlogging_folder=\/home\/xxxx\/routerSandbox\/log\nruntime_folder=\/home\/xxxx\/routerSandbox\/run\ndata_folder=\/home\/xxxx\/routerSandbox\/data\nkeyring_path=\/home\/xxxx\/routerSandbox\/data\/keyring\nmaster_key_path=\/home\/xxxx\/routerSandbox\/mysqlrouter.key\n\n[logger]\nlevel = INFO\n\n[metadata_cache:testcluster]\nrouter_id=6\nbootstrap_server_addresses=mysql:\/\/localhost:3310,mysql:\/\/localhost:3320,mysql:\/\/localhost:3330\nuser=mysql_router6_qy5a3dmn5y68\nmetadata_cluster=testcluster\nttl=300\n\n[routing:testcluster_default_rw]\nbind_address=0.0.0.0\nbind_port=6446\ndestinations=metadata-cache:\/\/testcluster\/default?role=PRIMARY\nmode=read-write\nprotocol=classic\n\n[routing:testcluster_default_ro]\nbind_address=0.0.0.0\nbind_port=6447\ndestinations=metadata-cache:\/\/testcluster\/default?role=SECONDARY\nmode=read-only\nprotocol=classic\n...<\/pre>\n<h2>Utiliser\u00a0MySQL Router<\/h2>\n<p>Evidemment il faut le d\u00e9marrer<\/p>\n<pre class=\"lang:sh decode:true \">$ ~\/routerSandbox\/start.sh<\/pre>\n<p>Un petit coup d\u2019\u0153il dans les logs:<\/p>\n<pre class=\"lang:sh decode:true \">$ tail -f ~\/routerSandbox\/log\/mysqlrouter.log\n \n2017-03-10 19:03:36 INFO    [7f81e420e700] Starting Metadata Cache\n2017-03-10 19:03:36 INFO    [7f81e420e700] Connections using ssl_mode 'PREFERRED'\n2017-03-10 19:03:36 INFO    [7f81e3a0d700] [routing:testcluster_default_ro] started: listening on 0.0.0.0:6447; read-only\n2017-03-10 19:03:36 INFO    [7f81e320c700] [routing:testcluster_default_rw] started: listening on 0.0.0.0:6446; read-write\n2017-03-10 19:03:36 INFO    [7f81e2a0b700] [routing:testcluster_default_x_ro] started: listening on 0.0.0.0:64470; read-only\n2017-03-10 19:03:36 INFO    [7f81e220a700] [routing:testcluster_default_x_rw] started: listening on 0.0.0.0:64460; read-write\n2017-03-10 19:03:36 INFO    [7f81e420e700] Connected with metadata server running on 127.0.0.1:3310\n2017-03-10 19:03:36 INFO    [7f81e420e700] Changes detected in cluster 'testcluster' after metadata refresh\n2017-03-10 19:03:36 INFO    [7f81e420e700] Metadata for cluster 'testcluster' has 1 replicasets:\n2017-03-10 19:03:36 INFO    [7f81e420e700] 'default' (3 members, single-master)\n2017-03-10 19:03:36 INFO    [7f81e420e700]     localhost:3310 \/ 33100 - role=HA mode=RW\n2017-03-10 19:03:36 INFO    [7f81e420e700]     localhost:3320 \/ 33200 - role=HA mode=RO\n2017-03-10 19:03:36 INFO    [7f81e420e700]     localhost:3330 \/ 33300 - role=HA mode=RO\n2017-03-10 19:03:36 INFO    [7f81c37fe700] Connected with metadata server running on 127.0.0.1:3310<\/pre>\n<p>Voila MySQL InnoDB Cluster configur\u00e9 et pr\u00eat \u00e0 \u00eatre test\u00e9 !<\/p>\n<p>L&rsquo;application doit se connecter au\u00a0port <strong>6446<\/strong>\u00a0(\u00e9critures et lectures vers le noeud primaire). Les lectures peuvent \u00e9galement \u00eatre dirig\u00e9es vers le port\u00a0<strong>6447<\/strong>.<\/p>\n<h2>Tests<\/h2>\n<p><span style=\"text-decoration: underline;\"><strong>Port de lecture<\/strong><\/span><\/p>\n<pre class=\"lang:sh decode:true\">$ watch -td -n1\u00a0\"mysql -uroot -P6447 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2&gt; \/dev\/null\"\n<\/pre>\n<p>=&gt; affiche : 3320, 3330, 3320, 3330, &#8230;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Port d&rsquo;\u00e9criture<\/strong><\/span><\/p>\n<pre class=\"lang:sh decode:true\">$ watch -td -n1\u00a0\"mysql -uroot -P6446 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2&gt; \/dev\/null\"<\/pre>\n<p>=&gt; affiche : 3310, 3310, 3310, &#8230;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Failover automatique<\/strong><\/span><\/p>\n<p>Gr\u00e2ce \u00e0 la m\u00e9thode <strong>dba.killSandboxInstance()<\/strong> on peut simuler un crash du serveur primaire et ainsi voir \u00e0 l&rsquo;oeuvre le failover automatique du cluster.<\/p>\n<p>Les 2 sessions qui suivent s&rsquo;ex\u00e9cutent en parall\u00e8le:<\/p>\n<p><span style=\"text-decoration: underline;\">Session 1<\/span><\/p>\n<pre class=\"lang:sh decode:true\">$ watch -td -n1\u00a0\"mysql -uroot -P6446 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2&gt; \/dev\/null\"<\/pre>\n<p><span style=\"text-decoration: underline;\">Session 2<\/span><\/p>\n<pre class=\"lang:sh decode:true\">mysql-js&gt; dba.killSandboxInstance(3310)\nThe MySQL sandbox instance on this host in \n\/...\/mysql-sandboxes\/3310 will be killed\n\n\nKilling MySQL instance...\n\nInstance localhost:3310 successfully killed.<\/pre>\n<p>=&gt; La session 1 va afficher 3310 puis 3320 apr\u00e8s le basculement de la base de donn\u00e9es (database failover).<\/p>\n<p>En compl\u00e9ment je vous invite \u00e0 lire :<\/p>\n<ul>\n<li><a href=\"http:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener noreferrer\">D\u00e9ployer un cluster MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener noreferrer\">Configurer ProxySQL pour MySQL Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener noreferrer\">FAQ Webinar 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 noreferrer\">Adopte un\u2026 cluster MySQL Group Replication<\/a><\/li>\n<\/ul>\n<p>et regarder la video de lefred :\u00a0<a href=\"http:\/\/lefred.be\/content\/mysql-innodb-cluster-mysql-shell-starter-guide\/\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL InnoDB Cluster: MySQL Shell starter guide<\/a><\/p>\n<p><strong><span style=\"text-decoration: underline;\">Documentation<\/span><\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL Group Replication<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-innodb-cluster-userguide.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL InnoDB Cluster<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-router\/2.1\/en\/mysql-router-general.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL Router 2.1<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-shell.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL Shell<\/a><\/li>\n<\/ul>\n<p>Thanks for using MySQL!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 :<\/p>\n<p>MySQL Group Replication<br \/>\nPlugin de r\u00e9plication multi-ma\u00eetre, avec r\u00e9solution de conflits et basculement (failover) automatique.<br \/>\nMySQL Router<br \/>\nMiddleware l\u00e9ger et performant qui fournit un routage transparent entre l&rsquo;application et le cluster.<br \/>\nMySQL Shell<br \/>\nClient interactif Javascript, Python et SQL qui permet d&rsquo;administrer le cluster.<\/p>\n<p>MySQL Group Replication est GA et peut donc \u00eatre utilis\u00e9 tel quel hors MySQL InnoDB Cluster (voir\u00a0D\u00e9ployer un cluster MySQL Group Replication).<\/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,337],"tags":[286,79,298,302,318,341],"class_list":["post-1832","post","type-post","status-publish","format-standard","hentry","category-group-replication","category-haute-disponibilite","category-innodb-cluster","category-tuto","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-ty","jetpack-related-posts":[{"id":1800,"url":"https:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/","url_meta":{"origin":1832,"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":1855,"url":"https:\/\/dasini.net\/blog\/2017\/04\/10\/adopte-un-cluster-mysql-group-replication\/","url_meta":{"origin":1832,"position":1},"title":"Adopte un&#8230; cluster MySQL Group Replication","author":"Olivier DASINI","date":"10 avril 2017","format":false,"excerpt":"Au menu d'aujourd'hui : comment passer de l'administration \"manuelle\" de votre solution HA MySQL Group Replication \u00e0 une administration plus simple, plus fun mais surtout facilement automatisable avec le pack MySQL InnoDB Cluster. En clair, on va voir comment utiliser MySQL Shell pour l'administration et l'orchestration du cluster et MySQL\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"MySQL InnoDB Cluster Overview","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_cluster_overview.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":3969,"url":"https:\/\/dasini.net\/blog\/2020\/11\/10\/resume-des-nouveautes-de-mysql-8-0-22\/","url_meta":{"origin":1832,"position":2},"title":"R\u00e9sum\u00e9 des nouveaut\u00e9s de MySQL 8.0.22","author":"Olivier DASINI","date":"10 novembre 2020","format":false,"excerpt":"J'ai cr\u00e9\u00e9, sp\u00e9cialement pour toi, une pr\u00e9sentation qui r\u00e9sume les nouvelles fonctionnalit\u00e9s de la derni\u00e8re version de MySQL, sortie le 19 octobre 2020, la 8.0.22.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/img.youtube.com\/vi\/CFFcaqhN-W8\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1915,"url":"https:\/\/dasini.net\/blog\/2017\/05\/11\/tutoriel-deployer-mysql-innodb-cluster\/","url_meta":{"origin":1832,"position":3},"title":"Tutoriel &#8211; D\u00e9ployer MySQL innoDB Cluster","author":"Olivier DASINI","date":"11 mai 2017","format":false,"excerpt":"Dans les \u00e9pisodes pr\u00e9c\u00e9dents on a vu comment d\u00e9ployer \"manuellement\" MySQL Group Replication, comprendre et tester MySQL InnoDB Cluster ainsi que comment g\u00e9rer ais\u00e9ment un cluster Group Replication d\u00e9ja d\u00e9ploy\u00e9 avec MySQL Shell. Aujourd'hui, dans la s\u00e9rie Haute Disponibilit\u00e9 avec MySQL on va voir comment d\u00e9ployer et g\u00e9rer un cluster\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"Warning","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/warning.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":1832,"position":4},"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":1654,"url":"https:\/\/dasini.net\/blog\/2016\/10\/05\/mysql-a-oracle-openworld-2016\/","url_meta":{"origin":1832,"position":5},"title":"MySQL \u00e0 Oracle OpenWorld 2016","author":"Olivier DASINI","date":"5 octobre 2016","format":false,"excerpt":"R\u00e9sum\u00e9 des annonces MySQL \u00e0 Oracle OpenWorld: MySQL dans le cloud, MySQL Group Replication, MySQL InnoDB Cluster, MySQL 8,...","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/Oracle_MySQL_Cloud_Service.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1832","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=1832"}],"version-history":[{"count":22,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1832\/revisions"}],"predecessor-version":[{"id":7244,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1832\/revisions\/7244"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}