{"id":1678,"date":"2016-11-08T10:45:38","date_gmt":"2016-11-08T09:45:38","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1678"},"modified":"2024-12-10T15:48:48","modified_gmt":"2024-12-10T14:48:48","slug":"deployer-un-cluster-mysql-group-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2016\/11\/08\/deployer-un-cluster-mysql-group-replication\/","title":{"rendered":"D\u00e9ployer un cluster MySQL Group Replication"},"content":{"rendered":"<p><em>Mise-\u00e0-jour: 9 janvier 2018<\/em><\/p>\n<p>Historiquement, les solutions de haute disponibilit\u00e9 (HA) avec MySQL tournent autour de la fonctionnalit\u00e9 native\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication.html\" target=\"_blank\" rel=\"noopener\">MySQL Replication<\/a>: replication asynchrone ou <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-semisync.html\" target=\"_blank\" rel=\"noopener\">semi-synchrone<\/a>. Ces modes de \u00a0replication sont tr\u00e8s largement utilis\u00e9s pour le besoins critiques d&rsquo;enterprises \u00ab\u00a0at scale\u00a0\u00bb comme <a href=\"https:\/\/fr-fr.facebook.com\/MySQLatFacebook\/\" target=\"_blank\" rel=\"noopener\">Facebook<\/a>, <a href=\"https:\/\/blog.twitter.com\/search\/node\/mysql%20term%3A149%20type%3Ablog%20language%3Aen\" target=\"_blank\" rel=\"noopener\">Twitter<\/a>, <a href=\"http:\/\/blog.booking.com\/category\/operations.html\" target=\"_blank\" rel=\"noopener\">Booking.com<\/a>, <a href=\"https:\/\/eng.uber.com\/mysql-migration\/\" target=\"_blank\" rel=\"noopener\">Uber<\/a>&#8230;<\/p>\n<p>Aujourd&rsquo;hui MySQL propose une nouvelle fonctionnalit\u00e9 native de haute disponibilit\u00e9 :<\/p>\n<p><strong>MySQL Group Replication<\/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\/Group_Replication_3_nodes.png?resize=286%2C259\" alt=\"\" width=\"286\" height=\"259\" \/><\/p>\n<h1>MySQL Group Replication<\/h1>\n<p>MySQL Group Replication est un plugin pour MySQL 5.7+ qui fournit nativement de la r\u00e9plication virtuellement synchrone avec d\u00e9tection\/r\u00e9solution de conflit et coh\u00e9rence garantie. Ce plugin est disponible sur tous les syst\u00e8mes d&rsquo;exploitation support\u00e9s par MySQL (Linux, Windows, Solaris, OSX, \u2026)<\/p>\n<p>C&rsquo;est la fonctionnalit\u00e9 id\u00e9ale pour les architectures multi-ma\u00eetres. Il n&rsquo;est alors plus n\u00e9cessaire de g\u00e9rer le failover de la base de donn\u00e9es (\u00e0 l&rsquo;aide d&rsquo;outils comme:\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-utilities\/1.5\/en\/mysqlfailover.html\" target=\"_blank\" rel=\"noopener\">mysqlfailover<\/a>; <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-utilities\/1.5\/en\/mysqlrpladmin.html\" target=\"_blank\" rel=\"noopener\">mysqlrpladmin<\/a>; <a href=\"https:\/\/code.google.com\/p\/mysql-master-ha\/wiki\/Installation\" target=\"_blank\" rel=\"noopener\">MHA<\/a>).<\/p>\n<p><span style=\"text-decoration: line-through;\">A noter que le plugin MySQL Group replication est en ce moment en Release Candidate (RC) ce qui veut dire qu&rsquo;il n&rsquo;est\u00a0pas encore conseill\u00e9 pour la production. \u00a0C&rsquo;est n\u00e9anmoins le bon moment pour le tester (binaires t\u00e9l\u00e9chargeable <a href=\"http:\/\/labs.mysql.com\/\" target=\"_blank\" rel=\"noopener\">ici<\/a>). Le statut RC est\u00a0la derni\u00e8re\u00a0\u00e9tape avant la GA, et selon mon petit doigt ce n&rsquo;est plus qu&rsquo;une question de semaines avant la sortie officielle en GA (qui a dit MySQL 5.7.17 ???).<\/span><\/p>\n<p>Le plugin MySQL Group Replication est GA et embarqu\u00e9 dans MySQL \u00e0 partir de la version 5.7.17. Pour t\u00e9l\u00e9charger MySQL rendez-vous sur la page : <a href=\"http:\/\/www.mysql.com\/downloads\/\" target=\"_blank\" rel=\"noopener\">http:\/\/www.mysql.com\/downloads\/\u00a0<\/a><\/p>\n<h1>D\u00e9ployer un cluster de 3 n\u0153uds<\/h1>\n<p><del>La version du plugin lors de l&rsquo;\u00e9criture de cet article est <strong>0.9<\/strong> disponible avec MySQL <strong>5.7.15<\/strong> dans le lab de MySQL : <a href=\"http:\/\/labs.mysql.com\/\" target=\"_blank\" rel=\"noopener\">http:\/\/labs.mysql.com\/<\/a><\/del><br \/>\n<del>Cette article pr\u00e9sume que MySQL 5.7.15 est d\u00e9ja install\u00e9 (<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/installing.html\" target=\"_blank\" rel=\"noopener\">installer MySQL<\/a>).<\/del><\/p>\n<p>Article mis-\u00e0-jour avec la version <strong>1.0<\/strong>\u00a0du plugin, qui est la version GA embarqu\u00e9e dans MySQL <strong>5.7.17<\/strong>.<\/p>\n<p>Cette article pr\u00e9sume qu&rsquo;une version de MySQL \u00e9gale ou sup\u00e9rieure \u00e0 5.7.17 est d\u00e9ja install\u00e9 (<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/installing.html\" target=\"_blank\" rel=\"noopener\">installer MySQL<\/a>).<\/p>\n<p><span style=\"text-decoration: underline;\">Caract\u00e9ristiques<\/span><\/p>\n<ul>\n<li>Version du serveur : 5.7.17<\/li>\n<li>Version du plugin : 1.0<\/li>\n<li>N\u0153ud 1 : 192.168.1.11 : 14115<\/li>\n<li>N\u0153ud 2 : 192.168.1.9 : 3301<\/li>\n<li>N\u0153ud 3 : 192.168.1.48 : 5709<\/li>\n<\/ul>\n<h2>Configurer les instances\u00a0MySQL<\/h2>\n<p>Comme toutes technologies, il y a des contraintes et quelques\u00a0limitations. MySQL Group Replication n\u00e9cessite :<\/p>\n<ul>\n<li>MySQL 5.7<\/li>\n<li>Tables avec le moteur de stockage <strong>InnoDB<\/strong><\/li>\n<li>Tables avec une <strong>cl\u00e9 primaire<\/strong><\/li>\n<li>Seul le protocole <strong>IPV4<\/strong> est support\u00e9<\/li>\n<li>Les DDL en parall\u00e8le de DDL\/DML ex\u00e9cut\u00e9s par diff\u00e9rents n\u0153uds sur le m\u00eame objet sont proscrits.<\/li>\n<\/ul>\n<p><span style=\"text-decoration: underline;\">Liste compl\u00e8te<\/span> : <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication-requirements-and-limitations.html\" target=\"_blank\" rel=\"noopener\">Requirements and Limitations<\/a><\/p>\n<p>Au niveau configuration des serveurs il faut:<\/p>\n<ul>\n<li>Activer le log binaire\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-binary-log.html#sysvar_log_bin\" target=\"_blank\" rel=\"noopener\"><strong>log_bin<\/strong><\/a><\/li>\n<\/ul>\n<\/li>\n<li>Le log binaire doit \u00eatre au format ROW :\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-binary-log.html#sysvar_binlog_format\" target=\"_blank\" rel=\"noopener\"><strong>binlog_format = ROW<\/strong><\/a><\/li>\n<\/ul>\n<\/li>\n<li>Journaliser les mise \u00e0 jours envoy\u00e9es\u00a0par le primaire\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-binary-log.html#sysvar_log_slave_updates\" target=\"_blank\" rel=\"noopener\"><strong>log_slave_updates = ON<\/strong><\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Activer le mode GTID\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-gtids.html#sysvar_gtid_mode\" target=\"_blank\" rel=\"noopener\"><strong>gtid_mode = ON<\/strong><\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-gtids.html#sysvar_enforce_gtid_consistency\" target=\"_blank\" rel=\"noopener\"><strong>enforce_gtid_consistency = ON<\/strong><\/a><\/li>\n<\/ul>\n<\/li>\n<li>master_info_repository et relay_log_info_repository doivent avoir la valeur TABLE\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-slave.html#sysvar_master_info_repository\" target=\"_blank\" rel=\"noopener\"><strong>master_info_repository = TABLE<\/strong><\/a><\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-slave.html#sysvar_relay_log_info_repository\" target=\"_blank\" rel=\"noopener\"><strong>relay_log_info_repository = TABLE<\/strong><\/a><\/li>\n<\/ul>\n<\/li>\n<li>D\u00e9sactiver le calcul de la somme de contr\u00f4le des \u00e9v\u00e9nements de r\u00e9plication\n<ul>\n<li><strong><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options-binary-log.html#option_mysqld_binlog-checksum\" target=\"_blank\" rel=\"noopener\">binlog-checksum = NONE<\/a><\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Il faut \u00e9galement avoir une valeur de la variable <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-options.html#option_mysqld_server-id\" target=\"_blank\" rel=\"noopener\"><strong>server_id<\/strong><\/a> diff\u00e9rente pour chacun des 3 n\u0153uds:<\/p>\n<ul>\n<li class=\"lang:ini decode:true \">N\u0153ud 1 : server_id=11<\/li>\n<li class=\"lang:ini decode:true \">N\u0153ud 2 : server_id=9<\/li>\n<li class=\"lang:ini decode:true \">N\u0153ud 3 : server_id=48<\/li>\n<\/ul>\n<p>Pour synth\u00e9tiser, dans le fichiers de configuration (<strong>my.cnf<\/strong> \/ <strong>my.ini<\/strong>) du n\u0153ud 1, il faut avoir (en plus des infos classiques):<\/p>\n<pre class=\"toolbar-overlay:false lang:ini decode:true\">server_id=11\ngtid_mode=ON\nenforce_gtid_consistency=ON\nmaster_info_repository=TABLE\nrelay_log_info_repository=TABLE\nbinlog_checksum=NONE\nlog_slave_updates=ON\nlog_bin\nbinlog_format=ROW<\/pre>\n<p>Pareil pour les 2 autres, \u00e0 la valeur de <em>server_id<\/em> pr\u00e8s.<\/p>\n<h3>Configuration sp\u00e9cifique \u00e0 MySQL Group Replication<\/h3>\n<p>Le cluster doit avoir un identifiant unique au format <a href=\"https:\/\/fr.wikipedia.org\/wiki\/Universal_Unique_Identifier\" target=\"_blank\" rel=\"noopener\">UUID<\/a>:<\/p>\n<ul>\n<li>le UUID doit \u00eatre valide<\/li>\n<li>il doit \u00eatre d\u00e9finit sur chacune des machines du groupe<\/li>\n<\/ul>\n<p>La fonction MySQL <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/miscellaneous-functions.html#function_uuid\" target=\"_blank\" rel=\"noopener\">UUID()<\/a> permet de g\u00e9n\u00e9rer un&#8230; uuid:<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSELECT UUID();\n+--------------------------------------+\n| UUID()                               |\n+--------------------------------------+\n| 78c1a27c-9dde-11e6-865d-dc53609b8b8d |\n+--------------------------------------+<\/pre>\n<ul>\n<li>Activer la journalisation des donn\u00e9es modifi\u00e9es par les transactions\n<ul>\n<li><strong><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_transaction_write_set_extraction\" target=\"_blank\" rel=\"noopener\">transaction_write_set_extraction = XXHASH64<\/a><\/strong><\/li>\n<\/ul>\n<\/li>\n<li>Charger le plugin au d\u00e9marrage et l\u2019emp\u00eacher d&rsquo;\u00eatre d\u00e9sinstall\u00e9 pendant l&rsquo;ex\u00e9cution\n<ul>\n<li class=\"lang:ini decode:true \">group_replication = FORCE_PLUS_PERMANENT<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Charger le pluging Group Replication au d\u00e9marrage du serveur\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-options.html#option_mysqld_plugin-load\" target=\"_blank\" rel=\"noopener\">plugin_load<\/a> = group_replication.so<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Donner un nom au cluster au format UUID\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/mysqlhighavailability.com\/gr\/doc\/configs_ref.html?highlight=group_replication_group_name#group-replication-group-name\" target=\"_blank\" rel=\"noopener\">group_replication_group_name<\/a> = \u00ab\u00a078c1a27c-9dde-11e6-865d-dc53609b8b8d\u00a0\u00bb<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Ne pas d\u00e9marrer automatiquement Group Replication au d\u00e9marrage de l&rsquo;instance (au choix)\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/mysqlhighavailability.com\/gr\/doc\/configs_ref.html?highlight=group_replication_group_name#group-replication-start-on-boot\" target=\"_blank\" rel=\"noopener\">group_replication_start_on_boot<\/a> = OFF<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Sert \u00e0 d\u00e9marrer le cluster. Doit \u00eatre \u00e0 OFF (valeur par d\u00e9faut)\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/mysqlhighavailability.com\/gr\/doc\/configs_ref.html?highlight=group_replication_group_name#group-replication-bootstrap-group\" target=\"_blank\" rel=\"noopener\">group_replication_bootstrap_group<\/a> = OFF<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Permet de choisir la\u00a0configuration mono-ma\u00eetre (au lieu de multi-ma\u00eetre)\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/mysqlhighavailability.com\/gr\/doc\/configs_ref.html?highlight=group_replication_group_name#group-replication-single-primary-mode\" target=\"_blank\" rel=\"noopener\">group_replication_single_primary_mode<\/a> = ON<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Adresse (locale) du n\u0153ud\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/mysqlhighavailability.com\/gr\/doc\/configs_ref.html?highlight=group_replication_group_name#group-replication-local-address\" target=\"_blank\" rel=\"noopener\">group_replication_local_address<\/a> = \u00ab\u00a0hostA:port\u00a0\u00bb<\/li>\n<\/ul>\n<\/li>\n<li class=\"lang:ini decode:true \">Liste d&rsquo;adresses des pairs. Utile lorsqu&rsquo;un nouveau noeud cherche \u00e0 se mettre \u00e0 jour\n<ul>\n<li class=\"lang:ini decode:true \"><a href=\"http:\/\/mysqlhighavailability.com\/gr\/doc\/configs_ref.html?highlight=group_replication_group_name#group-replication-group-seeds\" target=\"_blank\" rel=\"noopener\">group_replication_group_seeds<\/a> = \u00ab\u00a0hostB:port,hostC:port\u00a0\u00bb<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><em>Note.<\/em><\/p>\n<p><em>Pour avoir une architecture multi-master ie pouvoir \u00e9crire sur toutes les intances \u00e0 la fois il faut group_replication_single_primary_mode = OFF sur tous les\u00a0n\u0153uds<\/em><\/p>\n<h3>Activation\u00a0du plugin MySQL Group Replication<\/h3>\n<p>A partir de la version 5.7.17, \u00a0MySQL embarque le plugin Group Replication qui se nomme :\u00a0<strong>group_replication.so<\/strong>.<\/p>\n<p>Le plugin n&rsquo;est \u00e9videmment pas activ\u00e9 par d\u00e9faut, cependant vous avez 2 solutions pour l&rsquo;activer :<\/p>\n<ul>\n<li>dans le fichier de configuration comme indiqu\u00e9 pr\u00e9c\u00e9demment (n\u00e9cessite un red\u00e9marrage de l&rsquo;instance MySQL)<\/li>\n<\/ul>\n<pre class=\"lang:ini decode:true\">plugin_load = group_replication.so\n<\/pre>\n<ul>\n<li>\u00e0 la vol\u00e9e, avec la commande INSTALL PLUGIN si l&rsquo;instance est correctement configur\u00e9e<\/li>\n<\/ul>\n<pre class=\"lang:mysql decode:true\">INSTALL PLUGIN group_replication SONAME 'group_replication.so';\n<\/pre>\n<p>Un fois activ\u00e9, les informations relatives au plugin sont disponible avec (\u00e0 faire sur tout les n\u0153uds) :<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\" title=\"Info plugin\">node[1|2|3]&gt; SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS \n             FROM information_schema.PLUGINS \n             WHERE PLUGIN_NAME = 'group_replication';\n+-------------------+----------------+---------------+\n| PLUGIN_NAME       | PLUGIN_VERSION | PLUGIN_STATUS |\n+-------------------+----------------+---------------+\n| group_replication | 1.0            | ACTIVE        |\n+-------------------+----------------+---------------+<\/pre>\n<p>Le fichier\u00a0<strong>group_replication.so<\/strong> se trouve dans le\u00a0r\u00e9pertoire \u00e0 plugin &#8211; <strong>plugin_dir<\/strong>:<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">mysql&gt; SHOW VARIABLES LIKE 'plugin_dir';\n+---------------+------------------------------+\n| Variable_name | Value                        |\n+---------------+------------------------------+\n| plugin_dir    | \/usr\/local\/mysql\/lib\/plugin\/ |\n+---------------+------------------------------+<\/pre>\n<p>\/usr\/local\/mysql\/lib\/plugin\/ pour\u00a0ma configuration.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">mysql&gt; \\! ls -l \/usr\/local\/mysql\/lib\/plugin\/group_replication.so\n-rwxr-xr-x 1 root mysql 15872361 Nov 28 17:45 \/usr\/local\/mysql\/lib\/plugin\/group_replication.so<\/pre>\n<p>Synth\u00e9tisons encore!<\/p>\n<p>Dans la section\u00a0<strong>[mysqld] <\/strong>du fichier de configuration (<strong>my.cnf<\/strong> \/ <strong>my.ini<\/strong>) du n\u0153ud 1, il faut ajouter:<\/p>\n<pre class=\"toolbar-overlay:false lang:ini decode:true\">## Group Replication specific options\nplugin_load=group_replication.so\ngroup_replication=FORCE_PLUS_PERMANENT\ntransaction_write_set_extraction=XXHASH64\ngroup_replication_group_name=\"78c1a27c-9dde-11e6-865d-dc53609b8b8d\"\ngroup_replication_start_on_boot=OFF\ngroup_replication_bootstrap_group=OFF\ngroup_replication_single_primary_mode=ON\ngroup_replication_local_address=\"192.168.1.11:4406\"\ngroup_replication_group_seeds=\"192.168.1.9:4406,192.168.1.48:4406\"\n<\/pre>\n<p>Et donc pour les 2 n\u0153uds restant, il faut ajuster:<\/p>\n<p>N\u0153ud 2 :<\/p>\n<pre class=\"toolbar-overlay:false lang:ini decode:true\">group_replication_local_address=\"192.168.1.9:4406\"\ngroup_replication_group_seeds=\"192.168.1.11:4406,192.168.1.48:4406\"\n<\/pre>\n<p>N\u0153ud 3 :<\/p>\n<pre class=\"toolbar-overlay:false lang:ini decode:true\">group_replication_local_address=\"192.168.1.48:4406\"\ngroup_replication_group_seeds=\"192.168.1.11:4406,192.168.1.9:4406\"\n<\/pre>\n<p><em><span style=\"text-decoration: underline;\">Note<\/span>.<\/em><\/p>\n<p><em>Mon environnement \u00e9tant \u00ab\u00a0exotique\u00a0\u00bb j&rsquo;ai du configurer les 2 variables qui suivent, pour que le process de restauration fonctionne.\u00a0<\/em><\/p>\n<ul>\n<li><em>N\u0153ud 1 : report_port = 14115 \/ report_host = \u00ab\u00a0192.168.1.11\u00a0\u00bb<\/em><\/li>\n<li><em>N\u0153ud 2 : report_port = 3301 \/ report_host = \u00ab\u00a0192.168.1.9\u00a0\u00bb<\/em><\/li>\n<li><em>N\u0153ud 3 : report_port = 5709 \/ report_host = \u00ab\u00a0192.168.1.48\u00a0\u00bb<\/em><\/li>\n<\/ul>\n<p><em>Cependant il est plus que recommand\u00e9 de <strong>configurer correctement nom d&rsquo;h\u00f4te (\/etc\/hosts; \/etc\/hostname;) <\/strong>\u00a0et<strong> DNS<\/strong>.<\/em><\/p>\n<p>Optionnel : Pour emp\u00eacher l&rsquo;utilisation (malencontreuse) d&rsquo;autres moteurs de stockage autre qu&rsquo;InnoDB (non-transactionnel donc), il peut \u00eatre utile d&rsquo;ajouter \u00e0 la conf:<\/p>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_disabled_storage_engines\">disabled_storage_engines<\/a>=\u00a0\u00bbMyISAM,BLACKHOLE,FEDERATED,ARCHIVE\u00a0\u00bb<\/li>\n<\/ul>\n<p>Le fichier de configuration \u00e9tant modifi\u00e9, il faut red\u00e9marrer les instances MySQL pour que la nouvelle configuration prenne effet.<\/p>\n<p>A noter quand m\u00eame que la plupart des variables relatives \u00e0 Group Replication\u00a0 sont changeable \u00e0 chaud avec la commande SET GLOBAL &lt;commande&gt;.<\/p>\n<h3>Utilisateur de restauration<\/h3>\n<p>Cet utilisateur est n\u00e9cessaire pour le processus de r\u00e9cup\u00e9ration automatique (recovery) lorsqu&rsquo;un serveur (r)entre dans le groupe.<\/p>\n<p>A cr\u00e9er et \u00e0 configurer sur tout les n\u0153uds du groupe. Il permettra d&rsquo;\u00e9tablir une connexion replica\/source entre les membres du groupe en cas de process de recovery.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node[1|2|3]&gt;\n-- Create recovery user\nSET sql_log_bin=0;\n\nCREATE USER gr_user@'%' IDENTIFIED BY 'Mdp5uperS3cr&amp;t';\nGRANT REPLICATION SLAVE ON *.* TO gr_user@'%';\nFLUSH PRIVILEGES;\n\n-- Create recovery channel\nCHANGE MASTER TO MASTER_USER='gr_user', MASTER_PASSWORD='Mdp5uperS3cr&amp;t' FOR CHANNEL 'group_replication_recovery';\n\nSET sql_log_bin=1;<\/pre>\n<p>A partir de MySQL <strong>5.7.19<\/strong> il est recommand\u00e9 de configurer les membres du cluster avec la variable <strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-system-variables.html#sysvar_super_read_only\" target=\"_blank\" rel=\"noopener\">super_read_only<\/a> = 1<\/strong>. Une fois Group Replication d\u00e9marr\u00e9, il va ajuster la variable comme il le faut en fonction des \u00e9tats des n\u0153uds (e.g. d\u00e9sactiv\u00e9 pour le(s) primaire(s)).<\/p>\n<p>Plus d&rsquo;info <a href=\"https:\/\/mysqlhighavailability.com\/protecting-your-data-fail-safe-enhancements-to-group-replication\/\" target=\"_blank\" rel=\"noopener\">ici<\/a>.<\/p>\n<p>Pour une version de MySQL 5.7.19+ donc, dans la section <strong>[mysqld]<\/strong> des fichiers de configuration (<strong>my.cnf<\/strong> \/ <strong>my.ini<\/strong>) des n\u0153uds 1, 2 et 3 il faut alors ajouter:<\/p>\n<pre class=\"toolbar-overlay:false lang:ini decode:true\">super_read_only = 1\n<\/pre>\n<p>On est pas mal l\u00e0!<\/p>\n<p>Avant d&rsquo;aller plus loin, une bonne habitude est de v\u00e9rifier\u00a0que les serveurs sont correctement configur\u00e9s.<\/p>\n<h2>V\u00e9rification de la configuration<\/h2>\n<p><span style=\"text-decoration: underline;\">Configuration serveur<\/span><\/p>\n<p>La commande SHOW GLOBAL VARIABLES permet de voir la valeur des variables serveur.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSHOW GLOBAL VARIABLES WHERE Variable_name IN \n('server_id', 'log_bin', 'binlog_format', 'gtid_mode', 'enforce_gtid_consistency', 'log_slave_updates', 'master_info_repository', 'relay_log_info_repository','transaction_write_set_extraction', 'binlog_checksum');\n+----------------------------------+----------+\n| Variable_name                    | Value    |\n+----------------------------------+----------+\n| binlog_checksum                  | NONE     |\n| binlog_format                    | ROW      |\n| enforce_gtid_consistency         | ON       |\n| gtid_mode                        | ON       |\n| log_bin                          | ON       |\n| log_slave_updates                | ON       |\n| master_info_repository           | TABLE    |\n| relay_log_info_repository        | TABLE    |\n| server_id                        | 11       |\n| transaction_write_set_extraction | XXHASH64 |\n+----------------------------------+----------+<\/pre>\n<p><span style=\"text-decoration: underline;\">Configuration restauration<\/span><\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node[1|2|3]&gt;\nSELECT user_name, host, user_password\nFROM mysql.slave_master_info\nWHERE channel_name = 'group_replication_recovery';\n+-----------+--------+----------------+\n| user_name | host   | user_password  |\n+-----------+--------+----------------+\n| gr_user   | &lt;NULL&gt; | Mdp5uperS3cr&amp;t |\n+-----------+--------+----------------+\n\nSHOW GRANTS FOR gr_user@'%';\n+-------------------------------------------------+\n| Grants for gr_user@%                            |\n+-------------------------------------------------+\n| GRANT REPLICATION SLAVE ON *.* TO 'gr_user'@'%' |\n+-------------------------------------------------+<\/pre>\n<p><span style=\"text-decoration: underline;\">Configuration Group Replication<\/span><\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true \"> node1&gt;\nSHOW GLOBAL VARIABLES WHERE Variable_name IN \n('group_replication_bootstrap_group', 'group_replication_group_name', 'group_replication_group_seeds', 'group_replication_local_address','group_replication_start_on_boot', 'group_replication_single_primary_mode', 'server_uuid', 'super_read_only');\n+---------------------------------------+--------------------------------------+\n| Variable_name                         | Value                                |\n+---------------------------------------+--------------------------------------+\n| group_replication_bootstrap_group     | OFF                                  |\n| group_replication_group_name          | 78c1a27c-9dde-11e6-865d-dc53609b8b8d |\n| group_replication_group_seeds         | 192.168.1.9:4406,192.168.1.48:4406   |\n| group_replication_local_address       | 192.168.1.11:4406                    |\n| group_replication_single_primary_mode | ON                                   |\n| group_replication_start_on_boot       | OFF                                  |\n| server_uuid                           | dc12fd1a-83ff-11e5-91af-002710b3d914 |\n| super_read_only                       | ON                                   |\n+---------------------------------------+--------------------------------------+<\/pre>\n<p>Cela correspond \u00e0 ce qui a \u00e9t\u00e9 renseign\u00e9 un peu plus haut.<\/p>\n<p>Je vous conseille de toujours v\u00e9rifier les\u00a0configurations, sur tout les n\u0153uds. Ca permet de gagner du temps et de sauvegarder l&rsquo;\u00e9nergie par la suite&#8230; <em>\u00ab\u00a0Certified wise\u00a0DBA\u00a0\u00bb<\/em><\/p>\n<h1>D\u00e9ployer le cluster<\/h1>\n<p>Le moment tant attendu arrive&#8230;<\/p>\n<p>Pour la suite de cet article, je pr\u00e9sume que les instances ont les m\u00eames donn\u00e9es, donc le cas pr\u00e9sent, pas de donn\u00e9es.<\/p>\n<p>Dans le cas contraire il suffit simplement de faire une sauvegarde compl\u00e8te d&rsquo;une des instances et de la restaurer sur les 2 autres (<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/backup-and-recovery.html\" target=\"_blank\" rel=\"noopener\">Backup and Recovery<\/a>).<\/p>\n<h2>Amorcer le cluster<\/h2>\n<p>L&rsquo;amor\u00e7age (bootstrap) consiste \u00e0 cr\u00e9er un groupe d&rsquo;un seul n\u0153ud (faut bien commencer !). Ce dernier va par la suite \u00eatre en mesure de recevoir d&rsquo;autres membres. La proc\u00e9dure de bootstrap ne doit donc se faire que sur <strong>un seul membre<\/strong>, le tout premier n\u0153ud du groupe.<\/p>\n<p>Le n\u0153ud 1 est donc d\u00e9sign\u00e9 volontaire pour l&rsquo;amor\u00e7age.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSET GLOBAL group_replication_bootstrap_group = ON;\n\nSTART GROUP_REPLICATION;\n\nSET GLOBAL group_replication_bootstrap_group = OFF;<\/pre>\n<p>Revoyons la sc\u00e8ne au ralenti&#8230;<\/p>\n<ul>\n<li>j&rsquo;initie : group_replication_bootstrap_group = ON<\/li>\n<li>je d\u00e9marre : START GROUP_REPLICATION<\/li>\n<li>je termine l&rsquo;initialisation : group_replication_bootstrap_group = OFF<\/li>\n<\/ul>\n<p>C&rsquo;est vraiment important d&rsquo;ex\u00e9cuter group_replication_bootstrap_group = ON seulement sur le 1er n\u0153ud du cluster, sous peine de se retrouver avec des groupes ind\u00e9pendant (<a href=\"https:\/\/fr.wikipedia.org\/wiki\/Split-brain\" target=\"_blank\" rel=\"noopener\">split-brain<\/a> artificiel).<\/p>\n<p>R\u00e9cup\u00e9rer des infos sur le cluster c&rsquo;est facile, avec les tables:<\/p>\n<ul>\n<li>performance_schema.<strong>replication_group_members<\/strong><\/li>\n<li>performance_schema.<strong>replication_connection_status<\/strong><\/li>\n<\/ul>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014115-1111-1111-1111-111111111111\nMEMBER_HOST:192.168.1.11\nMEMBER_PORT: 14115 \nMEMBER_STATE: ONLINE<\/pre>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSELECT * FROM performance_schema.replication_connection_status\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nGROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d\nSOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d\nTHREAD_ID: NULL\nSERVICE_STATE: ON\nCOUNT_RECEIVED_HEARTBEATS: 0\nLAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00\nRECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:1\nLAST_ERROR_NUMBER: 0\nLAST_ERROR_MESSAGE:\nLAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_recovery\nGROUP_NAME:\nSOURCE_UUID:\nTHREAD_ID: NULL\nSERVICE_STATE: OFF\nCOUNT_RECEIVED_HEARTBEATS: 0\nLAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00\nRECEIVED_TRANSACTION_SET:\nLAST_ERROR_NUMBER: 0\nLAST_ERROR_MESSAGE:\nLAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00<\/pre>\n<p>Le groupe contient bien un seul membre, 192.168.1.11 aka n\u0153ud 1 et il est ONLINE.<\/p>\n<p>Jusqu&rsquo;ici tout va bien!<\/p>\n<h2>Ajouter des n\u0153uds<\/h2>\n<p>Un cluster compos\u00e9 d&rsquo;un seul membre, c&rsquo;est un bon d\u00e9but. Mais ce n&rsquo;est \u00e9videmment pas suffisant pour avoir de la haute disponibilit\u00e9 avec notre base de donn\u00e9es MySQL<\/p>\n<h3>Ajout du n\u0153ud 2<\/h3>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node2&gt;\nSTART GROUP_REPLICATION;<\/pre>\n<p>Trop facile !!!<\/p>\n<p>La supervision montre que le cluster est donc maintenant compos\u00e9 de 2 membres, ONLINE!<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node2&gt;\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014115-1111-1111-1111-111111111111\nMEMBER_HOST: 192.168.1.11\nMEMBER_PORT: 14115\nMEMBER_STATE: ONLINE\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6\nMEMBER_HOST: 192.168.1.9\nMEMBER_PORT: 3301\nMEMBER_STATE: ONLINE<\/pre>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node2&gt;\nSELECT * FROM performance_schema.replication_connection_status\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nGROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d\nSOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d\nTHREAD_ID: NULL\nSERVICE_STATE: ON\nCOUNT_RECEIVED_HEARTBEATS: 0\nLAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00\nRECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:1-2:5:7\nLAST_ERROR_NUMBER: 0\nLAST_ERROR_MESSAGE:\nLAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_recovery\nGROUP_NAME:\nSOURCE_UUID:\nTHREAD_ID: NULL\nSERVICE_STATE: OFF\nCOUNT_RECEIVED_HEARTBEATS: 0\nLAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00\nRECEIVED_TRANSACTION_SET:\nLAST_ERROR_NUMBER: 0\nLAST_ERROR_MESSAGE:\nLAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00<\/pre>\n<p>On ne vas pas s\u2019arr\u00eater en si bon chemin<\/p>\n<h3>Ajout du n\u0153ud 3<\/h3>\n<p>Cap ou pas cap ?<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node3&gt;\nSTART GROUP_REPLICATION;<\/pre>\n<p>Le process est \u00e9videmment le m\u00eame.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node3&gt;\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014115-1111-1111-1111-111111111111\nMEMBER_HOST: 192.168.1.11\nMEMBER_PORT: 14115\nMEMBER_STATE: ONLINE\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6\nMEMBER_HOST: 192.168.1.9\nMEMBER_PORT: 3301\nMEMBER_STATE: ONLINE\n*************************** 3. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914\nMEMBER_HOST: 192.168.1.48\nMEMBER_PORT: 5709\nMEMBER_STATE: ONLINE<\/pre>\n<p>Les 3 n\u0153uds sont bien actifs et fonctionnels.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node3&gt;\nSELECT * FROM performance_schema.replication_connection_status\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nGROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d\nSOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d\nTHREAD_ID: NULL\nSERVICE_STATE: ON\nCOUNT_RECEIVED_HEARTBEATS: 0\nLAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00\nRECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:4-5:8\nLAST_ERROR_NUMBER: 0\nLAST_ERROR_MESSAGE:\nLAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_recovery\nGROUP_NAME:\nSOURCE_UUID:\nTHREAD_ID: NULL\nSERVICE_STATE: OFF\nCOUNT_RECEIVED_HEARTBEATS: 0\nLAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00\nRECEIVED_TRANSACTION_SET:\nLAST_ERROR_NUMBER: 0\nLAST_ERROR_MESSAGE:\nLAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00<\/pre>\n<p>Alors ? Heureux ?<\/p>\n<p>Il est possible d&rsquo;avoir jusqu&rsquo;\u00e0 <strong>9 membres<\/strong>, donc tu peux y aller Marcel!!!<\/p>\n<h2>Identifier le n\u0153ud primaire<\/h2>\n<p><span style=\"text-decoration: underline;\">Rapide r\u00e9cap<\/span><\/p>\n<p>Je\u00a0viens de cr\u00e9er un cluster de 3 n\u0153uds en utilisant le plugin HA natif MySQL Group Replication.<\/p>\n<p>Sur ces 3 n\u0153uds, 2 sont en mode lecture seule (mode: <strong>super_read_only<\/strong>) et le troisi\u00e8me en mode lecture\/\u00e9criture, c&rsquo;est le n\u0153ud primaire.<\/p>\n<p>Un int\u00e9r\u00eat de cette architecture HA est qu&rsquo;elle est finalement tr\u00e8s proche d&rsquo;une architecture de r\u00e9plication classique master\/slaves. La grosse diff\u00e9rence, et c&rsquo;est ce qui fait sa puissance, est qu&rsquo;avec MySQL Group Replication, il n&rsquo;est plus utile de g\u00e9rer le <em>failover<\/em> base de donn\u00e9es. Est \u00e7a, ce n&rsquo;est pas rien !<\/p>\n<p>L&rsquo;information qui permet de savoir quel n\u0153ud est primaire est disponible dans la table performance_schema.<strong>global_status<\/strong> :<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">mysql&gt;\nSELECT * \nFROM performance_schema.global_status \nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nVARIABLE_NAME: group_replication_primary_member\nVARIABLE_VALUE: 00014115-1111-1111-1111-111111111111<\/pre>\n<p>En la joignant avec la table performance_schema.<strong>replication_group_members<\/strong> ont obtient un peu plus d&rsquo;infos:<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">mysql&gt;\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE\nFROM performance_schema.replication_group_members\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nMEMBER_ID: 00014115-1111-1111-1111-111111111111\nMEMBER_HOST: 192.168.1.11\nMEMBER_STATE: ONLINE<\/pre>\n<p>Dons cette architecture, le n\u0153ud 00014115-1111-1111-1111-111111111111 aka\u00a0192.168.1.11 aka\u00a0n\u0153ud 1 est le primaire.<\/p>\n<p><em>Note.<\/em><\/p>\n<p><em>En mode multi-ma\u00eetre, ils sont tous primaire<\/em><\/p>\n<p>Le corollaire imm\u00e9diat de cette information est que dans cette configuration un seul n\u0153ud accepte les \u00e9critures.\u00a0Cependant il est possible de lire sur tous les n\u0153uds.<br \/>\n<span style=\"text-decoration: underline;\">Sur le n\u0153ud 2 -n\u0153ud NON primaire<\/span><\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node2&gt;\nSHOW SCHEMAS;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n4 rows in set (0,00 sec)\n\nCREATE SCHEMA gr_test;\nERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement\n\nSHOW SCHEMAS;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n4 rows in set (0,00 sec)<\/pre>\n<p>Comme pr\u00e9vu les \u00e9critures sont impossibles.<\/p>\n<p><span style=\"text-decoration: underline;\">Sur le n\u0153ud 1 &#8211; le n\u0153ud primaire<\/span><\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSHOW SCHEMAS;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n4 rows in set (0,00 sec)\n\nCREATE SCHEMA gr_test;\nQuery OK, 1 row affected (0,00 sec)\n\nSHOW SCHEMAS;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| gr_test            |\n| mysql              |\n| performance_schema |\n| sys                |\n+--------------------+\n5 rows in set (0,00 sec)<\/pre>\n<p>Le sch\u00e9ma (database) gr_test est cr\u00e9\u00e9 sur le n\u0153ud 1. La transaction est <strong>r\u00e9pliqu\u00e9e automatiquement<\/strong> sur les autres n\u0153uds.<\/p>\n<h2>Arr\u00eat du\u00a0n\u0153ud primaire<\/h2>\n<p>Si le n\u0153ud primaire n&rsquo;est plus en ligne (arr\u00eat du serveur pour cause de maintenance ou crash) l&rsquo;un des 2 autres n\u0153uds devient alors primaire.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSHUTDOWN; -- Arr\u00eat du n\u0153ud 1, n\u0153ud primaire\nQuery OK, 0 rows affected (0,00 sec)<\/pre>\n<p>Les autres membres du cluster sont au courant de la disparition de leur pote.<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node3&gt;\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE\nFROM performance_schema.replication_group_members\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nMEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6\nMEMBER_HOST: 192.168.1.9\nMEMBER_STATE: ONLINE\n\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6\nMEMBER_HOST: 192.168.1.9\nMEMBER_PORT: 3301\nMEMBER_STATE: ONLINE\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914\nMEMBER_HOST: 192.168.1.48\nMEMBER_PORT: 5709\nMEMBER_STATE: ONLINE<\/pre>\n<p>Le groupe ne se compose alors plus que de 2 membres :\u00a0192.168.1.9 &amp;\u00a0192.168.1.48<\/p>\n<p>Et l&rsquo;un des deux devient primaire.<\/p>\n<p>Le n\u0153ud 2 est pass\u00e9 primaire (automatic database failover)<br \/>\nJe peux donc \u00e9crire sur le n\u0153ud 2 :<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node2&gt;\nCREATE TABLE gr_test.t1(i int auto_increment primary key);\nQuery OK, 0 rows affected (0.12 sec)\n\nSHOW TABLES IN gr_test;\n+-------------------+\n| Tables_in_gr_test |\n+-------------------+\n| t1                |\n+-------------------+<\/pre>\n<p>Mais\u00a0pas sur le n\u0153ud 3 (pas une surprise):<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node3&gt;\nSHOW TABLES IN gr_test;\n+-------------------+\n| Tables_in_gr_test |\n+-------------------+\n| t1                |\n+-------------------+\n1 row in set (0.00 sec)\n\nDROP TABLE gr_test.t1;\nERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement\n\nSHOW TABLES IN gr_test;\n+-------------------+\n| Tables_in_gr_test |\n+-------------------+\n| t1                |\n+-------------------+\n1 row in set (0.00 sec)<\/pre>\n<p>Ayant fini la\u00a0t\u00e2che de maintenance sur le n\u0153ud 1 je le remet dans le cluster.<\/p>\n<p><em>Note.<\/em><\/p>\n<p><em>Si la dur\u00e9e d&rsquo;indisponibilit\u00e9 est longue, il peut \u00eatre judicieux de restaurer une sauvegarde fra\u00eeche\u00a0sur le serveur avant de le remettre dans le cluster. C&rsquo;est le m\u00eame principe qu&rsquo;avec une architecture de r\u00e9plication, en utilisant :\u00a0<\/em><\/p>\n<ul>\n<li><em><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysqldump.html\" target=\"_blank\" rel=\"noopener\">mysqldump<\/a><\/em><\/li>\n<li><em><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/4.0\/en\/mysqlbackup.usage.html\" target=\"_blank\" rel=\"noopener\">mysqlbackup<\/a><\/em><\/li>\n<\/ul>\n<h2>Faire revenir un\u00a0n\u0153ud dans le cluster<\/h2>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">-- Red\u00e9marrer l'instance MySQL\n-- ...\n-- V\u00e9rifier l'\u00e9tat du n\u0153ud 1\nnode1&gt;\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID:\nMEMBER_HOST:\nMEMBER_PORT: NULL\nMEMBER_STATE: OFFLINE<\/pre>\n<p>La variable <strong>group_replication_start_on_boot<\/strong> \u00e9tant \u00e0 OFF, je dois rajouter le n\u0153ud au cluster de mani\u00e8re explicite:<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">-- D\u00e9marrer le n\u0153ud 1\nnode1&gt; \nSTART GROUP_REPLICATION; \nQuery OK, 0 rows affected (2,74 sec)<\/pre>\n<p>Apr\u00e8s s&rsquo;\u00eatre enregistr\u00e9, le \u00ab\u00a0nouveau\u00a0\u00bb membre va se connecter \u00e0 un autre pour mettre \u00e0 jour ses donn\u00e9es (recovery mode).<br \/>\nPuis finalement participer \u00e0 nouveau \u00e0 la vie du cluster:<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true\">node1&gt;\nSELECT * FROM performance_schema.replication_group_members\\G\n*************************** 1. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: 00014115-1111-1111-1111-111111111111\nMEMBER_HOST: 192.168.1.11\nMEMBER_PORT: 14115\nMEMBER_STATE: ONLINE\n*************************** 2. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6\nMEMBER_HOST: 192.168.1.9\nMEMBER_PORT: 3301\nMEMBER_STATE: ONLINE\n*************************** 3. row ***************************\nCHANNEL_NAME: group_replication_applier\nMEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914\nMEMBER_HOST: 192.168.1.48\nMEMBER_PORT: 5709\nMEMBER_STATE: ONLINE\n3 rows in set (0,00 sec)\n\n\nSHOW TABLES IN gr_test;\n+-------------------+\n| Tables_in_gr_test |\n+-------------------+\n| t1                |\n+-------------------+\n1 row in set (0,00 sec)\n<\/pre>\n<p>A noter que le n\u0153ud 1 ne redevient pas primaire (no failback).<\/p>\n<pre class=\"toolbar-overlay:false lang:mysql decode:true \">node1&gt;\nDROP TABLE gr_test.t1;\nERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement\n\nSELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE\nFROM performance_schema.replication_group_members\nINNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)\nWHERE VARIABLE_NAME='group_replication_primary_member'\\G\n*************************** 1. row ***************************\nMEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6\nMEMBER_HOST: 192.168.1.9\nMEMBER_STATE: ONLINE<\/pre>\n<p>Le n\u0153ud 2 (d0853b8c-8d92-11e6-875b-0800273276e6 ou 192.168.1.9) est donc bien rest\u00e9 primaire<\/p>\n<p><em>Note.<\/em><br \/>\n<em> Pour avoir une vraie configuration multi-ma\u00eetre il faut modifier la variable group_replication_single_primary_mode sur tout les n\u0153uds\u00a0du cluster.<\/em><br \/>\n<em> Cette manipulation ne peut se faire que cluster arr\u00eat\u00e9.<\/em><\/p>\n<p>Bien bien bien!<\/p>\n<p>Je propose de s\u2019arr\u00eater l\u00e0 m\u00eame si j&rsquo;ai encore des choses \u00e0 raconter. Mais je ne souhaite pas trop rallonger ce long billet.<\/p>\n<p>En guise de conclusion, j&rsquo;\u00e9voquerai simplement les solutions disponibles en frontal, pour que l&rsquo;application se connecte au cluster.<\/p>\n<h1>Router, proxy and Co<\/h1>\n<ul>\n<li><strong><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-router\/en\/\" target=\"_blank\" rel=\"noopener\">mysqlrouter<\/a><\/strong> :\u00a0Outil d\u00e9velopp\u00e9 par MySQL Oracle, il est donc pr\u00e9conis\u00e9 pour MySQL Group Replication.<br \/>\nLa version 2.1 (GA pr\u00e9vue pour <span style=\"text-decoration: line-through;\">cette fin d&rsquo;ann\u00e9e<\/span> 2017) est intrins\u00e8quement li\u00e9 \u00e0\u00a0MySQL Group Replication. C&rsquo;est donc son compagnon naturel. Il faut juste patienter un peu \ud83d\ude42<\/li>\n<\/ul>\n<p>La GA actuelle (2.0) peut d\u00e9panner mais elle n&rsquo;est pas suffisamment int\u00e9gr\u00e9e (intelligent) avec MySQL Group replication.<\/p>\n<p>A noter que MySQL Group Replication + mysqlrouter 2.1 + MySQL Shell nous donne<a href=\"http:\/\/dasini.net\/blog\/2017\/03\/13\/tester-mysql-innodb-cluster\/\" target=\"_blank\" rel=\"noopener\"> MySQL InnoDB Cluster<\/a>.<br \/>\nJ&rsquo;y reviendrai plus en d\u00e9tails dans un prochain article.<\/p>\n<p>OK tu es un impatient, je le sens \ud83d\ude42<\/p>\n<p>Jette un coup d\u2019\u0153il <a href=\"http:\/\/dasini.net\/blog\/2016\/10\/05\/mysql-a-oracle-openworld-2016\/\" target=\"_blank\" rel=\"noopener\">ici<\/a>, tu trouveras des liens int\u00e9ressants<\/p>\n<ul>\n<li><a href=\"http:\/\/lefred.be\/content\/ha-with-mysql-group-replication-and-proxysql\/\" target=\"_blank\" rel=\"noopener\">Proxy SQL<\/a> : le palliatif id\u00e9al en attendant MySQL Router 2.1. Tout le monde en dit du bien. Si tu ne connais pas encore, regardes! tu me remercieras plus tard.\n<ul>\n<li>(maj. 11\/01\/2017) j&rsquo;ai \u00e9cris un tuto :\u00a0<a href=\"http:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL pour MySQL Group Replication<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li><a href=\"http:\/\/lefred.be\/content\/mysql-group-replication-as-ha-solution\/\" target=\"_blank\" rel=\"noopener\">HA proxy<\/a> : certainement le plus connu des 3.<\/li>\n<\/ul>\n<ul>\n<li>Si vous \u00eates familiers avec les <a href=\"http:\/\/dev.mysql.com\/doc\/connectors\/en\/\" target=\"_blank\" rel=\"noopener\">connecteurs MySQL<\/a>, cela peut sans doutes \u00eatre une solution.<\/li>\n<\/ul>\n<p>Petite pr\u00e9cision pas anodine, tous ces outils sont en <strong>GPL<\/strong>.<\/p>\n<p>Aller! Cette fois ci je conclus pour de vrai.<\/p>\n<p>La gestion de la <strong>Haute Disponibilit\u00e9 avec MySQL<\/strong> se fait traditionnellement avec :<\/p>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication.html\" target=\"_blank\" rel=\"noopener\">MySQL Replication<\/a>\u00a0ou\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replication-semisync.html\" target=\"_blank\" rel=\"noopener\">MySQL Replication semi-sync<\/a>\n<ul>\n<li>saupoudr\u00e9 de <a href=\"http:\/\/dev.mysql.com\/doc\/mysql-utilities\/1.6\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL Utilities<\/a> ou de <a href=\"http:\/\/dasini.net\/blog\/presentations\/?#mha_viadeo\" target=\"_blank\" rel=\"noopener\">MHA<\/a>.<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-cluster.html\" target=\"_blank\" rel=\"noopener\">MySQL Cluster<\/a><\/li>\n<\/ul>\n<p>Le plugin <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-replication.html\" target=\"_blank\" rel=\"noopener\">MySQL Group Replication<\/a> apporte une nouvelle solution de HA native et permet \u00e9galement d&rsquo;avoir une vraie solution multi-ma\u00eetre native et open-source.<\/p>\n<p>Que du bon \ud83d\ude42<\/p>\n<p>Autres articles Haute Dispo avec MySQL:<\/p>\n<ul>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/01\/09\/configurer-proxysql-1-4-pour-mysql-5-7-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2017\/01\/11\/configurer-proxysql-pour-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Configurer ProxySQL 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\">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\/04\/10\/adopte-un-cluster-mysql-group-replication\/\" target=\"_blank\" rel=\"noopener\">Adopte un\u2026 cluster MySQL Group Replication<\/a><\/li>\n<\/ul>\n<p>Thanks for using MySQL!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Historiquement, les solutions de haute disponibilit\u00e9 (HA) avec MySQL tournent autour de la fonctionnalit\u00e9 native\u00a0MySQL Replication: replication asynchrone ou semi-synchrone. Ces modes de \u00a0replication sont tr\u00e8s largement utilis\u00e9s pour le besoins critiques d&rsquo;enterprises \u00ab\u00a0at scale\u00a0\u00bb comme Facebook, Twitter, Booking.com, Uber&#8230;<\/p>\n<p>Aujourd&rsquo;hui MySQL propose une nouvelle fonctionnalit\u00e9 native de haute disponibilit\u00e9 :<\/p>\n<p>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,288,8],"tags":[75,286,292,298,296,300,302],"class_list":["post-1678","post","type-post","status-publish","format-standard","hentry","category-group-replication","category-innodb-cluster","category-mysql","tag-cluster","tag-group-replication","tag-ha","tag-innodb-cluster","tag-proxy","tag-proxysql","tag-router"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-r4","jetpack-related-posts":[{"id":4381,"url":"https:\/\/dasini.net\/blog\/2021\/02\/24\/webinar-la-haute-disponibilite-avec-mysql\/","url_meta":{"origin":1678,"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":1654,"url":"https:\/\/dasini.net\/blog\/2016\/10\/05\/mysql-a-oracle-openworld-2016\/","url_meta":{"origin":1678,"position":1},"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":2035,"url":"https:\/\/dasini.net\/blog\/2018\/01\/09\/configurer-proxysql-1-4-pour-mysql-5-7-group-replication\/","url_meta":{"origin":1678,"position":2},"title":"Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication","author":"Olivier DASINI","date":"9 janvier 2018","format":false,"excerpt":"Toute architecture de base de donn\u00e9es se doit de se reposer sur 3 piliers, la supervision (monitoring) , la sauvegarde\/restauration et la haute disponibilit\u00e9. Mon premier article de l'ann\u00e9e 2018\u00a0 concerne l'un des meilleurs combos\u00a0du moment, en mati\u00e8re de haute disponibilit\u00e9 niveau base de donn\u00e9es : - MySQL 5.7 Group\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","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/mysql_300x161.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":1678,"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":1855,"url":"https:\/\/dasini.net\/blog\/2017\/04\/10\/adopte-un-cluster-mysql-group-replication\/","url_meta":{"origin":1678,"position":4},"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":1800,"url":"https:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/","url_meta":{"origin":1678,"position":5},"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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1678","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=1678"}],"version-history":[{"count":62,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1678\/revisions"}],"predecessor-version":[{"id":7242,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1678\/revisions\/7242"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1678"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1678"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1678"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}