Tutoriel – Déployer MySQL innoDB Cluster

mai 11, 2017

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

L’ article que tu t’apprêtes à lire est maintenant obsolète 🙁 Ohhhhhh !

Mais rassure toi, il est remplacé par un nouvel article, tout nouveau, tout beau et surtout plus complet et à jour 🙂 Ahhhhh !!

C’est par ici.

Dans les épisodes précédents on a vu comment déployer « manuellement » MySQL Group Replication, comprendre et tester MySQL InnoDB Cluster ainsi que comment gérer aisément un cluster Group Replication déja déployé avec MySQL Shell.

Aujourd’hui, dans la série Haute Disponibilité avec MySQL on va voir comment déployer et gérer un cluster from scratch , sous la forme d’un tutoriel, grâce à la solution tout en un : MySQL 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 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 : 192.168.1.22; Nœud numéro 1 du cluster
Instance MySQL 2 : 192.168.1.50; Nœud numéro 2 du cluster
Instance MySQL 3 : 192.168.1.48; Nœud numéro 3 du cluster

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

MySQL Shell est installé sur toutes les instances.

En ce qui concerne les versions des logiciels:

—
Update 08-08-2018

Note: TOUJOURS prendre la dernière version du Router et du Shell. C’est actuellement la branche 8.0.x qui est bien évidemment compatible avec MySQL 5.7 InnoDB Cluster.

ex: MySQL Router 8.0.12 + MySQL Shell 8.0.12 avec MySQL 5.7.23
—

Avec un schéma sa donne (à peu près) ça :

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.

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

$ mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.



mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')
Please provide the password for 'root@192.168.1.22:3306': 
Validating instance...

The instance '192.168.1.22:3306' is not valid for Cluster usage.

The following issues were encountered:

 - Some configuration options need to be fixed.

+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable or restart the server |
| enforce_gtid_consistency         | OFF           | ON             | Restart the server                               |
| gtid_mode                        | OFF           | ON             | Restart the server                               |
| log_bin                          | 0             | 1              | Restart the server                               |
| log_slave_updates                | 0             | ON             | Restart the server                               |
| master_info_repository           | FILE          | TABLE          | Restart the server                               |
| relay_log_info_repository        | FILE          | TABLE          | Restart the server                               |
| transaction_write_set_extraction | OFF           | XXHASH64       | Restart the server                               |
+----------------------------------+---------------+----------------+--------------------------------------------------+


Please fix these issues , restart the serverand try again.

{
    "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": "OFF", 
            "option": "transaction_write_set_extraction", 
            "required": "XXHASH64"
        }
    ], 
    "errors": [], 
    "restart_required": true, 
    "status": "error"
}

$ mysqlsh

Welcome to MySQL Shell 1.0.9

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

affiliates. Other names may be trademarks of their respective

owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')

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

Validating instance...

The instance '192.168.1.22:3306' is not valid for Cluster usage.

The following issues were encountered:

- Some configuration options need to be fixed.

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

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

| enforce_gtid_consistency | OFF | ON | Restart the server |

| gtid_mode | OFF | ON | Restart the server |

| log_bin | 0 | 1 | Restart the server |

| log_slave_updates | 0 | ON | Restart the server |

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

Please fix these issues , restart the serverand try again.

{

"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": "OFF",

"option": "transaction_write_set_extraction",

"required": "XXHASH64"

}

"errors": [],

"restart_required": true,

"status": "error"

}

Dans mon cas, avec l’installation par défaut sous Ubuntu, niveau configuration 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) comme expliqué dans l’article comment configurer un groupe.
2/ je me connecte à chaque instance en local, et j’utilise la méthode : configureLocalInstance()

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

mysql-js> dba.configureLocalInstance('root@192.168.1.22:3306')
Dba.configureLocalInstance: This function only works with local instances (RuntimeError)

1 2	mysql-js> dba.configureLocalInstance('root@192.168.1.22:3306') Dba.configureLocalInstance: This function only works with local instances (RuntimeError)

Ouppss!!! dba.configureLocalInstance ne fonctionne qu’en local, c’est-à-dire, si je suis connecté sur la machine hôte de l’instance MySQL (ce qui est une bonne chose). Du coup après m’être connecté à l’hôte 192.168.1.22 :

sudo mysqlsh
Welcome to MySQL Shell 1.0.9
...

mysql-js> dba.configureLocalInstance('root@localhost:3306')
Please provide the password for 'root@localhost:3306': 

Detecting the configuration file...
Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf
Do you want to modify this file? [Y|n]: Y
Validating instance...

The configuration has been updated but it is required to restart the server.

{
    "config_errors": [
        {
            "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": "OFF", 
            "option": "transaction_write_set_extraction", 
            "required": "XXHASH64"
        }
    ], 
    "errors": [], 
    "restart_required": true, 
    "status": "error"
}

sudo mysqlsh

Welcome to MySQL Shell 1.0.9

...

mysql-js> dba.configureLocalInstance('root@localhost:3306')

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

Detecting the configuration file...

Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf

Do you want to modify this file? [Y|n]: Y

Validating instance...

The configuration has been updated but it is required to restart the server.

{

"config_errors": [

{

"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": "OFF",

"option": "transaction_write_set_extraction",

"required": "XXHASH64"

}

"errors": [],

"restart_required": true,

"status": "error"

}

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

Les informations ajoutées dans le fichier de configuration se trouvent en fin de fichier :

# cat /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
... <data previously in the file> ...

log_slave_updates = ON
server_id = 2323701546
relay_log_info_repository = TABLE
master_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
binlog_format = ROW
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port = 3306
binlog_checksum = NONE
enforce_gtid_consistency = ON
log_bin
gtid_mode = ON

# cat /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

... <data previously in the file> ...

log_slave_updates = ON

server_id = 2323701546

relay_log_info_repository = TABLE

master_info_repository = TABLE

transaction_write_set_extraction = XXHASH64

binlog_format = ROW

disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

report_port = 3306

binlog_checksum = NONE

enforce_gtid_consistency = ON

log_bin

gtid_mode = ON

192.168.1.22 est configurée. Après redémarrage de l’instance MySQL, la sortie de checkInstanceConfiguration est beaucoup moins anxiogène :

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')
Please provide the password for 'root@192.168.1.22:3306': 
Validating instance...

The instance '192.168.1.22:3306' is valid for Cluster usage
{
    "status": "ok"
}

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.22:3306')

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

Validating instance...

The instance '192.168.1.22:3306' is valid for Cluster usage

{

"status": "ok"

}

OK ! Le membre est prêt pour faire parti d’un groupe.

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

…

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Validating instance...

The instance '192.168.1.50:3306' is valid for Cluster usage
{
    "status": "ok"
}
mysql-js> dba.checkInstanceConfiguration('root@192.168.1.48:3306')
Please provide the password for 'root@192.168.1.48:3306': 
Validating instance...

The instance '192.168.1.48:3306' is valid for Cluster usage
{
    "status": "ok"
}

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.50:3306')

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

Validating instance...

The instance '192.168.1.50:3306' is valid for Cluster usage

{

"status": "ok"

}

mysql-js> dba.checkInstanceConfiguration('root@192.168.1.48:3306')

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

Validating instance...

The instance '192.168.1.48:3306' is valid for Cluster usage

{

"status": "ok"

}

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 192.168.1.22, elle va permettre de bootstrapper le cluster:

mysql-js> \c root@192.168.1.22
Creating a Session to 'root@192.168.1.22'
Enter password: 
Classic Session successfully established. No default schema selected.


mysql-js> var cluster = dba.createCluster('pocCluster', {ipWhitelist: "192.168.1.0/16"});
A new InnoDB cluster will be created on instance 'root@192.168.1.22:3306'.

Creating InnoDB cluster 'pocCluster' on 'root@192.168.1.22: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.

mysql-js> \c root@192.168.1.22

Creating a Session to 'root@192.168.1.22'

Enter password:

Classic Session successfully established. No default schema selected.

mysql-js> var cluster = dba.createCluster('pocCluster', {ipWhitelist: "192.168.1.0/16"});

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

Creating InnoDB cluster 'pocCluster' on 'root@192.168.1.22: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() :

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

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

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.
The instance is new to Group Replication.

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


mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.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.

Please provide the password for 'root@192.168.1.50:3306': 
Adding instance to the cluster ...

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


mysql-js> cluster.status();
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

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

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.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.

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

Adding instance to the cluster ...

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

mysql-js> cluster.status();

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

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

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

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

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

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

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

Analyzing the instance replication state...

The instance '192.168.1.50: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

mysql-js> cluster.checkInstanceState('root@192.168.1.48:3306')
Please provide the password for 'root@192.168.1.48:3306': 
Analyzing the instance replication state...

The instance '192.168.1.48:3306' is valid for the cluster.
The instance is new to Group Replication.

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


mysql-js> cluster.addInstance("root@192.168.1.48:3306", {ipWhitelist: "192.168.1.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.

Please provide the password for 'root@192.168.1.48:3306': 
Adding instance to the cluster ...

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


mysql-js> cluster.status();
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.checkInstanceState('root@192.168.1.48:3306')

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

Analyzing the instance replication state...

The instance '192.168.1.48:3306' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

mysql-js> cluster.addInstance("root@192.168.1.48:3306", {ipWhitelist: "192.168.1.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.

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

Adding instance to the cluster ...

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

mysql-js> cluster.status();

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Et voilà!

Cluster MySQL Group Replication de 3 nœuds déployé grâce à MySQL Shell !

Nœud 1 : 192.168.1.22 : Primaire (lecture/écriture)
Nœud 2 : 192.168.1.50 : Secondaire (lecture seule)
Nœud 3 : 192.168.1.48 : Secondaire (lecture seule)

Pour rendre la configuration persistante ie l’écrire dans le fichier de configuration, il faut exécuter, après que le noeud soit configuré, la méthode dba.configureLocalInstance() :

daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/mysql.conf.d/mysqld.cnf
0


daz@192.168.1.50:~$ sudo mysqlsh --uri=root@localhost
Creating a Session to 'root@localhost'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.9
...


mysql-js> dba.configureLocalInstance("root@localhost:3306")  
Please provide the password for 'root@localhost:3306': 

Detecting the configuration file...
Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf
Do you want to modify this file? [Y|n]: Y
Validating instance...

The instance 'localhost:3306' is valid for Cluster usage
You can now use it in an InnoDB Cluster.

{
    "status": "ok"
}



daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/my.cnf
32

daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/mysql.conf.d/mysqld.cnf

daz@192.168.1.50:~$ sudo mysqlsh --uri=root@localhost

Creating a Session to 'root@localhost'

Enter password:

Classic Session successfully established. No default schema selected.

Welcome to MySQL Shell 1.0.9

...

mysql-js> dba.configureLocalInstance("root@localhost:3306")

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

Detecting the configuration file...

Found configuration file at standard location: /etc/mysql/mysql.conf.d/mysqld.cnf

Do you want to modify this file? [Y|n]: Y

Validating instance...

The instance 'localhost:3306' is valid for Cluster usage

You can now use it in an InnoDB Cluster.

{

"status": "ok"

}

daz@192.168.1.50:~$ grep -c group_replication /etc/mysql/my.cnf

A noter que cette opération ne peut se faire qu’en local.

La suite ?

Configuration de MySQL Router

Les recommandations de MySQL sont d’installer MySQL Router sur la machine hôte de l’application, je vais donc 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

daz@192.168.1.11 ~ $ mysqlrouter --bootstrap root@192.168.1.22:3306 --directory RouterPoC
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at /home/daz/RouterPoC...
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:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'pocCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

daz@192.168.1.11 ~ $ mysqlrouter --bootstrap root@192.168.1.22:3306 --directory RouterPoC

Please enter MySQL password for root:

Bootstrapping MySQL Router instance at /home/daz/RouterPoC...

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:6446

- Read/Only Connections: localhost:6447

X protocol connections to cluster 'pocCluster':

- Read/Write Connections: localhost:64460

- Read/Only Connections: localhost:64470

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

6446 : lectures / écritures pour le noeud primaire
6447 : lectures seules pour les nœuds secondaires (Round-Robin)
Et le pendant pour les connexions avec le protocole X (64460 & 64470), pour une utilisation NoSQL Document Store de MySQL.

Le fichier de configuration du Router contient quelques informations importantes, tel que le(s) port(s) à utiliser par l’application :

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

[logger]
level = INFO

[metadata_cache:pocCluster]
router_id=3
bootstrap_server_addresses=mysql://192.168.1.22:3306,mysql://192.168.1.50:3306,mysql://192.168.1.48:3306
user=mysql_router3_qeteek4wi41s
metadata_cluster=pocCluster
ttl=300

[routing:pocCluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://pocCluster/default?role=PRIMARY
mode=read-write
protocol=classic

[routing:pocCluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://pocCluster/default?role=SECONDARY
mode=read-only
protocol=classic

[routing:pocCluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://pocCluster/default?role=PRIMARY
mode=read-write
protocol=x

[routing:pocCluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://pocCluster/default?role=SECONDARY
mode=read-only
protocol=x

daz@192.168.1.11 ~ $ cat ~/RouterPoC/mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap

[DEFAULT]

logging_folder=/home/daz/RouterPoC/log

runtime_folder=/home/daz/RouterPoC/run

data_folder=/home/daz/RouterPoC/data

keyring_path=/home/daz/RouterPoC/data/keyring

master_key_path=/home/daz/RouterPoC/mysqlrouter.key

[logger]

level = INFO

[metadata_cache:pocCluster]

router_id=3

bootstrap_server_addresses=mysql://192.168.1.22:3306,mysql://192.168.1.50:3306,mysql://192.168.1.48:3306

user=mysql_router3_qeteek4wi41s

metadata_cluster=pocCluster

ttl=300

[routing:pocCluster_default_rw]

bind_address=0.0.0.0

bind_port=6446

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

mode=read-write

protocol=classic

[routing:pocCluster_default_ro]

bind_address=0.0.0.0

bind_port=6447

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

mode=read-only

protocol=classic

[routing:pocCluster_default_x_rw]

bind_address=0.0.0.0

bind_port=64460

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

mode=read-write

protocol=x

[routing:pocCluster_default_x_ro]

bind_address=0.0.0.0

bind_port=64470

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

mode=read-only

protocol=x

Il est évidemment possible de modifier ce fichier. Par exemple, souvent les applications se connectent au port 3306, ça peut donc avoir du sens de modifier le port du noeud primaire en le passant de 6446 à 3306 :

daz@192.168.1.11 ~ $ grep -A6 pocCluster_default_rw ~/RouterPoC/mysqlrouter.conf
[routing:pocCluster_default_rw]
bind_address=0.0.0.0
bind_port=3306
destinations=metadata-cache://pocCluster/default?role=PRIMARY
mode=read-write
protocol=classic

daz@192.168.1.11 ~ $ grep -A6 pocCluster_default_rw ~/RouterPoC/mysqlrouter.conf

[routing:pocCluster_default_rw]

bind_address=0.0.0.0

bind_port=3306

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

mode=read-write

protocol=classic

Ton application va donc (continuer à) se connecter au port 3306, sauf que maintenant ce n’est pas 1 instance, mais bel et bien 3 instances qui sont en backend, et ceci en toute transparence.

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

daz@192.168.1.11 ~ $ ~/RouterPoC/start.sh

1	daz@192.168.1.11 ~ $ ~/RouterPoC/start.sh

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

daz@192.168.1.11 ~ $ ~/RouterPoC/stop.sh

1	daz@192.168.1.11 ~ $ ~/RouterPoC/stop.sh

Gestion des nœuds

Quelques commandes qui vont te simplifier la vie…

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 :

mysql> SHOW SCHEMAS;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0,00 sec)


mysql> SHOW TABLES IN mysql_innodb_cluster_metadata;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| routers                                 |
| schema_version                          |
+-----------------------------------------+
6 rows in set (0,01 sec)


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

mysql> SHOW SCHEMAS;

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

| Database |

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

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

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

5 rows in set (0,00 sec)

mysql> SHOW TABLES IN mysql_innodb_cluster_metadata;

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

| Tables_in_mysql_innodb_cluster_metadata |

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

| clusters |

| hosts |

| instances |

| replicasets |

| routers |

| schema_version |

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

6 rows in set (0,01 sec)

mysql> SELECT cluster_name FROM mysql_innodb_cluster_metadata.clusters;

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

| cluster_name |

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

| pocCluster |

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

Pour récupérer ces informations dans une nouvelle session il faut utiliser la méthode getCluster :

mysql-js> cluster.status()
ReferenceError: cluster is not defined


mysql-js> var cluster = dba.getCluster('pocCluster')


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

ReferenceError: cluster is not defined

mysql-js> var cluster = dba.getCluster('pocCluster')

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Failover

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

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Crash du noeud primaire (192.168.1.22)…

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Nouveau primaire élu par le groupe : 192.168.1.48.

Et 192.168.1.22 est porté disparu (MIA).

Un rejoinInstance() est nécessaire pour remettre le membre dans le cluster. Il aura un rôle de secondaire (voir paragraphe suivant Remettre un membre dans le groupe).

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Remettre un membre dans le groupe

Un membre avec son instance MySQL qui tourne et qui est correctement configuré peut être remis (après un redémarrage de l’instance par exemple) dans le groupe avec la commande rejoinInstance() :

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }
}


mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.
The instance is fully recoverable.

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


mysql-js> cluster.rejoinInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.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.

Please provide the password for 'root@192.168.1.50:3306': 
Rejoining instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully rejoined on the cluster.

The instance '192.168.1.50:3306' was successfully added to the MySQL Cluster.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

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

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.

The instance is fully recoverable.

{

"reason": "recoverable",

"state": "ok"

}

mysql-js> cluster.rejoinInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.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.

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

Rejoining instance to the cluster ...

The instance 'root@192.168.1.50:3306' was successfully rejoined on the cluster.

The instance '192.168.1.50:3306' was successfully added to the MySQL Cluster.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Supprimer une instance du groupe

Sans grande surprise, c’est la commande removeInstance

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}


mysql-js> cluster.removeInstance("root@192.168.1.50: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.

The instance 'root@192.168.1.50:3306' was successfully removed from the cluster.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

mysql-js> cluster.removeInstance("root@192.168.1.50: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.

The instance 'root@192.168.1.50:3306' was successfully removed from the cluster.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

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

mysql-js> \sql
Switching to SQL mode... Commands end with ;


mysql-sql> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;
+-------------------+
| instance_name     |
+-------------------+
| 192.168.1.22:3306 |
| 192.168.1.48:3306 |
+-------------------+
2 rows in set (0.00 sec)

mysql-js> \sql

Switching to SQL mode... Commands end with ;

mysql-sql> SELECT instance_name FROM mysql_innodb_cluster_metadata.instances;

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

| instance_name |

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

| 192.168.1.22:3306 |

| 192.168.1.48:3306 |

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

2 rows in set (0.00 sec)

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

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')
Please provide the password for 'root@192.168.1.50:3306': 
Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.
The instance is fully recoverable.

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


mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.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.

Please provide the password for 'root@192.168.1.50:3306': 
Adding instance to the cluster ...

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


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.checkInstanceState('root@192.168.1.50:3306')

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

Analyzing the instance replication state...

The instance '192.168.1.50:3306' is valid for the cluster.

The instance is fully recoverable.

{

"reason": "recoverable",

"state": "ok"

}

mysql-js> cluster.addInstance("root@192.168.1.50:3306", {ipWhitelist: "192.168.1.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.

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

Adding instance to the cluster ...

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

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Perte du quorum

Si le cluster perd plus de la moitié de ses membres il se retrouve dans un état assez désagréable, network partitioning, en clair il faut une intervention externe au groupe pour permettre aux membres restant de continuer à faire leur boulot de serveur MySQL.

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

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Perte des nœuds (crash) 192.168.1.22 & 192.168.1.50… (Mayday, Mayday, Mayday!!!)

mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.22:3306", 
        "status": "NO_QUORUM", 
        "statusText": "Cluster has no quorum as visible from 'localhost:3306' and cannot process write transactions. 2 members are not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "UNREACHABLE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "UNREACHABLE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.22:3306",

"status": "NO_QUORUM",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "UNREACHABLE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "UNREACHABLE"

}

Le failover automatique ne peut pas s’enclencher, le membre survivant (192.168.1.48) est en lecture seule. Il faut donc intervenir :

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

mysql-js> var cluster = dba.getCluster("pocCluster")
WARNING: The session is on a Read Only instance.
         Write operations on the InnoDB cluster will not be allowed


mysql-js> cluster.forceQuorumUsingPartitionOf('192.168.1.48:3306')
Restoring replicaset 'default' from loss of quorum, by using the partition composed of [192.168.1.48:3306]

Please provide the password for 'root@192.168.1.48:3306': 
Restoring the InnoDB cluster ...

The InnoDB cluster was successfully restored using the partition from the instance 'root@192.168.1.48: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.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 2 members are not active", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }
}

$ mysqlsh --uri=root@192.168.1.48

Creating a Session to 'root@192.168.1.48'

...

mysql-js> var cluster = dba.getCluster("pocCluster")

WARNING: The session is on a Read Only instance.

Write operations on the InnoDB cluster will not be allowed

mysql-js> cluster.forceQuorumUsingPartitionOf('192.168.1.48:3306')

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

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

Restoring the InnoDB cluster ...

The InnoDB cluster was successfully restored using the partition from the instance 'root@192.168.1.48: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.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "(MISSING)"

}

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

Une fois les instances en condition, il faut utiliser rejoinInstance() pour remettre les membres dans le cluster, en tant que secondaire (voir paragraphe Remettre un membre dans le groupe).

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 --uri=root@192.168.1.48
Creating a Session to 'root@mysql_node1'
...

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

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

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


The cluster was successfully rebooted.


mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.48:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.22:3306": {
                "address": "192.168.1.22:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.48:3306": {
                "address": "192.168.1.48:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.50:3306": {
                "address": "192.168.1.50:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

$ mysqlsh --uri=root@192.168.1.48

Creating a Session to 'root@mysql_node1'

...

mysql-js> var cluster = dba.rebootClusterFromCompleteOutage('pocCluster')

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

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

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

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

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

The cluster was successfully rebooted.

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "192.168.1.48:3306",

"status": "OK",

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

"topology": {

"192.168.1.22:3306": {

"address": "192.168.1.22:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.48:3306": {

"address": "192.168.1.48:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"192.168.1.50:3306": {

"address": "192.168.1.50:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

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

Dans la même thématique :

Thanks for using MySQL!

Filed under:
Group Replication, Haute Disponibilité, InnoDB Cluster, MySQL, tuto by Olivier DASINI

Slides du meetup « Mise en bouche PHP »

mai 3, 2017

Tags: meetup

Mardi soir, j’ai eu le plaisir d’échanger avec la communauté PHP parisienne et de présenter MySQL InnoDB Cluster lors du meetup « Mise en bouche PHP » organisé par l’AFUP Paris et sponsorisé par Oracle MySQL.

Voici les slides

MySQL InnoDB Cluster – Meetup Oracle MySQL / AFUP Paris from Olivier DASINI

Prochain rendez-vous, le 18 mai pour le PHP Tour 2017 à Nantes.

Thanks for using MySQL!

Commentaires fermés

Filed under:
Conférence, Group Replication, Haute Disponibilité, InnoDB Cluster, Meetup, PHP, Présentation by Olivier DASINI

Mes confs MySQL pour avril et mai 2017

avril 24, 2017

J’aurai le plaisir de vous rencontrer (et accessoirement de parler de MySQL) les 25 avril, 2 et 18 mai.

MySQL Day Paris

Toutes l’info MySQL par l’équipe MySQL France

Info et inscription

Mise en bouche PHP – Paris

Meetup organisé par l’antenne AFUP Paris

Info et inscription

PHP tour 2017 – Nantes

La prochaine étape du cycle de conférences itinérant, réunissant toutes les communautés PHP se déroule à Nantes

Info et inscription

Commentaires fermés

Filed under:
Conférence, InnoDB Cluster, MySQL, PHP by Olivier DASINI

Adopte un… cluster MySQL Group Replication

avril 10, 2017

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

Autant le dire tout de suite, rien avoir avec un site internet de rencontre en ligne! 🙂
C’est bel et bien un nouvel article dans la série, Haute Disponibilité avec MySQL.

Au menu d’aujourd’hui : comment passer de l’administration « manuelle » de votre solution HA MySQL Group Replication à une administration plus simple, plus fun mais surtout facilement automatisable avec le pack MySQL InnoDB Cluster. En clair, on va voir comment utiliser MySQL Shell pour l’administration et l’orchestration du cluster et MySQL Router pour rediriger automatiquement les transactions de l’application vers le noeud primaire du cluster.

Quelques pré-requis sont nécessaire pour optimiser ta compréhension de cet article, je te conseille donc la lecture préalable des articles suivants:

Note:
L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server, solution à ne pas confondre avec MySQL NDB Cluster.

Le contexte

Pour ce PoC, j’ai un cluster MySQL Group Replication de 3 nœuds, fonctionnel, en mode « Single Primary » (déployé avec Docker Compose):

Instance 1 : mysql_node1 (172.19.0.2)
Instance 2 : mysql_node2 (172.19.0.4)
Instance 3 : mysql_node3 (172.19.0.3)

$ docker inspect mysql_node1 | grep IPAddress | tail -1
                    "IPAddress": "172.19.0.2",
$ docker inspect mysql_node2 | grep IPAddress | tail -1
                    "IPAddress": "172.19.0.4",
$ docker inspect mysql_node3 | grep IPAddress | tail -1
                    "IPAddress": "172.19.0.3",

$ docker inspect mysql_node1 | grep IPAddress | tail -1

"IPAddress": "172.19.0.2",

$ docker inspect mysql_node2 | grep IPAddress | tail -1

"IPAddress": "172.19.0.4",

$ docker inspect mysql_node3 | grep IPAddress | tail -1

"IPAddress": "172.19.0.3",

MySQL Router et mon application (simulée avec le client texte mysql) sont sur la machine host (par commodité). C’est également le cas de MySQL Shell.

En ce qui concerne les versions des softs:

MySQL Server 5.7.17
MySQL Router 2.1.2 rc
MySQL Shell 1.0.8-rc

Docker 1.12.6 & Docker-compose 1.11.2. Docker est hors du cadre de cet article, mais tu trouveras à la fin de cet article le fichier docker-compose.yml utilisé.

Ah oui, j’ai failli oublier :

TL;DR
Tu as un cluster MySQL Group Replication configuré/administré manuellement et qui tourne. Tu peux l’administrer / le configurer avec MySQL Shell et gérer le routage des requêtes applicatives avec MySQL Router, ces 3 composants forment MySQL InnoDB Cluster.

MySQL Group Replication

Les étapes de déploiement du cluster Group Replication ont déjà été traitées ici.

Voici mes 3 instances MySQL 5.7

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS                     NAMES
f4c4aa2d3726        mysql:5.7           "docker-entrypoint.sh"   About a minute ago   Up About a minute   0.0.0.0:14002->3306/tcp   mysql_node2
2304f0e44d4c        mysql:5.7           "docker-entrypoint.sh"   About a minute ago   Up About a minute   0.0.0.0:14003->3306/tcp   mysql_node3
fb6ae3c76a06        mysql:5.7           "docker-entrypoint.sh"   About a minute ago   Up About a minute   0.0.0.0:14001->3306/tcp   mysql_node1

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

f4c4aa2d3726 mysql:5.7 "docker-entrypoint.sh" About a minute ago Up About a minute 0.0.0.0:14002->3306/tcp mysql_node2

2304f0e44d4c mysql:5.7 "docker-entrypoint.sh" About a minute ago Up About a minute 0.0.0.0:14003->3306/tcp mysql_node3

fb6ae3c76a06 mysql:5.7 "docker-entrypoint.sh" About a minute ago Up About a minute 0.0.0.0:14001->3306/tcp mysql_node1

MySQL 5.7.17 plus précisément.

 $ docker exec -it mysql_node1 mysql -uroot -p -e"SELECT version();"
Enter password: 
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+

$ docker exec -it mysql_node1 mysql -uroot -p -e"SELECT version();"

Enter password:

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

| version() |

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

| 5.7.17-log |

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

A quoi ressemble mon cluster Group Replication ?

Je peux avoir la description de l’architecture avec la table performance_schema.replication_group_members :

docker exec -it mysql_node1 mysql -uroot -p -e"SELECT * FROM performance_schema.replication_group_members\G" 
Enter password: 
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d300be14-1797-11e7-a22e-0242ac130002
 MEMBER_HOST: mysql_node1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d3100569-1797-11e7-a278-0242ac130004
 MEMBER_HOST: mysql_node2
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d321b2db-1797-11e7-a16f-0242ac130003
 MEMBER_HOST: mysql_node3
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

docker exec -it mysql_node1 mysql -uroot -p -e"SELECT * FROM performance_schema.replication_group_members\G"

Enter password:

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d300be14-1797-11e7-a22e-0242ac130002

MEMBER_HOST: mysql_node1

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d3100569-1797-11e7-a278-0242ac130004

MEMBER_HOST: mysql_node2

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d321b2db-1797-11e7-a16f-0242ac130003

MEMBER_HOST: mysql_node3

MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

L’identification du noeud primaire peut se faire de la manière suivante :

$ docker exec -it mysql_node1 mysql -uroot -p -e"SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE) WHERE VARIABLE_NAME='group_replication_primary_member'\G" 
Enter password: 
*************************** 1. row ***************************
   MEMBER_ID: 8b5bad71-1720-11e7-94f0-0242ac130002
 MEMBER_HOST: mysql_node1
MEMBER_STATE: ONLINE

$ docker exec -it mysql_node1 mysql -uroot -p -e"SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE) WHERE VARIABLE_NAME='group_replication_primary_member'\G"

Enter password:

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

MEMBER_ID: 8b5bad71-1720-11e7-94f0-0242ac130002

MEMBER_HOST: mysql_node1

MEMBER_STATE: ONLINE

Le noeud mysql_node1 est donc en mode lecture écriture aka le noeud primaire (cette info nous sera utile pour la suite) et les 2 autres en lecture seule (super read only activé):

$ docker exec -it mysql_node2 mysql -uroot -p -e"CREATE SCHEMA gr_test"
Enter password: 
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

$ docker exec -it mysql_node2 mysql -uroot -p -e"CREATE SCHEMA gr_test"

Enter password:

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

$ docker exec -it mysql_node1 mysql -uroot -p -e"CREATE SCHEMA gr_test;"
Enter password: 

$ docker exec -it mysql_node2 mysql -uroot -p -e"SHOW SCHEMAS;"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gr_test            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

$ docker exec -it mysql_node1 mysql -uroot -p -e"CREATE SCHEMA gr_test;"

Enter password:

$ docker exec -it mysql_node2 mysql -uroot -p -e"SHOW SCHEMAS;"

Enter password:

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

| Database |

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

| information_schema |

| gr_test |

| mysql |

| performance_schema |

| sys |

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

On a donc un cluster MySQL Group Replication avec 3 nœuds online.
Le membre mysql_node1 est (pour le moment) le primaire, mysql_node2 et mysql_node3 sont les secondaires.

L’étape suivant consistera à gérer le cluster avec MySQL Shell.

MySQL Shell, interface pour gérer son cluster

On va se connecter avec le client MySQL Shell au noeud primaire :

$ # Connect to the primary node : mysql_node1
$ mysqlsh --uri=root@mysql_node1
Creating a Session to 'root@mysql_node1'super read only
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.8-rc

Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js>

$ # Connect to the primary node : mysql_node1

$ mysqlsh --uri=root@mysql_node1

Creating a Session to 'root@mysql_node1'super read only

Enter password:

Classic Session successfully established. No default schema selected.

Welcome to MySQL Shell 1.0.8-rc

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

affiliates. Other names may be trademarks of their respective

owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

mysql-js>

Ensuite, je « crée » mon cluster, en fait je vais rendre persistante les informations relatives à l’architecture du groupe dans mon cluster (plus d’info sur ce sujet plus bas).

mysql-js> var cluster=dba.createCluster('pocCluster', {adoptFromGR: true, ipWhitelist:'172.19.0.0/16'})
A new InnoDB cluster will be created on instance 'root@mysql_node1:3306'.

Creating InnoDB cluster 'pocCluster' on 'root@mysql_node1: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.

mysql-js> var cluster=dba.createCluster('pocCluster', {adoptFromGR: true, ipWhitelist:'172.19.0.0/16'})

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

Creating InnoDB cluster 'pocCluster' on 'root@mysql_node1: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.

La méthode createCluster() prends comme paramètres, le nom du cluster (pocCluster) ainsi que des paramètres optionnels comme ipWhitelist (172.19.0.0/16)…

Pour plus de détails connecte toi à MySQL Shell (mysqlsh) et tape : dba.help(‘createCluster’)

$ mysqlsh
*Welcome to MySQL Shell 1.0.8-rc

Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

mysql-js> dba.help('createCluster')

$ mysqlsh

*Welcome to MySQL Shell 1.0.8-rc

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

affiliates. Other names may be trademarks of their respective

owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

mysql-js> dba.help('createCluster')

Vérifions l’état du cluster

mysql-js> /* Check cluster status */
mysql-js> cluster.status()
{
    "clusterName": "pocCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql_node1:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql_node1:3306": {
                "address": "mysql_node1:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "mysql_node2:3306": {
                "address": "mysql_node2:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "mysql_node3:3306": {
                "address": "mysql_node3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> /* Check cluster status */

mysql-js> cluster.status()

{

"clusterName": "pocCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "mysql_node1:3306",

"status": "OK",

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

"topology": {

"mysql_node1:3306": {

"address": "mysql_node1:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"mysql_node2:3306": {

"address": "mysql_node2:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"mysql_node3:3306": {

"address": "mysql_node3:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

MySQL Shell nous confirme ce que nous savions déjà:

je m’auto cite; un grand (1m89) DBA à dit un jour :

« On a donc un cluster MySQL Group Replication déployé avec 3 nœuds online. Le membre mysql_node1 est (pour le moment) le primaire, mysql_node2 et mysql_node3 sont les secondaires. »

En zoomant dans les entrailles du groupe, on constate que la méthode createCluster() a écrit des données dans le cluster :

$ docker exec -it mysql_node1 mysql -uroot -p -e"SHOW SCHEMAS; SHOW TABLES IN mysql_innodb_cluster_metadata;"
Enter password: 
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| gr_test                       |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+

+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| routers                                 |
| schema_version                          |
+-----------------------------------------+

$ docker exec -it mysql_node1 mysql -uroot -p -e"SHOW SCHEMAS; SHOW TABLES IN mysql_innodb_cluster_metadata;"

Enter password:

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

| Database |

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

| information_schema |

| gr_test |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

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

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

| Tables_in_mysql_innodb_cluster_metadata |

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

| clusters |

| hosts |

| instances |

| replicasets |

| routers |

| schema_version |

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

Le schéma mysql_innodb_cluster_metadata a donc été créé pour contenir les informations relatives au cluster.

Le nom des tables est assez explicite :

hosts

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.hosts\G"
Enter password: 
*************************** 1. row ***************************
           host_id: 6
         host_name: mysql_node1
        ip_address: 
 public_ip_address: NULL
          location: 
        attributes: NULL
admin_user_account: NULL
*************************** 2. row ***************************
           host_id: 13
         host_name: mysql_node2
        ip_address: 
 public_ip_address: NULL
          location: 
        attributes: NULL
admin_user_account: NULL
*************************** 3. row ***************************
           host_id: 20
         host_name: mysql_node3
        ip_address: 
 public_ip_address: NULL
          location: 
        attributes: NULL
admin_user_account: NULL

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.hosts\G"

Enter password:

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

host_id: 6

host_name: mysql_node1

ip_address:

public_ip_address: NULL

location:

attributes: NULL

admin_user_account: NULL

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

host_id: 13

host_name: mysql_node2

ip_address:

public_ip_address: NULL

location:

attributes: NULL

admin_user_account: NULL

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

host_id: 20

host_name: mysql_node3

ip_address:

public_ip_address: NULL

location:

attributes: NULL

admin_user_account: NULL

clusters

 $ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.clusters\G"
Enter password: 
*************************** 1. row ***************************
         cluster_id: 6
       cluster_name: pocCluster
 default_replicaset: 6
        description: Default Cluster
mysql_user_accounts: NULL
            options: {"adminType": "local"}
         attributes: {"default": true}

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.clusters\G"

Enter password:

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

cluster_id: 6

cluster_name: pocCluster

default_replicaset: 6

description: Default Cluster

mysql_user_accounts: NULL

options: {"adminType": "local"}

attributes: {"default": true}

replicasets

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.replicasets\G"
Enter password: 
*************************** 1. row ***************************
  replicaset_id: 6
     cluster_id: 6
replicaset_type: gr
  topology_type: pm
replicaset_name: default
         active: 1
     attributes: {"adopted": "true", "group_replication_group_name": "4e0f05b7-d9d0-11e6-87cf-002710cccc64"}
    description: NULL

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.replicasets\G"

Enter password:

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

replicaset_id: 6

cluster_id: 6

replicaset_type: gr

topology_type: pm

replicaset_name: default

active: 1

attributes: {"adopted": "true", "group_replication_group_name": "4e0f05b7-d9d0-11e6-87cf-002710cccc64"}

description: NULL

instances

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.instances\G"
Enter password: 
*************************** 1. row ***************************
      instance_id: 6
          host_id: 6
    replicaset_id: 6
mysql_server_uuid: d300be14-1797-11e7-a22e-0242ac130002
    instance_name: mysql_node1:3306
             role: HA
           weight: NULL
        addresses: {"mysqlX": "mysql_node1:33060", "grLocal": "mysql_node1:4999", "mysqlClassic": "mysql_node1:3306"}
       attributes: NULL
    version_token: NULL
      description: NULL
*************************** 2. row ***************************
      instance_id: 13
          host_id: 13
    replicaset_id: 6
mysql_server_uuid: d3100569-1797-11e7-a278-0242ac130004
    instance_name: mysql_node2:3306
             role: HA
           weight: NULL
        addresses: {"mysqlX": "mysql_node2:33060", "grLocal": "mysql_node2:4999", "mysqlClassic": "mysql_node2:3306"}
       attributes: NULL
    version_token: NULL
      description: NULL
*************************** 3. row ***************************
      instance_id: 20
          host_id: 20
    replicaset_id: 6
mysql_server_uuid: d321b2db-1797-11e7-a16f-0242ac130003
    instance_name: mysql_node3:3306
             role: HA
           weight: NULL
        addresses: {"mysqlX": "mysql_node3:33060", "grLocal": "mysql_node3:4999", "mysqlClassic": "mysql_node3:3306"}
       attributes: NULL
    version_token: NULL
      description: NULL

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.instances\G"

Enter password:

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

instance_id: 6

host_id: 6

replicaset_id: 6

mysql_server_uuid: d300be14-1797-11e7-a22e-0242ac130002

instance_name: mysql_node1:3306

role: HA

weight: NULL

addresses: {"mysqlX": "mysql_node1:33060", "grLocal": "mysql_node1:4999", "mysqlClassic": "mysql_node1:3306"}

attributes: NULL

version_token: NULL

description: NULL

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

instance_id: 13

host_id: 13

replicaset_id: 6

mysql_server_uuid: d3100569-1797-11e7-a278-0242ac130004

instance_name: mysql_node2:3306

role: HA

weight: NULL

addresses: {"mysqlX": "mysql_node2:33060", "grLocal": "mysql_node2:4999", "mysqlClassic": "mysql_node2:3306"}

attributes: NULL

version_token: NULL

description: NULL

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

instance_id: 20

host_id: 20

replicaset_id: 6

mysql_server_uuid: d321b2db-1797-11e7-a16f-0242ac130003

instance_name: mysql_node3:3306

role: HA

weight: NULL

addresses: {"mysqlX": "mysql_node3:33060", "grLocal": "mysql_node3:4999", "mysqlClassic": "mysql_node3:3306"}

attributes: NULL

version_token: NULL

description: NULL

Déploiement de MySQL Router

Le déploiement du router est trivial, il faut pour commencer le bootstrapper au cluster, c’est à dire le lier au cluster en le connectant aux méta-données :

$ mysqlrouter --bootstrap root@mysql_node1:3306 --directory routerDocker --name routerDocker
[sudo] password for daz: 
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at /home/daz/routerDocker...
MySQL Router 'routerDocker' 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:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'pocCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

$ mysqlrouter --bootstrap root@mysql_node1:3306 --directory routerDocker --name routerDocker

[sudo] password for daz:

Please enter MySQL password for root:

Bootstrapping MySQL Router instance at /home/daz/routerDocker...

MySQL Router 'routerDocker' 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:6446

- Read/Only Connections: localhost:6447

X protocol connections to cluster 'pocCluster':

- Read/Write Connections: localhost:64460

- Read/Only Connections: localhost:64470

Les paramètres directory et name sont optionnels.

Lancer MySQL Router :

$ mysqlrouter -c ~/routerDocker/mysqlrouter.conf &

1	$ mysqlrouter -c ~/routerDocker/mysqlrouter.conf &

L’application doit se connecter (par défaut) au port 6446 (écritures et lectures vers le noeud primaire). En cas de besoin de read scalability, les lectures peuvent être dirigées vers le port 6447.

Inspectons de nouveau les méta données :

routers

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.routers\G"
Enter password: 
*************************** 1. row ***************************
  router_id: 6
router_name: routerDocker
    host_id: 27
 attributes: NULL

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.routers\G"

Enter password:

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

router_id: 6

router_name: routerDocker

host_id: 27

attributes: NULL

hosts

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.hosts\G"
Enter password: 
*************************** 1. row ***************************
           host_id: 6
         host_name: mysql_node1
        ip_address: 
 public_ip_address: NULL
          location: 
        attributes: NULL
admin_user_account: NULL
*************************** 2. row ***************************
           host_id: 13
         host_name: mysql_node2
        ip_address: 
 public_ip_address: NULL
          location: 
        attributes: NULL
admin_user_account: NULL
*************************** 3. row ***************************
           host_id: 20
         host_name: mysql_node3
        ip_address: 
 public_ip_address: NULL
          location: 
        attributes: NULL
admin_user_account: NULL
*************************** 4. row ***************************
           host_id: 27
         host_name: 
        ip_address: NULL
 public_ip_address: NULL
          location: 
        attributes: {"registeredFrom": "mysql-router"}
admin_user_account: NULL

$ docker exec -it mysql_node1 mysql -uroot -p -e" SELECT * FROM mysql_innodb_cluster_metadata.hosts\G"

Enter password:

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

host_id: 6

host_name: mysql_node1

ip_address:

public_ip_address: NULL

location:

attributes: NULL

admin_user_account: NULL

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

host_id: 13

host_name: mysql_node2

ip_address:

public_ip_address: NULL

location:

attributes: NULL

admin_user_account: NULL

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

host_id: 20

host_name: mysql_node3

ip_address:

public_ip_address: NULL

location:

attributes: NULL

admin_user_account: NULL

*************************** 4. row ***************************

host_id: 27

host_name:

ip_address: NULL

public_ip_address: NULL

location:

attributes: {"registeredFrom": "mysql-router"}

admin_user_account: NULL

Voilà, mon cluster Group Replication paramétré « à la main » fait maintenant partie intégrante de mon InnoDB Cluster, je peux donc l’administrer avec MySQL Shell et je peux vous assurer que c’est vraiment pratique.

Mais ceci est une autre histoire et fera l’objet d’un autre article 🙂

Annexe

Le fichier docker-compose est le suivant :

version: '2'
services:
  node1:
    container_name: mysql_node1
    image: "mysql:5.7"
    volumes:
      - ~/Documents/Docker/confdir/mysql1:/etc/mysql/conf.d
    ports:
      - "14001:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root
    networks:
      app_net:
        ipv4_address: 172.19.0.2
  node2:
    container_name: mysql_node2
    image: "mysql:5.7"
    volumes:
      - ~/Documents/Docker/confdir/mysql2:/etc/mysql/conf.d
    ports:
      - "14002:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root
    networks:
      app_net:
        ipv4_address: 172.19.0.4
  node3:
    container_name: mysql_node3
    image: "mysql:5.7"
    volumes:
      - ~/Documents/Docker/confdir/mysql3:/etc/mysql/conf.d
    ports:
      - "14003:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root
    networks:
      app_net:
        ipv4_address: 172.19.0.3

networks:
  app_net:
    driver: bridge
    ipam:
      driver: default
      config:
      -
        subnet: 172.19.0.0/24

version: '2'

services:

node1:

container_name: mysql_node1

image: "mysql:5.7"

volumes:

- ~/Documents/Docker/confdir/mysql1:/etc/mysql/conf.d

ports:

- "14001:3306"

environment:

- MYSQL_ROOT_PASSWORD=root

networks:

app_net:

ipv4_address: 172.19.0.2

node2:

container_name: mysql_node2

image: "mysql:5.7"

volumes:

- ~/Documents/Docker/confdir/mysql2:/etc/mysql/conf.d

ports:

- "14002:3306"

environment:

- MYSQL_ROOT_PASSWORD=root

networks:

app_net:

ipv4_address: 172.19.0.4

node3:

container_name: mysql_node3

image: "mysql:5.7"

volumes:

- ~/Documents/Docker/confdir/mysql3:/etc/mysql/conf.d

ports:

- "14003:3306"

environment:

- MYSQL_ROOT_PASSWORD=root

networks:

app_net:

ipv4_address: 172.19.0.3

networks:

app_net:

driver: bridge

ipam:

driver: default

config:

subnet: 172.19.0.0/24

Thanks for using MySQL!

Filed under:
Group Replication, Haute Disponibilité, InnoDB Cluster, MySQL, tuto by Olivier DASINI

Tester MySQL InnoDB Cluster

mars 13, 2017

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

MySQL InnoDB Cluster est la (future) solution out-of-the-box HA de MySQL (à ne pas confondre avec MySQL NDB Cluster). Ce produit est composé de 3 éléments :

MySQL Group Replication
- Plugin de réplication multi-maître, avec résolution de conflits et basculement (failover) automatique.
MySQL Router
- Middleware léger et performant qui fournit un routage transparent entre l’application et le cluster.
MySQL Shell
- Client interactif Javascript, Python et SQL qui permet d’administrer le cluster.

MySQL Group Replication est GA et peut donc être utilisé tel quel hors MySQL InnoDB Cluster (voir Déployer un cluster MySQL Group Replication).

Ce n’est par contre pas encore le cas pour les 2 autres composants, MySQL Shell et MySQL Router qui sont en Release Candidate (RC), il n’est donc pas recommandé à ce jour de les utiliser dans un environnement de production.

Note:
L’article traite de MySQL InnoDB Cluster, HA natif de MySQL Server, solution à ne pas confondre avec MySQL NDB Cluster.

Installer MySQL InnoDB Cluster

Dans le cadre de cet article, les versions utilisées sont:

MySQL Server : 5.7.17
MySQL Shell : 1.0.8-rc
MySQL Router : 2.1.2 rc

Pour utiliser MySQL InnoDB Cluster, il faut simplement installer ces 3 composants :

Déployer les instances de test

MySQL Shell permet de déployer simplement des instances MySQL de test (sandbox).

Connexion avec MySQL Shell :

$ mysqlsh
Welcome to MySQL Shell 1.0.8-rc

Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js>

$ mysqlsh

Welcome to MySQL Shell 1.0.8-rc

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

affiliates. Other names may be trademarks of their respective

owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.

mysql-js>

Déployer la 1ère instance MySQL qui fera partie de notre cluster :

Host : localhost
Port : 3310

mysql-js> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in 
/home/daz/mysql-sandboxes/3310

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(3310)

A new MySQL sandbox instance will be created on this host in

/home/daz/mysql-sandboxes/3310

Please enter a MySQL root password for the new instance:

Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.

Use shell.connect('root@localhost:3310'); to connect to the instance.

Il suffit de rentrer le mot de passe root, puis l’instance est crée dans ~/mysql-sandboxes :

$ ls ~/mysql-sandboxes/3310/
3310.pid my.cnf mysqld mysqld.sock mysqld.sock.lock mysql-files mysqlx.sock mysqlx.sock.lock sandboxdata start.sh stop.sh

1 2	$ ls ~/mysql-sandboxes/3310/ 3310.pid my.cnf mysqld mysqld.sock mysqld.sock.lock mysql-files mysqlx.sock mysqlx.sock.lock sandboxdata start.sh stop.sh

Créons 2 autres instances pour le cluster:

Host : localhost
Ports : 3320 & 3330

mysql-js> dba.deploySandboxInstance(3320)
A new MySQL sandbox instance will be created on this host in 
/home/daz/mysql-sandboxes/3320

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.
Use shell.connect('root@localhost:3320'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(3330)
A new MySQL sandbox instance will be created on this host in 
/home/daz/mysql-sandboxes/3330

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use shell.connect('root@localhost:3330'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(3320)

A new MySQL sandbox instance will be created on this host in

/home/daz/mysql-sandboxes/3320

Please enter a MySQL root password for the new instance:

Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.

Use shell.connect('root@localhost:3320'); to connect to the instance.

mysql-js> dba.deploySandboxInstance(3330)

A new MySQL sandbox instance will be created on this host in

/home/daz/mysql-sandboxes/3330

Please enter a MySQL root password for the new instance:

Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.

Use shell.connect('root@localhost:3330'); to connect to the instance.

On a donc 3 instances MySQL dans notre sandbox.

A Noter que si vous avez déjà un cluster MySQL Group Replication actif, MySQL InnoDB Cluster est capable de l’adopter. Ceci fera l’objet d’un prochain article.

Gérer les instances

D’autres méthodes existent pour gérer les instances:

Stop
- dba.stopSandboxInstance()
Start
- dba.startSandboxInstance()
Kill : permet de simuler le crash d’un nœud
- dba.killSandboxInstance()
Delete : suppression totale de l’instance de la sandbox
- dba.deleteSandboxInstance()

Exemple – Arrêt et suppression d’une instance

mysql-js> dba.stopSandboxInstance(3320)
The MySQL sandbox instance on this host in 
/.../mysql-sandboxes/3320 will be stopped

Please enter the MySQL root password for the instance 'localhost:3320':

Stopping MySQL instance...

Instance localhost:3320 successfully stopped.

mysql-js> dba.deleteSandboxInstance(3320);
The MySQL sandbox instance on this host in 
/.../mysql-sandboxes/3320 will be deleted


Deleting MySQL instance...

Instance localhost:3320 successfully deleted.

mysql-js> dba.stopSandboxInstance(3320)

The MySQL sandbox instance on this host in

/.../mysql-sandboxes/3320 will be stopped

Please enter the MySQL root password for the instance 'localhost:3320':

Stopping MySQL instance...

Instance localhost:3320 successfully stopped.

mysql-js> dba.deleteSandboxInstance(3320);

The MySQL sandbox instance on this host in

/.../mysql-sandboxes/3320 will be deleted

Deleting MySQL instance...

Instance localhost:3320 successfully deleted.

L’aide est disponible dans MySQL Shell avec dba.help()

mysql-js> dba.help()

The global variable 'dba' is used to access the MySQL AdminAPI functionality
and perform DBA operations. It is used for managing MySQL InnoDB clusters.

The following properties are currently supported.

- verbose Enables verbose mode on the Dba operations.


The following functions are currently supported.

- checkInstanceConfiguration
- configureLocalInstance Validates and configures an instance for
cluster usage.
- createCluster Creates a MySQL InnoDB cluster.
- deleteSandboxInstance Deletes an existing MySQL Server instance on
localhost.
- deploySandboxInstance Creates a new MySQL Server instance on
localhost.
- dropMetadataSchema Drops the Metadata Schema.
- getCluster Retrieves a cluster from the Metadata Store.
- help Provides help about this class and it's
members
- killSandboxInstance Kills a running MySQL Server instance on
localhost.
- rebootClusterFromCompleteOutage Reboots a cluster from complete outage.
- resetSession Sets the session object to be used on the
Dba operations.
- startSandboxInstance Starts an existing MySQL Server instance on
localhost.
- stopSandboxInstance Stops a running MySQL Server instance on
localhost.

For more help on a specific function use: dba.help('<functionName>')

e.g. dba.help('deploySandboxInstance')

mysql-js> dba.help()

The global variable 'dba' is used to access the MySQL AdminAPI functionality

and perform DBA operations. It is used for managing MySQL InnoDB clusters.

The following properties are currently supported.

- verbose Enables verbose mode on the Dba operations.

The following functions are currently supported.

- checkInstanceConfiguration

- configureLocalInstance Validates and configures an instance for

cluster usage.

- createCluster Creates a MySQL InnoDB cluster.

- deleteSandboxInstance Deletes an existing MySQL Server instance on

localhost.

- deploySandboxInstance Creates a new MySQL Server instance on

localhost.

- dropMetadataSchema Drops the Metadata Schema.

- getCluster Retrieves a cluster from the Metadata Store.

- help Provides help about this class and it's

members

- killSandboxInstance Kills a running MySQL Server instance on

localhost.

- rebootClusterFromCompleteOutage Reboots a cluster from complete outage.

- resetSession Sets the session object to be used on the

Dba operations.

- startSandboxInstance Starts an existing MySQL Server instance on

localhost.

- stopSandboxInstance Stops a running MySQL Server instance on

localhost.

For more help on a specific function use: dba.help('<functionName>')

e.g. dba.help('deploySandboxInstance')

Vérifier la configuration des instances

Un moyen simple de savoir si les instances ont la configuration requise pour faire partie du cluster est d’utiliser : dba.checkInstanceConfiguration()

mysql-js> dba.checkInstanceConfiguration('root@localhost:3310')
Please provide the password for 'root@localhost:3310': 
Validating instance...

The instance 'localhost:3310' is valid for Cluster usage
{
"status": "ok"
}
mysql-js> dba.checkInstanceConfiguration('root@localhost:3320')
Please provide the password for 'root@localhost:3320': 
Validating instance...

The instance 'localhost:3320' is valid for Cluster usage
{
"status": "ok"
}
mysql-js> dba.checkInstanceConfiguration('root@localhost:3330')
Please provide the password for 'root@localhost:3330': 
Validating instance...

The instance 'localhost:3330' is valid for Cluster usage
{
"status": "ok"
}

mysql-js> dba.checkInstanceConfiguration('root@localhost:3310')

Please provide the password for 'root@localhost:3310':

Validating instance...

The instance 'localhost:3310' is valid for Cluster usage

{

"status": "ok"

}

mysql-js> dba.checkInstanceConfiguration('root@localhost:3320')

Please provide the password for 'root@localhost:3320':

Validating instance...

The instance 'localhost:3320' is valid for Cluster usage

{

"status": "ok"

}

mysql-js> dba.checkInstanceConfiguration('root@localhost:3330')

Please provide the password for 'root@localhost:3330':

Validating instance...

The instance 'localhost:3330' is valid for Cluster usage

{

"status": "ok"

}

Créer le cluster

On a donc 3 instances MySQL, en standalone, configurées et prêtes à se transformer en une base de données distribuée.

Je vais donc me connecter à une de mes instances :

User : root
Host : localhost
Ports : 3310

mysql-js> \c root@localhost:3310
Creating a Session to 'root@localhost:3310'
Enter password: 
Classic Session successfully established. No default schema selected.

mysql-js> \c root@localhost:3310

Creating a Session to 'root@localhost:3310'

Enter password:

Classic Session successfully established. No default schema selected.

Puis commencer la création effective de mon instance MySQL InnoDB Cluster, nommée testcluster

mysql-js> var cluster=dba.createCluster('testcluster')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Creating InnoDB cluster 'testcluster' on 'root@localhost:3310'...
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.

mysql-js> var cluster=dba.createCluster('testcluster')

A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Creating InnoDB cluster 'testcluster' on 'root@localhost:3310'...

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.

Je me retrouve pour le moment avec un cluster d’1 nœud. Certes, pas encore hautement disponible, mais c’est un début 🙂

La méthode status() me le confirme:

mysql-js> cluster.status()
{
    "clusterName": "testcluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "testcluster",

"defaultReplicaSet": {

"name": "default",

"primary": "localhost:3310",

"status": "OK_NO_TOLERANCE",

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

"topology": {

"localhost:3310": {

"address": "localhost:3310",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

Avant de lui rajouter des petits copains, on va vérifier que toutes les instances ont la même liste de transactions exécutées:

mysql-js> cluster.checkInstanceState('root@localhost:3310')
Please provide the password for 'root@localhost:3310': 
Analyzing the instance replication state...

The instance 'localhost:3310' is valid for the cluster.
The instance is fully recoverable.

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

mysql-js> cluster.checkInstanceState('root@localhost:3320')
Please provide the password for 'root@localhost:3320': 
Analyzing the instance replication state...

The instance 'localhost:3320' is valid for the cluster.
The instance is new to Group Replication.

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

mysql-js> cluster.checkInstanceState('root@localhost:3330')
Please provide the password for 'root@localhost:3330': 
Analyzing the instance replication state...

The instance 'localhost:3330' is valid for the cluster.
The instance is new to Group Replication.

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

mysql-js> cluster.checkInstanceState('root@localhost:3310')

Please provide the password for 'root@localhost:3310':

Analyzing the instance replication state...

The instance 'localhost:3310' is valid for the cluster.

The instance is fully recoverable.

{

"reason": "recoverable",

"state": "ok"

}

mysql-js> cluster.checkInstanceState('root@localhost:3320')

Please provide the password for 'root@localhost:3320':

Analyzing the instance replication state...

The instance 'localhost:3320' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

mysql-js> cluster.checkInstanceState('root@localhost:3330')

Please provide the password for 'root@localhost:3330':

Analyzing the instance replication state...

The instance 'localhost:3330' is valid for the cluster.

The instance is new to Group Replication.

{

"reason": "new",

"state": "ok"

}

Parfait !

Ajouter les autres nœuds

addInstance(), la bien nommée :

Ajout de localhost:3320

mysql-js> cluster.addInstance('root@localhost:3320')
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.

Please provide the password for 'root@localhost:3320': 
Adding instance to the cluster ...

The instance 'root@localhost:3320' was successfully added to the cluster.

mysql-js> cluster.addInstance('root@localhost:3320')

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.

Please provide the password for 'root@localhost:3320':

Adding instance to the cluster ...

The instance 'root@localhost:3320' was successfully added to the cluster.

Ajout de localhost:3330

mysql-js> cluster.addInstance('root@localhost:3330')
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.

Please provide the password for 'root@localhost:3330': 
Adding instance to the cluster ...

The instance 'root@localhost:3330' was successfully added to the cluster.

mysql-js> cluster.addInstance('root@localhost:3330')

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.

Please provide the password for 'root@localhost:3330':

Adding instance to the cluster ...

The instance 'root@localhost:3330' was successfully added to the cluster.

L’architecture de notre cluster est maintenant:

mysql-js> cluster.status()
{
    "clusterName": "testcluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

mysql-js> cluster.status()

{

"clusterName": "testcluster",

"defaultReplicaSet": {

"name": "default",

"primary": "localhost:3310",

"status": "OK",

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

"topology": {

"localhost:3310": {

"address": "localhost:3310",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"localhost:3320": {

"address": "localhost:3320",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"localhost:3330": {

"address": "localhost:3330",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

localhost:3310 est le primaire il est donc le seul à accepter les écritures. Les 2 autres membres ne sont accessibles qu’en lecture.

C’est le comportement par défaut de MySQL Group Replication est donc de MySQL InnoDB Cluster. Pour avoir un cluster en multi-master, il faut le préciser lors de la création du cluster (dba.createCluster()).

Les informations révélées par les différentes commandes exécutée jusqu’ici, sont persistante. Elles sont en stockées dans les nœuds du cluster, dans le schéma mysql_innodb_cluster_metadata :

$ mysql --protocol=tcp -uroot -p -P3310

mysql> show schemas;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0,00 sec)


mysql> show tables in mysql_innodb_cluster_metadata;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| routers                                 |
| schema_version                          |
+-----------------------------------------+
6 rows in set (0,00 sec)

$ mysql --protocol=tcp -uroot -p -P3310

mysql> show schemas;

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

| Database |

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

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

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

5 rows in set (0,00 sec)

mysql> show tables in mysql_innodb_cluster_metadata;

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

| Tables_in_mysql_innodb_cluster_metadata |

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

| clusters |

| hosts |

| instances |

| replicasets |

| routers |

| schema_version |

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

6 rows in set (0,00 sec)

Déployer MySQL Router

MySQL Router étant déjà installé, on va le configurer pour l’interfacer avec notre cluster:

$ mysqlrouter --bootstrap root@localhost:3310 --directory routerSandbox
Please enter MySQL password for root:

Bootstrapping system MySQL Router instance...
MySQL Router has now been configured for the InnoDB cluster 'testcluster'.

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

Classic MySQL protocol connections to cluster 'testcluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'testcluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

$ mysqlrouter --bootstrap root@localhost:3310 --directory routerSandbox

Please enter MySQL password for root:

Bootstrapping system MySQL Router instance...

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

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

Classic MySQL protocol connections to cluster 'testcluster':

- Read/Write Connections: localhost:6446

- Read/Only Connections: localhost:6447

X protocol connections to cluster 'testcluster':

- Read/Write Connections: localhost:64460

- Read/Only Connections: localhost:64470

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

6446 : lectures / écritures pour le noeud primaire
6447 : lectures seules pour les nœuds secondaires (Round-Robin)

Et le pendant pour les connexions avec le protocole X (64460 & 64470), pour une utilisation NoSQL Document Store de MySQL.

Le bootstrap à généré un fichier de configuration pour MySQL Router

$ view ~/routerSandbox/mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=routerSandbox
logging_folder=/home/xxxx/routerSandbox/log
runtime_folder=/home/xxxx/routerSandbox/run
data_folder=/home/xxxx/routerSandbox/data
keyring_path=/home/xxxx/routerSandbox/data/keyring
master_key_path=/home/xxxx/routerSandbox/mysqlrouter.key

[logger]
level = INFO

[metadata_cache:testcluster]
router_id=6
bootstrap_server_addresses=mysql://localhost:3310,mysql://localhost:3320,mysql://localhost:3330
user=mysql_router6_qy5a3dmn5y68
metadata_cluster=testcluster
ttl=300

[routing:testcluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://testcluster/default?role=PRIMARY
mode=read-write
protocol=classic

[routing:testcluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://testcluster/default?role=SECONDARY
mode=read-only
protocol=classic
...

$ view ~/routerSandbox/mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap

[DEFAULT]

name=routerSandbox

logging_folder=/home/xxxx/routerSandbox/log

runtime_folder=/home/xxxx/routerSandbox/run

data_folder=/home/xxxx/routerSandbox/data

keyring_path=/home/xxxx/routerSandbox/data/keyring

master_key_path=/home/xxxx/routerSandbox/mysqlrouter.key

[logger]

level = INFO

[metadata_cache:testcluster]

router_id=6

bootstrap_server_addresses=mysql://localhost:3310,mysql://localhost:3320,mysql://localhost:3330

user=mysql_router6_qy5a3dmn5y68

metadata_cluster=testcluster

ttl=300

[routing:testcluster_default_rw]

bind_address=0.0.0.0

bind_port=6446

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

mode=read-write

protocol=classic

[routing:testcluster_default_ro]

bind_address=0.0.0.0

bind_port=6447

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

mode=read-only

protocol=classic

...

Utiliser MySQL Router

Evidemment il faut le démarrer

$ ~/routerSandbox/start.sh

1	$ ~/routerSandbox/start.sh

Un petit coup d’œil dans les logs:

$ tail -f ~/routerSandbox/log/mysqlrouter.log
 
2017-03-10 19:03:36 INFO    [7f81e420e700] Starting Metadata Cache
2017-03-10 19:03:36 INFO    [7f81e420e700] Connections using ssl_mode 'PREFERRED'
2017-03-10 19:03:36 INFO    [7f81e3a0d700] [routing:testcluster_default_ro] started: listening on 0.0.0.0:6447; read-only
2017-03-10 19:03:36 INFO    [7f81e320c700] [routing:testcluster_default_rw] started: listening on 0.0.0.0:6446; read-write
2017-03-10 19:03:36 INFO    [7f81e2a0b700] [routing:testcluster_default_x_ro] started: listening on 0.0.0.0:64470; read-only
2017-03-10 19:03:36 INFO    [7f81e220a700] [routing:testcluster_default_x_rw] started: listening on 0.0.0.0:64460; read-write
2017-03-10 19:03:36 INFO    [7f81e420e700] Connected with metadata server running on 127.0.0.1:3310
2017-03-10 19:03:36 INFO    [7f81e420e700] Changes detected in cluster 'testcluster' after metadata refresh
2017-03-10 19:03:36 INFO    [7f81e420e700] Metadata for cluster 'testcluster' has 1 replicasets:
2017-03-10 19:03:36 INFO    [7f81e420e700] 'default' (3 members, single-master)
2017-03-10 19:03:36 INFO    [7f81e420e700]     localhost:3310 / 33100 - role=HA mode=RW
2017-03-10 19:03:36 INFO    [7f81e420e700]     localhost:3320 / 33200 - role=HA mode=RO
2017-03-10 19:03:36 INFO    [7f81e420e700]     localhost:3330 / 33300 - role=HA mode=RO
2017-03-10 19:03:36 INFO    [7f81c37fe700] Connected with metadata server running on 127.0.0.1:3310

$ tail -f ~/routerSandbox/log/mysqlrouter.log

2017-03-10 19:03:36 INFO [7f81e420e700] Starting Metadata Cache

2017-03-10 19:03:36 INFO [7f81e420e700] Connections using ssl_mode 'PREFERRED'

2017-03-10 19:03:36 INFO [7f81e3a0d700] [routing:testcluster_default_ro] started: listening on 0.0.0.0:6447; read-only

2017-03-10 19:03:36 INFO [7f81e320c700] [routing:testcluster_default_rw] started: listening on 0.0.0.0:6446; read-write

2017-03-10 19:03:36 INFO [7f81e2a0b700] [routing:testcluster_default_x_ro] started: listening on 0.0.0.0:64470; read-only

2017-03-10 19:03:36 INFO [7f81e220a700] [routing:testcluster_default_x_rw] started: listening on 0.0.0.0:64460; read-write

2017-03-10 19:03:36 INFO [7f81e420e700] Connected with metadata server running on 127.0.0.1:3310

2017-03-10 19:03:36 INFO [7f81e420e700] Changes detected in cluster 'testcluster' after metadata refresh

2017-03-10 19:03:36 INFO [7f81e420e700] Metadata for cluster 'testcluster' has 1 replicasets:

2017-03-10 19:03:36 INFO [7f81e420e700] 'default' (3 members, single-master)

2017-03-10 19:03:36 INFO [7f81e420e700] localhost:3310 / 33100 - role=HA mode=RW

2017-03-10 19:03:36 INFO [7f81e420e700] localhost:3320 / 33200 - role=HA mode=RO

2017-03-10 19:03:36 INFO [7f81e420e700] localhost:3330 / 33300 - role=HA mode=RO

2017-03-10 19:03:36 INFO [7f81c37fe700] Connected with metadata server running on 127.0.0.1:3310

Voila MySQL InnoDB Cluster configuré et prêt à être testé !

L’application doit se connecter au port 6446 (écritures et lectures vers le noeud primaire). Les lectures peuvent également être dirigées vers le port 6447.

Tests

Port de lecture

$ watch -td -n1 "mysql -uroot -P6447 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2> /dev/null"

1	$ watch -td -n1 "mysql -uroot -P6447 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2> /dev/null"

=> affiche : 3320, 3330, 3320, 3330, …

Port d’écriture

$ watch -td -n1 "mysql -uroot -P6446 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2> /dev/null"

1	$ watch -td -n1 "mysql -uroot -P6446 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2> /dev/null"

=> affiche : 3310, 3310, 3310, …

Failover automatique

Grâce à la méthode dba.killSandboxInstance() on peut simuler un crash du serveur primaire et ainsi voir à l’oeuvre le failover automatique du cluster.

Les 2 sessions qui suivent s’exécutent en parallèle:

Session 1

$ watch -td -n1 "mysql -uroot -P6446 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2> /dev/null"

1	$ watch -td -n1 "mysql -uroot -P6446 --protocol=tcp -pxxx -BNe'SELECT @@port;' 2> /dev/null"

Session 2

mysql-js> dba.killSandboxInstance(3310)
The MySQL sandbox instance on this host in 
/.../mysql-sandboxes/3310 will be killed


Killing MySQL instance...

Instance localhost:3310 successfully killed.

mysql-js> dba.killSandboxInstance(3310)

The MySQL sandbox instance on this host in

/.../mysql-sandboxes/3310 will be killed

Killing MySQL instance...

Instance localhost:3310 successfully killed.

=> La session 1 va afficher 3310 puis 3320 après le basculement de la base de données (database failover).

En complément je vous invite à lire :

et regarder la video de lefred : MySQL InnoDB Cluster: MySQL Shell starter guide

Documentation

Thanks for using MySQL!

Filed under:
Group Replication, Haute Disponibilité, InnoDB Cluster, tuto by Olivier DASINI

FAQ Webinar MySQL Group Replication

mars 3, 2017

Tags: group replication, Haute disponibilité, InnoDB Cluster, ProxySQL, router

Mise-à-jour: 7 Mars 2017

Le 1er mars dernier, j’ai présenté lors d’un webinar, la technologie de haute disponibilité MySQL Group Replication. On a explosé notre record d’affluence et j’ai été inondé de questions, preuve s’il en faut de votre intérêt, toujours plus important, pour la base de données Open Source la plus populaire au monde.

Je n’ai malheureusement pas été en mesure de répondre à toutes les questions, ce qui me permet aujourd’hui de les centraliser dans cette FAQ. Cette dernière pourra être mise à jour de temps à autres, en fonctions des questions que je récupérerai du terrain.

En complément de cette FAQ

Sur quels OS peut on utiliser MySQL Group Replication ?

MySQL Group Replication est un plugin du serveur MySQL. Il est donc présent sur toutes les plateformes où MySQL 5.7 est disponible :

Linux
Windows
Solaris
OSX
FreeBSD

Liste complete

Est ce que la feature de multi-master nécessitera un update du driver MySQL ou cela sera vu comme un master « standard » d’un point de vu client ?

L’architecture classique MySQL Group Replication est la suivante :

Client + MySQL Router | MySQL Group Replication

Client | ProxySQL | MySQL Group Replication

C’est du moins le cas en mode Single Primary car il faut pouvoir identifier le nœud primaire.

Cette architecture est valable également en mode multi-master (ou multi-Primaire), cependant dans ce cas, vu que tout les membres sont master, il suffit simplement que le driver contiennent la liste des membres.

Est-ce que l’on a une contrainte sur le nombre de nœuds minimum ?

Pas de contraintes par « design ». Cependant, pour qu’un system distribué puisse fournir du failover automatique, il faut minimum 3 nœuds. Cela permet d’éviter le split brain.

A noter qu’il est possible de mettre en place un cluster MySQL Group Replication avec seulement 2 nœuds. Mais en cas d’arrêt non prévu (crash) d’un des membres, l’autre membre n’acceptera pas de nouvelles requêtes. On se trouve alors dans un cas de Network Partitioning, et la partition doit être débloquée selon la procedure inscrite dans la doc.

Par contre si l’un des 2 membres est arrêté « gracieusement » (STOP GROUP_REPLICATION;), l’autre continue de fonctionner normalement.

Dans le cas d’une utilisation multi-master, est-ce que l’on peut ajouter/retirer des nœuds dynamiquement ?

Oui, dans les 2 modes (Single Primary ou multi-master) il est possible d’ajouter et/ou de retirer des nœuds dynamiquement.

Au sein d’un cluster MySQL Group Replication, l’élection d’un nœud master R/W est-il automatique ou manuel ?
Dans le mode Single Primary, lorsque le primaire tombe, comment est choisi le nouveau nœud primaire ?

Le process d’election est automatique et transparent pour le client. Alors pas mal de process se passent « behind the scene » !

Il faut notamment que les autres membres identifient le fait que le nœud ne fasse plus partie du cluster et puis ensuite enclencher le process d’élection du nouveau nœud primaire (le prochain dans la liste ordonnée en fonction de l’UUID de instances du cluster) et aussi désactiver le mode read-only.

Identifier le nœud primaire : https://dev.mysql.com/doc/refman/5.7/en/group-replication-find-primary.html

Exemples:

L’information qui permet de savoir quel nœud est primaire est disponible dans la table performance_schema.global_status :

mysql>
SELECT * 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
VARIABLE_NAME: group_replication_primary_member
VARIABLE_VALUE: 00014115-1111-1111-1111-111111111111

mysql>

SELECT *

FROM performance_schema.global_status

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

VARIABLE_NAME: group_replication_primary_member

VARIABLE_VALUE: 00014115-1111-1111-1111-111111111111

En la joignant avec la table performance_schema.replication_group_members ont obtient un peu plus d’infos:

mysql>
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: 00014115-1111-1111-1111-111111111111
MEMBER_HOST: 192.168.1.11
MEMBER_STATE: ONLINE

mysql>

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: 00014115-1111-1111-1111-111111111111

MEMBER_HOST: 192.168.1.11

MEMBER_STATE: ONLINE

Est-ce que l’on a des limites en terme de taille de base de données pour MySQL Group Replication ?

Le moteur de stockage utilisé avec MySQL Group Replication est InnoDB. Les limites sont donc celles imposées par InnoDB.

Est-ce que l’on peut utiliser MySQL Router avec une replication asynchrone classique ?

MySQL Router 2.0, peut être utilisé avec MySQL Replication : https://dev.mysql.com/doc/mysql-router/2.0/en/

Le plugin Connection Routing fournit 2 modes de « routage »:

Le mode Read-Only : route les requêtes en mode round-robin sur une liste d’instances MySQL.
=> Cela a du sens pour router les requêtes de lectures sur les slaves.

Le mode Read-Write : route les requêtes sur le premier serveur de la liste, si celui si n’est plus accessible, les requêtes sont alors routées sur le suivant de la liste et ainsi de suite jusqu’au dernier.
=> Perso, je ne suis pas trop fan de ce mode pour MySQL Replication.

Sur une configuration multi-master, est il toujours conseillé de mettre en place un offset sur les ID pour chaque node pour éviter les conflits ?

Avec la réplication classique (asynchrone) de MySQL, pour une architecture multi-master en actif/actif (que je ne conseille toujours pas), il est nécessaire de mettre en place différents offsets pour prévenir les conflits liés aux clés primaire en auto increment (details).

Dans le cas de MySQL Group Replication, ce problème se pose également en mode multi-master. C’est pour cela qu’il est géré automatiquement par le cluster. Il est cependant possible de modifier les paramètres manuellement (details).

La modification d’une table est-elle automatiquement répliquée ?

Toutes les modifications certifiées d’un nœud vont être répliquées sur les autres. Cela concerne évidemment les DDL.

A noter que certaines précautions doivent être prisent lorsque vous exécutez des DDL en mode multi-master (details).

Avec 3 nœuds, si on perd 1 nœud et que les 2 autres ne se voient plus, pas de quorum donc plus d’écriture ?

Avec une architecture MySQL Group Replication de 3 nœuds, l’arrêt non prévu (crash) d’1 membre génère la recomposition automatique du cluster avec 2 nœuds ie aucune intervention n’est nécessaire.

Par contre vous vous trouvez dans une situation « inconfortable » et il faut donc re-provisionner un 3ème nœuds ASAP. Parce qu’avec ce cluster de 2 membres, en cas de crash d’un des 2 ou en cas de split-brain, la recomposition automatique du cluster ne peut plus se faire.

On se trouve alors dans le cas du Network Partitioning (les membres n’acceptent plus d’écriture), et la partition doit être débloquée « manuellement ».

A quel format le log binaire doit être positionné ?

Seul le format ROW est valide (binlog-format=ROW)

Peut on contrôler les délais de réplication ?

Le Flow-control permet de maintenir les membres du groupe raisonnablement proches les uns des autres.

A noter que d’autres approches pour contrôler les délais de réplication seront disponibles dans le futur.

Je vous conseille la lecture de cet article qui traite du sujet des performances de MySQL group Replication.

Pourquoi 9 nœuds maximum ?

Selon les résultats de nos tests, jusqu’à 9 membres les performances restent très satisfaisantes. De plus ce nombre assure une très bonne disponibilité car le cluster peut alors gérer automatiquement jusqu’à 4 pertes de nœuds simultanément !

A noter qu’il est toujours possible de rajouter des slaves au cluster MySQL Group Replication.

Est-il possible d’avoir une configuration multi OS par exemple un membre sur Linux et un autre membre sur du Windows ?

Tout à fait !

Je peux par exemple avoir l’architecture MySQL Group Replication suivante :

Nœud A sur Linux
Nœud B sur Windows
Nœud C sur OSX

Quelques liens utiles

Documentation MySQL Group Replication
- https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
MySQL Group Replication: Distributed Recovery behind the scenes
- http://mysqlhighavailability.com/distributed-recovery-behind-the-scenes/
Transaction life cycle explained
- http://mysqlhighavailability.com/mysql-group-replication-transaction-life-cycle-explained/
Group communication behind the scenes
- http://mysqlhighavailability.com/group-communication-behind-the-scenes/
Tous les articles de l’équipe MySQL Group Replication
- http://mysqlhighavailability.com/tag/mysql-group-replication/

Webinar à venir
- https://www.mysql.fr/news-and-events/web-seminars/
Webinars sur demande
- https://www.mysql.fr/news-and-events/on-demand-webinars/

Thanks for using MySQL!

Filed under:
Group Replication, MySQL, ProxySQL by Olivier DASINI

Configurer ProxySQL pour MySQL Group Replication

janvier 11, 2017

Tags: group replication, ha, high availability, InnoDB Cluster, proxy, ProxySQL, tuto

Ami lecteur, toi qui t’intéresse à MySQL Group Replication et ProxySQL, une version plus récente de cet article existe : Configurer ProxySQL 1.4 pour MySQL 5.7 Group Replication

Cet article s’inspire de HA with MySQL Group Replication and ProxySQL de Frédéric Descamps aka @lefred

Dans un précédent article je vous ai présenté comment déployer un cluster MySQL Group Replication, la nouvelle solution de haute disponibilité de MySQL.

Ce type d’architecture est souvent utilisé avec un composant qui se place entre l’application et le cluster,composant généralement appelé proxy (quelque chose) ou router quelque chose. Dans cet article, je vais vous présenter le meilleur (selon moi) proxy open source du moment : ProxySQL (1.3.2 : la version GA à la date d’écriture).

Le but de cet article est de créer un PoC Solution HA Base de Données Open Source : MySQL Group Replication et ProxySQL.

L’article étant suffisamment long, je ne vais couvrir ni l’installation des nœuds MySQL (5.7.17), ni le déploiement du cluster. Mais comme je suis sympa, voici les ressources nécessaires pour accomplir ces différentes tâches:

Avertissement!
Ce qui suit est effectué par un professionnel… sur son ordi portable 🙂 Toutes les instances sont en local, ~~car je suis fainéant~~ par commodité.
Attention, à ne pas reproduire tel quel en production.

MySQL Group Replication

MySQL Group Replication se présente comme un plugin embarqué dans MySQL à partir de MySQL 5.7.17.

Caractéristiques

Version du serveur : 5.7.17
Version du plugin : 1.0
Nœud 1 : 127.0.0.1 : 14418
Nœud 2 : 127.0.0.1 : 14419
Nœud 3 : 127.0.0.1 : 14420

A partir d’ici, on a donc un cluster MySQL Group Replication de 3 nœuds, installé, déployé et qui fonctionne :

node3 [14420]>
SELECT left(version(),6);
+-------------------+
| left(version(),6) |
+-------------------+
| 5.7.17            |
+-------------------+

SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014418-1111-1111-1111-111111111111
MEMBER_HOST: localhost
MEMBER_PORT: 14418
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014419-2222-2222-2222-222222222222
MEMBER_HOST: localhost
MEMBER_PORT: 14419
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014420-3333-3333-3333-333333333333
MEMBER_HOST: localhost
MEMBER_PORT: 14420
MEMBER_STATE: ONLINE

node3 [14420]>

SELECT left(version(),6);

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

| left(version(),6) |

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

| 5.7.17 |

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

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014418-1111-1111-1111-111111111111

MEMBER_HOST: localhost

MEMBER_PORT: 14418

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014419-2222-2222-2222-222222222222

MEMBER_HOST: localhost

MEMBER_PORT: 14419

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014420-3333-3333-3333-333333333333

MEMBER_HOST: localhost

MEMBER_PORT: 14420

MEMBER_STATE: ONLINE

Le cluster est en mode single primary, c’est à dire qu’un seul nœud est disponible en écriture (à la fois).

node3 [14420]>
SHOW VARIABLES LIKE 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: 00014418-1111-1111-1111-111111111111
MEMBER_HOST: localhost
MEMBER_PORT: 14418
MEMBER_STATE: ONLINE

node3 [14420]>

SHOW VARIABLES LIKE 'group_replication_single_primary_mode';

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

| Variable_name | Value |

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

| group_replication_single_primary_mode | ON |

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

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: 00014418-1111-1111-1111-111111111111

MEMBER_HOST: localhost

MEMBER_PORT: 14418

MEMBER_STATE: ONLINE

Pour pouvoir automatiser le routage des requêtes, mais aussi permettre le failover avec ProxySQL nous allons enrichir le schéma sys de MySQL 5.7.17 avec le code ci-dessous (disponible ici également) :

USE sys;

SET GLOBAL log_bin_trust_function_creators=ON;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
  RETURNS INT
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
  RETURNS INT
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
  RETURNS TEXT(10000)
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
  RETURNS INT
  BEGIN
    DECLARE result BIGINT DEFAULT 0;
    DECLARE colon_pos INT;
    DECLARE next_dash_pos INT;
    DECLARE next_colon_pos INT;
    DECLARE next_comma_pos INT;
    SET gtid_set = GTID_NORMALIZE(gtid_set);
    SET colon_pos = LOCATE2(':', gtid_set, 1);
    WHILE colon_pos != LENGTH(gtid_set) + 1 DO
      SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
      SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
      SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
      IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
        SET result = result +
          SUBSTR(gtid_set, next_dash_pos + 1,
            LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
          SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
      ELSE
        SET result = result + 1;
      END IF;
      SET colon_pos = next_colon_pos;
    END WHILE;
    RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
  RETURNS INT
  BEGIN
    RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
  RETURNS VARCHAR(3)
  BEGIN
    RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind$$

DELIMITER ;

SET GLOBAL log_bin_trust_function_creators=OFF;

USE sys;

SET GLOBAL log_bin_trust_function_creators=ON;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)

RETURNS INT

RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)

RETURNS INT

RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))

RETURNS TEXT(10000)

RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))

RETURNS INT

BEGIN

DECLARE result BIGINT DEFAULT 0;

DECLARE colon_pos INT;

DECLARE next_dash_pos INT;

DECLARE next_colon_pos INT;

DECLARE next_comma_pos INT;

SET gtid_set = GTID_NORMALIZE(gtid_set);

SET colon_pos = LOCATE2(':', gtid_set, 1);

WHILE colon_pos != LENGTH(gtid_set) + 1 DO

SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);

SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);

SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);

IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN

SET result = result +

SUBSTR(gtid_set, next_dash_pos + 1,

LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -

SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;

ELSE

SET result = result + 1;

END IF;

SET colon_pos = next_colon_pos;

END WHILE;

RETURN result;

END$$

CREATE FUNCTION gr_applier_queue_length()

RETURNS INT

BEGIN

RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT

Received_transaction_set FROM performance_schema.replication_connection_status

WHERE Channel_name = 'group_replication_applier' ), (SELECT

@@global.GTID_EXECUTED) )));

END$$

CREATE FUNCTION gr_member_in_primary_partition()

RETURNS VARCHAR(3)

BEGIN

RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM

performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=

((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),

'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN

performance_schema.replication_group_member_stats USING(member_id));

END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT

sys.gr_member_in_primary_partition() as viable_candidate,

IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM

performance_schema.global_variables WHERE variable_name IN ('read_only',

'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,

sys.gr_applier_queue_length() as transactions_behind$$

DELIMITER ;

SET GLOBAL log_bin_trust_function_creators=OFF;

Vous pouvez retrouver ce bout de code (version originale) et bien plus encore dans l’article du Product Manager de MySQL Group Replication, Matt Lord : MySQL Group Replication: A Quick Start Guide

Important
Dans la version originale du script il faut passer la commande SET GLOBAL log_bin_trust_function_creators=ON; sur TOUT les nœuds du cluster, avant de jouer le script qui contient le code ci-dessus :

node[2|3] (secondaries)>
SET GLOBAL log_bin_trust_function_creators=ON;

1 2	node[2\|3] (secondaries)> SET GLOBAL log_bin_trust_function_creators=ON;

node[1](primary)>
SET GLOBAL log_bin_trust_function_creators=ON;
source Group_Replication_helper_functions_and_views.sql
SET GLOBAL log_bin_trust_function_creators=OFF;

node[1](primary)>

SET GLOBAL log_bin_trust_function_creators=ON;

source Group_Replication_helper_functions_and_views.sql

SET GLOBAL log_bin_trust_function_creators=OFF;

node[2|3] (secondaries)>
SET GLOBAL log_bin_trust_function_creators=OFF;

1 2	node[2\|3] (secondaries)> SET GLOBAL log_bin_trust_function_creators=OFF;

ProxySQL

Caractéristiques

Version du proxy : 1.3.2.0

Dans le cadre de cet article je ne vais aborder que les fonctionnalités qui relative au routage et failover de ProxySQL. Je vous invite cependant à lire la documentation.

Les grandes étapes pour la préparation de notre environnement sont :

Téléchargement

$
wget https://github.com/sysown/proxysql/releases/download/v1.3.2/proxysql_1.3.2-ubuntu16_amd64.deb

1 2	$ wget https://github.com/sysown/proxysql/releases/download/v1.3.2/proxysql_1.3.2-ubuntu16_amd64.deb

Installation

$
dpkg -i proxysql_1.3.2-ubuntu16_amd64.deb

1 2	$ dpkg -i proxysql_1.3.2-ubuntu16_amd64.deb

Démarrage du service

$
service proxysql start

1 2	$ service proxysql start

Et voilà le travail!

ProxySQL est installé et fonctionnel :

$ service proxysql status
? proxysql.service - LSB: High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
Active: active (running) since Mon 2017-01-02 11:03:20 CET; 4h 50min ago
...

$ service proxysql status

? proxysql.service - LSB: High Performance Advanced Proxy for MySQL

Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)

Active: active (running) since Mon 2017-01-02 11:03:20 CET; 4h 50min ago

...

Faisons un point. A ce stade on a :

déployé un cluster MySQL Group Replication de 3 nœuds
ajouté des fonctions et des vues dans sys schema (Group_Replication_helper_functions_and_views.sql)
installé ProxySQL

Il nous reste pas mal de choses intéressantes à voir. Cependant, avant de passer à la suite regardons à quoi ressemble notre architecture cible :

L’application se connecte à ProxySQL qui redirige les requêtes sur les bons nœuds. Les flèches rouges représentent les écritures et les vertes les lectures.

La suite ? on va donc maintenant rendre ProxySQL conscient de MySQL Group Replication.
Pour se faire on va se connecter à ProxySQL en mode administrateur (utilisateur admin) :

$
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Proxy> '

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2016, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Proxy> '

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.30 (ProxySQL Admin Module)

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

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

La syntaxe est celle de MySQL \o/

Un DBA se sent un peu comme à la maison et c’est plutôt agréable.

Voyons un peu ce qu’il y a sous la surface :

Proxy>
SHOW SCHEMAS;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+

Proxy>

SHOW SCHEMAS;

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

| seq | name | file |

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

| 0 | main | |

| 2 | disk | /var/lib/proxysql/proxysql.db |

| 3 | stats | |

| 4 | monitor | |

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

ProxySQL contient 4 schémas (base de données). En voici une description succincte:

main : la configuration courante
disk : configuration persistante (stockée sur le disque)
stats : statistiques liées à l’utilisation du proxy
monitor : données collectée en vue de la supervision

Succinct, comme convenu!
Suffisant néanmoins dans le cadre de cet article, car nous allons principalement utiliser les tables du schéma main. tu veux en savoir plus ? RTFM !

Configuration des groupes dans ProxySQL

ProxySQL utilise la notion de Hostgroup pour regrouper de manière logique des serveurs par type. Ils sont définit dans la table main.mysql_servers :

Proxy>
SHOW CREATE TABLE main.mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )

Proxy>

SHOW CREATE TABLE main.mysql_servers\G

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

table: mysql_servers

Create Table: CREATE TABLE mysql_servers (

hostgroup_id INT NOT NULL DEFAULT 0,

hostname VARCHAR NOT NULL,

port INT NOT NULL DEFAULT 3306,

status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',

weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,

compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,

max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,

max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,

use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,

max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,

comment VARCHAR NOT NULL DEFAULT '',

PRIMARY KEY (hostgroup_id, hostname, port) )

Comme vu sur le schéma, on va séparer les lectures des écritures. Pour se faire on va donc créer 2 groupes, l’un dédié aux écritures et l’autres… aux lectures :

Proxy>
-- Hostgroup 1 <=> Writes
INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14418);
INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14419);
INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14420);

-- Hostgroup 2 <=> Reads
INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14418);
INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14419);
INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14420);

-- Load & save config
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Check
SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;
+--------------+-----------+-------+--------+
| hostgroup_id | hostname  | port  | status |
+--------------+-----------+-------+--------+
| 1            | 127.0.0.1 | 14418 | ONLINE |
| 1            | 127.0.0.1 | 14419 | ONLINE |
| 1            | 127.0.0.1 | 14420 | ONLINE |
| 2            | 127.0.0.1 | 14418 | ONLINE |
| 2            | 127.0.0.1 | 14419 | ONLINE |
| 2            | 127.0.0.1 | 14420 | ONLINE |
+--------------+-----------+-------+--------+

Proxy>

-- Hostgroup 1 <=> Writes

INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14418);

INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14419);

INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 14420);

-- Hostgroup 2 <=> Reads

INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14418);

INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14419);

INSERT INTO main.mysql_servers(hostgroup_id, hostname, port) VALUES (2, '127.0.0.1', 14420);

-- Load & save config

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

-- Check

SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | ONLINE |

| 1 | 127.0.0.1 | 14419 | ONLINE |

| 1 | 127.0.0.1 | 14420 | ONLINE |

| 2 | 127.0.0.1 | 14418 | ONLINE |

| 2 | 127.0.0.1 | 14419 | ONLINE |

| 2 | 127.0.0.1 | 14420 | ONLINE |

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

Supervision

ProxySQL comprend un module de supervision (monitoring) qui a pour utilisateur et mot de passe par défaut, respectivement monitor et monitor (oui 2 fois monitor). Cet utilisateur doit bien évidemment exister au niveau des membres du cluster et il n’a besoin que du privilège MySQL USAGE, afin de pinger et vérifier la variable read_only.
Le privilège REPLICATION CLIENT est cependant nécessaire s’il doit superviser aussi le retard de réplication.

Proxy>
-- Set user name & password for monitoring module
SET mysql-monitor_username='monitoring';
SET mysql-monitor_password='Very-S3cr3t';

-- Load & save config
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Proxy>

-- Set user name & password for monitoring module

SET mysql-monitor_username='monitoring';

SET mysql-monitor_password='Very-S3cr3t';

-- Load & save config

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

Scheduler

Le scheduler permet d’exécuter des scripts externes à ProxySQL. Nous avons besoin d’un script pour superviser les membres du cluster pour le routage des requêtes et le failover. Ce script est disponible sur mon repo GitHub : proxysql_groupreplication_checker.sh et doit être placé dans « /var/lib/proxysql/ »

Petit rappel: ce script n’est fournit que pour l’exemple. Il ne doit pas être utiliser tel quel en production.

Note
Ce script est légèrement modifié par rapport à la version originale que Lefred propose : https://github.com/lefred/proxysql_groupreplication_checker pour pouvoir fonctionner dans le cadre de ce PoC avec ProxySQL 1.3.2.

Les jobs à exécuter sont stockés dans la table scheduler du schema main :

Proxy>
SHOW CREATE TABLE main.scheduler\G
*************************** 1. row ***************************
table: scheduler
Create Table: CREATE TABLE scheduler (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
filename VARCHAR NOT NULL,
arg1 VARCHAR,
arg2 VARCHAR,
arg3 VARCHAR,
arg4 VARCHAR,
arg5 VARCHAR,
comment VARCHAR NOT NULL DEFAULT '')

Proxy>

SHOW CREATE TABLE main.scheduler\G

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

table: scheduler

Create Table: CREATE TABLE scheduler (

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,

interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,

filename VARCHAR NOT NULL,

arg1 VARCHAR,

arg2 VARCHAR,

arg3 VARCHAR,

arg4 VARCHAR,

arg5 VARCHAR,

comment VARCHAR NOT NULL DEFAULT '')

Quelques infos pour la route :

arg1 identifiant d’hostgroup pour les écritures
arg2 identifiant d’hostgroup pour les lectures
arg3 nombre d’écrivain actif simultanément
arg4 booléen qui permet de choisir si le membre actif pour les écritures est aussi candidat pour les lectures
arg5 fichier de log

Paramétrons le scheduler :

Proxy>
INSERT INTO main.scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES 
(1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');

-- Load & save config
SAVE SCHEDULER TO DISK;
LOAD SCHEDULER TO RUNTIME;

SELECT * FROM main.scheduler\G
*************************** 1. row ***************************
id: 1
active: 1
interval_ms: 10000
filename: /var/lib/proxysql/proxysql_groupreplication_checker.sh
arg1: 1
arg2: 2
arg3: 1
arg4: 0
arg5: /var/lib/proxysql/proxysql_groupreplication_checker.log
comment:

Proxy>

INSERT INTO main.scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES

(1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');

-- Load & save config

SAVE SCHEDULER TO DISK;

LOAD SCHEDULER TO RUNTIME;

SELECT * FROM main.scheduler\G

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

id: 1

active: 1

interval_ms: 10000

filename: /var/lib/proxysql/proxysql_groupreplication_checker.sh

arg1: 1

arg2: 2

arg3: 1

arg4: 0

arg5: /var/lib/proxysql/proxysql_groupreplication_checker.log

comment:

Et on peut constater que le script à modifié le statut des membres du cluster :

Proxy>
SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers WHERE hostgroup_id ;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |
| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14419 | ONLINE       | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers WHERE hostgroup_id ;

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

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

| 1 | 127.0.0.1 | 14418 | ONLINE | 0 |

| 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14419 | ONLINE | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

En clair, du point de vue de l’application, qui se connecte au proxy :

14418 aka node 1 (primaire) : accessible en écriture / inaccessible en lecture
14419 aka node 2 (secondaire) : inaccessible en écriture / accessible en lecture
14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture

On est bien !

Test du failover

A ce stade on a tout ce qu’il faut pour tester le comportement du proxy en cas de perte du primaire. Chiche?

Vérification du statut des membres à l’aide de la supervision ProxySQL (perspective du proxy) :

Proxy>
SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |
| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14419 | ONLINE       | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status FROM main.mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | ONLINE | 0 |

| 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14419 | ONLINE | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

Vérification de l’identité du nœud primaire (perspective du cluster) :

node2 [14419]>
-- Find the primary node
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: 00014418-1111-1111-1111-111111111111
MEMBER_HOST: localhost
MEMBER_PORT: 14418
MEMBER_STATE: ONLINE

node2 [14419]>

-- Find the primary node

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: 00014418-1111-1111-1111-111111111111

MEMBER_HOST: localhost

MEMBER_PORT: 14418

MEMBER_STATE: ONLINE

Vérification du statut des membres en contactant les nœuds directement (perspective client) :

$
for port in 14418 14419 14420; do 
  mysql -P$port --protocol=TCP -BNe"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');" 2> /dev/null;
  echo;
done;

port 14418
read_only OFF
super_read_only OFF

port 14419
read_only ON
super_read_only ON

port 14420
read_only ON
super_read_only ON

for port in 14418 14419 14420; do

mysql -P$port --protocol=TCP -BNe"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');" 2> /dev/null;

echo;

done;

port 14418

read_only OFF

super_read_only OFF

port 14419

read_only ON

super_read_only ON

port 14420

read_only ON

super_read_only ON

Il y a manifestement un consensus pour dire que :

Membre 1, port 11418 est le nœud primaire, en mode lecture/écriture (mais lecture seule pour ProxySQL)
Membre 2 (port 14419) & 3 (port 14420) sont des nœuds secondaires, en mode lecture seule.

Note
En fonction de votre configuration un utilisateur et un mot de passe peuvent être requis

La première action de notre test consiste en l’ arrêt du nœud primaire (node 1, port 14418) :

node1 [14418]> SHUTDOWN;
Query OK, 0 rows affected (0,00 sec)

1 2	node1 [14418]> SHUTDOWN; Query OK, 0 rows affected (0,00 sec)

MySQL Group Replication va donc élire un nouveau membre primaire :

node2 [14419]>
-- Find the primary node
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: 00014419-2222-2222-2222-222222222222
MEMBER_HOST: localhost
MEMBER_PORT: 14419
MEMBER_STATE: ONLINE

node2 [14419]>

-- Find the primary node

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: 00014419-2222-2222-2222-222222222222

MEMBER_HOST: localhost

MEMBER_PORT: 14419

MEMBER_STATE: ONLINE

Le nœud 2 (port 14419) est donc le nouveau membre primaire du cluster.

Une autre vue de ce nouveau statut :

$
for port in 14418 14419 14420; do
  mysql -P$port --protocol=TCP -BNe"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');" 2> /dev/null;
  echo;
done;


port 14419
read_only OFF
super_read_only OFF

port 14420
read_only ON
super_read_only ON

for port in 14418 14419 14420; do

mysql -P$port --protocol=TCP -BNe"SHOW VARIABLES WHERE Variable_name IN ('port', 'read_only', 'super_read_only');" 2> /dev/null;

echo;

done;

port 14419

read_only OFF

super_read_only OFF

port 14420

read_only ON

super_read_only ON

En ce qui concerne la vision ProxySQL :

Proxy>
SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14419 | ONLINE       | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM main.mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14419 | ONLINE | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

Confirmation, avec ce nouvel état :

14418 aka node 1 (down) : inaccessible
14419 aka node 2 (primaire) : accessible en écriture / inaccessible en lecture
14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture

Que c’est beau l’informatique… quand ça fonctionne.

Que se passe t’il si le membre 1 revient ?

Proxy>
SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14419 | ONLINE       | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | ONLINE       | 0                   |
| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14419 | ONLINE | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | ONLINE | 0 |

| 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

Le proxy nous montre un nouvel état, une fois le nœud 1 de retour dans le cluster :

14418 aka node 1 (secondaire) : inaccessible en écriture / accessible en lecture
14419 aka node 2 (primaire) : accessible en écriture / inaccessible en lecture
14420 aka node 3 (secondaire) : inaccessible en écriture / accessible en lecture

Pour la suite de l’artcile, le membre 1 (14418) est à nouveau le nœud primaire.

Ci dessous extraits de quelques états du proxy durant la phase de failover :

Proxy>
SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | ONLINE       | 0                   |
| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

Proxy>

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | ONLINE | 0 |

| 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

## few seconds…

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |
| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | ONLINE | 0 |

| 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

## few seconds…

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;
+--------------+-----------+-------+--------------+---------------------+
| hostgroup_id | hostname  | port  | status       | max_replication_lag |
+--------------+-----------+-------+--------------+---------------------+
| 1            | 127.0.0.1 | 14418 | ONLINE       | 0                   |
| 1            | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0                   |
| 1            | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0                   |
| 2            | 127.0.0.1 | 14419 | ONLINE       | 0                   |
| 2            | 127.0.0.1 | 14420 | ONLINE       | 0                   |
+--------------+-----------+-------+--------------+---------------------+

SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers;

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

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

| 1 | 127.0.0.1 | 14418 | ONLINE | 0 |

| 1 | 127.0.0.1 | 14419 | OFFLINE_SOFT | 0 |

| 1 | 127.0.0.1 | 14420 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14418 | OFFLINE_SOFT | 0 |

| 2 | 127.0.0.1 | 14419 | ONLINE | 0 |

| 2 | 127.0.0.1 | 14420 | ONLINE | 0 |

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

Règles de routage

La table mysql_query_rules du schema main va contenir les règles de routages. C’est donc là que nous allons pouvoir séparer les requêtes d’écriture des requêtes de lecture :

Proxy> 
SHOW CREATE TABLE main.mysql_query_rules\G
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
comment VARCHAR)

Proxy>

SHOW CREATE TABLE main.mysql_query_rules\G

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

table: mysql_query_rules

Create Table: CREATE TABLE mysql_query_rules (

rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,

username VARCHAR,

schemaname VARCHAR,

flagIN INT NOT NULL DEFAULT 0,

client_addr VARCHAR,

proxy_addr VARCHAR,

proxy_port INT,

digest VARCHAR,

match_digest VARCHAR,

match_pattern VARCHAR,

negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,

flagOUT INT,

replace_pattern VARCHAR,

destination_hostgroup INT DEFAULT NULL,

cache_ttl INT CHECK(cache_ttl > 0),

reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,

timeout INT UNSIGNED,

retries INT CHECK (retries>=0 AND retries <=1000),

delay INT UNSIGNED,

mirror_flagOUT INT UNSIGNED,

mirror_hostgroup INT UNSIGNED,

error_msg VARCHAR,

log INT CHECK (log IN (0,1)),

apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,

comment VARCHAR)

Pour ce PoC nos règles seront simples et définies à base de regex :

Groupe lecture : requêtes commençant par le mot clé SELECT (^SELECT) vont dans le hostgroup 2.
Groupe écriture : toutes les autres requêtes (DDL, DML,…), ainsi que les SELECT contenant la clause FOR UPDATE (^SELECT.*FOR UPDATE$) vont dans le hostgroup 1.

Si une requête ne correspond à aucune des règles, elle sera dirigée vers le hostgroup par défaut (champ default_hostgroup de la table mysql_users) de l’utilisateur qui exécute la requête.

Regardons la structure de la table mysql_users :

Proxy>
SHOW CREATE TABLE main.mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))

Proxy>

SHOW CREATE TABLE main.mysql_users\G

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

table: mysql_users

Create Table: CREATE TABLE mysql_users (

username VARCHAR NOT NULL,

password VARCHAR,

active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,

use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,

default_hostgroup INT NOT NULL DEFAULT 0,

default_schema VARCHAR,

schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,

transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,

fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,

backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,

frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,

max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,

PRIMARY KEY (username, backend),

UNIQUE (username, frontend))

Créons l’utilisateur MySQL utilisé pour se connecter au backend (app_user / app_pwd) :

Proxy>
SELECT username, password, active, default_hostgroup FROM main.mysql_users;
Empty set (0,00 sec)

INSERT INTO main.mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'app_pwd', 1);

SELECT username, password, active, default_hostgroup FROM main.mysql_users;
+----------+----------+--------+-------------------+
| username | password | active | default_hostgroup |
+----------+----------+--------+-------------------+
| app_user | app_pwd  | 1      | 1                 |
+----------+----------+--------+-------------------+

-- Load & save config
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Proxy>

SELECT username, password, active, default_hostgroup FROM main.mysql_users;

Empty set (0,00 sec)

INSERT INTO main.mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'app_pwd', 1);

SELECT username, password, active, default_hostgroup FROM main.mysql_users;

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

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

| app_user | app_pwd | 1 | 1 |

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

-- Load & save config

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

Maintenant nous pouvons renseigner la table mysql_query_rules avec nos 2 règles de routages (écriture et lecture) :

Proxy>
-- Rule for writes : hostgroup 1
INSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, "^SELECT .* FOR UPDATE", 1, 1);

-- Rule for reads : hostgroup 2
INSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, "^SELECT ", 2, 1);

-- Load & save config
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- Check
SELECT rule_id, active, match_pattern, destination_hostgroup FROM main.mysql_query_rules;
+---------+--------+-----------------------+-----------------------+
| rule_id | active | match_pattern         | destination_hostgroup |
+---------+--------+-----------------------+-----------------------+
| 1       | 1      | ^SELECT .* FOR UPDATE | 1                     |
| 2       | 1      | ^SELECT               | 2                     |
+---------+--------+-----------------------+-----------------------+

Proxy>

-- Rule for writes : hostgroup 1

INSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, "^SELECT .* FOR UPDATE", 1, 1);

-- Rule for reads : hostgroup 2

INSERT INTO main.mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, "^SELECT ", 2, 1);

-- Load & save config

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

-- Check

SELECT rule_id, active, match_pattern, destination_hostgroup FROM main.mysql_query_rules;

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

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

| 1 | 1 | ^SELECT .* FOR UPDATE | 1 |

| 2 | 1 | ^SELECT | 2 |

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

Et voilà!

Il est temps de passer aux choses sérieuses.

Playtime

Toutes les briques de notre archi sont assemblées et configurées, c’est donc l’heure du test final.

Notre appli, simulée par un script bash, va se connecter au proxy (ProxySQL) pour interroger la base de données distribuée (MySQL Group Replication).

Au fait on fait comment pour se connecter au proxy ?

Proxy>
SHOW VARIABLES LIKE 'mysql-interfaces'\G
*************************** 1. row ***************************
Variable_name: mysql-interfaces
Value: 0.0.0.0:6033;/tmp/proxysql.sock

Proxy>

SHOW VARIABLES LIKE 'mysql-interfaces'\G

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

Variable_name: mysql-interfaces

Value: 0.0.0.0:6033;/tmp/proxysql.sock

Pour se connecter à la base de données, par l’intermédiaire du proxy, l’application peut soit utiliser le port 6033, soit le socket /tmp/proxysql.sock :

$
for x in {1..10}; do 
  mysql -u app_user -h127.0.0.1 -P6033 -BNe"SELECT @@port;" ; 
done;

14420
14420
14420
14420
14419
14419
14420
14419
14419
14419

for x in {1..10}; do

mysql -u app_user -h127.0.0.1 -P6033 -BNe"SELECT @@port;" ;

done;

14420

14419

14420

14419

Comme prévu, les requêtes de lecture se connectent sur l’un des 2 nœuds secondaires (node 2 : port 14419 || node 3: port 14420).

Allons plus loin dans le test et créons la table test.poc :

app [6033]>
CREATE TABLE test.poc (
id mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
port mediumint unsigned,
time timestamp,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0,08 sec)

app [6033]>

CREATE TABLE test.poc (

id mediumint(9) unsigned NOT NULL AUTO_INCREMENT,

port mediumint unsigned,

time timestamp,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

Query OK, 0 rows affected (0,08 sec)

L’application test jouera les 2 requêtes suivante :

INSERT INTO test.poc (port) VALUES (@@port);
SELECT * FROM test.poc;

Grâce à ces 2 requêtes et le petit scripts ci-dessous, on va être en mesure de suivre les processus de routage et de failover.
En clair :

écriture sur le primaire
lectures sur le(s) secondaire(s)
crash du master
failover automatique et transparent

$
while true; do
  mysql -u app_user -h127.0.0.1 -P6033 -BNe"INSERT INTO test.poc (port) VALUES (@@port); SELECT *, @@port FROM test.poc;";
  echo;
  sleep 4;
done

3 14418 2017-01-04 16:51:05 14419 <=> Row 1

3 14418 2017-01-04 16:51:05 14419
10 14418 2017-01-04 16:51:09 14419 <=> Row 2

3 14418 2017-01-04 16:51:05 14420
10 14418 2017-01-04 16:51:09 14420
17 14418 2017-01-04 16:51:13 14420 <=> Row 3

3 14418 2017-01-04 16:51:05 14420
10 14418 2017-01-04 16:51:09 14420
17 14418 2017-01-04 16:51:13 14420
18 14419 2017-01-04 16:51:21 14420 <=> Row 4 => Failover, new primary node (14419)

3 14418 2017-01-04 16:51:05 14420
10 14418 2017-01-04 16:51:09 14420
17 14418 2017-01-04 16:51:13 14420
18 14419 2017-01-04 16:51:21 14420
25 14419 2017-01-04 16:51:25 14420 <=> Row 5

while true; do

mysql -u app_user -h127.0.0.1 -P6033 -BNe"INSERT INTO test.poc (port) VALUES (@@port); SELECT *, @@port FROM test.poc;";

echo;

sleep 4;

done

3 14418 2017-01-04 16:51:05 14419 <=> Row 1

3 14418 2017-01-04 16:51:05 14419

10 14418 2017-01-04 16:51:09 14419 <=> Row 2

3 14418 2017-01-04 16:51:05 14420

10 14418 2017-01-04 16:51:09 14420

17 14418 2017-01-04 16:51:13 14420 <=> Row 3

3 14418 2017-01-04 16:51:05 14420

10 14418 2017-01-04 16:51:09 14420

17 14418 2017-01-04 16:51:13 14420

18 14419 2017-01-04 16:51:21 14420 <=> Row 4 => Failover, new primary node (14419)

3 14418 2017-01-04 16:51:05 14420

10 14418 2017-01-04 16:51:09 14420

17 14418 2017-01-04 16:51:13 14420

18 14419 2017-01-04 16:51:21 14420

25 14419 2017-01-04 16:51:25 14420 <=> Row 5

Revoyons la scène au ralenti…

Row 1 : Ecriture sur 14418, Lecture sur 14419
Row 2 : Ecriture sur 14418, Lecture sur 14419
Row 3 : Ecriture sur 14418, Lecture sur 14420
Row 4 : Ecriture sur 14419, Lecture sur 14420
- Le membre 14418 est stoppé, failover automatique de MySQL Group Replication et ProxySQL redirige les écritures sur 14419
Row 5 : Ecriture sur 14419, Lecture sur 14420

Trop cool !

Le mot de la fin

Récapitulons: pour ce PoC, une architecture ProxySQL 1.3 avec MySQL Group Replication s’est montée en plusieurs étapes :

Déployement d’un cluster MySQL Group Replication (3 ou 5 nœuds en général)
Enrichissement de sys schema afin gérer le routage et le failover
Installation et paramétrage de ProxySQL
Ajout du job de routage et de failover

L’étape 2 ne sera plus nécessaire à terme, sys schema sera nativement compatible avec Goup Replication dans une prochaine version de MySQL.

En ce qui concerne les étapes 3 et 4, elles vont être simplifiées car ProxySQL 1.4 qui est en cours de développement, est nativement compatible Group Replication.

Et surtout n’oubliez pas que MySQL InnoDB Cluster est la solution maison tout en un, qui simplifie grandement le déploiement de ce type d’architecture. Pour rappel InnoDB Cluster c’est :

Vous pouvez d’ores et déjà tester la dernière pré-version (labs preview) de MySQL InnoDB Cluster: MySQL InnoDB Cluster 5.7.17 Preview 2
C’est inutile mais je vais le rappeler quand même : il n’est pas recommandé d’utiliser les binaires du Labs en production.

Références

MySQL Group Replication

ProxySQL

MySQL InnoDB Cluster

Thanks for using MySQL!

Filed under:
Group Replication, InnoDB Cluster, MySQL, ProxySQL, tuto by Olivier DASINI

Retour sur le MySQL Day Paris 2016

novembre 24, 2016

Tags: Cloud, group replication, ha, InnoDB Cluster, MySQL 8, MySQL Cluster, NoSQL

Chose promise, chose due 🙂

Voici les présentations de l’Oracle MySQL Day Paris du 22 novembre 2016.

State Of The Dolphin

MySQL Day Paris 2016 – State Of The Dolphin from Olivier DASINI

MySQL High Availability – InnoDB Cluster and NDB Cluster

MySQL Day Paris 2016 – MySQL HA: InnoDB Cluster and NDB Cluster from Olivier DASINI

MySQL as a Document Store

MySQL Day Paris 2016 – MySQL as a Document Store from Olivier DASINI

Introducing Oracle MySQL Cloud Service

MySQL Day Paris 2016 – Introducing Oracle MySQL Cloud Service from Olivier DASINI

MySQL Enterprise Edition – Achieve the Highest Levels of Security

MySQL Day Paris 2016 – MySQL Enterprise Edition from Olivier DASINI

Thanks for using MySQL!

Commentaires fermés

Filed under:
Conférence, Group Replication, InnoDB Cluster, json, MySQL, NoSQL, Présentation by Olivier DASINI

Déployer un cluster MySQL Group Replication

novembre 8, 2016

Tags: cluster, group replication, ha, InnoDB Cluster, proxy, ProxySQL, router

Mise-à-jour: 9 janvier 2018

Historiquement, les solutions de haute disponibilité (HA) avec MySQL tournent autour de la fonctionnalité native MySQL Replication: replication asynchrone ou semi-synchrone. Ces modes de replication sont très largement utilisés pour le besoins critiques d’enterprises « at scale » comme Facebook, Twitter, Booking.com, Uber…

Aujourd’hui MySQL propose une nouvelle fonctionnalité native de haute disponibilité :

MySQL Group Replication.

MySQL Group Replication

MySQL Group Replication est un plugin pour MySQL 5.7+ qui fournit nativement de la réplication virtuellement synchrone avec détection/résolution de conflit et cohérence garantie. Ce plugin est disponible sur tous les systèmes d’exploitation supportés par MySQL (Linux, Windows, Solaris, OSX, …)

C’est la fonctionnalité idéale pour les architectures multi-maîtres. Il n’est alors plus nécessaire de gérer le failover de la base de données (à l’aide d’outils comme: mysqlfailover; mysqlrpladmin; MHA).

A noter que le plugin MySQL Group replication est en ce moment en Release Candidate (RC) ce qui veut dire qu’il n’est pas encore conseillé pour la production. C’est néanmoins le bon moment pour le tester (binaires téléchargeable ici). Le statut RC est la dernière étape avant la GA, et selon mon petit doigt ce n’est plus qu’une question de semaines avant la sortie officielle en GA (qui a dit MySQL 5.7.17 ???).

Le plugin MySQL Group Replication est GA et embarqué dans MySQL à partir de la version 5.7.17. Pour télécharger MySQL rendez-vous sur la page : http://www.mysql.com/downloads/

Déployer un cluster de 3 nœuds

~~La version du plugin lors de l’écriture de cet article est 0.9 disponible avec MySQL 5.7.15 dans le lab de MySQL : http://labs.mysql.com/~~
~~Cette article présume que MySQL 5.7.15 est déja installé (installer MySQL).~~

Article mis-à-jour avec la version 1.0 du plugin, qui est la version GA embarquée dans MySQL 5.7.17.

Cette article présume qu’une version de MySQL égale ou supérieure à 5.7.17 est déja installé (installer MySQL).

Caractéristiques

Version du serveur : 5.7.17
Version du plugin : 1.0
Nœud 1 : 192.168.1.11 : 14115
Nœud 2 : 192.168.1.9 : 3301
Nœud 3 : 192.168.1.48 : 5709

Configurer les instances MySQL

Comme toutes technologies, il y a des contraintes et quelques limitations. MySQL Group Replication nécessite :

MySQL 5.7
Tables avec le moteur de stockage InnoDB
Tables avec une clé primaire
Seul le protocole IPV4 est supporté
Les DDL en parallèle de DDL/DML exécutés par différents nœuds sur le même objet sont proscrits.

Liste complète : Requirements and Limitations

Au niveau configuration des serveurs il faut:

Activer le log binaire
- log_bin
Le log binaire doit être au format ROW :
- binlog_format = ROW
Journaliser les mise à jours envoyées par le primaire
- log_slave_updates = ON

Activer le mode GTID
- gtid_mode = ON
- enforce_gtid_consistency = ON
master_info_repository et relay_log_info_repository doivent avoir la valeur TABLE
- master_info_repository = TABLE
- relay_log_info_repository = TABLE
Désactiver le calcul de la somme de contrôle des événements de réplication
- binlog-checksum = NONE

Il faut également avoir une valeur de la variable server_id différente pour chacun des 3 nœuds:

Nœud 1 : server_id=11
Nœud 2 : server_id=9
Nœud 3 : server_id=48

Pour synthétiser, dans le fichiers de configuration (my.cnf / my.ini) du nœud 1, il faut avoir (en plus des infos classiques):

server_id=11
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin
binlog_format=ROW

server_id=11

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

log_bin

binlog_format=ROW

Pareil pour les 2 autres, à la valeur de server_id près.

Configuration spécifique à MySQL Group Replication

Le cluster doit avoir un identifiant unique au format UUID:

le UUID doit être valide
il doit être définit sur chacune des machines du groupe

La fonction MySQL UUID() permet de générer un… uuid:

node1>
SELECT UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 78c1a27c-9dde-11e6-865d-dc53609b8b8d |
+--------------------------------------+

node1>

SELECT UUID();

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

| UUID() |

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

| 78c1a27c-9dde-11e6-865d-dc53609b8b8d |

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

Activer la journalisation des données modifiées par les transactions
- transaction_write_set_extraction = XXHASH64
Charger le plugin au démarrage et l’empêcher d’être désinstallé pendant l’exécution
- group_replication = FORCE_PLUS_PERMANENT
Charger le pluging Group Replication au démarrage du serveur
- plugin_load = group_replication.so
Donner un nom au cluster au format UUID
- group_replication_group_name = « 78c1a27c-9dde-11e6-865d-dc53609b8b8d »
Ne pas démarrer automatiquement Group Replication au démarrage de l’instance (au choix)
- group_replication_start_on_boot = OFF
Sert à démarrer le cluster. Doit être à OFF (valeur par défaut)
- group_replication_bootstrap_group = OFF
Permet de choisir la configuration mono-maître (au lieu de multi-maître)
- group_replication_single_primary_mode = ON
Adresse (locale) du nœud
- group_replication_local_address = « hostA:port »
Liste d’adresses des pairs. Utile lorsqu’un nouveau noeud cherche à se mettre à jour
- group_replication_group_seeds = « hostB:port,hostC:port »

Note.

Pour avoir une architecture multi-master ie pouvoir écrire sur toutes les intances à la fois il faut group_replication_single_primary_mode = OFF sur tous les nœuds

Activation du plugin MySQL Group Replication

A partir de la version 5.7.17, MySQL embarque le plugin Group Replication qui se nomme : group_replication.so.

Le plugin n’est évidemment pas activé par défaut, cependant vous avez 2 solutions pour l’activer :

dans le fichier de configuration comme indiqué précédemment (nécessite un redémarrage de l’instance MySQL)

plugin_load = group_replication.so

1	plugin_load = group_replication.so

à la volée, avec la commande INSTALL PLUGIN si l’instance est correctement configurée

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

1	INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Un fois activé, les informations relatives au plugin sont disponible avec (à faire sur tout les nœuds) :

node[1|2|3]> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS 
             FROM information_schema.PLUGINS 
             WHERE PLUGIN_NAME = 'group_replication';
+-------------------+----------------+---------------+
| PLUGIN_NAME       | PLUGIN_VERSION | PLUGIN_STATUS |
+-------------------+----------------+---------------+
| group_replication | 1.0            | ACTIVE        |
+-------------------+----------------+---------------+

node[1|2|3]> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS

FROM information_schema.PLUGINS

WHERE PLUGIN_NAME = 'group_replication';

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

| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS |

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

| group_replication | 1.0 | ACTIVE |

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

Le fichier group_replication.so se trouve dans le répertoire à plugin – plugin_dir:

mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+

mysql> SHOW VARIABLES LIKE 'plugin_dir';

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

| Variable_name | Value |

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

| plugin_dir | /usr/local/mysql/lib/plugin/ |

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

/usr/local/mysql/lib/plugin/ pour ma configuration.

mysql> \! ls -l /usr/local/mysql/lib/plugin/group_replication.so
-rwxr-xr-x 1 root mysql 15872361 Nov 28 17:45 /usr/local/mysql/lib/plugin/group_replication.so

1 2	mysql> \! ls -l /usr/local/mysql/lib/plugin/group_replication.so -rwxr-xr-x 1 root mysql 15872361 Nov 28 17:45 /usr/local/mysql/lib/plugin/group_replication.so

Synthétisons encore!

Dans la section [mysqld] du fichier de configuration (my.cnf / my.ini) du nœud 1, il faut ajouter:

## Group Replication specific options
plugin_load=group_replication.so
group_replication=FORCE_PLUS_PERMANENT
transaction_write_set_extraction=XXHASH64
group_replication_group_name="78c1a27c-9dde-11e6-865d-dc53609b8b8d"
group_replication_start_on_boot=OFF
group_replication_bootstrap_group=OFF
group_replication_single_primary_mode=ON
group_replication_local_address="192.168.1.11:4406"
group_replication_group_seeds="192.168.1.9:4406,192.168.1.48:4406"

## Group Replication specific options

plugin_load=group_replication.so

group_replication=FORCE_PLUS_PERMANENT

transaction_write_set_extraction=XXHASH64

group_replication_group_name="78c1a27c-9dde-11e6-865d-dc53609b8b8d"

group_replication_start_on_boot=OFF

group_replication_bootstrap_group=OFF

group_replication_single_primary_mode=ON

group_replication_local_address="192.168.1.11:4406"

group_replication_group_seeds="192.168.1.9:4406,192.168.1.48:4406"

Et donc pour les 2 nœuds restant, il faut ajuster:

Nœud 2 :

group_replication_local_address="192.168.1.9:4406"
group_replication_group_seeds="192.168.1.11:4406,192.168.1.48:4406"

1 2	group_replication_local_address="192.168.1.9:4406" group_replication_group_seeds="192.168.1.11:4406,192.168.1.48:4406"

Nœud 3 :

group_replication_local_address="192.168.1.48:4406"
group_replication_group_seeds="192.168.1.11:4406,192.168.1.9:4406"

1 2	group_replication_local_address="192.168.1.48:4406" group_replication_group_seeds="192.168.1.11:4406,192.168.1.9:4406"

Note.

Mon environnement étant « exotique » j’ai du configurer les 2 variables qui suivent, pour que le process de restauration fonctionne.

Nœud 1 : report_port = 14115 / report_host = « 192.168.1.11 »
Nœud 2 : report_port = 3301 / report_host = « 192.168.1.9 »
Nœud 3 : report_port = 5709 / report_host = « 192.168.1.48 »

Cependant il est plus que recommandé de configurer correctement nom d’hôte (/etc/hosts; /etc/hostname;) et DNS.

Optionnel : Pour empêcher l’utilisation (malencontreuse) d’autres moteurs de stockage autre qu’InnoDB (non-transactionnel donc), il peut être utile d’ajouter à la conf:

disabled_storage_engines= »MyISAM,BLACKHOLE,FEDERATED,ARCHIVE »

Le fichier de configuration étant modifié, il faut redémarrer les instances MySQL pour que la nouvelle configuration prenne effet.

A noter quand même que la plupart des variables relatives à Group Replication sont changeable à chaud avec la commande SET GLOBAL <commande>.

Utilisateur de restauration

Cet utilisateur est nécessaire pour le processus de récupération automatique (recovery) lorsqu’un serveur (r)entre dans le groupe.

A créer et à configurer sur tout les nœuds du groupe. Il permettra d’établir une connexion replica/source entre les membres du groupe en cas de process de recovery.

node[1|2|3]>
-- Create recovery user
SET sql_log_bin=0;

CREATE USER gr_user@'%' IDENTIFIED BY 'Mdp5uperS3cr&t';
GRANT REPLICATION SLAVE ON *.* TO gr_user@'%';
FLUSH PRIVILEGES;

-- Create recovery channel
CHANGE MASTER TO MASTER_USER='gr_user', MASTER_PASSWORD='Mdp5uperS3cr&t' FOR CHANNEL 'group_replication_recovery';

SET sql_log_bin=1;

node[1|2|3]>

-- Create recovery user

SET sql_log_bin=0;

CREATE USER gr_user@'%' IDENTIFIED BY 'Mdp5uperS3cr&t';

GRANT REPLICATION SLAVE ON *.* TO gr_user@'%';

FLUSH PRIVILEGES;

-- Create recovery channel

CHANGE MASTER TO MASTER_USER='gr_user', MASTER_PASSWORD='Mdp5uperS3cr&t' FOR CHANNEL 'group_replication_recovery';

SET sql_log_bin=1;

A partir de MySQL 5.7.19 il est recommandé de configurer les membres du cluster avec la variable super_read_only = 1. Une fois Group Replication démarré, il va ajuster la variable comme il le faut en fonction des états des nœuds (e.g. désactivé pour le(s) primaire(s)).

Plus d’info ici.

Pour une version de MySQL 5.7.19+ donc, dans la section [mysqld] des fichiers de configuration (my.cnf / my.ini) des nœuds 1, 2 et 3 il faut alors ajouter:

super_read_only = 1

1	super_read_only = 1

On est pas mal là!

Avant d’aller plus loin, une bonne habitude est de vérifier que les serveurs sont correctement configurés.

Vérification de la configuration

Configuration serveur

La commande SHOW GLOBAL VARIABLES permet de voir la valeur des variables serveur.

node1>
SHOW GLOBAL VARIABLES WHERE Variable_name IN 
('server_id', 'log_bin', 'binlog_format', 'gtid_mode', 'enforce_gtid_consistency', 'log_slave_updates', 'master_info_repository', 'relay_log_info_repository','transaction_write_set_extraction', 'binlog_checksum');
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| binlog_checksum                  | NONE     |
| binlog_format                    | ROW      |
| enforce_gtid_consistency         | ON       |
| gtid_mode                        | ON       |
| log_bin                          | ON       |
| log_slave_updates                | ON       |
| master_info_repository           | TABLE    |
| relay_log_info_repository        | TABLE    |
| server_id                        | 11       |
| transaction_write_set_extraction | XXHASH64 |
+----------------------------------+----------+

node1>

SHOW GLOBAL VARIABLES WHERE Variable_name IN

('server_id', 'log_bin', 'binlog_format', 'gtid_mode', 'enforce_gtid_consistency', 'log_slave_updates', 'master_info_repository', 'relay_log_info_repository','transaction_write_set_extraction', 'binlog_checksum');

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

| Variable_name | Value |

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

| binlog_checksum | NONE |

| binlog_format | ROW |

| enforce_gtid_consistency | ON |

| gtid_mode | ON |

| log_bin | ON |

| log_slave_updates | ON |

| master_info_repository | TABLE |

| relay_log_info_repository | TABLE |

| server_id | 11 |

| transaction_write_set_extraction | XXHASH64 |

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

Configuration restauration

node[1|2|3]>
SELECT user_name, host, user_password
FROM mysql.slave_master_info
WHERE channel_name = 'group_replication_recovery';
+-----------+--------+----------------+
| user_name | host   | user_password  |
+-----------+--------+----------------+
| gr_user   | <NULL> | Mdp5uperS3cr&t |
+-----------+--------+----------------+

SHOW GRANTS FOR gr_user@'%';
+-------------------------------------------------+
| Grants for gr_user@%                            |
+-------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'gr_user'@'%' |
+-------------------------------------------------+

node[1|2|3]>

SELECT user_name, host, user_password

FROM mysql.slave_master_info

WHERE channel_name = 'group_replication_recovery';

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

| user_name | host | user_password |

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

| gr_user | <NULL> | Mdp5uperS3cr&t |

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

SHOW GRANTS FOR gr_user@'%';

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

| Grants for gr_user@% |

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

| GRANT REPLICATION SLAVE ON *.* TO 'gr_user'@'%' |

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

Configuration Group Replication

 node1>
SHOW GLOBAL VARIABLES WHERE Variable_name IN 
('group_replication_bootstrap_group', 'group_replication_group_name', 'group_replication_group_seeds', 'group_replication_local_address','group_replication_start_on_boot', 'group_replication_single_primary_mode', 'server_uuid', 'super_read_only');
+---------------------------------------+--------------------------------------+
| Variable_name                         | Value                                |
+---------------------------------------+--------------------------------------+
| group_replication_bootstrap_group     | OFF                                  |
| group_replication_group_name          | 78c1a27c-9dde-11e6-865d-dc53609b8b8d |
| group_replication_group_seeds         | 192.168.1.9:4406,192.168.1.48:4406   |
| group_replication_local_address       | 192.168.1.11:4406                    |
| group_replication_single_primary_mode | ON                                   |
| group_replication_start_on_boot       | OFF                                  |
| server_uuid                           | dc12fd1a-83ff-11e5-91af-002710b3d914 |
| super_read_only                       | ON                                   |
+---------------------------------------+--------------------------------------+

node1>

SHOW GLOBAL VARIABLES WHERE Variable_name IN

('group_replication_bootstrap_group', 'group_replication_group_name', 'group_replication_group_seeds', 'group_replication_local_address','group_replication_start_on_boot', 'group_replication_single_primary_mode', 'server_uuid', 'super_read_only');

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

| Variable_name | Value |

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

| group_replication_bootstrap_group | OFF |

| group_replication_group_name | 78c1a27c-9dde-11e6-865d-dc53609b8b8d |

| group_replication_group_seeds | 192.168.1.9:4406,192.168.1.48:4406 |

| group_replication_local_address | 192.168.1.11:4406 |

| group_replication_single_primary_mode | ON |

| group_replication_start_on_boot | OFF |

| server_uuid | dc12fd1a-83ff-11e5-91af-002710b3d914 |

| super_read_only | ON |

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

Cela correspond à ce qui a été renseigné un peu plus haut.

Je vous conseille de toujours vérifier les configurations, sur tout les nœuds. Ca permet de gagner du temps et de sauvegarder l’énergie par la suite… « Certified wise DBA »

Déployer le cluster

Le moment tant attendu arrive…

Pour la suite de cet article, je présume que les instances ont les mêmes données, donc le cas présent, pas de données.

Dans le cas contraire il suffit simplement de faire une sauvegarde complète d’une des instances et de la restaurer sur les 2 autres (Backup and Recovery).

Amorcer le cluster

L’amorçage (bootstrap) consiste à créer un groupe d’un seul nœud (faut bien commencer !). Ce dernier va par la suite être en mesure de recevoir d’autres membres. La procédure de bootstrap ne doit donc se faire que sur un seul membre, le tout premier nœud du groupe.

Le nœud 1 est donc désigné volontaire pour l’amorçage.

node1>
SET GLOBAL group_replication_bootstrap_group = ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group = OFF;

node1>

SET GLOBAL group_replication_bootstrap_group = ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group = OFF;

Revoyons la scène au ralenti…

j’initie : group_replication_bootstrap_group = ON
je démarre : START GROUP_REPLICATION
je termine l’initialisation : group_replication_bootstrap_group = OFF

C’est vraiment important d’exécuter group_replication_bootstrap_group = ON seulement sur le 1er nœud du cluster, sous peine de se retrouver avec des groupes indépendant (split-brain artificiel).

Récupérer des infos sur le cluster c’est facile, avec les tables:

performance_schema.replication_group_members
performance_schema.replication_connection_status

node1>
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014115-1111-1111-1111-111111111111
MEMBER_HOST:192.168.1.11
MEMBER_PORT: 14115 
MEMBER_STATE: ONLINE

node1>

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014115-1111-1111-1111-111111111111

MEMBER_HOST:192.168.1.11

MEMBER_PORT: 14115

MEMBER_STATE: ONLINE

node1>
SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
GROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d
SOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d
THREAD_ID: NULL
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:1
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_recovery
GROUP_NAME:
SOURCE_UUID:
THREAD_ID: NULL
SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

node1>

SELECT * FROM performance_schema.replication_connection_status\G

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

CHANNEL_NAME: group_replication_applier

GROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d

SOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d

THREAD_ID: NULL

SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

RECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:1

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

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

CHANNEL_NAME: group_replication_recovery

GROUP_NAME:

SOURCE_UUID:

THREAD_ID: NULL

SERVICE_STATE: OFF

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

RECEIVED_TRANSACTION_SET:

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

Le groupe contient bien un seul membre, 192.168.1.11 aka nœud 1 et il est ONLINE.

Jusqu’ici tout va bien!

Ajouter des nœuds

Un cluster composé d’un seul membre, c’est un bon début. Mais ce n’est évidemment pas suffisant pour avoir de la haute disponibilité avec notre base de données MySQL

Ajout du nœud 2

node2>
START GROUP_REPLICATION;

1 2	node2> START GROUP_REPLICATION;

Trop facile !!!

La supervision montre que le cluster est donc maintenant composé de 2 membres, ONLINE!

node2>
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014115-1111-1111-1111-111111111111
MEMBER_HOST: 192.168.1.11
MEMBER_PORT: 14115
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6
MEMBER_HOST: 192.168.1.9
MEMBER_PORT: 3301
MEMBER_STATE: ONLINE

node2>

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014115-1111-1111-1111-111111111111

MEMBER_HOST: 192.168.1.11

MEMBER_PORT: 14115

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6

MEMBER_HOST: 192.168.1.9

MEMBER_PORT: 3301

MEMBER_STATE: ONLINE

node2>
SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
GROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d
SOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d
THREAD_ID: NULL
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:1-2:5:7
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_recovery
GROUP_NAME:
SOURCE_UUID:
THREAD_ID: NULL
SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

node2>

SELECT * FROM performance_schema.replication_connection_status\G

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

CHANNEL_NAME: group_replication_applier

GROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d

SOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d

THREAD_ID: NULL

SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

RECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:1-2:5:7

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

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

CHANNEL_NAME: group_replication_recovery

GROUP_NAME:

SOURCE_UUID:

THREAD_ID: NULL

SERVICE_STATE: OFF

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

RECEIVED_TRANSACTION_SET:

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

On ne vas pas s’arrêter en si bon chemin

Ajout du nœud 3

Cap ou pas cap ?

node3>
START GROUP_REPLICATION;

1 2	node3> START GROUP_REPLICATION;

Le process est évidemment le même.

node3>
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014115-1111-1111-1111-111111111111
MEMBER_HOST: 192.168.1.11
MEMBER_PORT: 14115
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6
MEMBER_HOST: 192.168.1.9
MEMBER_PORT: 3301
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914
MEMBER_HOST: 192.168.1.48
MEMBER_PORT: 5709
MEMBER_STATE: ONLINE

node3>

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014115-1111-1111-1111-111111111111

MEMBER_HOST: 192.168.1.11

MEMBER_PORT: 14115

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6

MEMBER_HOST: 192.168.1.9

MEMBER_PORT: 3301

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914

MEMBER_HOST: 192.168.1.48

MEMBER_PORT: 5709

MEMBER_STATE: ONLINE

Les 3 nœuds sont bien actifs et fonctionnels.

node3>
SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
GROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d
SOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d
THREAD_ID: NULL
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:4-5:8
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_recovery
GROUP_NAME:
SOURCE_UUID:
THREAD_ID: NULL
SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

node3>

SELECT * FROM performance_schema.replication_connection_status\G

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

CHANNEL_NAME: group_replication_applier

GROUP_NAME: 78c1a27c-9dde-11e6-865d-dc53609b8b8d

SOURCE_UUID: 78c1a27c-9dde-11e6-865d-dc53609b8b8d

THREAD_ID: NULL

SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

RECEIVED_TRANSACTION_SET: 78c1a27c-9dde-11e6-865d-dc53609b8b8d:4-5:8

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

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

CHANNEL_NAME: group_replication_recovery

GROUP_NAME:

SOURCE_UUID:

THREAD_ID: NULL

SERVICE_STATE: OFF

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

RECEIVED_TRANSACTION_SET:

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

Alors ? Heureux ?

Il est possible d’avoir jusqu’à 9 membres, donc tu peux y aller Marcel!!!

Identifier le nœud primaire

Rapide récap

Je viens de créer un cluster de 3 nœuds en utilisant le plugin HA natif MySQL Group Replication.

Sur ces 3 nœuds, 2 sont en mode lecture seule (mode: super_read_only) et le troisième en mode lecture/écriture, c’est le nœud primaire.

Un intérêt de cette architecture HA est qu’elle est finalement très proche d’une architecture de réplication classique master/slaves. La grosse différence, et c’est ce qui fait sa puissance, est qu’avec MySQL Group Replication, il n’est plus utile de gérer le failover base de données. Est ça, ce n’est pas rien !

L’information qui permet de savoir quel nœud est primaire est disponible dans la table performance_schema.global_status :

mysql>
SELECT * 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
VARIABLE_NAME: group_replication_primary_member
VARIABLE_VALUE: 00014115-1111-1111-1111-111111111111

mysql>

SELECT *

FROM performance_schema.global_status

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

VARIABLE_NAME: group_replication_primary_member

VARIABLE_VALUE: 00014115-1111-1111-1111-111111111111

En la joignant avec la table performance_schema.replication_group_members ont obtient un peu plus d’infos:

mysql>
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: 00014115-1111-1111-1111-111111111111
MEMBER_HOST: 192.168.1.11
MEMBER_STATE: ONLINE

mysql>

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: 00014115-1111-1111-1111-111111111111

MEMBER_HOST: 192.168.1.11

MEMBER_STATE: ONLINE

Dons cette architecture, le nœud 00014115-1111-1111-1111-111111111111 aka 192.168.1.11 aka nœud 1 est le primaire.

Note.

En mode multi-maître, ils sont tous primaire

Le corollaire immédiat de cette information est que dans cette configuration un seul nœud accepte les écritures. Cependant il est possible de lire sur tous les nœuds.
Sur le nœud 2 -nœud NON primaire

node2>
SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0,00 sec)

CREATE SCHEMA gr_test;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0,00 sec)

node2>

SHOW SCHEMAS;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0,00 sec)

CREATE SCHEMA gr_test;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

SHOW SCHEMAS;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0,00 sec)

Comme prévu les écritures sont impossibles.

Sur le nœud 1 – le nœud primaire

node1>
SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0,00 sec)

CREATE SCHEMA gr_test;
Query OK, 1 row affected (0,00 sec)

SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gr_test            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0,00 sec)

node1>

SHOW SCHEMAS;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0,00 sec)

CREATE SCHEMA gr_test;

Query OK, 1 row affected (0,00 sec)

SHOW SCHEMAS;

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

| Database |

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

| information_schema |

| gr_test |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0,00 sec)

Le schéma (database) gr_test est créé sur le nœud 1. La transaction est répliquée automatiquement sur les autres nœuds.

Arrêt du nœud primaire

Si le nœud primaire n’est plus en ligne (arrêt du serveur pour cause de maintenance ou crash) l’un des 2 autres nœuds devient alors primaire.

node1>
SHUTDOWN; -- Arrêt du nœud 1, nœud primaire
Query OK, 0 rows affected (0,00 sec)

node1>

SHUTDOWN; -- Arrêt du nœud 1, nœud primaire

Query OK, 0 rows affected (0,00 sec)

Les autres membres du cluster sont au courant de la disparition de leur pote.

node3>
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6
MEMBER_HOST: 192.168.1.9
MEMBER_STATE: ONLINE

SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6
MEMBER_HOST: 192.168.1.9
MEMBER_PORT: 3301
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914
MEMBER_HOST: 192.168.1.48
MEMBER_PORT: 5709
MEMBER_STATE: ONLINE

node3>

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6

MEMBER_HOST: 192.168.1.9

MEMBER_STATE: ONLINE

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6

MEMBER_HOST: 192.168.1.9

MEMBER_PORT: 3301

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914

MEMBER_HOST: 192.168.1.48

MEMBER_PORT: 5709

MEMBER_STATE: ONLINE

Le groupe ne se compose alors plus que de 2 membres : 192.168.1.9 & 192.168.1.48

Et l’un des deux devient primaire.

Le nœud 2 est passé primaire (automatic database failover)
Je peux donc écrire sur le nœud 2 :

node2>
CREATE TABLE gr_test.t1(i int auto_increment primary key);
Query OK, 0 rows affected (0.12 sec)

SHOW TABLES IN gr_test;
+-------------------+
| Tables_in_gr_test |
+-------------------+
| t1                |
+-------------------+

node2>

CREATE TABLE gr_test.t1(i int auto_increment primary key);

Query OK, 0 rows affected (0.12 sec)

SHOW TABLES IN gr_test;

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

| Tables_in_gr_test |

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

| t1 |

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

Mais pas sur le nœud 3 (pas une surprise):

node3>
SHOW TABLES IN gr_test;
+-------------------+
| Tables_in_gr_test |
+-------------------+
| t1                |
+-------------------+
1 row in set (0.00 sec)

DROP TABLE gr_test.t1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

SHOW TABLES IN gr_test;
+-------------------+
| Tables_in_gr_test |
+-------------------+
| t1                |
+-------------------+
1 row in set (0.00 sec)

node3>

SHOW TABLES IN gr_test;

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

| Tables_in_gr_test |

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

| t1 |

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

1 row in set (0.00 sec)

DROP TABLE gr_test.t1;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

SHOW TABLES IN gr_test;

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

| Tables_in_gr_test |

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

| t1 |

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

1 row in set (0.00 sec)

Ayant fini la tâche de maintenance sur le nœud 1 je le remet dans le cluster.

Note.

Si la durée d’indisponibilité est longue, il peut être judicieux de restaurer une sauvegarde fraîche sur le serveur avant de le remettre dans le cluster. C’est le même principe qu’avec une architecture de réplication, en utilisant :

mysqldump
mysqlbackup

Faire revenir un nœud dans le cluster

-- Redémarrer l'instance MySQL
-- ...
-- Vérifier l'état du nœud 1
node1>
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID:
MEMBER_HOST:
MEMBER_PORT: NULL
MEMBER_STATE: OFFLINE

-- Redémarrer l'instance MySQL

-- ...

-- Vérifier l'état du nœud 1

node1>

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID:

MEMBER_HOST:

MEMBER_PORT: NULL

MEMBER_STATE: OFFLINE

La variable group_replication_start_on_boot étant à OFF, je dois rajouter le nœud au cluster de manière explicite:

-- Démarrer le nœud 1
node1> 
START GROUP_REPLICATION; 
Query OK, 0 rows affected (2,74 sec)

-- Démarrer le nœud 1

node1>

START GROUP_REPLICATION;

Query OK, 0 rows affected (2,74 sec)

Après s’être enregistré, le « nouveau » membre va se connecter à un autre pour mettre à jour ses données (recovery mode).
Puis finalement participer à nouveau à la vie du cluster:

node1>
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 00014115-1111-1111-1111-111111111111
MEMBER_HOST: 192.168.1.11
MEMBER_PORT: 14115
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6
MEMBER_HOST: 192.168.1.9
MEMBER_PORT: 3301
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914
MEMBER_HOST: 192.168.1.48
MEMBER_PORT: 5709
MEMBER_STATE: ONLINE
3 rows in set (0,00 sec)


SHOW TABLES IN gr_test;
+-------------------+
| Tables_in_gr_test |
+-------------------+
| t1                |
+-------------------+
1 row in set (0,00 sec)

node1>

SELECT * FROM performance_schema.replication_group_members\G

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 00014115-1111-1111-1111-111111111111

MEMBER_HOST: 192.168.1.11

MEMBER_PORT: 14115

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6

MEMBER_HOST: 192.168.1.9

MEMBER_PORT: 3301

MEMBER_STATE: ONLINE

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

CHANNEL_NAME: group_replication_applier

MEMBER_ID: dc12fd1a-83ff-11e5-91af-002710b3d914

MEMBER_HOST: 192.168.1.48

MEMBER_PORT: 5709

MEMBER_STATE: ONLINE

3 rows in set (0,00 sec)

SHOW TABLES IN gr_test;

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

| Tables_in_gr_test |

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

| t1 |

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

1 row in set (0,00 sec)

A noter que le nœud 1 ne redevient pas primaire (no failback).

node1>
DROP TABLE gr_test.t1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6
MEMBER_HOST: 192.168.1.9
MEMBER_STATE: ONLINE

node1>

DROP TABLE gr_test.t1;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE

FROM performance_schema.replication_group_members

INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)

WHERE VARIABLE_NAME='group_replication_primary_member'\G

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

MEMBER_ID: d0853b8c-8d92-11e6-875b-0800273276e6

MEMBER_HOST: 192.168.1.9

MEMBER_STATE: ONLINE

Le nœud 2 (d0853b8c-8d92-11e6-875b-0800273276e6 ou 192.168.1.9) est donc bien resté primaire

Note.
Pour avoir une vraie configuration multi-maître il faut modifier la variable group_replication_single_primary_mode sur tout les nœuds du cluster.
Cette manipulation ne peut se faire que cluster arrêté.

Bien bien bien!

Je propose de s’arrêter là même si j’ai encore des choses à raconter. Mais je ne souhaite pas trop rallonger ce long billet.

En guise de conclusion, j’évoquerai simplement les solutions disponibles en frontal, pour que l’application se connecte au cluster.

Router, proxy and Co

mysqlrouter : Outil développé par MySQL Oracle, il est donc préconisé pour MySQL Group Replication.
La version 2.1 (GA prévue pour cette fin d’année 2017) est intrinsèquement lié à MySQL Group Replication. C’est donc son compagnon naturel. Il faut juste patienter un peu 🙂

La GA actuelle (2.0) peut dépanner mais elle n’est pas suffisamment intégrée (intelligent) avec MySQL Group replication.

A noter que MySQL Group Replication + mysqlrouter 2.1 + MySQL Shell nous donne MySQL InnoDB Cluster.
J’y reviendrai plus en détails dans un prochain article.

OK tu es un impatient, je le sens 🙂

Jette un coup d’œil ici, tu trouveras des liens intéressants

Proxy SQL : le palliatif idéal en attendant MySQL Router 2.1. Tout le monde en dit du bien. Si tu ne connais pas encore, regardes! tu me remercieras plus tard.
- (maj. 11/01/2017) j’ai écris un tuto : Configurer ProxySQL pour MySQL Group Replication

HA proxy : certainement le plus connu des 3.

Si vous êtes familiers avec les connecteurs MySQL, cela peut sans doutes être une solution.

Petite précision pas anodine, tous ces outils sont en GPL.

Aller! Cette fois ci je conclus pour de vrai.

La gestion de la Haute Disponibilité avec MySQL se fait traditionnellement avec :

MySQL Replication ou MySQL Replication semi-sync
- saupoudré de MySQL Utilities ou de MHA.
MySQL Cluster

Le plugin MySQL Group Replication apporte une nouvelle solution de HA native et permet également d’avoir une vraie solution multi-maître native et open-source.

Que du bon 🙂

Autres articles Haute Dispo avec MySQL:

Thanks for using MySQL!

Filed under:
Group Replication, InnoDB Cluster, MySQL by Olivier DASINI

MySQL à Oracle OpenWorld 2016

octobre 5, 2016

Tags: Cloud, group replication, ha, InnoDB Cluster, MySQL 8, MySQL Cluster, oow

Du 18 au 22 septembre 2016 c’est déroulé l’Oracle OpenWorld, un ensemble de conférences parlant des technologies Oracle.

Bien entendu, MySQL était au programme, voici un petit résumé des annonces.

MySQL dans le cloud

MySQL, la base de données open source la plus populaire au monde a enfin son cloud officiel avec Oracle MySQL Cloud Service.

Parmi les principaux points :

Dernière version GA de MySQL (5.7) en version Enterprise.
Support technique réalisé par les équipes d’Oracle MySQL (en lien direct avec les devs)

Pour l’essayer : https://cloud.oracle.com/mysql

Plus d’infos:

MySQL Cloud Service Deep Dive from Morgan Tocker

MySQL HA

MySQL Group Replication est en RC, en clair la prochaine version sera la première GA.

MySQL Group Replication est la solution native MySQL pour faire de la haute disponibilité. C’est un plugin pour MySQL qui permet un mode de réplication virtuellement synchrone, avec detection et résolution des transactions (éventuellement) en conflit pour des architectures multi-master.

Plus d’infos:

Cependant, la grosse sensation a été l’annonce de la version Release Candidate de MySQL InnoDB Cluster !

MySQL InnoDB Cluster permet de faciliter le déploiement de solutions de haute disponibilité basées sur MySQL Group Replication.

Concrètement, avec l’aide du MySQL Shell il devient alors possible de mettre en oeuvre facilement un cluster MySQL Group Replication avec des instances de MySQL Router en frontal.

Pour avoir une explication encore plus simple, regardez la demo de Fred et un tutorial.

Plus d’infos:

MySQL High Availability — InnoDB Clusters from Matt Lord

MySQL 8.0.0. DMR

La prochaine GA de MySQL sera la 8.

On passe donc de 5 (5.7) à 8… pourquoi ? Tout simplement parce que MySQL 6 a déjà existé (en 2007 puis abandonné).

Et pas 7 non plus car MySQL Cluster est en version 7 (7.4) actuellement. Du coup le premier chiffre de libre pour unifier les 2 solutions est le… 8 ! CQFD 🙂

MySQL 8.0.0 est une DMR, c’est donc la branche de développement. En clair à ne pas utiliser en production. Par contre je vous encourage à la télécharger (code source dispo également sur GitHub ou si vous préférez une image Docker ) et à tester les nouvelles fonctionnalités.

Au programme de cette version 8:

Common Table Expressions (CTE) une sorte de table temporaire associée à une requête qui permet à l’aide de la commande WITH d’exprimer la récursivité (mais pas seulement). A noter qu’il est cependant possible d’émuler WITH RECURSIVE avec MySQL et ce sans attendre la 8, lire cet article de Guilhem.
Invisible indexes : index maintenu par l’optimiseur mais pas utilisé. Permet de tester le comportement du serveur lors de l’évaluation de la pertinence ou non d’un index.
Persisting configuration variables, la possibilité de rendre persistant les changements de configuration serveur fait en ligne.
Les roles : faciliter la gestion des utilisateurs
La base de données est en UTF-8 par défaut
et plein d’autres choses…

Plus d’infos:

Autres annonces

La version 3.3 de MySQL Enterprise Monitor (MEM) : outil de gestion et de supervision des bases MySQL. Cette version intègre un tout nouveau tableau de bord dédié à la sauvegarde. MEM 3.3 permet donc un intégration très fine avec MySQL Enterprise Backup notre outil de sauvegarde physique à chaud.
Group Backup Overview

MySQL Cluster 7.5 notre base de données distribuée en mémoire est en RC.

Videos

Présentations

Les présentations sont disponibles sur le site d’OOW.

Thank you for using MySQL!

Commentaires fermés

Filed under:
Group Replication, InnoDB Cluster, MySQL by Olivier DASINI

Le contexte

Vérifier la configuration des instances

Créer le cluster

Configuration de MySQL Router

Bootstrap MySQL Router

Gestion des nœuds

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

Failover

Remettre un membre dans le groupe

Supprimer une instance du groupe

Perte du quorum

Repartir après un arrêt total du cluster

Partager :

WordPress:

Partager :

WordPress:

Partager :

WordPress:

Le contexte

MySQL Group Replication

MySQL Shell, interface pour gérer son cluster

Déploiement de MySQL Router

Annexe

Partager :

WordPress:

Installer MySQL InnoDB Cluster

Déployer les instances de test

Gérer les instances

Vérifier la configuration des instances

Créer le cluster

Ajouter les autres nœuds

Déployer MySQL Router

Utiliser MySQL Router

Tests

Partager :

WordPress:

Quelques liens utiles

Partager :

WordPress:

MySQL Group Replication

ProxySQL

Configuration des groupes dans ProxySQL

Supervision

Scheduler

Test du failover

Règles de routage

Playtime

Le mot de la fin

Références

MySQL Group Replication

ProxySQL

MySQL InnoDB Cluster

Partager :

WordPress:

State Of The Dolphin

MySQL High Availability – InnoDB Cluster and NDB Cluster

MySQL as a Document Store

Introducing Oracle MySQL Cloud Service

MySQL Enterprise Edition – Achieve the Highest Levels of Security

Partager :

WordPress:

MySQL Group Replication

Déployer un cluster de 3 nœuds

Configurer les instances MySQL

Configuration spécifique à MySQL Group Replication

Activation du plugin MySQL Group Replication

Utilisateur de restauration

Vérification de la configuration

Déployer le cluster

Amorcer le cluster

Ajouter des nœuds

Ajout du nœud 2

Ajout du nœud 3

Identifier le nœud primaire

Arrêt du nœud primaire

Faire revenir un nœud dans le cluster

Router, proxy and Co

Partager :

WordPress:

MySQL dans le cloud