dasini.net - Journal d'un expert MySQL dasini.net - Journal d\'experts MySQL

Tutoriel – Déployer MySQL 8.0 InnoDB Cluster

août 30, 2018

Tags: cluster, group replication, Haute disponibilité, InnoDB Cluster, router, tuto

Une nouvelle version de ce tutoriel est disponible.

Il concerne les versions MySQL supérieures ou égales à 8.0.17:

Tutoriel – Déployer MySQL 8.0 InnoDB Cluster (09-2019)

Pour les versions inférieures tu peux rester sur cette page, mais cependant, je t’invite vivement à utiliser la dernière GA en date.

C’est dommage de se priver des nouvelles fonctionnalités.

😉

Cela fait maintenant plus d’un trimestre que MySQL 8.0 est GA (8.0.11; 8.0.12), il est grand temps que je t’en parle 🙂

Dans la liste des besoins essentiels de mes clients se trouve la Haute Disponibilité avec MySQL. On va voir, dans cet article, comment déployer et gérer un cluster MySQL « from scratch » , sous la forme d’un tutoriel, grâce à la solution HA tout en un : MySQL InnoDB Cluster.

Si tu utilises MySQL 5.7 tu peux également mettre en oeuvre InnoDB Cluster, je te recommande cet article: Tutoriel – Déployer MySQL 5.7 InnoDB Cluster.

Note: L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server (plugin Group Replication) avec pour moteur de stockage InnoDB, solution à ne pas confondre avec MySQL NDB Cluster (moteur de stockage NDB).

Le contexte

3 instances MySQL autonomes, <spoil alert> qui vont grâce au plugin MySQL Group Replication se transformer en une base de données distribuée.</spoil alert>

Instance MySQL 1 (mysql_8.0_node1) : 172.19.0.11; Nœud numéro 1 du cluster
Instance MySQL 2 (mysql_8.0_node2) : 172.19.0.12; Nœud numéro 2 du cluster
Instance MySQL 3 (mysql_8.0_node3) : 172.19.0.13; Nœud numéro 3 du cluster

1 instance applicative : 192.168.1.11; MySQL Router + mon application.

Note: J’utilise l’image Docker MySQL Server supportée par l’équipe MySQL d’Oracle.

Note: Je n’aborde pas dans cet article la redondance de MySQL Router. Plusieurs scénarios sont possibles, je te recommande de lire ça, ceci et cela.

MySQL Shell n’a pas besoin d’être installé sur toutes les instances (contrairement à 5.7, pour persister la configuration), cependant ce client texte est quand même beaucoup plus puissant que le client texte par défaut. Si tu ne le connais pas encore, essaie le et tu verras tu ne pourras plus t’en passer 🙂

En ce qui concerne les versions des logiciels, ce sont les plus récentes à ce jour (journée caniculaire du mois d’août 2018):

Note: Dans cet article j’utilise la dernière GA de MySQL 8.0. En ce qui concerne MySQL Router et MySQL Shell, il est impératif d’utiliser la dernière version courante.

Pour récapituler notre architecture, une image valant (au moins) 1000 mots, ça nous donne à :

Vérifier la configuration des instances

La première étape consiste à s’assurer que les instances MySQL sont correctement configurées pour l’utilisation de MySQL Group Replication, la couche haute disponibilité de notre architecture. A noter qu’il est préférable de provisionner ses instances déjà correctement configurées (comme détaillé dans cet article) pour MySQL Group Replication.

Note: J’utilise le compte utilisateur root pour configurer le cluster, cependant ce n’est pas un pré-requis. Il est possible (et souhaitable) de créer un compte utilisateur spécifique (ou plusieurs), avec les droits qui vont bien. La méthode recommandée pour créer cet utilisateur et d’utiliser l’option clusterAdmin des méthodes dba.configureInstance() et cluster.addInstance(). Plus d’info ici (Paragraphe « User Privileges »).

La vérification de la configuration se fait grâce à MySQL Shell et la méthode checkInstanceConfiguration() :

daz@192.168.1.11:~$ mysqlsh
MySQL Shell 8.0.12-commercial

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


JS > dba.checkInstanceConfiguration('root@172.19.0.11:3306')
Please provide the password for 'root@172.19.0.11:3306':
Validating MySQL instance at 172.19.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node1
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update", 
            "current": "CRC32", 
            "option": "binlog_checksum", 
            "required": "NONE"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "1", 
            "option": "server_id", 
            "required": "<unique ID>"
        }
    ], 
    "errors": [], 
    "status": "error"
}

daz@192.168.1.11:~$ mysqlsh

MySQL Shell 8.0.12-commercial

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type '\help' or '\?' for help; '\quit' to exit.

JS > dba.checkInstanceConfiguration('root@172.19.0.11:3306')

Please provide the password for 'root@172.19.0.11:3306':

Validating MySQL instance at 172.19.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node1

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:

+--------------------------+---------------+----------------+--------------------------------------------------+

+--------------------------+---------------+----------------+--------------------------------------------------+

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

+--------------------------+---------------+----------------+--------------------------------------------------+

Please use the dba.configureInstance() command to repair these issues.

{

"config_errors": [

{

"action": "server_update",

"current": "CRC32",

"option": "binlog_checksum",

"required": "NONE"

{

"action": "restart",

"current": "OFF",

"option": "enforce_gtid_consistency",

"required": "ON"

{

"action": "restart",

"current": "OFF",

"option": "gtid_mode",

"required": "ON"

{

"action": "restart",

"current": "1",

"option": "server_id",

"required": "<unique ID>"

}

"errors": [],

"status": "error"

}

Dans mon cas, avec l’installation Docker de MySQL 8.0 par défaut sous Ubuntu, niveau configuration j’ai quasiment tout à faire 🙂

La méthode renvoie un document JSON (pratique pour l’automatisation) avec la liste des tâches à effectuer pour être conforme… Configurons donc !

J’ai deux solutions :

1/ je prépare mes instances « manuellement » (cette tâche peut bien évidemment s’automatiser) comme expliqué dans l’article comment configurer un groupe.
2/ je laisse MySQL Shell faire le boulot en utilisant la méthode : configureInstance()

je sens que ton cœur balance pour la 2… moi aussi 🙂 :

JS > dba.configureInstance('root@172.19.0.11:3306')
Please provide the password for 'root@172.19.0.11:3306': ****
Configuring MySQL instance at 172.19.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as 9475cf8d5192
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance...
The instance '172.19.0.11:3306' was configured for cluster usage.
Restarting MySQL...
ERROR: Remote restart of MySQL server failed: MySQL Error 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
Please restart MySQL manually

JS > dba.configureInstance('root@172.19.0.11:3306')

Please provide the password for 'root@172.19.0.11:3306': ****

Configuring MySQL instance at 172.19.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as 9475cf8d5192

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Some configuration options need to be fixed:

+--------------------------+---------------+----------------+--------------------------------------------------+

+--------------------------+---------------+----------------+--------------------------------------------------+

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

+--------------------------+---------------+----------------+--------------------------------------------------+

Do you want to perform the required configuration changes? [y/n]: y

Do you want to restart the instance after configuring it? [y/n]: y

Configuring instance...

The instance '172.19.0.11:3306' was configured for cluster usage.

Restarting MySQL...

ERROR: Remote restart of MySQL server failed: MySQL Error 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

Please restart MySQL manually

Alors plusieurs commentaires !

Les informations de configurations sont automatiquement sauvegardées avec la commande SET PERSIST. Commande super pratique (Cloud Friendly) apparue en 8.0, qui me permet de faire des changements de configurations à chaud (online) et persistants, stockés dans le fichier mysqld-auto.cnf.

# cat /var/lib/mysql/mysqld-auto.cnf 
{
  "Version": 1,
  "mysql_server": {
    "server_id": {
      "Value": "866646436",
      "Metadata": {
        "Timestamp": 1534169971463437,
        "User": "root",
        "Host": ""
      }
    },
    "mysql_server_static_options": {
      "binlog_checksum": {
        "Value": "NONE",
        "Metadata": {
          "Timestamp": 1534169971419313,
          "User": "root",
          "Host": ""
        }
      },
      "enforce_gtid_consistency": {
        "Value": "ON",
        "Metadata": {
          "Timestamp": 1534169970411779,
          "User": "root",
          "Host": ""
        }
      },
      "gtid_mode": {
        "Value": "ON",
        "Metadata": {
          "Timestamp": 1534169971458247,
          "User": "root",
          "Host": ""
        }
      }
    }
  }
}

# cat /var/lib/mysql/mysqld-auto.cnf

{

"Version": 1,

"mysql_server": {

"server_id": {

"Value": "866646436",

"Metadata": {

"Timestamp": 1534169971463437,

"User": "root",

"Host": ""

}

"mysql_server_static_options": {

"binlog_checksum": {

"Value": "NONE",

"Metadata": {

"Timestamp": 1534169971419313,

"User": "root",

"Host": ""

}

"enforce_gtid_consistency": {

"Value": "ON",

"Metadata": {

"Timestamp": 1534169970411779,

"User": "root",

"Host": ""

}

"gtid_mode": {

"Value": "ON",

"Metadata": {

"Timestamp": 1534169971458247,

"User": "root",

"Host": ""

}

On peut également voir ces informations en SQL, grâce à la table persisted_variables de performance_schema :

172.19.0.11:33060+ SQL> SELECT * FROM performance_schema.persisted_variables;
+--------------------------+----------------+
| VARIABLE_NAME            | VARIABLE_VALUE |
+--------------------------+----------------+
| server_id                | 866646436      |
| binlog_checksum          | NONE           |
| enforce_gtid_consistency | ON             |
| gtid_mode                | ON             |
+--------------------------+----------------+

172.19.0.11:33060+ SQL> SELECT * FROM performance_schema.persisted_variables;

+--------------------------+----------------+

| VARIABLE_NAME | VARIABLE_VALUE |

+--------------------------+----------------+

| server_id | 866646436 |

| binlog_checksum | NONE |

| enforce_gtid_consistency | ON |

| gtid_mode | ON |

+--------------------------+----------------+

La persistance des changements ce fait automatiquement grâce au paramètre persisted_globals_load activé par défaut (ON). Elle est effective après l’exécution des commandes:

dba.configureInstance()
dba.createCluster()
Cluster.addInstance()
Cluster.removeInstance()
Cluster.rejoinInstance()

Note: je te recommande cet excellent article sur les variables persistantes de mon non moins excellent collègue Jesper.

SET PERSIST est très appréciable dans les environnements où accéder au fichier de configuration est compliqué voir impossible (Cloud Friendly, je te dis!!!). Cependant, dans un environnement plus maîtrisé (plus classique), il peut être préférable, de centraliser toutes les informations de configurations dans le fichier de configuration original (my.cnf / my.ini).

C’est bien évidemment possible grâce à l’option mycnfPath de la méthode dba.configureInstance().

Exemple:

JS> dba.configureInstance('root@172.19.0.11:3306', {mycnfPath: "/etc/my.cnf"})
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node1

Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+------------------------+
| Variable                         | Current Value | Required Value | Note                   |
+----------------------------------+---------------+----------------+------------------------+
| binlog_checksum                  | <not set>     | NONE           | Update the config file |
| binlog_format                    | <not set>     | ROW            | Update the config file |
| log_slave_updates                | <not set>     | ON             | Update the config file |
| transaction_write_set_extraction | <not set>     | XXHASH64       | Update the config file |
+----------------------------------+---------------+----------------+------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...
The instance '172.19.0.11:3306' was configured for use in an InnoDB cluster.

JS> dba.configureInstance('root@172.19.0.11:3306', {mycnfPath: "/etc/my.cnf"})

Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node1

Some configuration options need to be fixed:

+----------------------------------+---------------+----------------+------------------------+

+----------------------------------+---------------+----------------+------------------------+

+----------------------------------+---------------+----------------+------------------------+

Do you want to perform the required configuration changes? [y/n]: y

Configuring instance...

The instance '172.19.0.11:3306' was configured for use in an InnoDB cluster.

Tu as donc le choix !

Grâce à ton œil aiguisé, tu as remarqué, que l’outil me propose de redémarrer l’instance MySQL, ce qui est bien pratique. Cependant, dans le cas présent, la plateforme que j’utilise (image Docker) ne dispose pas d’un processus de supervision de mysqld (comme c’est généralement le cas sur ta plateforme préférée. Info ici). En clair, je vais devoir redémarrer sans l’outil (mais je devrai m’en sortir… ou pas 🙂 ).

Note: Assure toi d’avoir les droits nécessaires pour mettre à jour le fichier de configuration de MySQL.

L’instance 172.19.0.11 est configurée et prête pour être un membre d’un cluster MySQL InnoDB Cluster !

On peut le vérifier en redémarrant l’instance MySQL et refaire un checkInstanceConfiguration :

JS> dba.checkInstanceConfiguration('root@172.19.0.11:3306')
Please provide the password for 'root@172.19.0.11:3306':
Validating MySQL instance at 172.19.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node1
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.11:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

JS> dba.checkInstanceConfiguration('root@172.19.0.11:3306')

Please provide the password for 'root@172.19.0.11:3306':

Validating MySQL instance at 172.19.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node1

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.11:3306' is valid for InnoDB cluster usage.

{

"status": "ok"

}

Statut: OK.

La même procédure doit être appliquée sur les autres instances MySQL.

JS> dba.configureInstance('root@172.19.0.12:3306')
...

JS> dba.configureInstance('root@172.19.0.13:3306')
...

JS> dba.configureInstance('root@172.19.0.12:3306')

...

JS> dba.configureInstance('root@172.19.0.13:3306')

...

Je me retrouve, après configuration et redémarrage avec le résultat suivant:

JS > dba.checkInstanceConfiguration('root@172.19.0.12:3306')
Please provide the password for 'root@172.19.0.12:3306': 
Validating MySQL instance at 172.19.0.12:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node2
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.12:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}



JS > dba.checkInstanceConfiguration('root@172.19.0.13:3306')
Please provide the password for 'root@172.19.0.13:3306': 
Validating MySQL instance at 172.19.0.13:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node3
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.13:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

JS > dba.checkInstanceConfiguration('root@172.19.0.12:3306')

Please provide the password for 'root@172.19.0.12:3306':

Validating MySQL instance at 172.19.0.12:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node2

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.12:3306' is valid for InnoDB cluster usage.

{

"status": "ok"

}

JS > dba.checkInstanceConfiguration('root@172.19.0.13:3306')

Please provide the password for 'root@172.19.0.13:3306':

Validating MySQL instance at 172.19.0.13:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node3

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.13:3306' is valid for InnoDB cluster usage.

{

"status": "ok"

}

All good!

Créer le cluster

Une fois les 3 instances correctement configurées, l’étape suivante consiste à créer le cluster avec createCluster. Cette méthode va être jouée sur le premier membre, l’instance MySQL sur 172.19.0.11, elle va permettre de créer un cluster… d’un nœud. Bon faut bien commencer quelque part 🙂 :

$ mysqlsh --uri=root@172.19.0.11
Creating a Session to 'root@172.19.0.11'
Please provide the password for 'root@172.19.0.11': 
...

172.19.0.11 JS> var cluster = dba.createCluster('pocCluster', {ipWhitelist: "172.19.0.0/16"})
A new InnoDB cluster will be created on instance 'root@172.19.0.11:3306'.

Validating instance at 172.19.0.11:3306...

This instance reports its own address as 9475cf8d5192

Instance configuration is suitable.
Creating InnoDB cluster 'pocCluster' on 'root@172.19.0.11:3306'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

$ mysqlsh --uri=root@172.19.0.11

Creating a Session to 'root@172.19.0.11'

Please provide the password for 'root@172.19.0.11':

...

172.19.0.11 JS> var cluster = dba.createCluster('pocCluster', {ipWhitelist: "172.19.0.0/16"})

A new InnoDB cluster will be created on instance 'root@172.19.0.11:3306'.

Validating instance at 172.19.0.11:3306...

This instance reports its own address as 9475cf8d5192

Instance configuration is suitable.

Creating InnoDB cluster 'pocCluster' on 'root@172.19.0.11:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

createCluster() prend comme paramètre le nom du cluster (pocCluster). Je peux lui passer également quelques information optionnelles comme la whitelist.

On peut vérifier l’état du nœud dans le cluster avec status() :

172.19.0.11 JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.11:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.11:3306"
}

172.19.0.11 JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.11:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.11:3306"

}

Notes : Assure toi que ton DNS (ou /etc/hosts) est correctement configuré, sinon tu vas avoir des soucis de connections…

L’ajouts des nœuds suivant se fait avec addInstance(), il est néanmoins conseillé d’exécuter checkInstanceState() au préalable pour s’assurer de la compatibilité des GTID sets :

Nœud 2

172.19.0.11 JS> cluster.checkInstanceState('root@172.19.0.12:3306')
Analyzing the instance replication state...
Please provide the password for 'root@172.19.0.12:3306': 

The instance 'root@172.19.0.12:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new", 
    "state": "ok"
}



JS> cluster.addInstance("root@172.19.0.12:3306", {ipWhitelist: "172.19.0.0/16"})
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'root@172.19.0.12:3306': 
Validating instance at 172.19.0.12:3306...

This instance reports its own address as mysql_8.0_node2

Instance configuration is suitable.
The instance 'root@172.19.0.12:3306' was successfully added to the cluster.



JS> cluster.status();
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.11:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.11:3306"
}

172.19.0.11 JS> cluster.checkInstanceState('root@172.19.0.12:3306')

Analyzing the instance replication state...

Please provide the password for 'root@172.19.0.12:3306':

The instance 'root@172.19.0.12:3306' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

JS> cluster.addInstance("root@172.19.0.12:3306", {ipWhitelist: "172.19.0.0/16"})

A new instance will be added to the InnoDB cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'root@172.19.0.12:3306':

Validating instance at 172.19.0.12:3306...

This instance reports its own address as mysql_8.0_node2

Instance configuration is suitable.

The instance 'root@172.19.0.12:3306' was successfully added to the cluster.

JS> cluster.status();

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.11:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.11:3306"

}

Au cas où l’instance ajoutée contient plus de transactions que le groupe checkInstanceState le fait savoir :

JS> cluster.checkInstanceState('root@172.19.0.12:3306')
Analyzing the instance replication state...
Please provide the password for 'root@172.19.0.12:3306': 

The instance '172.19.0.12:3306' is invalid for the cluster.
The instance contains additional transactions in relation to the cluster.

{
    "reason": "diverged", 
    "state": "error"
}

JS> cluster.checkInstanceState('root@172.19.0.12:3306')

Analyzing the instance replication state...

Please provide the password for 'root@172.19.0.12:3306':

The instance '172.19.0.12:3306' is invalid for the cluster.

The instance contains additional transactions in relation to the cluster.

{

"reason": "diverged",

"state": "error"

}

En fonction du contexte, il faut alors soit restaurer une sauvegarde d’un membre du cluster sur l’instance problématique (celle qui diverge) ou alors si tu sais ce que tu fais, une synchronisation des GTIDs est toujours possible, voir un reset master.

Nœud 3

172.19.0.11 JS> cluster.checkInstanceState('root@172.19.0.13:3306')
Analyzing the instance replication state...
Please provide the password for 'root@172.19.0.13:3306': 

The instance 'root@172.19.0.13:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new", 
    "state": "ok"
}



JS> cluster.addInstance("root@172.19.0.13:3306", {ipWhitelist: "172.19.0.0/16"})
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'root@172.19.0.13:3306': 
Validating instance at 172.19.0.13:3306...

This instance reports its own address as mysql_8.0_node3

Instance configuration is suitable.
The instance 'root@172.19.0.13:3306' was successfully added to the cluster.

172.19.0.11 JS> cluster.checkInstanceState('root@172.19.0.13:3306')

Analyzing the instance replication state...

Please provide the password for 'root@172.19.0.13:3306':

The instance 'root@172.19.0.13:3306' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

JS> cluster.addInstance("root@172.19.0.13:3306", {ipWhitelist: "172.19.0.0/16"})

A new instance will be added to the InnoDB cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'root@172.19.0.13:3306':

Validating instance at 172.19.0.13:3306...

This instance reports its own address as mysql_8.0_node3

Instance configuration is suitable.

The instance 'root@172.19.0.13:3306' was successfully added to the cluster.

Et le résultat final:

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.11:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.11:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.11:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.11:3306"

}

Et voilà!

Un Cluster MySQL Group Replication de 3 nœuds facilement et rapidement déployé grâce à MySQL Shell, c’est ça MySQL InnoDB Cluster (enfin presque, il manque encore un élément).

La configuration actuelle est la suivante:

Nœud 1 (mysql_8.0_node1) = 172.19.0.11 : Primaire (lecture/écriture)
Nœud 2 (mysql_8.0_node2) = 172.19.0.12 : Secondaire (lecture seule)
Nœud 3 (mysql_8.0_node3) = 172.19.0.13 : Secondaire (lecture seule)

Et qu’est ce que l’on fait maintenant ???

Le Router !

Configuration de MySQL Router

Il est recommandé d’installer MySQL Router sur la machine hôte de l’application, je vais donc suivre cette recommandation et l’installer sur la machine 192.168.1.11.

Note: Si tu ne peux (veux) pas mettre MySQL Router sur l’application, il va alors te falloir gérer le HA du Router. Plusieurs solutions sont envisageables comme :

Pacemaker
Keepalived

Bootstrap MySQL Router

La première étape est le bootstrap, c’est à dire créer un lien entre MySQL Router et le cluster. Il faut donc fournir à mysqlrouter au minimum l’adresse d’un membre du cluster :

daz@192.168.1.11:~$ mysqlrouter --bootstrap root@172.19.0.11:3306 --conf-base-port 3306 --directory ~/routerConf/RouterPoC2
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at '/home/daz/routerConf/RouterPoC2'...
Checking for old Router accounts
Creating account mysql_router6_2ah00zu9lfdj@'%'
MySQL Router  has now been configured for the InnoDB cluster 'pocCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'pocCluster':
- Read/Write Connections: localhost:3306
- Read/Only Connections: localhost:3307
X protocol connections to cluster 'pocCluster':
- Read/Write Connections: localhost:3308
- Read/Only Connections: localhost:3309

daz@192.168.1.11:~$ mysqlrouter --bootstrap root@172.19.0.11:3306 --conf-base-port 3306 --directory ~/routerConf/RouterPoC2

Please enter MySQL password for root:

Bootstrapping MySQL Router instance at '/home/daz/routerConf/RouterPoC2'...

Checking for old Router accounts

Creating account mysql_router6_2ah00zu9lfdj@'%'

MySQL Router has now been configured for the InnoDB cluster 'pocCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'pocCluster':

- Read/Write Connections: localhost:3306

- Read/Only Connections: localhost:3307

X protocol connections to cluster 'pocCluster':

- Read/Write Connections: localhost:3308

- Read/Only Connections: localhost:3309

Note: il se peut que tu rencontres un problème de permission. Probablement dû à la configuration de AppArmor… Google (ou équivalent) est ton ami 🙂 (si tu es sous Ubuntu clic ici)

J’ai créé une configuration différente de celle par défaut, en personnalisant avec quelques options:

conf-base-port : le port proposé par défaut est 6446 pour la lecture/écriture. Dans mon cas, je veux utiliser le célèbre port 3306.
directory : histoire de ranger tout le bazar de cette instance de Router dans le répertoire spécifié.

La liste complète des options est disponible ici.

Pour résumer, 4 ports TCP ont été configurés, dont 2 pour les connexions MySQL traditionnelles:

3306 (au lieu de 6446 par défaut) : lectures / écritures pour le nœud primaire
3307 (au lieu de 6447 par défaut) : lectures seules pour les nœuds secondaires (en Round-Robin)
Et le pendant pour les connexions avec le protocole X (3308 & 3309 (au lieu de respectivement 64460 & 64470)), pour une utilisation NoSQL Document Store de MySQL.

Le fichier de configuration de MySQL Router contient quelques informations importantes, tel que le(s) port(s) à utiliser par l’application (comme vu précédemment) :

daz@192.168.1.11:~$ cat ~/routerConf/RouterPoC2/mysqlrouter.conf 
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/home/daz/routerConf/RouterPoC2/log
runtime_folder=/home/daz/routerConf/RouterPoC2/run
data_folder=/home/daz/routerConf/RouterPoC2/data
keyring_path=/home/daz/routerConf/RouterPoC2/data/keyring
master_key_path=/home/daz/routerConf/RouterPoC2/mysqlrouter.key
connect_timeout=30
read_timeout=30

[logger]
level = INFO

[metadata_cache:pocCluster]
router_id=6
bootstrap_server_addresses=mysql://172.19.0.11:3306,mysql://172.19.0.12:3306,mysql://172.19.0.13:3306
user=mysql_router6_2ah00zu9lfdj
metadata_cluster=pocCluster
ttl=0.5

[routing:pocCluster_default_rw]
bind_address=0.0.0.0
bind_port=3306
destinations=metadata-cache://pocCluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=classic

[routing:pocCluster_default_ro]
bind_address=0.0.0.0
bind_port=3307
destinations=metadata-cache://pocCluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=classic

[routing:pocCluster_default_x_rw]
bind_address=0.0.0.0
bind_port=3308
destinations=metadata-cache://pocCluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=x

[routing:pocCluster_default_x_ro]
bind_address=0.0.0.0
bind_port=3309
destinations=metadata-cache://pocCluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=x

daz@192.168.1.11:~$ cat ~/routerConf/RouterPoC2/mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap

[DEFAULT]

logging_folder=/home/daz/routerConf/RouterPoC2/log

runtime_folder=/home/daz/routerConf/RouterPoC2/run

data_folder=/home/daz/routerConf/RouterPoC2/data

keyring_path=/home/daz/routerConf/RouterPoC2/data/keyring

master_key_path=/home/daz/routerConf/RouterPoC2/mysqlrouter.key

connect_timeout=30

read_timeout=30

[logger]

level = INFO

[metadata_cache:pocCluster]

router_id=6

bootstrap_server_addresses=mysql://172.19.0.11:3306,mysql://172.19.0.12:3306,mysql://172.19.0.13:3306

user=mysql_router6_2ah00zu9lfdj

metadata_cluster=pocCluster

ttl=0.5

[routing:pocCluster_default_rw]

bind_address=0.0.0.0

bind_port=3306

destinations=metadata-cache://pocCluster/default?role=PRIMARY

routing_strategy=round-robin

protocol=classic

[routing:pocCluster_default_ro]

bind_address=0.0.0.0

bind_port=3307

destinations=metadata-cache://pocCluster/default?role=SECONDARY

routing_strategy=round-robin

protocol=classic

[routing:pocCluster_default_x_rw]

bind_address=0.0.0.0

bind_port=3308

destinations=metadata-cache://pocCluster/default?role=PRIMARY

routing_strategy=round-robin

protocol=x

[routing:pocCluster_default_x_ro]

bind_address=0.0.0.0

bind_port=3309

destinations=metadata-cache://pocCluster/default?role=SECONDARY

routing_strategy=round-robin

protocol=x

Il est évidemment possible de modifier ce fichier.

Ensuite, il faut démarrer MySQL Router avec le script start.sh

daz@192.168.1.11:~$ ~/routerConf/RouterPoC2/start.sh

1	daz@192.168.1.11:~$ ~/routerConf/RouterPoC2/start.sh

L’arrêt du Router se fait avec le script stop.sh (mais tu l’avais deviné)

daz@192.168.1.11:~$ ~/routerConf/RouterPoC2/stop.sh

1	daz@192.168.1.11:~$ ~/routerConf/RouterPoC2/stop.sh

Voilà pour le Router !

C’est terminé pour la phase de déploiement du cluster.

Simple, rapide et surtout facilement automatisable, tels sont les principales caractéristiques de MySQL InnoDB Cluster. Qualités qui constituent le cœur même de l’ADN de MySQL.

Se connecter au cluster

A partir de maintenant, ton cluster est « up and running ». Ton application va donc devoir se connecter au port 3306 (car on l’a configuré comme cela, sinon c’est 6446 par défaut) pour utiliser la base de donnée. D’ailleurs du point de vue de l’application, la base de donnée est MySQL Router, sauf qu’en réalité ce n’est pas 1 instance, mais bel et bien 3 instances MySQL qui sont en backend et ceci en toute transparence \o/.

La partie utilisation du cluster est hors du scope de cet article, mais on peut facilement simuler le comportement de l’application avec un client MySQL et MySQL router.

daz@192.168.1.11:~$ mysqlsh --uri=root@localhost:3306 --sql
SQL> SELECT @@report_host;
+-----------------+
| @@report_host   |
+-----------------+
| mysql_8.0_node1 |
+-----------------+

daz@192.168.1.11:~$ mysqlsh --uri=root@localhost:3306 --sql

SQL> SELECT @@report_host;

+-----------------+

| @@report_host |

+-----------------+

| mysql_8.0_node1 |

+-----------------+

Je me connecte avec MySQL Shell en mode SQL (ça c’est l’applicatif), au cluster (à mysql_8.0_node1, nœud primaire InnoDB Cluster), par l’intermédiaire de MySQL Router en localhost (car je suis sur la machine 192.168.1.11) sur le port 3306.

Le paramètre report_host (défini dans mon fichier de configuration) me renvoi la valeur du nœud 1, le primaire.

En cas d’arrêt du primaire, un nouveau va être automatiquement élu par le cluster (voir paragraphe failover plus bas) est la même commande me donnera un résultat différent:

-- /!\ Stop Node 1
SQL> SELECT @@report_host;
ERROR: 2013 (HY000): Lost connection to MySQL server during query
The global session got disconnected..
Attempting to reconnect to 'mysql://root@localhost:3306'..
The global session was successfully reconnected.

SQL> SELECT @@report_host;
+-----------------+
| @@report_host   |
+-----------------+
| mysql_8.0_node2 |
+-----------------+

-- /!\ Stop Node 1

SQL> SELECT @@report_host;

ERROR: 2013 (HY000): Lost connection to MySQL server during query

The global session got disconnected..

Attempting to reconnect to 'mysql://root@localhost:3306'..

The global session was successfully reconnected.

SQL> SELECT @@report_host;

+-----------------+

| @@report_host |

+-----------------+

| mysql_8.0_node2 |

+-----------------+

Gestion des nœuds

Quelques commandes qui vont te simplifier la vie…

Performance_Schema

Quelques informations sont disponibles en SQL au niveau des instances.

Identifier le nœud primaire

SQL> SELECT * 
FROM performance_schema.replication_group_members 
WHERE MEMBER_ROLE='PRIMARY'\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 99bc527e-9f00-11e8-910a-0242ac13000b
   MEMBER_HOST: mysql_8.0_node1
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.12

SQL> SELECT *

FROM performance_schema.replication_group_members

WHERE MEMBER_ROLE='PRIMARY'\G

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 99bc527e-9f00-11e8-910a-0242ac13000b

MEMBER_HOST: mysql_8.0_node1

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: PRIMARY

MEMBER_VERSION: 8.0.12

Description des membres du cluster

SQL> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 99bc527e-9f00-11e8-910a-0242ac13000b
   MEMBER_HOST: mysql_8.0_node1
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.12
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 9aceda6d-9f00-11e8-a3b3-0242ac13000c
   MEMBER_HOST: mysql_8.0_node2
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.12
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 9c362837-9f00-11e8-b6a1-0242ac13000d
   MEMBER_HOST: mysql_8.0_node3
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.12

SQL> SELECT * FROM performance_schema.replication_group_members\G

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 99bc527e-9f00-11e8-910a-0242ac13000b

MEMBER_HOST: mysql_8.0_node1

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: PRIMARY

MEMBER_VERSION: 8.0.12

*************************** 2. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 9aceda6d-9f00-11e8-a3b3-0242ac13000c

MEMBER_HOST: mysql_8.0_node2

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: SECONDARY

MEMBER_VERSION: 8.0.12

*************************** 3. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 9c362837-9f00-11e8-b6a1-0242ac13000d

MEMBER_HOST: mysql_8.0_node3

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

MEMBER_ROLE: SECONDARY

MEMBER_VERSION: 8.0.12

Récupérer les méta-données d’un cluster

Les méta-données du cluster sont stockées sur les membres dans le schéma mysql_innodb_cluster_metadata :

daz@172.19.0.11 ~ $ mysqlsh --uri root@172.19.0.11 --sql
...

SQL> SHOW SCHEMAS;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+


SQL> SHOW TABLES IN mysql_innodb_cluster_metadata;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| routers                                 |
| schema_version                          |
+-----------------------------------------+


SQL> SELECT cluster_name FROM mysql_innodb_cluster_metadata.clusters;
+--------------+
| cluster_name |
+--------------+
| pocCluster   |
+--------------+


SQL > SELECT host_name FROM mysql_innodb_cluster_metadata.hosts;
+-----------------+
| host_name       |
+-----------------+
| 172.19.0.11     |
| 172.19.0.12     |
| 172.19.0.13     |
| 192.168.1.11    |
+-----------------+

daz@172.19.0.11 ~ $ mysqlsh --uri root@172.19.0.11 --sql

...

SQL> SHOW SCHEMAS;

+-------------------------------+

| Database |

+-------------------------------+

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

+-------------------------------+

SQL> SHOW TABLES IN mysql_innodb_cluster_metadata;

+-----------------------------------------+

| Tables_in_mysql_innodb_cluster_metadata |

+-----------------------------------------+

| clusters |

| hosts |

| instances |

| replicasets |

| routers |

| schema_version |

+-----------------------------------------+

SQL> SELECT cluster_name FROM mysql_innodb_cluster_metadata.clusters;

+--------------+

| cluster_name |

+--------------+

| pocCluster |

+--------------+

SQL > SELECT host_name FROM mysql_innodb_cluster_metadata.hosts;

+-----------------+

| host_name |

+-----------------+

| 172.19.0.11 |

| 172.19.0.12 |

| 172.19.0.13 |

| 192.168.1.11 |

+-----------------+

Pour récupérer les informations relatives à l’état du cluster dans une nouvelle session il faut utiliser la méthode dba.getCluster :

JS> cluster.status()
ReferenceError: cluster is not defined


JS> var cluster = dba.getCluster('pocCluster')


JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.11:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.11:3306"
}

JS> cluster.status()

ReferenceError: cluster is not defined

JS> var cluster = dba.getCluster('pocCluster')

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.11:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.11:3306"

}

Failover

Le basculement niveau base de données (changement de primaire) est automatiquement géré par les membres du cluster entre eux.

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.11:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.11:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.11:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.11:3306"

}

Crash du noeud primaire (172.19.0.11)…

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.13:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.13:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.13:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.13:3306"

}

Nouveau primaire élu par le groupe : 172.19.0.13.

Et 172.19.0.11 est porté disparu (MIA).

Les données configuration cluster étant sauvegardées, une fois le nœud redémarré/réparé/restauré il fera automatiquement parti du cluster à nouveau. et il aura un rôle de secondaire.

En cas de configuration non persistante, un rejoinInstance() est nécessaire pour remettre le nœud dans le cluster. (voir paragraphe suivant Remettre un membre dans le groupe).

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.13:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.13:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.13:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.13:3306"

}

Remettre un membre dans le groupe

Nécessaire si la conf n’est pas persistante ou si la variable group_replication_start_on_boot = OFF.

Le nœud peut alors être remit dans le groupe avec la commande rejoinInstance() :

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}



JS> dba.checkInstanceConfiguration('root@172.19.0.13:3306')
Please provide the password for 'root@172.19.0.13:3306': 
Validating MySQL instance at 172.19.0.13:3306 for use in an InnoDB cluster...
 
This instance reports its own address as mysql_8.0_node3

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Note: verifyMyCnf option was not given so only dynamic configuration will be verified.
Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.13:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}



JS> cluster.rejoinInstance("root@172.19.0.13:3306", {ipWhitelist: "172.19.0.0/16"})
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Rejoining instance to the cluster ...

Please provide the password for 'root@172.19.0.13:3306': 
The instance '172.19.0.13:3306' was successfully rejoined on the cluster.



JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}

100

101

102

103

104

105

106

107

108

109

110

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

JS> dba.checkInstanceConfiguration('root@172.19.0.13:3306')

Please provide the password for 'root@172.19.0.13:3306':

Validating MySQL instance at 172.19.0.13:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_8.0_node3

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

Note: verifyMyCnf option was not given so only dynamic configuration will be verified.

Instance configuration is compatible with InnoDB cluster

The instance '172.19.0.13:3306' is valid for InnoDB cluster usage.

{

"status": "ok"

}

JS> cluster.rejoinInstance("root@172.19.0.13:3306", {ipWhitelist: "172.19.0.0/16"})

Rejoining the instance to the InnoDB cluster. Depending on the original

problem that made the instance unavailable, the rejoin operation might not be

successful and further manual steps will be needed to fix the underlying

problem.

Please monitor the output of the rejoin operation and take necessary action if

the instance cannot rejoin.

Rejoining instance to the cluster ...

Please provide the password for 'root@172.19.0.13:3306':

The instance '172.19.0.13:3306' was successfully rejoined on the cluster.

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

Supprimer une instance du groupe

Sans grande surprise, c’est la commande removeInstance

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}



JS> cluster.removeInstance("root@172.19.0.13:3306")
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

Attempting to leave from the Group Replication group...

The instance '172.19.0.13:3306' was successfully removed from the cluster.



JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

JS> cluster.removeInstance("root@172.19.0.13:3306")

The instance will be removed from the InnoDB cluster. Depending on the instance

being the Seed or not, the Metadata session might become invalid. If so, please

start a new session to the Metadata Storage R/W instance.

Attempting to leave from the Group Replication group...

The instance '172.19.0.13:3306' was successfully removed from the cluster.

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

L’instance n’est alors plus listée dans les méta-données :

JS> \sql
Switching to SQL mode... Commands end with ;


SQL> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;
+------------------+
| instance_name    |
+------------------+
| 172.19.0.11:3306 |
| 172.19.0.12:3306 |
+------------------+

JS> \sql

Switching to SQL mode... Commands end with ;

SQL> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;

+------------------+

| instance_name |

+------------------+

| 172.19.0.11:3306 |

| 172.19.0.12:3306 |

+------------------+

Pour la remettre dans le groupe, il faut donc rejouer le processus de l’ajout d’instance vu plus haut :

JS> cluster.checkInstanceState('root@172.19.0.13:3306')
Analyzing the instance replication state...
Please provide the password for 'root@172.19.0.13:3306': 

The instance 'root@172.19.0.13:3306' is valid for the cluster.
The instance is fully recoverable.

{
    "reason": "recoverable", 
    "state": "ok"
}



JS> cluster.addInstance("root@172.19.0.13:3306", {ipWhitelist: "172.19.0.0/16"})
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'root@172.19.0.13:3306': 
Validating instance at 172.19.0.13:3306...

This instance reports its own address as mysql_8.0_node3

Instance configuration is suitable.
The instance 'root@172.19.0.13:3306' was successfully added to the cluster.



JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}

JS> cluster.checkInstanceState('root@172.19.0.13:3306')

Analyzing the instance replication state...

Please provide the password for 'root@172.19.0.13:3306':

The instance 'root@172.19.0.13:3306' is valid for the cluster.

The instance is fully recoverable.

{

"reason": "recoverable",

"state": "ok"

}

JS> cluster.addInstance("root@172.19.0.13:3306", {ipWhitelist: "172.19.0.0/16"})

A new instance will be added to the InnoDB cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'root@172.19.0.13:3306':

Validating instance at 172.19.0.13:3306...

This instance reports its own address as mysql_8.0_node3

Instance configuration is suitable.

The instance 'root@172.19.0.13:3306' was successfully added to the cluster.

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

Perte du quorum

Si le cluster perd plus de la moitié de ses membres (crash ou split brain par exemple) il se retrouve dans un état assez désagréable, network partitioning, en clair il faut une intervention externe au cluster pour permettre aux membres restant de continuer à faire leur boulot.

Note: Par perte j’entend arrêt non prévu (crash). En cas d’arrêt normal ou propre, même si le cluster perd son quorum (dans ce cas présent arrêt normal de 2 nœuds), le nœud restant sait que les autres nœuds ne sont plus là (en clair pas de risque de split brain) donc le cluster continue de fonctionner. Mais c’est un cluster avec un seul nœud…

JS > cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 2 members are not active", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}

JS > cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

Dans notre cas, avec 3 instances, il faut en perdre 2 d’un coup :

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.12:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.12:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.12:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.12:3306"

}

Perte des nœuds (crash) 172.19.0.11 & 172.19.0.12… (Mayday, Mayday, Mayday!!!)

JS> cluster.status()
WARNING: Cluster has no quorum and cannot process write transactions: 2 out of 3 members of the InnoDB cluster are unreachable from the member we’re connected to, which is not sufficient for a quorum to be reached.
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.13:3306", 
        "ssl": "REQUIRED", 
        "status": "NO_QUORUM", 
        "statusText": "Cluster has no quorum as visible from '172.19.0.13:3306' and cannot process write transactions. 2 members are not active", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "UNREACHABLE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "UNREACHABLE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.13:3306"
}

JS> cluster.status()

WARNING: Cluster has no quorum and cannot process write transactions: 2 out of 3 members of the InnoDB cluster are unreachable from the member we’re connected to, which is not sufficient for a quorum to be reached.

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.13:3306",

"ssl": "REQUIRED",

"status": "NO_QUORUM",

"statusText": "Cluster has no quorum as visible from '172.19.0.13:3306' and cannot process write transactions. 2 members are not active",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "UNREACHABLE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "UNREACHABLE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.13:3306"

}

Le failover automatique ne peut pas s’enclencher, le nœud survivant (172.19.0.13) est bloqué.

Il faut donc intervenir :

$ mysqlsh --uri=root@172.19.0.13
Creating a Session to 'root@172.19.0.13'
...


JS> var cluster = dba.getCluster("pocCluster")
WARNING: Cluster has no quorum and cannot process write transactions: 2 out of 3 members of the InnoDB cluster are unreachable from the member we’re connected to, which is not sufficient for a quorum to be reached.



JS>  cluster.forceQuorumUsingPartitionOf('root@172.19.0.13:3306')
Restoring replicaset 'default' from loss of quorum, by using the partition composed of [172.19.0.13:3306]

Restoring the InnoDB cluster ...

Please provide the password for 'root@172.19.0.13:3306': 
The InnoDB cluster was successfully restored using the partition from the instance 'root@172.19.0.13:3306'.

WARNING: 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.



JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.13:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 2 members are not active", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.13:3306"
}

$ mysqlsh --uri=root@172.19.0.13

Creating a Session to 'root@172.19.0.13'

...

JS> var cluster = dba.getCluster("pocCluster")

JS> cluster.forceQuorumUsingPartitionOf('root@172.19.0.13:3306')

Restoring replicaset 'default' from loss of quorum, by using the partition composed of [172.19.0.13:3306]

Restoring the InnoDB cluster ...

Please provide the password for 'root@172.19.0.13:3306':

The InnoDB cluster was successfully restored using the partition from the instance 'root@172.19.0.13:3306'.

WARNING: 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.

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.13:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.13:3306"

}

Evidemment, sauf si tu es joueur 🙂 , il faut éviter de rester trop longtemps dans cet état.

Une fois les instances remisent en condition, il faut soit simplement les démarrer ou alors utiliser rejoinInstance() pour les remettre dans le cluster, en tant que secondaire.

JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.13:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.13:3306"
}

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.13:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.13:3306"

}

Repartir après un arrêt total du cluster

La perte du quorum est une chose, mais il y a pire, perdre tout les nœuds…

En cas d’arrêt total du cluster i.e. toutes les instances sont éteintes, il faut utiliser, une fois les instances MySQL de nouveau démarrées rebootClusterFromCompleteOutage() :

$ mysqlsh root@172.19.0.12:3306
...

JS > var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')
Reconfiguring the cluster 'pocCluster' from complete outage...

The instance '172.19.0.11:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance '172.19.0.13:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

Dba.rebootClusterFromCompleteOutage: The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. Please use the most up to date instance: '172.19.0.13:3306'. (RuntimeError)

$ mysqlsh root@172.19.0.12:3306

...

JS > var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')

Reconfiguring the cluster 'pocCluster' from complete outage...

The instance '172.19.0.11:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y/N]: y

The instance '172.19.0.13:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y/N]: y

Dba.rebootClusterFromCompleteOutage: The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. Please use the most up to date instance: '172.19.0.13:3306'. (RuntimeError)

Le reboot doit se faire sur l’instance la plus à jour (ici la machine 172.19.0.13) :

$ mysqlsh --uri=root@172.19.0.13
Creating a session to 'root@172.19.0.13'
...

JS> var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')
var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')
Reconfiguring the cluster 'pocCluster' from complete outage...

The instance '172.19.0.11:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance '172.19.0.12:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y


The cluster was successfully rebooted.



JS> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "172.19.0.13:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "172.19.0.11:3306": {
                "address": "172.19.0.11:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.12:3306": {
                "address": "172.19.0.12:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "172.19.0.13:3306": {
                "address": "172.19.0.13:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@172.19.0.13:3306"
}

$ mysqlsh --uri=root@172.19.0.13

Creating a session to 'root@172.19.0.13'

...

JS> var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')

var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')

Reconfiguring the cluster 'pocCluster' from complete outage...

The instance '172.19.0.11:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y/N]: y

The instance '172.19.0.12:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y/N]: y

The cluster was successfully rebooted.

JS> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "172.19.0.13:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"172.19.0.11:3306": {

"address": "172.19.0.11:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.12:3306": {

"address": "172.19.0.12:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"172.19.0.13:3306": {

"address": "172.19.0.13:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@172.19.0.13:3306"

}

Le membre sur lequel la commande est exécutée est le nouveau primaire.

Voilà c’est tout pour aujourd’hui 🙂

Dans la même thématique :

Video:

Déployer 1 cluster MySQL avec seulement 2 commandes

Tutoriel – Déployer MySQL 5.7 InnoDB Cluster

août 21, 2018

Tags: cluster, group replication, Haute disponibilité, InnoDB Cluster

Cet article remplace le précédent tuto : Tutoriel – Déployer MySQL innoDB Cluster.

Si tu utilises MySQL 8.0, alors lit plutôt ce tuto : Tutoriel – Déployer MySQL 8.0 InnoDB Cluster.

Je reçois pas mal de questions de clients et d’utilisateurs de MySQL 5.7, j’espère donc que ce post t’apportera l’essentiel des réponses et bonnes pratiques pour te permettre de déployer un cluster InnoDB avec MySQL 5.7.

De plus, les nouvelles versions de MySQL Router et de MySQL Shell amènent de sensibles améliorations qu’il faut que je te montre à tout prix 🙂

L’un des principaux besoins de mes clients est la Haute Disponibilité avec MySQL. On va voir, dans cet article, comment déployer et gérer un cluster MySQL 5.7 « from scratch » , sous la forme d’un tutoriel, grâce à la solution HA tout en un : MySQL (5.7) InnoDB Cluster.

Note: L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server (via le plugin Group Replication) avec pour moteur de stockage InnoDB, solution à ne pas confondre avec MySQL NDB Cluster (moteur de stockage NDB).

Le contexte

3 instances MySQL autonomes, <spoil altert> qui vont grâce au plugin MySQL Group Replication se transformer en une base de données distribuée.</spoil altert>

Instance MySQL 1 (mysql_5.7_node1) : 172.18.0.11; Nœud numéro 1 du cluster
Instance MySQL 2 (mysql_5.7_node2) : 172.18.0.12; Nœud numéro 2 du cluster
Instance MySQL 3 (mysql_5.7_node3) : 172.18.0.13; Nœud numéro 3 du cluster

1 instance applicative : 192.168.1.11; MySQL Router + mon application.

Note: J’utilise l’image Docker MySQL Server supportée par l’équipe MySQL d’Oracle.

Note: Je n’aborde pas dans cet article la redondance de MySQL Router. Plusieurs scénarios sont possibles, je te recommande de lire ça, ceci et cela.

MySQL Shell est installé sur toutes les instances.

En ce qui concerne les versions des logiciels, ce sont les plus récentes à ce jour (journée caniculaire du mois d’août 2018):

Note: Dans cet article j’utilise la dernière GA de MySQL 5.7. Je publierai un autre tuto avec MySQL 8.0. Cependant, en ce qu’il concerne MySQL Router et MySQL Shell, il faut TOUJOURS prendre la dernière version (branche 8.0).

Pour récapituler notre architecture, une image valant (au moins) 1000 mots, ça nous donne :

Vérifier la configuration des instances

Note: J’utiliser le compte utilisateur root pour configurer le cluster, cependant ce n’est pas une obligation. Il est effectivement possible de créer un compte utilisateur spécifique (ou plusieurs), avec les droits qui vont bien (accès total sur les tables des méta-données d’InnoDB Cluster + des droits d’administration de l’instance MySQL). Plus d’info ici (Paragraphe « User Privileges) ».

La vérification de la configuration se fait grâce à MySQL Shell et la méthode dba.checkInstanceConfiguration() :

$ mysqlsh --uri root@172.18.0.11
...
Server version: 5.7.23 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.12
...

JS> dba.checkInstanceConfiguration('root@172.18.0.11:3306')
Please provide the password for 'root@172.18.0.11:3306': 
Validating MySQL instance at 172.18.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as 51306ade1992
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Note: verifyMyCnf option was not given so only dynamic configuration will be verified.

Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency         | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                        | OFF           | ON             | Update read-only variable and restart the server |
| log_bin                          | 0             | 1              | Update read-only variable and restart the server |
| log_slave_updates                | 0             | ON             | Update read-only variable and restart the server |
| master_info_repository           | FILE          | TABLE          | Update read-only variable and restart the server |
| relay_log_info_repository        | FILE          | TABLE          | Update read-only variable and restart the server |
| server_id                        | 0             | <unique ID>    | Update read-only variable and restart the server |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update read-only variable and restart the server |
+----------------------------------+---------------+----------------+--------------------------------------------------+

The following variable needs to be changed, but cannot be done dynamically: 'log_bin'
Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update", 
            "current": "CRC32", 
            "option": "binlog_checksum", 
            "required": "NONE"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "log_bin", 
            "required": "1"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "log_slave_updates", 
            "required": "ON"
        },
        {
            "action": "restart", 
            "current": "FILE", 
            "option": "master_info_repository", 
            "required": "TABLE"
        },
        {
            "action": "restart", 
            "current": "FILE", 
            "option": "relay_log_info_repository", 
            "required": "TABLE"
        },
        {
            "action": "restart", 
            "current": "0", 
            "option": "server_id", 
            "required": "<unique ID>"
        },
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "transaction_write_set_extraction", 
            "required": "XXHASH64"
        }
    ], 
    "errors": [], 
    "status": "error"
}

$ mysqlsh --uri root@172.18.0.11

...

Server version: 5.7.23 MySQL Community Server (GPL)

No default schema selected; type \use <schema> to set one.

MySQL Shell 8.0.12

...

JS> dba.checkInstanceConfiguration('root@172.18.0.11:3306')

Please provide the password for 'root@172.18.0.11:3306':

Validating MySQL instance at 172.18.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as 51306ade1992

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

Note: verifyMyCnf option was not given so only dynamic configuration will be verified.

Some configuration options need to be fixed:

+----------------------------------+---------------+----------------+--------------------------------------------------+

+----------------------------------+---------------+----------------+--------------------------------------------------+

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

| log_bin | 0 | 1 | Update read-only variable and restart the server |

| log_slave_updates | 0 | ON | Update read-only variable and restart the server |

+----------------------------------+---------------+----------------+--------------------------------------------------+

The following variable needs to be changed, but cannot be done dynamically: 'log_bin'

Please use the dba.configureInstance() command to repair these issues.

{

"config_errors": [

{

"action": "server_update",

"current": "CRC32",

"option": "binlog_checksum",

"required": "NONE"

{

"action": "restart",

"current": "OFF",

"option": "enforce_gtid_consistency",

"required": "ON"

{

"action": "restart",

"current": "OFF",

"option": "gtid_mode",

"required": "ON"

{

"action": "restart",

"current": "0",

"option": "log_bin",

"required": "1"

{

"action": "restart",

"current": "0",

"option": "log_slave_updates",

"required": "ON"

{

"action": "restart",

"current": "FILE",

"option": "master_info_repository",

"required": "TABLE"

{

"action": "restart",

"current": "FILE",

"option": "relay_log_info_repository",

"required": "TABLE"

{

"action": "restart",

"current": "0",

"option": "server_id",

"required": "<unique ID>"

{

"action": "restart",

"current": "OFF",

"option": "transaction_write_set_extraction",

"required": "XXHASH64"

}

"errors": [],

"status": "error"

}

Dans mon cas, avec l’installation de MySQL 5.7 par défaut sous Ubuntu (avec l’image Docker), niveau configuration… bah j’ai tout à faire 🙂

La méthode renvoie un document JSON (pratique pour l’automatisation) avec la liste des tâches à effectuer pour être conforme… Configurons donc !

J’ai deux solutions :

1/ je prépare mes instances « manuellement » (cette tâche peut bien évidemment s’automatiser e.g. Ansible, Puppet, Chef, …) comme expliqué dans l’article comment configurer un groupe.
2/ je me connecte à chaque instance en local, et j’utilise la méthode : configureLocalInstance()

Et ensuite je ne dois pas oublier de redémarrer les instances 🙂

Soyons fou ! allons y pour la méthode 2 :

JS> dba.configureLocalInstance('root@172.18.0.11:3306')
Please provide the password for 'root@172.18.0.11:3306': 
Configuring MySQL instance at 172.18.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_5.7_node1
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency         | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                        | OFF           | ON             | Update read-only variable and restart the server |
| log_bin                          | 0             | 1              | Update read-only variable and restart the server |
| log_slave_updates                | 0             | ON             | Update read-only variable and restart the server |
| master_info_repository           | FILE          | TABLE          | Update read-only variable and restart the server |
| relay_log_info_repository        | FILE          | TABLE          | Update read-only variable and restart the server |
| server_id                        | 0             | <unique ID>    | Update read-only variable and restart the server |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update read-only variable and restart the server |
+----------------------------------+---------------+----------------+--------------------------------------------------+

The following variable needs to be changed, but cannot be done dynamically: 'log_bin'
WARNING: Cannot update configuration file for a remote target instance.
ERROR: Unable to change MySQL configuration.
MySQL server configuration needs to be updated, but neither remote nor local configuration is possible.
Please run this command locally, in the same host as the MySQL server being configured, and pass the path to its configuration file through the mycnfPath option.
Dba.configureLocalInstance: Unable to update configuration (RuntimeError)

JS> dba.configureLocalInstance('root@172.18.0.11:3306')

Please provide the password for 'root@172.18.0.11:3306':

Configuring MySQL instance at 172.18.0.11:3306 for use in an InnoDB cluster...

This instance reports its own address as mysql_5.7_node1