![\"Warning\"](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/warning.png?resize=200%2C200\")
<\/a><\/p>\n<\/p>\n
L’ article que tu t\u2019appr\u00eates \u00e0 lire est maintenant obsol\u00e8te \ud83d\ude41 Ohhhhhh !<\/p>\n
Mais rassure toi, il est remplac\u00e9 par un nouvel article, tout nouveau, tout beau et surtout plus complet et \u00e0 jour \ud83d\ude42 Ahhhhh !!<\/p>\n
C’est par ici.<\/a><\/span><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n Dans les \u00e9pisodes pr\u00e9c\u00e9dents on a vu comment \u00a0d\u00e9ployer \u00ab\u00a0manuellement\u00a0\u00bb MySQL Group Replication<\/a>, \u00a0comprendre et tester MySQL InnoDB Cluster<\/a>\u00a0 ainsi que comment \u00a0g\u00e9rer ais\u00e9ment un cluster Group Replication d\u00e9ja d\u00e9ploy\u00e9<\/a> avec MySQL Shell.<\/p>\n Aujourd’hui, dans la s\u00e9rie\u00a0Haute Disponibilit\u00e9 avec MySQL<\/strong>\u00a0on va voir comment d\u00e9ployer et g\u00e9rer un cluster from scratch<\/em>\u00a0, sous la forme d’un tutoriel, gr\u00e2ce \u00e0 la solution tout en un : MySQL InnoDB Cluster<\/strong>.<\/p>\n Note:\u00a0<\/em> <\/p>\n 3 instances MySQL autonomes, <spoil altert> qui vont gr\u00e2ce au plugin MySQL Group Replication<\/a> se transformer en une base de donn\u00e9es distribu\u00e9e.<\/spoil altert><\/p>\n 1 instance applicative :\u00a0192.168.1.11; MySQL Router + mon application.<\/p>\n MySQL Shell est install\u00e9 sur toutes les instances.<\/p>\n En ce qui concerne les versions des logiciels:<\/p>\n — Note<\/span>: TOUJOURS<\/strong> prendre la derni\u00e8re<\/strong> version du Router<\/a> et<\/strong> du Shell<\/a>. C’est actuellement la branche 8.0<\/strong>.x qui est bien \u00e9videmment compatible<\/span> avec MySQL 5.7<\/strong> InnoDB Cluster.<\/em><\/span><\/p>\n ex: MySQL Router 8.0.12 + MySQL Shell 8.0.12 avec MySQL 5.7.23 <\/p>\n Avec un sch\u00e9ma sa donne (\u00e0 peu pr\u00e8s) \u00e7a :<\/p>\n <\/p>\n La premi\u00e8re \u00e9tape consiste \u00e0 s’assurer que les instances MySQL sont correctement configur\u00e9es pour l’utilisation de MySQL Group Replication<\/a>, la couche haute disponibilit\u00e9<\/strong> de notre architecture. A noter qu’il est pr\u00e9f\u00e9rable de provisionner ses instances d\u00e9j\u00e0 correctement configur\u00e9es (comme d\u00e9taill\u00e9 dans cet article<\/a>) pour MySQL Group Replication.<\/p>\n La v\u00e9rification de la configuration se fait gr\u00e2ce \u00e0 MySQL Shell et la m\u00e9thode checkInstanceConfiguration()<\/strong><\/em>\u00a0:<\/p>\n Dans mon cas, avec l’installation par d\u00e9faut sous Ubuntu, niveau configuration j’ai tout \u00e0 faire \ud83d\ude42<\/p>\n La m\u00e9thode renvoie un document JSON (pratique pour l’automatisation) avec la liste des t\u00e2ches \u00e0 effectuer pour \u00eatre conforme… Configurons donc !<\/p>\n <\/p>\n J’ai deux solutions :<\/p>\n Soyons fou ! allons y\u00a0pour la m\u00e9thode 2 :<\/p>\n Ouppss!!!\u00a0dba.configureLocalInstance<\/em>\u00a0ne fonctionne qu’en local, c’est-\u00e0-dire, si je suis connect\u00e9 sur la machine h\u00f4te de l’instance MySQL (ce qui est une bonne chose). Du coup apr\u00e8s m’\u00eatre connect\u00e9 \u00e0 l’h\u00f4te 192.168.1.22 :<\/p>\n Note<\/span>: Assure toi d’avoir les droits n\u00e9cessaires\u00a0pour mettre \u00e0 jour le fichier de configuration de MySQL.<\/em><\/p>\n Les informations ajout\u00e9es dans le fichier de configuration se trouvent en fin de fichier :<\/p>\n 192.168.1.22 est configur\u00e9e. Apr\u00e8s red\u00e9marrage de l’instance MySQL, la sortie de\u00a0checkInstanceConfiguration<\/em> est beaucoup moins anxiog\u00e8ne :<\/p>\n OK ! Le membre est pr\u00eat pour faire parti d’un groupe.<\/p>\n La m\u00eame proc\u00e9dure doit \u00eatre appliqu\u00e9e sur les autres instances MySQL.<\/p>\n …<\/p>\n <\/p>\n <\/p>\n Une fois les 3 instances correctement configur\u00e9es, l’\u00e9tape suivante consiste \u00e0 cr\u00e9er le cluster avec\u00a0createCluster<\/em><\/strong>. Cette m\u00e9thode va \u00eatre jou\u00e9e sur le premier membre, l’instance MySQL sur \u00a0192.168.1.22, \u00a0elle va permettre de bootstrapper le cluster:<\/p>\n createCluster()<\/strong><\/em> prend comme param\u00e8tre le nom du cluster (pocCluster). Je peux lui passer \u00e9galement quelques information optionnelles comme la whitelist<\/a>.<\/p>\n On peut v\u00e9rifier l’\u00e9tat du n\u0153ud dans le cluster avec\u00a0status()<\/em><\/strong> :<\/p>\n Notes<\/span> : Assure toi que ton\u00a0DNS (ou \/etc\/hosts) est\u00a0correctement configur\u00e9, sinon tu vas avoir des soucis de connections…<\/em><\/p>\n <\/p>\n L’ajouts des\u00a0n\u0153uds suivant se fait avec addInstance()<\/strong>, il est n\u00e9anmoins conseill\u00e9 d’ex\u00e9cuter checkInstanceState()<\/strong>\u00a0au pr\u00e9alable pour s’assurer de la compatibilit\u00e9 des GTID sets :<\/p>\n N\u0153ud 2<\/span><\/p>\n Au cas o\u00f9 l’instance ajout\u00e9e contient plus de transactions que le groupe checkInstanceState<\/em>\u00a0le fait savoir :<\/p>\n En fonction du contexte, il faut alors soit restaurer une sauvegarde d’un membre du cluster sur l’instance probl\u00e9matique (celle qui diverge) ou alors si tu sais ce que tu fais<\/span><\/a>, une synchronisation des GTIDs<\/a> est toujours possible, voir un reset master<\/a>.<\/p>\n <\/p>\n N\u0153ud 3<\/span><\/p>\n Et voil\u00e0!<\/p>\n Cluster MySQL Group Replication de 3 n\u0153uds d\u00e9ploy\u00e9 gr\u00e2ce \u00e0 MySQL Shell !<\/p>\n <\/p>\n Pour rendre la configuration persistante ie l’\u00e9crire dans le fichier de configuration, il faut ex\u00e9cuter, apr\u00e8s que le noeud soit configur\u00e9, la m\u00e9thode\u00a0dba.configureLocalInstance()<\/strong> :<\/p>\n A noter que cette op\u00e9ration ne peut se faire qu’en local.<\/p>\n La suite ?<\/p>\n <\/p>\n <\/p>\n Les recommandations de MySQL sont d’installer MySQL Router sur la machine h\u00f4te de l’application, je vais donc l’installer sur la machine\u00a0192.168.1.11.<\/p>\n <\/p>\n Note<\/span>: Si tu ne peux (veux) pas mettre MySQL Router sur l’application, il va alors te falloir g\u00e9rer le HA du Router. Plusieurs solutions sont envisageables comme :<\/em><\/p>\n <\/p>\n <\/p>\n 4 ports TCP ont \u00e9t\u00e9 configur\u00e9s, dont 2 pour les connexions MySQL traditionnelles:<\/p>\n 6446 : lectures \/ \u00e9critures pour le noeud primaire <\/p>\n Le fichier de configuration du Router contient quelques informations importantes, tel que le(s) port(s) \u00e0 utiliser par l’application :<\/p>\n Il est \u00e9videmment possible de modifier ce fichier. Par exemple, souvent les applications se connectent au port 3306<\/strong>, \u00e7a peut donc avoir du sens de modifier le port du noeud primaire en le passant de 6446 \u00e0 3306 :<\/p>\n Ton application va donc (continuer \u00e0) se connecter au port 3306, sauf que maintenant ce n’est pas 1 instance, mais bel et bien 3 instances qui sont en backend<\/em>, et ceci en toute transparence.<\/p>\n <\/p>\n Ensuite, il faut d\u00e9marrer MySQL Router avec le script start.sh<\/strong><\/p>\n L’arr\u00eat du Router se fait avec le script stop.sh<\/strong>\u00a0(mais tu l’avais devin\u00e9)<\/p>\n <\/p>\n <\/p>\n Quelques commandes qui vont te simplifier la vie…<\/p>\n <\/p>\n Les m\u00e9ta-donn\u00e9es du cluster sont stock\u00e9es sur les membres dans le sch\u00e9ma\u00a0mysql_innodb_cluster_metadata<\/em><\/strong> :<\/p>\n Pour r\u00e9cup\u00e9rer ces informations dans une nouvelle session il faut utiliser la m\u00e9thode\u00a0getCluster<\/em><\/strong> :<\/p>\n <\/p>\n <\/p>\n Le basculement niveau base de donn\u00e9es (changement de primaire) est automatiquement g\u00e9r\u00e9 par les membres du cluster entre eux.<\/p>\n Crash du noeud primaire (192.168.1.22)…<\/p>\n Nouveau primaire \u00e9lu par le groupe :\u00a0192.168.1.48.<\/p>\n Et 192.168.1.22 est port\u00e9 disparu (MIA).<\/p>\n <\/p>\n Un rejoinInstance()<\/strong><\/em> est n\u00e9cessaire pour remettre le membre dans le cluster. Il aura un r\u00f4le de secondaire (voir paragraphe suivant\u00a0Remettre un membre dans le groupe<\/span><\/strong>).<\/p>\n <\/p>\n <\/p>\n Un membre avec son instance MySQL qui\u00a0tourne et qui est correctement configur\u00e9 peut \u00eatre remis (apr\u00e8s un red\u00e9marrage de l’instance par exemple) dans le groupe avec la commande rejoinInstance()<\/strong><\/em> :<\/p>\n <\/p>\n <\/p>\n Sans grande surprise, c’est la commande\u00a0removeInstance<\/em><\/strong><\/p>\n L’instance n’est alors plus list\u00e9e dans les m\u00e9ta-donn\u00e9es :<\/p>\n Pour la remettre dans le groupe, il faut donc rejouer le processus de l’ajout d’instance vu plus haut :<\/p>\n <\/p>\n <\/p>\n Si le cluster perd plus de la moiti\u00e9 de ses membres il se retrouve dans un \u00e9tat assez d\u00e9sagr\u00e9able, network partitioning<\/a>, en clair il faut une intervention externe au groupe pour permettre aux membres restant de continuer \u00e0 faire leur boulot de serveur MySQL.<\/p>\n Dans notre cas, avec 3 instances, il faut en perdre\u00a02 d’un coup :<\/p>\n Perte des n\u0153uds (crash) 192.168.1.22 &\u00a0192.168.1.50… \u00a0(Mayday, Mayday, Mayday!!!)<\/p>\n Le failover automatique ne peut pas s\u2019enclencher, le membre survivant (192.168.1.48) est en lecture seule. Il faut donc intervenir :<\/p>\n Evidemment, sauf si tu es joueur \ud83d\ude42 , il faut \u00e9viter de rester trop longtemps dans cet \u00e9tat.<\/p>\n Une fois les instances en condition, il faut utiliser\u00a0rejoinInstance()<\/strong><\/em>\u00a0pour remettre les membres dans le cluster, en tant que secondaire (voir paragraphe Remettre un membre dans le groupe<\/span><\/strong>).<\/p>\n <\/p>\n <\/p>\n La perte du quorum est une chose, mais il y a pire, perdre tout les n\u0153uds…<\/p>\n En cas d’arr\u00eat total du cluster i.e. toutes les instances\u00a0sont \u00e9teintes, il faut utiliser, une fois les instances MySQL de nouveau d\u00e9marr\u00e9es \u00a0rebootClusterFromCompleteOutage()<\/em><\/strong> :<\/p>\n Le membre sur lequel la commande est ex\u00e9cut\u00e9e est le nouveau primaire.<\/p>\n <\/p>\n Dans la m\u00eame th\u00e9matique :<\/p>\n <\/p>\n Thanks for using MySQL!<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Dans les \u00e9pisodes pr\u00e9c\u00e9dents on a vu comment d\u00e9ployer \u00ab\u00a0manuellement\u00a0\u00bb 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.<\/p>\n Aujourd’hui, dans la s\u00e9rie Haute Disponibilit\u00e9 avec MySQL on va voir comment d\u00e9ployer et g\u00e9rer un cluster from scratch , sous la forme d’un tutoriel, gr\u00e2ce \u00e0 la solution tout en un : MySQL InnoDB Cluster.<\/p>\n Note:
\n
\n
\n
\nL’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server (plugin GRoup Replication) avec pour moteur de stockage InnoDB<\/a>, solution \u00e0 ne pas confondre avec MySQL NDB Cluster<\/a>\u00a0(moteur de stockage NDB).<\/em><\/p>\nLe contexte<\/h2>\n
\n
\n
\nUpdate 08-08-2018<\/em><\/span><\/p>\n
\n—<\/em><\/span><\/p>\n![\"MySQL](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_Cluster_Production_post.png?resize=600%2C837\")
<\/p>\nV\u00e9rifier la configuration des instances<\/h2>\n
$ mysqlsh\r\nWelcome to MySQL Shell 1.0.9\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\n\r\n\r\nmysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')\r\nPlease provide the password for 'root@192.168.1.22:3306': \r\nValidating instance...\r\n\r\nThe instance '192.168.1.22:3306' is not valid for Cluster usage.\r\n\r\nThe following issues were encountered:\r\n\r\n - Some configuration options need to be fixed.\r\n\r\n+----------------------------------+---------------+----------------+--------------------------------------------------+\r\n| Variable | Current Value | Required Value | Note |\r\n+----------------------------------+---------------+----------------+--------------------------------------------------+\r\n| binlog_checksum | CRC32 | NONE | Update the server variable or restart the server |\r\n| enforce_gtid_consistency | OFF | ON | Restart the server |\r\n| gtid_mode | OFF | ON | Restart the server |\r\n| log_bin | 0 | 1 | Restart the server |\r\n| log_slave_updates | 0 | ON | Restart the server |\r\n| master_info_repository | FILE | TABLE | Restart the server |\r\n| relay_log_info_repository | FILE | TABLE | Restart the server |\r\n| transaction_write_set_extraction | OFF | XXHASH64 | Restart the server |\r\n+----------------------------------+---------------+----------------+--------------------------------------------------+\r\n\r\n\r\nPlease fix these issues , restart the serverand try again.\r\n\r\n{\r\n \"config_errors\": [\r\n {\r\n \"action\": \"server_update\", \r\n \"current\": \"CRC32\", \r\n \"option\": \"binlog_checksum\", \r\n \"required\": \"NONE\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"OFF\", \r\n \"option\": \"enforce_gtid_consistency\", \r\n \"required\": \"ON\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"OFF\", \r\n \"option\": \"gtid_mode\", \r\n \"required\": \"ON\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"0\", \r\n \"option\": \"log_bin\", \r\n \"required\": \"1\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"0\", \r\n \"option\": \"log_slave_updates\", \r\n \"required\": \"ON\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"FILE\", \r\n \"option\": \"master_info_repository\", \r\n \"required\": \"TABLE\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"FILE\", \r\n \"option\": \"relay_log_info_repository\", \r\n \"required\": \"TABLE\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"OFF\", \r\n \"option\": \"transaction_write_set_extraction\", \r\n \"required\": \"XXHASH64\"\r\n }\r\n ], \r\n \"errors\": [], \r\n \"restart_required\": true, \r\n \"status\": \"error\"\r\n}<\/pre>\n\n
mysql-js> dba.configureLocalInstance('root@192.168.1.22:3306')\r\nDba.configureLocalInstance: This function only works with local instances (RuntimeError)<\/pre>\nsudo mysqlsh\r\nWelcome to MySQL Shell 1.0.9\r\n...\r\n\r\nmysql-js> dba.configureLocalInstance('root@localhost:3306')\r\nPlease provide the password for 'root@localhost:3306': \r\n\r\nDetecting the configuration file...\r\nFound configuration file at standard location: \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\nDo you want to modify this file? [Y|n]: Y\r\nValidating instance...\r\n\r\nThe configuration has been updated but it is required to restart the server.\r\n\r\n{\r\n \"config_errors\": [\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"OFF\", \r\n \"option\": \"enforce_gtid_consistency\", \r\n \"required\": \"ON\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"OFF\", \r\n \"option\": \"gtid_mode\", \r\n \"required\": \"ON\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"0\", \r\n \"option\": \"log_bin\", \r\n \"required\": \"1\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"0\", \r\n \"option\": \"log_slave_updates\", \r\n \"required\": \"ON\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"FILE\", \r\n \"option\": \"master_info_repository\", \r\n \"required\": \"TABLE\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"FILE\", \r\n \"option\": \"relay_log_info_repository\", \r\n \"required\": \"TABLE\"\r\n },\r\n {\r\n \"action\": \"restart\", \r\n \"current\": \"OFF\", \r\n \"option\": \"transaction_write_set_extraction\", \r\n \"required\": \"XXHASH64\"\r\n }\r\n ], \r\n \"errors\": [], \r\n \"restart_required\": true, \r\n \"status\": \"error\"\r\n}<\/pre>\n# cat \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\n\r\n[mysqld]\r\n... <data previously in the file> ...\r\n\r\nlog_slave_updates = ON\r\nserver_id = 2323701546\r\nrelay_log_info_repository = TABLE\r\nmaster_info_repository = TABLE\r\ntransaction_write_set_extraction = XXHASH64\r\nbinlog_format = ROW\r\ndisabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE\r\nreport_port = 3306\r\nbinlog_checksum = NONE\r\nenforce_gtid_consistency = ON\r\nlog_bin\r\ngtid_mode = ON<\/pre>\n
mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')\r\nPlease provide the password for 'root@192.168.1.22:3306': \r\nValidating instance...\r\n\r\nThe instance '192.168.1.22:3306' is valid for Cluster usage\r\n{\r\n \"status\": \"ok\"\r\n}<\/pre>\nmysql-js> dba.checkInstanceConfiguration('root@192.168.1.50:3306')\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nValidating instance...\r\n\r\nThe instance '192.168.1.50:3306' is valid for Cluster usage\r\n{\r\n \"status\": \"ok\"\r\n}\r\nmysql-js> dba.checkInstanceConfiguration('root@192.168.1.48:3306')\r\nPlease provide the password for 'root@192.168.1.48:3306': \r\nValidating instance...\r\n\r\nThe instance '192.168.1.48:3306' is valid for Cluster usage\r\n{\r\n \"status\": \"ok\"\r\n}<\/pre>\nCr\u00e9er le cluster<\/h2>\n
mysql-js> \\c root@192.168.1.22\r\nCreating a Session to 'root@192.168.1.22'\r\nEnter password: \r\nClassic Session successfully established. No default schema selected.\r\n\r\n\r\nmysql-js> var cluster = dba.createCluster('pocCluster', {ipWhitelist: \"192.168.1.0\/16\"});\r\nA new InnoDB cluster will be created on instance 'root@192.168.1.22:3306'.\r\n\r\nCreating InnoDB cluster 'pocCluster' on 'root@192.168.1.22: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>\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK_NO_TOLERANCE\", \r\n \"statusText\": \"Cluster is NOT tolerant to any failures.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }\r\n }\r\n }\r\n}<\/pre>\nmysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nAnalyzing the instance replication state...\r\n\r\nThe instance '192.168.1.50:3306' is valid for the cluster.\r\nThe instance is new to Group Replication.\r\n\r\n{\r\n \"reason\": \"new\", \r\n \"state\": \"ok\"\r\n}\r\n\r\n\r\nmysql-js> cluster.addInstance(\"root@192.168.1.50:3306\", {ipWhitelist: \"192.168.1.0\/16\"})\r\nA new instance will be added to the InnoDB cluster. Depending on the amount of\r\ndata on the cluster this might take from a few seconds to several hours.\r\n\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nAdding instance to the cluster ...\r\n\r\nThe instance 'root@192.168.1.50:3306' was successfully added to the cluster.\r\n\r\n\r\nmysql-js> cluster.status();\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK_NO_TOLERANCE\", \r\n \"statusText\": \"Cluster is NOT tolerant to any failures.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nmysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nAnalyzing the instance replication state...\r\n\r\nThe instance '192.168.1.50:3306' is invalid for the cluster.\r\nThe instance contains additional transactions in relation to the cluster.\r\n\r\n{\r\n \"reason\": \"diverged\", \r\n \"state\": \"error\"\r\n}<\/pre>\nmysql-js> cluster.checkInstanceState('root@192.168.1.48:3306')\r\nPlease provide the password for 'root@192.168.1.48:3306': \r\nAnalyzing the instance replication state...\r\n\r\nThe instance '192.168.1.48:3306' is valid for the cluster.\r\nThe instance is new to Group Replication.\r\n\r\n{\r\n \"reason\": \"new\", \r\n \"state\": \"ok\"\r\n}\r\n\r\n\r\nmysql-js> cluster.addInstance(\"root@192.168.1.48:3306\", {ipWhitelist: \"192.168.1.0\/16\"})\r\nA new instance will be added to the InnoDB cluster. Depending on the amount of\r\ndata on the cluster this might take from a few seconds to several hours.\r\n\r\nPlease provide the password for 'root@192.168.1.48:3306': \r\nAdding instance to the cluster ...\r\n\r\nThe instance 'root@192.168.1.48:3306' was successfully added to the cluster.\r\n\r\n\r\nmysql-js> cluster.status();\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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}\r\n<\/pre>\n\n
daz@192.168.1.50:~$ grep -c group_replication \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\n0\r\n\r\n\r\ndaz@192.168.1.50:~$ sudo mysqlsh --uri=root@localhost\r\nCreating a Session to 'root@localhost'\r\nEnter password: \r\nClassic Session successfully established. No default schema selected.\r\nWelcome to MySQL Shell 1.0.9\r\n...\r\n\r\n\r\nmysql-js> dba.configureLocalInstance(\"root@localhost:3306\") \r\nPlease provide the password for 'root@localhost:3306': \r\n\r\nDetecting the configuration file...\r\nFound configuration file at standard location: \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\nDo you want to modify this file? [Y|n]: Y\r\nValidating instance...\r\n\r\nThe instance 'localhost:3306' is valid for Cluster usage\r\nYou can now use it in an InnoDB Cluster.\r\n\r\n{\r\n \"status\": \"ok\"\r\n}\r\n\r\n\r\n\r\ndaz@192.168.1.50:~$ grep -c group_replication \/etc\/mysql\/my.cnf\r\n32\r\n<\/pre>\nConfiguration de MySQL Router<\/h2>\n
\n
Bootstrap MySQL Router<\/h3>\n
daz@192.168.1.11 ~ $ mysqlrouter --bootstrap root@192.168.1.22:3306 --directory RouterPoC\r\nPlease enter MySQL password for root: \r\n\r\nBootstrapping MySQL Router instance at \/home\/daz\/RouterPoC...\r\nMySQL Router 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
\n6447 : lectures seules pour les n\u0153uds secondaires (Round-Robin)
\nEt le pendant pour les connexions avec le protocole X (64460 & 64470), pour une utilisation NoSQL Document Store<\/a> de MySQL.<\/p>\ndaz@192.168.1.11 ~ $ cat ~\/RouterPoC\/mysqlrouter.conf\r\n# File automatically generated during MySQL Router bootstrap\r\n[DEFAULT]\r\nlogging_folder=\/home\/daz\/RouterPoC\/log\r\nruntime_folder=\/home\/daz\/RouterPoC\/run\r\ndata_folder=\/home\/daz\/RouterPoC\/data\r\nkeyring_path=\/home\/daz\/RouterPoC\/data\/keyring\r\nmaster_key_path=\/home\/daz\/RouterPoC\/mysqlrouter.key\r\n\r\n[logger]\r\nlevel = INFO\r\n\r\n[metadata_cache:pocCluster]\r\nrouter_id=3\r\nbootstrap_server_addresses=mysql:\/\/192.168.1.22:3306,mysql:\/\/192.168.1.50:3306,mysql:\/\/192.168.1.48:3306\r\nuser=mysql_router3_qeteek4wi41s\r\nmetadata_cluster=pocCluster\r\nttl=300\r\n\r\n[routing:pocCluster_default_rw]\r\nbind_address=0.0.0.0\r\nbind_port=6446\r\ndestinations=metadata-cache:\/\/pocCluster\/default?role=PRIMARY\r\nmode=read-write\r\nprotocol=classic\r\n\r\n[routing:pocCluster_default_ro]\r\nbind_address=0.0.0.0\r\nbind_port=6447\r\ndestinations=metadata-cache:\/\/pocCluster\/default?role=SECONDARY\r\nmode=read-only\r\nprotocol=classic\r\n\r\n[routing:pocCluster_default_x_rw]\r\nbind_address=0.0.0.0\r\nbind_port=64460\r\ndestinations=metadata-cache:\/\/pocCluster\/default?role=PRIMARY\r\nmode=read-write\r\nprotocol=x\r\n\r\n[routing:pocCluster_default_x_ro]\r\nbind_address=0.0.0.0\r\nbind_port=64470\r\ndestinations=metadata-cache:\/\/pocCluster\/default?role=SECONDARY\r\nmode=read-only\r\nprotocol=x<\/pre>\n
daz@192.168.1.11 ~ $ grep -A6 pocCluster_default_rw ~\/RouterPoC\/mysqlrouter.conf\r\n[routing:pocCluster_default_rw]\r\nbind_address=0.0.0.0\r\nbind_port=3306\r\ndestinations=metadata-cache:\/\/pocCluster\/default?role=PRIMARY\r\nmode=read-write\r\nprotocol=classic<\/pre>\n
daz@192.168.1.11 ~ $ ~\/RouterPoC\/start.sh<\/pre>\n
daz@192.168.1.11 ~ $ ~\/RouterPoC\/stop.sh<\/pre>\n
Gestion des n\u0153uds<\/h2>\n
R\u00e9cup\u00e9rer les m\u00e9ta-donn\u00e9es d’un cluster<\/h3>\n
mysql> SHOW SCHEMAS;\r\n+-------------------------------+\r\n| Database |\r\n+-------------------------------+\r\n| information_schema |\r\n| mysql |\r\n| mysql_innodb_cluster_metadata |\r\n| performance_schema |\r\n| sys |\r\n+-------------------------------+\r\n5 rows in set (0,00 sec)\r\n\r\n\r\nmysql> SHOW TABLES IN mysql_innodb_cluster_metadata;\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+-----------------------------------------+\r\n6 rows in set (0,01 sec)\r\n\r\n\r\nmysql> SELECT cluster_name FROM mysql_innodb_cluster_metadata.clusters;\r\n+--------------+\r\n| cluster_name |\r\n+--------------+\r\n| pocCluster |\r\n+--------------+<\/pre>\n
mysql-js> cluster.status()\r\nReferenceError: cluster is not defined\r\n\r\n\r\nmysql-js> var cluster = dba.getCluster('pocCluster')\r\n\r\n\r\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nFailover<\/h3>\n
mysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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}\r\n<\/pre>\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.48:3306\", \r\n \"status\": \"OK_NO_TOLERANCE\", \r\n \"statusText\": \"Cluster is NOT tolerant to any failures. 1 member is not active\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"(MISSING)\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.48:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nRemettre un membre dans le groupe<\/h3>\n
mysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK_NO_TOLERANCE\", \r\n \"statusText\": \"Cluster is NOT tolerant to any failures. 1 member is not active\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"(MISSING)\"\r\n }\r\n }\r\n }\r\n}\r\n\r\n\r\nmysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nAnalyzing the instance replication state...\r\n\r\nThe instance '192.168.1.50:3306' is valid for the cluster.\r\nThe instance is fully recoverable.\r\n\r\n{\r\n \"reason\": \"recoverable\", \r\n \"state\": \"ok\"\r\n}\r\n\r\n\r\nmysql-js> cluster.rejoinInstance(\"root@192.168.1.50:3306\", {ipWhitelist: \"192.168.1.0\/16\"})\r\nRejoining the instance to the InnoDB cluster. Depending on the original\r\nproblem that made the instance unavailable, the rejoin operation might not be\r\nsuccessful and further manual steps will be needed to fix the underlying\r\nproblem.\r\n\r\nPlease monitor the output of the rejoin operation and take necessary action if\r\nthe instance cannot rejoin.\r\n\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nRejoining instance to the cluster ...\r\n\r\nThe instance 'root@192.168.1.50:3306' was successfully rejoined on the cluster.\r\n\r\nThe instance '192.168.1.50:3306' was successfully added to the MySQL Cluster.\r\n\r\n\r\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nSupprimer\u00a0une instance du groupe<\/h3>\n
mysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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}\r\n\r\n\r\nmysql-js> cluster.removeInstance(\"root@192.168.1.50:3306\")\r\nThe instance will be removed from the InnoDB cluster. Depending on the \r\ninstance being the Seed or not, the Metadata session might become invalid. \r\nIf so, please start a new session to the Metadata Storage R\/W instance.\r\n\r\nThe instance 'root@192.168.1.50:3306' was successfully removed from the cluster.\r\n\r\n\r\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK_NO_TOLERANCE\", \r\n \"statusText\": \"Cluster is NOT tolerant to any failures.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48: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>\nmysql-js> \\sql\r\nSwitching to SQL mode... Commands end with ;\r\n\r\n\r\nmysql-sql> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;\r\n+-------------------+\r\n| instance_name |\r\n+-------------------+\r\n| 192.168.1.22:3306 |\r\n| 192.168.1.48:3306 |\r\n+-------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n
mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nAnalyzing the instance replication state...\r\n\r\nThe instance '192.168.1.50:3306' is valid for the cluster.\r\nThe instance is fully recoverable.\r\n\r\n{\r\n \"reason\": \"recoverable\", \r\n \"state\": \"ok\"\r\n}\r\n\r\n\r\nmysql-js> cluster.addInstance(\"root@192.168.1.50:3306\", {ipWhitelist: \"192.168.1.0\/16\"})\r\nA new instance will be added to the InnoDB cluster. Depending on the amount of\r\ndata on the cluster this might take from a few seconds to several hours.\r\n\r\nPlease provide the password for 'root@192.168.1.50:3306': \r\nAdding instance to the cluster ...\r\n\r\nThe instance 'root@192.168.1.50:3306' was successfully added to the cluster.\r\n\r\n\r\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nPerte du quorum<\/h3>\n
mysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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>\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.22:3306\", \r\n \"status\": \"NO_QUORUM\", \r\n \"statusText\": \"Cluster has no quorum as visible from 'localhost:3306' and cannot process write transactions. 2 members are not active\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"UNREACHABLE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"UNREACHABLE\"\r\n }\r\n }\r\n }\r\n}<\/pre>\n$ mysqlsh --uri=root@192.168.1.48\r\nCreating a Session to 'root@192.168.1.48'\r\n...\r\n\r\nmysql-js> var cluster = dba.getCluster(\"pocCluster\")\r\nWARNING: The session is on a Read Only instance.\r\n Write operations on the InnoDB cluster will not be allowed\r\n\r\n\r\nmysql-js> cluster.forceQuorumUsingPartitionOf('192.168.1.48:3306')\r\nRestoring replicaset 'default' from loss of quorum, by using the partition composed of [192.168.1.48:3306]\r\n\r\nPlease provide the password for 'root@192.168.1.48:3306': \r\nRestoring the InnoDB cluster ...\r\n\r\nThe InnoDB cluster was successfully restored using the partition from the instance 'root@192.168.1.48:3306'.\r\n\r\nWARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored.\r\n\r\n\r\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.48:3306\", \r\n \"status\": \"OK_NO_TOLERANCE\", \r\n \"statusText\": \"Cluster is NOT tolerant to any failures. 2 members are not active\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"(MISSING)\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"(MISSING)\"\r\n }\r\n }\r\n }\r\n}<\/pre>\nRepartir apr\u00e8s un arr\u00eat total du cluster<\/h3>\n
$ mysqlsh --uri=root@192.168.1.48\r\nCreating a Session to 'root@mysql_node1'\r\n...\r\n\r\nmysql-js> var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')\r\nReconfiguring the cluster 'pocCluster' from complete outage...\r\n\r\nThe instance '192.168.1.22:3306' was part of the cluster configuration.\r\nWould you like to rejoin it to the cluster? [y|N]: y\r\n\r\nThe instance '192.168.1.50:3306' was part of the cluster configuration.\r\nWould you like to rejoin it to the cluster? [y|N]: y\r\n\r\n\r\nThe cluster was successfully rebooted.\r\n\r\n\r\nmysql-js> cluster.status()\r\n{\r\n \"clusterName\": \"pocCluster\", \r\n \"defaultReplicaSet\": {\r\n \"name\": \"default\", \r\n \"primary\": \"192.168.1.48:3306\", \r\n \"status\": \"OK\", \r\n \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \r\n \"topology\": {\r\n \"192.168.1.22:3306\": {\r\n \"address\": \"192.168.1.22:3306\", \r\n \"mode\": \"R\/O\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.48:3306\": {\r\n \"address\": \"192.168.1.48:3306\", \r\n \"mode\": \"R\/W\", \r\n \"readReplicas\": {}, \r\n \"role\": \"HA\", \r\n \"status\": \"ONLINE\"\r\n }, \r\n \"192.168.1.50:3306\": {\r\n \"address\": \"192.168.1.50: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\n
\nL’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server avec pour moteur de stockage InnoDB, solution \u00e0 ne pas confondre avec MySQL NDB Cluster (moteur de stockage NDB).<\/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":[286,79,24,298,302,318,341],"class_list":["post-1915","post","type-post","status-publish","format-standard","hentry","category-group-replication","category-haute-disponibilite","category-innodb-cluster","category-mysql","category-tuto","tag-group-replication","tag-haute-disponibilite","tag-innodb","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-uT","jetpack-related-posts":[{"id":3291,"url":"https:\/\/dasini.net\/blog\/2019\/07\/16\/deployer-1-cluster-mysql-avec-2-commandes\/","url_meta":{"origin":1915,"position":0},"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 "Group Replication"","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":1855,"url":"https:\/\/dasini.net\/blog\/2017\/04\/10\/adopte-un-cluster-mysql-group-replication\/","url_meta":{"origin":1915,"position":1},"title":"Adopte un… 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 "Group Replication"","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"MySQL InnoDB Cluster Overview","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_InnoDB_cluster_overview.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1832,"url":"https:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/","url_meta":{"origin":1915,"position":2},"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 "Group Replication"","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":1800,"url":"https:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/","url_meta":{"origin":1915,"position":3},"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 "Group Replication"","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"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":1915,"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 "Group Replication"","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":4381,"url":"https:\/\/dasini.net\/blog\/2021\/02\/24\/webinar-la-haute-disponibilite-avec-mysql\/","url_meta":{"origin":1915,"position":5},"title":"Webinar \u2013 La haute disponibilit\u00e9 avec MySQL","author":"Olivier DASINI","date":"24 f\u00e9vrier 2021","format":false,"excerpt":"MySQL propose plusieurs solutions pour vous aider \u00e0 b\u00e2tir une architecture hautement disponible. On retrouve InnoDB Cluster avec Group Replication, la r\u00e9plication asynchrone en utilisant InnoDB ReplicaSet ou encore la r\u00e9plication semi-synchrone, et enfin NDB Cluster. Dans cette session, nous examinerons ces diff\u00e9rents sc\u00e9narios, les \u00e9cueils \u00e0 \u00e9viter et les\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1915","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=1915"}],"version-history":[{"count":26,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1915\/revisions"}],"predecessor-version":[{"id":2626,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1915\/revisions\/2626"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1915"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1915"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1915"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}