SED501 - Administration de MySQL NDB Cluster

Dans ce cours, vous allez apprendre par la pratique :

• LAB #1 - Configurer les Nœuds,
• LAB #2 - Installation de NDB Cluster sur Linux,
• LAB #3 - Configuration Initial de NDB Cluster,
• LAB #4 - Premier Démarrage de NDB Cluster,
• LAB #5 - La Sauvegarde de NDB Cluster,
• LAB #6 - Le Rolling Restart d'un NDB Cluster,
• LAB #7 - La Journalisation de NDB Cluster,
• LAB #8 - Les Requêtes SQL de NDB Cluster,
• LAB #9 - L'Ajout de Nœuds de Données NDB Cluster En Ligne,
• LAB #10 - Les Privilèges Distribués de NDB Cluster,
• LAB #11 - La Mise en Place de la Réplication de NDB Cluster,
• LAB #12 - Le Démarrage de la Réplication de NDB Cluster en Utilisant un Seul Canal,
• LAB #13 - Mise en Place d'un Cluster à Haute Disponibilité,
• LAB #14 - Tester le Cluster à Haute Disponibilité.

MySQL Cluster est une solution de haute disponibilité MySQL qui :

• utilise un moteur de stockage clusterisé appelé NDBCLUSTER ou simplement NDB (Network DataBase),
• utilise un binaire mysqld différent du binaire inclus dans le MySQL standard,
• est compatible avec Linux, Windows et Solaris - voir ce lien,
• peut être téléchargé gratuitement à ce lien.

Le cluster est composé de différent types de nœuds ( services ) :

• Nœuds de données avec le service ndbd,
  • données du cluster,
• Nœud(s) SQL avec le service mysqld,
  • accès aux données du cluster via mysqld en utilisant les options –ndbcluster et –ndb-connectstring,
• Nœud de management avec le service ndb_mgmd,
  • données de configuration, démarrage et arrêt des nœuds, sauvegardes.

Il est possible d'avoir plusieurs nœuds (services) hébergés sur la même machine physique. Dans ce cas on parle d'un cluster host. Il n'est pas conseillé d'avoir plusieurs instances de ndbd qui tournent sur la même machine physique.

Chaque nœud dans le cluster obtient sa configuration du nœud de management. Ce nœud est aussi responsable pour la journalisation du cluster.

Lors de la modification de la configuration un rolling restart est fait ou chaque nœud est redémarré à tour de rôle.

Quand les données sont sauvegardées sur disque on parle d'un checkpoint :

• Local Checkpoint (LCP) : La sauvegarde des données d'un nœud de données sur son disque.
• Global Checkpoint (GCP) : La synchronisation des transactions de tous les nœuds.

Chaque groupe de nœuds doit avoir le même nombre de noeuds.

Les données sont partitionnées dans autant de partitions qu'il y a de nœuds.

Chaque nœud dans un groupe de nœuds stocke un replica (copie d'une partition). Il y a donc autant de replicas qu'il y a de nœuds dans un groupe de nœuds.

La haute disponibilité est donc assurée dans le cas où au moins un nœud par groupe fonctionne :

Les principaux caractéristiques du moteur NDBCLUSTER sont :

• Il est transactionnel de type ACID (Atomicité, Cohérence, Isolation, Durabilité),
• Il implémente le MVCC (Multi Version Concurrency Control) qui permet d'avoir des lectures qui ne bloquent pas des écritures et inversement,
• Il implémente un verrou au niveau enregistrement,
• Il implémente la réplication synchrone,
• Il implémente le basculement automatique sur un autre nœud en cas de panne et la synchronisation automatique du nœud à son démarrage,
• Il peut être sauvegardé à chaud,
• Il permet le dimensionnement des requêtes de lecture et d'écriture,
• Il utilise l'architecture Shared Nothing où les nœuds ne partagent pas un disque de données,
• Il implémente l’absence du SPOF (Single Point Of Failure).

MySQL NDB Cluster a déjà été téléchargé vers le répertoire /opt/mysql dans chaque noeud :

root@debian9:~# cd /opt
root@debian9:/opt# wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
--2018-12-29 09:47:58--  https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com//Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz [following]
--2018-12-29 09:47:59--  https://cdn.mysql.com//Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 23.212.225.186
Connecting to cdn.mysql.com (cdn.mysql.com)|23.212.225.186|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 897219779 (856M) [application/x-tar-gz]
Saving to: ‘mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz’

mysql-cluster-gpl-7.5.12-linux-glibc2.12-x 100%[=======================================================================================>] 855.66M  3.71MB/s   in 3m 54s 

2018-12-29 09:51:54 (3.65 MB/s) - ‘mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz’ saved [897219779/897219779]

root@debian9:/opt# tar -xf mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz 
root@debian9:/opt# mv mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64 mysql  

Le dépendances suivantes ont déjà été installées dans chaque noeud :

root@debian8:~# apt-get install python-paramiko python-pycryptopp python-cryptography tree

Les noeuds ont été déjà configurés de la façon suivante :

Modifiez la directive PermitRootLogin du fichier /etc/ssh/sshd_config :

root@ndb02:~# vi /etc/ssh/sshd_config
root@ndb02:~# cat /etc/ssh/sshd_config
...
PermitRootLogin yes 
...

Créez le groupe et l'utilisateur mysql :

root@ndb02:~# groupadd mysql
root@ndb02:~# useradd -g mysql -s /bin/false mysql

Installez libaio1 et libaio-dev :

root@ndb02:~# apt-get install libaio1 libaio-dev

Redémarrez NDB02 :

root@ndb02:~# shutdown -r now

Modifiez la directive PermitRootLogin du fichier /etc/ssh/sshd_config :

root@ndb03:~# vi /etc/ssh/sshd_config
root@ndb03:~# cat /etc/ssh/sshd_config
...
PermitRootLogin yes 
...

Redémarrez NDB03 :

root@ndb03:~# shutdown -r now

Modifiez la directive PermitRootLogin du fichier /etc/ssh/sshd_config :

root@ndb04:~# vi /etc/ssh/sshd_config
root@ndb04:~# cat /etc/ssh/sshd_config
...
PermitRootLogin yes 
...

Redémarrez NDB04 :

root@ndb04:~# shutdown -r now

Ouvrez un terminal graphique sur ndb01 et tapez la commande suivante :

root@ndb01:~# /opt/mysql/bin/ndb_setup.py 

Si le navigateur web ne se lance pas automatiquement, ouvrez-le et saisissez l'adresse suivante : http://localhost:8081/welcome.html.

Cliquez sur le lien de création d'un nouveau cluster :

Remplissez la première page en faisant référence aux indications suivantes :

• Cluster name,
  • Un nom unique pour identifier le cluster,
  • La valeur par défaut est MyCluster,
• Host list,
  • Une liste d'adresses IP des hôtes, membres du cluster, séparés par des virgules,
  • La valeur par défaut est 127.0.0.1,
• Application type,
  • Un choix entre Simple testing, Web et Real-time où :
    • Simple testing indique une plate-forme de test non-compatible avec la production,
    • Web indique un système avec une performance maximisée par rapport au hardware,
    • Real-time indique un système avec une performance maximisée par rapport au hardware et une augmentation de la sensibilité aux time-outs,
  • La valeur par défaut est Simple testing,
• Write load,
  • Un choix entre Low, Medium et High où :
    • Low indique moins de 100 opérations d'écriture par seconde,
    • Medium indique entre 100 et 1000 opérations d'écriture par seconde,
    • Real-time plus de 1000 opérations d'écriture par seconde,
  • La valeur par défaut est Medium,
• SSH Credentials,
  • Un choix entre une authentification par clefs asymétriques ou par mot de passe,
    • Notez que cette authentification doit être valide pour tous les noeuds du cluster,
  • La valeur par défaut est clefs asymétriques.

Décochez Key based SSH, indiquez le nom d'utilisateur root et le mot de passe fenestros puis cliquez sur le bouton Next :

Vos nœuds seront automatiquement détectés. Modifiez les chemins comme il est indiqué dans l'exemple suivant puis cliquez sur le bouton Next :

Passez en revue la topologie physique du cluster. Notez comment l'installateur a réparti les différents services afin d'obtenir une solution à haute disponibilité.

Supprimez les services afin de ne laisser que les quatre services suivants en les placant sur les nœuds indiqués :

Vous obtiendrez le résultat suivant :

Cliquez sur le bouton Next :

Passez en revue la topologie par couche du cluster puis cliquez sur le bouton Next :

Cliquez sur le bouton Deploy and start cluster :

Lors du premier démarrage du cluster, le SQL node 1 ne démarrera pas. Démarrez donc ce service manuellement :

root@ndb02:~# vi /opt/mysql/bin/mysql.init
root@ndb02:~# cat /opt/mysql/bin/mysql.init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'fenestros';
root@ndb02:~# chown -R mysql:mysql /opt/MySQL_Cluster/55
root@ndb02:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &

Revenez à l'interface HTML dans ndb01 et vérifiez que tous les services soient démarrés :

Vérifiez que tous les noeuds sont en état de fonctionnement :

root@ndb01:~# /opt/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

ndb_mgm> ALL REPORT MEMORY
Connected to Management Server at: localhost:1186
Node 1: Data usage is 71%(23 32K pages of total 32)
Node 1: Index usage is 12%(20 8K pages of total 160)
Node 2: Data usage is 71%(23 32K pages of total 32)
Node 2: Index usage is 12%(20 8K pages of total 160)

ndb_mgm> exit
root@ndb01:~#

L'état du cluster peut aussi être vu directement à partir du shell :

root@ndb01:~# /opt/mysql/bin/ndb_mgm -e SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

Vérifiez que vous pouvez vous connecter au noeud SQL :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql>

Dernièrement, vérifiez l'activation du moteur NDBCLUSTER en créant une table :

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW CREATE TABLE ctest \G
*************************** 1. row ***************************
       Table: ctest
Create Table: CREATE TABLE `ctest` (
  `i` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> exit

L'exécutable ndb_mgmd est configuré par le fichier global de configuration du cluster :

root@ndb01:~# cat /opt/MySQL_Cluster/49/config.ini
#
# Configuration file for MyCluster
#

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=49
HostName=10.0.2.80
DataDir=/opt/MySQL_Cluster/49/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M

[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=1M
IndexMemory=1M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
DiskPageBufferMemory=64M
SharedGlobalMemory=20M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=16384
BatchSizePerLocalScan=512
FragmentLogFileSize=64M
NoOfFragmentLogFiles=16
RedoBuffer=32M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

[NDBD]
NodeId=1
HostName=10.0.2.100
DataDir=/opt/MySQL_Cluster/1/

[NDBD]
NodeId=2
HostName=10.0.2.110
DataDir=/opt/MySQL_Cluster/2/

[MYSQLD DEFAULT]

[MYSQLD]
NodeId=55
HostName=10.0.2.90

Dans ce fichier se trouvent les sections suivantes :

• [NDB_MGMD DEFAULT],
  • Cette section contient des directives dont les valeurs s'appliquent à tous les neouds de gestion,
• [NDB_MGMD],
  • Cette section définit un noeud de gestion,
• [TCP DEFAULT],
  • Cette section contient des directives dont les valeurs s'appliquent au protocole TCP,
• [NDBD DEFAULT],
  • Cette section contient des directives dont les valeurs s'appliquent à tous les neouds de données,
• [NDBD],
  • Ces sections définissent des noeuds de données,
• [MYSQLD DEFAULT],
  • Cette section contient des directives dont les valeurs s'appliquent à tous les neouds de MySQL,
• [MYSQLD],
  • Cette section définit un noeud de MySQL.

Les directives les plus importantes dans ce fichier sont :

L'exécutable mysqld est configuré par le fichier my.cnf :

root@ndb02:~# cat /opt/MySQL_Cluster/55/my.cnf
#
# Configuration file for MyCluster
# Generated by mcc
#
[mysqld]
log-error=mysqld.55.err
datadir="/opt/MySQL_Cluster/55/data"
tmpdir="/opt/MySQL_Cluster/55/tmp"
basedir="/opt/mysql/"
port=3306
ndbcluster=on
ndb-nodeid=55
ndb-connectstring=10.0.2.80:1186,
socket="/opt/MySQL_Cluster/55/mysql.socket"

Les directives NDBCLUSTER dans ce fichier sont :

Une sauvegarde d'une base de données NDB est un snapshot à un instant t qui consiste en trois parties :

• Métadonnées (Metadata),
  • Les noms et les définitions de toutes les tables de la base de données,
• Enregistrements (Table records),
  • Les données stockées dans les tables au moement de la sauvegarde,
• Journal des transactions (Transaction log),
  • Un journal séquentiel qui détaille comment et quand les données ont été stockées dans la base de données.

Chaque partie est sauvagardée sur tous les noeuds participant à la sauvegarde dans trois fichiers sur disque :

• BACKUP-backup_id.node_id.ctl,
  • Un fichier de contrôle contenant les métadonnées,
  • Chaque noeud sauvegarde les mêmes définitions des tables pour toutes les tables dans toutes les bases de données du cluster,
• BACKUP-backup_id-0.node_id.data,
  • Un fichier contenant les enregistrements de la partition du neoud,
  • Le fichier commence avec une entête qui détaille les tables à partir desquelles les enregistrements ont été sauvegardés,
  • Le fichier se termine avec un bas de page contenant un checksum pour tous les enregistrements,
• BACKUP-backup_id.node_id.log,
  • Le journal séquentiel.

Les valeurs de cinq directives ont un impact sur le déroulement et sur la performance des sauvegardes :

• BackupDataBufferSize,
  • La quantité de mémoire utilisée en tant que tampon des données avant écriture sur disque,
• BackupLogBufferSize,
  • La quantité de mémoire utilisée en tant que tampon des enregistrements des journaux avant écriture sur disque,
• BackupMemory,
  • La quantité de mémoire totale utilisée en tant que tampons. Ce chiffre est égal donc à l'addition de BackupDataBufferSize et BackupLogBufferSize,
• BackupWriteSize,
  • La taille par défaut des blocs écrits sur disque,
• BackupMaxWriteSize,
  • La taille maximale des blocs écrits sur disque.

Dans le cas de notre cluster, les valeurs par défaut sont :

root@ndb01:~# cat /opt/MySQL_Cluster/49/config.ini
#
# Configuration file for MyCluster
#

...
[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
...

La commande START BACKUP est utilisée pour créer une sauvegarde. Cette commande peut prendre plusieurs options :

Par exemple :

root@ndb01:~# /opt/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> START BACKUP
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 1: Backup 1 started from node 49
ndb_mgm> Node 1: Backup 1 started from node 49 completed
 StartGCP: 33703 StopGCP: 33706
 #Records: 2063 #LogRecords: 0
 Data: 52408 bytes Log: 0 bytes

ndb_mgm> 

Les sauvegardes se trouvent sur chaque noeud :

root@ndb01:~# updatedb
root@ndb01:~# locate BACKUP
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1-0.1.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1-0.2.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.1.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.1.log
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.2.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.2.log
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2-0.1.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2-0.2.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.1.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.1.log
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.2.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.2.log
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1-0.1.Data
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1-0.2.Data
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.1.ctl
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.1.log
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.2.ctl
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.2.log
...

root@ndb02:~# updatedb
root@ndb02:~# locate BACKUP
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1-0.1.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1-0.2.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.1.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.1.log
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.2.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.2.log
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2-0.1.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2-0.2.Data
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.1.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.1.log
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.2.ctl
/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-2.2.log
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1-0.1.Data
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1-0.2.Data
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.1.ctl
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.1.log
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.2.ctl
/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.2.log

root@ndb03:~# updatedb
root@ndb03:~# locate BACKUP
/opt/MySQL_Cluster/1/BACKUP
/opt/MySQL_Cluster/1/BACKUP/BACKUP-1
/opt/MySQL_Cluster/1/BACKUP/BACKUP-1/BACKUP-1-0.1.Data
/opt/MySQL_Cluster/1/BACKUP/BACKUP-1/BACKUP-1.1.ctl
/opt/MySQL_Cluster/1/BACKUP/BACKUP-1/BACKUP-1.1.log
...

root@ndb04:~# updatedb
root@ndb04:~# locate BACKUP
/opt/MySQL_Cluster/2/BACKUP
/opt/MySQL_Cluster/2/BACKUP/BACKUP-1
/opt/MySQL_Cluster/2/BACKUP/BACKUP-1/BACKUP-1-0.2.Data
/opt/MySQL_Cluster/2/BACKUP/BACKUP-1/BACKUP-1.2.ctl
/opt/MySQL_Cluster/2/BACKUP/BACKUP-1/BACKUP-1.2.log
...

Mettez le cluster en mode single user :

ndb_mgm> ENTER SINGLE USER MODE 55
Single user mode entered
Access is granted for API node 55 only.

Le statut peut être consulté à partir du noeud SQL, le noeud 55 :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> USE ndbinfo;
Database changed

mysql> SELECT * FROM nodes;
+---------+--------+------------+-------------+-------------------+
| node_id | uptime | status     | start_phase | config_generation |
+---------+--------+------------+-------------+-------------------+
|       1 |   3779 | SINGLEUSER |           0 |                 1 |
|       2 |   3774 | SINGLEUSER |           0 |                 1 |
+---------+--------+------------+-------------+-------------------+
2 rows in set (0.00 sec)

mysql> 

Restaurez maintenant la sauvegarde :

root@ndb01:~# /opt/mysql/bin/ndb_restore -n 1 -b 1 --backup_path=/opt/mysql/mysql-test/suite/ndb/backups/50/
Nodeid = 1
Backup Id = 1
backup path = /opt/mysql/mysql-test/suite/ndb/backups/50/
2019-01-07 11:07:39 [restore_metadata] Read meta data file header
Opening file '/opt/mysql/mysql-test/suite/ndb/backups/50/BACKUP-1.1.ctl'
File size 7936 bytes
Backup version in files: ndb-5.1.2
2019-01-07 11:07:39 [restore_metadata] Load content
Stop GCP of Backup: 331
2019-01-07 11:07:39 [restore_metadata] Get number of Tables
2019-01-07 11:07:39 [restore_metadata] Validate Footer
2019-01-07 11:07:39 [restore_metadata] Restore objects (tablespaces, ..)
2019-01-07 11:07:39 [restore_metadata] Restoring tables
2019-01-07 11:07:39 [restore_metadata] Save foreign key info
2019-01-07 11:07:39 [restore_data] Start restoring table data

NDBT_ProgramExit: 0 - OK

root@ndb01:~# /opt/mysql/bin/ndb_restore -n 1 -b 1 --backup_path=/opt/mysql/mysql-test/suite/ndb/backups/51/
Nodeid = 1
Backup Id = 1
backup path = /opt/mysql/mysql-test/suite/ndb/backups/51/
2019-01-07 11:08:36 [restore_metadata] Read meta data file header
Opening file '/opt/mysql/mysql-test/suite/ndb/backups/51/BACKUP-1.1.ctl'
File size 12320 bytes
Backup version in files: ndb-5.1.10
2019-01-07 11:08:36 [restore_metadata] Load content
Stop GCP of Backup: 151
2019-01-07 11:08:36 [restore_metadata] Get number of Tables
2019-01-07 11:08:36 [restore_metadata] Validate Footer
2019-01-07 11:08:36 [restore_metadata] Restore objects (tablespaces, ..)
2019-01-07 11:08:36 [restore_metadata] Restoring tables
2019-01-07 11:08:36 [restore_metadata] Save foreign key info
2019-01-07 11:08:36 [restore_data] Start restoring table data

NDBT_ProgramExit: 0 - OK

Quittez le mode Single User :

root@ndb01:~# /opt/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> EXIT SINGLE USER MODE
Connected to Management Server at: localhost:1186
Exiting single user mode in progress.
Use ALL STATUS or SHOW to see when single user mode has been exited.
ndb_mgm> ALL STATUS
Node 1: started (mysql-5.7.24 ndb-7.5.12)
Node 2: started (mysql-5.7.24 ndb-7.5.12)

ndb_mgm> 

Vérifiez le statut à partir du noeud SQL :

<root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> USE ndbinfo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM nodes;
+---------+--------+---------+-------------+-------------------+
| node_id | uptime | status  | start_phase | config_generation |
+---------+--------+---------+-------------+-------------------+
|       1 |   4763 | STARTED |           0 |                 1 |
|       2 |   4759 | STARTED |           0 |                 1 |
+---------+--------+---------+-------------+-------------------+
2 rows in set (0.00 sec)

mysql> 

Arrêtez maintenant le cluster :

root@ndb01:~# /opt/mysql/bin/ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

Modifiez la configuration du cluster :

root@ndb01:~# vi /opt/MySQL_Cluster/49/config.ini 
root@ndb01:~# cat /opt/MySQL_Cluster/49/config.ini 
#
# Configuration file for MyCluster
#

...
DataMemory=2M
IndexMemory=2M
...

Démarrez maintenant les composants du cluster à tour de rôle :

root@ndb01:~# /opt/mysql/bin/ndb_mgmd ndb_mgmd -f /opt/MySQL_Cluster/49/config.ini --config-dir=/opt/MySQL_Cluster/49
MySQL Cluster Management Server mysql-5.7.24 ndb-7.5.12

root@ndb03:~# /opt/mysql/bin/ndbmtd --ndb-nodeid=1 --ndb-connectstring=10.0.2.80:1186
2018-12-30 09:17:54 [ndbd] INFO     -- Angel connected to '10.0.2.80:1186'
2018-12-30 09:17:54 [ndbd] INFO     -- Angel allocated nodeid: 1

root@ndb04:~# /opt/mysql/bin/ndbmtd --ndb-nodeid=2 --ndb-connectstring=10.0.2.80:1186
2018-12-30 09:18:00 [ndbd] INFO     -- Angel connected to '10.0.2.80:1186'
2018-12-30 09:18:00 [ndbd] INFO     -- Angel allocated nodeid: 2

root@ndb02:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &
[1] 30895

NDB Cluster fournit deux types de journaux :

• Le Journal du Cluster (Cluster Log),
  • Ce journal se trouve dans le DataDir du noeud de management et s'appelle ndb_node_id_cluster.log,
• Les Journaux des Noeuds (Node Logs),
  • Ces journaux se trouvent dans le DataDir de chaque noeud et s'appellent ndb_node_id_out.log.

Chaque trace comporte trois critères :

• Catégorie - STARTUP, SHUTDOWN, STATISTICS, CHECKPOINT, NODERESTART, CONNECTION, ERROR ou INFO.
• Priorité - Un chiffre entre 0 et 15 où 0 est leplus important,
• Séverité - ALERT, CRITICAL, ERROR, WARNING, INFO, ou DEBUG.

root@ndb01:~# tail /opt/MySQL_Cluster/49/ndb_49_cluster.log
2019-01-07 11:58:42 [MgmtSrvr] INFO     -- Node 2: Data usage is 93%(30 32K pages of total 32)
2019-01-07 11:58:42 [MgmtSrvr] INFO     -- Node 2: Index usage is 18%(29 8K pages of total 160)
2019-01-07 11:59:05 [MgmtSrvr] INFO     -- Node 1: Data usage is 93%(30 32K pages of total 32)
2019-01-07 11:59:05 [MgmtSrvr] INFO     -- Node 1: Index usage is 18%(29 8K pages of total 160)
2019-01-07 11:59:12 [MgmtSrvr] INFO     -- Node 2: Data usage is 93%(30 32K pages of total 32)
2019-01-07 11:59:12 [MgmtSrvr] INFO     -- Node 2: Index usage is 18%(29 8K pages of total 160)
2019-01-07 11:59:35 [MgmtSrvr] INFO     -- Node 1: Data usage is 93%(30 32K pages of total 32)
2019-01-07 11:59:35 [MgmtSrvr] INFO     -- Node 1: Index usage is 18%(29 8K pages of total 160)
2019-01-07 11:59:42 [MgmtSrvr] INFO     -- Node 2: Data usage is 93%(30 32K pages of total 32)
2019-01-07 11:59:42 [MgmtSrvr] INFO     -- Node 2: Index usage is 18%(29 8K pages of total 160)

root@ndb03:~# more /opt/MySQL_Cluster/1/ndb_1_out.log 
2018-12-29 13:41:12 [ndbd] INFO     -- Angel pid: 9842 started child: 9843
2018-12-29 13:41:12 [ndbd] INFO     -- Normal start of data node using checkpoint and log info if existing
2018-12-29 13:41:12 [ndbd] INFO     -- Configuration fetched from '10.0.2.80:1186', generation: 1
2018-12-29 13:41:12 [ndbd] INFO     -- Changing directory to '/opt/MySQL_Cluster/1/'
2018-12-29 13:41:12 [ndbd] INFO     -- MaxNoOfTriggers set to 25208
NDBMT: non-mt
2018-12-29 13:41:12 [ndbd] INFO     -- NDB Cluster -- DB node 1
2018-12-29 13:41:12 [ndbd] INFO     -- mysql-5.7.24 ndb-7.5.12 --
2018-12-29 13:41:12 [ndbd] INFO     -- WatchDog timer is set to 6000 ms
2018-12-29 13:41:12 [ndbd] INFO     -- Memory Allocation for global memory pools Starting
2018-12-29 13:41:12 [ndbd] INFO     -- numa_set_interleave_mask(numa_all_nodes) : no numa support
2018-12-29 13:41:12 [ndbd] INFO     -- Ndbd_mem_manager::init(1) min: 110Mb initial: 130Mb
2018-12-29 13:41:12 [ndbd] INFO     -- Touch Memory Starting, 1152 pages, page size = 32768
2018-12-29 13:41:12 [ndbd] INFO     -- Touch Memory Completed
2018-12-29 13:41:12 [ndbd] INFO     -- Lock Memory Starting, 1152 pages, page size = 32768
2018-12-29 13:41:12 [ndbd] INFO     -- Lock memory Completed
2018-12-29 13:41:12 [ndbd] INFO     -- Adding 36Mb to ZONE_LO (1,1151)
2018-12-29 13:41:12 [ndbd] INFO     -- Memory Allocation for global memory pools Completed
2018-12-29 13:41:12 [ndbd] INFO     -- Loading blocks for data node run-time environment
2018-12-29 13:41:12 [ndbd] INFO     -- Starting Sending and Receiving services
Started thread, index = 0, id = 9845, type = SocketClientThread
Started thread, index = 1, id = 9844, type = WatchDogThread
Started thread, index = 2, id = 9846, type = SocketServerThread
2018-12-29 13:41:12 [ndbd] INFO     -- Starting the data node run-time environment
2018-12-29 13:41:12 [ndbd] INFO     -- First START_ORD executed to connect MGM servers
2018-12-29 13:41:12 [ndbd] INFO     -- Received second START_ORD as part of normal start
2018-12-29 13:41:12 [ndbd] INFO     -- Disconnect all non-MGM servers
2018-12-29 13:41:12 [ndbd] INFO     -- Start excuting the start phases
2018-12-29 13:41:12 [ndbd] INFO     -- Sending READ_CONFIG_REQ to index = 0, name = CMVMI
2018-12-29 13:41:12 [ndbd] INFO     -- Start initiated (mysql-5.7.24 ndb-7.5.12)
NDBFS/AsyncFile: Allocating 308024 for In/Deflate buffer
Started thread, index = 4, id = 9847, type = NdbfsThread
2018-12-29 13:41:12 [ndbd] INFO     -- Touch Memory Starting, 3016 pages, page size = 32768
2018-12-29 13:41:12 [ndbd] INFO     -- Touch Memory Completed
2018-12-29 13:41:12 [ndbd] INFO     -- Lock Memory Starting, 3016 pages, page size = 32768
2018-12-29 13:41:12 [ndbd] INFO     -- Lock memory Completed
2018-12-29 13:41:12 [ndbd] INFO     -- Adding 95Mb to ZONE_LO (1152,3016)
2018-12-29 13:41:12 [ndbd] INFO     -- Sending READ_CONFIG_REQ to index = 1, name = NDBFS
NDBFS/AsyncFile: Allocating 308024 for In/Deflate buffer
--More--(1%)

root@ndb04:~# more /opt/MySQL_Cluster/2/ndb_2_out.log 
2018-12-29 13:41:54 [ndbd] INFO     -- Angel pid: 8857 started child: 8858
2018-12-29 13:41:54 [ndbd] INFO     -- Normal start of data node using checkpoint and log info if existing
2018-12-29 13:41:54 [ndbd] INFO     -- Configuration fetched from '10.0.2.80:1186', generation: 1
2018-12-29 13:41:54 [ndbd] INFO     -- Changing directory to '/opt/MySQL_Cluster/2/'
2018-12-29 13:41:54 [ndbd] INFO     -- MaxNoOfTriggers set to 25208
NDBMT: non-mt
2018-12-29 13:41:54 [ndbd] INFO     -- NDB Cluster -- DB node 2
2018-12-29 13:41:54 [ndbd] INFO     -- mysql-5.7.24 ndb-7.5.12 --
2018-12-29 13:41:54 [ndbd] INFO     -- WatchDog timer is set to 6000 ms
2018-12-29 13:41:54 [ndbd] INFO     -- Memory Allocation for global memory pools Starting
2018-12-29 13:41:54 [ndbd] INFO     -- numa_set_interleave_mask(numa_all_nodes) : no numa support
2018-12-29 13:41:54 [ndbd] INFO     -- Ndbd_mem_manager::init(1) min: 110Mb initial: 130Mb
2018-12-29 13:41:54 [ndbd] INFO     -- Touch Memory Starting, 1152 pages, page size = 32768
2018-12-29 13:41:54 [ndbd] INFO     -- Touch Memory Completed
2018-12-29 13:41:54 [ndbd] INFO     -- Lock Memory Starting, 1152 pages, page size = 32768
2018-12-29 13:41:54 [ndbd] INFO     -- Lock memory Completed
2018-12-29 13:41:54 [ndbd] INFO     -- Adding 36Mb to ZONE_LO (1,1151)
2018-12-29 13:41:54 [ndbd] INFO     -- Memory Allocation for global memory pools Completed
2018-12-29 13:41:54 [ndbd] INFO     -- Loading blocks for data node run-time environment
2018-12-29 13:41:54 [ndbd] INFO     -- Starting Sending and Receiving services
Started thread, index = 0, id = 8860, type = SocketClientThread
Started thread, index = 1, id = 8859, type = WatchDogThread
Started thread, index = 2, id = 8861, type = SocketServerThread
2018-12-29 13:41:54 [ndbd] INFO     -- Starting the data node run-time environment
2018-12-29 13:41:54 [ndbd] INFO     -- First START_ORD executed to connect MGM servers
2018-12-29 13:41:54 [ndbd] INFO     -- Received second START_ORD as part of normal start
2018-12-29 13:41:54 [ndbd] INFO     -- Disconnect all non-MGM servers
2018-12-29 13:41:54 [ndbd] INFO     -- Start excuting the start phases
2018-12-29 13:41:54 [ndbd] INFO     -- Sending READ_CONFIG_REQ to index = 0, name = CMVMI
2018-12-29 13:41:54 [ndbd] INFO     -- Start initiated (mysql-5.7.24 ndb-7.5.12)
NDBFS/AsyncFile: Allocating 308024 for In/Deflate buffer
Started thread, index = 4, id = 8862, type = NdbfsThread
2018-12-29 13:41:54 [ndbd] INFO     -- Touch Memory Starting, 3016 pages, page size = 32768
2018-12-29 13:41:54 [ndbd] INFO     -- Touch Memory Completed
2018-12-29 13:41:54 [ndbd] INFO     -- Lock Memory Starting, 3016 pages, page size = 32768
2018-12-29 13:41:54 [ndbd] INFO     -- Lock memory Completed
2018-12-29 13:41:54 [ndbd] INFO     -- Adding 95Mb to ZONE_LO (1152,3016)
2018-12-29 13:41:54 [ndbd] INFO     -- Sending READ_CONFIG_REQ to index = 1, name = NDBFS
NDBFS/AsyncFile: Allocating 308024 for In/Deflate buffer
--More--(1%)

ndb_mgm est utilisé pour configurer et pour gérer les journaux grâce aux sous-commandes suivantes :

• CLUSTERLOG ON,
  • Active le journal du cluster,
• CLUSTERLOG OFF,
  • Désactive le journal du cluster,
• CLUSTERLOG INFO,
  • Fournit des informations concernant la configuration des journaux,
• node_id CLUSTERLOG category=priority,
  • Journalise des traces d'une priorité inférieure ou égale à priority pour la catégorie category stipulée sur le noeud node_id spécifié,
• CLUSTERLOG FILTER severity_level,
  • Active ou désactive une séverité donnée.

Par exemple :

ndb_mgm> CLUSTERLOG INFO
Severities enabled: INFO WARNING ERROR CRITICAL ALERT 
ndb_mgm> CLUSTERLOG OFF
Cluster logging is disabled
ndb_mgm> CLUSTERLOG INFO
Cluster logging is disabled.
ndb_mgm> CLUSTERLOG ON
Cluster logging is enabled.
ndb_mgm> CLUSTERLOG INFO
Severities enabled: INFO WARNING ERROR CRITICAL ALERT 
ndb_mgm> CLUSTERLOG FILTER ALERT
ALERT disabled
ndb_mgm> CLUSTERLOG INFO
Severities enabled: INFO WARNING ERROR CRITICAL 
ndb_mgm> CLUSTERLOG FILTER ALERT
ALERT enabled
ndb_mgm> CLUSTERLOG INFO
Severities enabled: INFO WARNING ERROR CRITICAL ALERT 
ndb_mgm> 2 CLUSTERLOG INFO=7
Executing CLUSTERLOG INFO=7 on node 2 OK!
ndb_mgm> 2 CLUSTERLOG INFO=15
Executing CLUSTERLOG INFO=15 on node 2 OK!

Les priorités par défaut pour tous les noeuds sont :

Les niveaux de sévérité sont :

Dernièrement, afin de déboguer le cluster, augmentez la verbosité des journaux :

ndb_mgm> ALL CLUSTERLOG STATISTICS=15
Executing CLUSTERLOG STATISTICS=15 on node 1 OK!
Executing CLUSTERLOG STATISTICS=15 on node 2 OK!

Cette requête indique des informations concernant la connexion du serveur au cluster, la création et l'utilisation des objets NDB ainsi que le journalisation binaire necéssaire pour la réplication :

mysql> SHOW ENGINE NDB STATUS;
+------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type       | Name                  | Status                                                                                                                                                                       |
+------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ndbcluster | connection            | cluster_node_id=55, connected_host=10.0.2.80, connected_port=1186, number_of_data_nodes=2, number_of_ready_data_nodes=2, connect_count=3                                  |
| ndbcluster | NdbTransaction        | created=2, free=2, sizeof=368                                                                                                                                                |
| ndbcluster | NdbOperation          | created=4, free=4, sizeof=944                                                                                                                                                |
| ndbcluster | NdbIndexScanOperation | created=0, free=0, sizeof=1152                                                                                                                                               |
| ndbcluster | NdbIndexOperation     | created=0, free=0, sizeof=952                                                                                                                                                |
| ndbcluster | NdbRecAttr            | created=0, free=0, sizeof=88                                                                                                                                                 |
| ndbcluster | NdbApiSignal          | created=16, free=16, sizeof=144                                                                                                                                              |
| ndbcluster | NdbLabel              | created=0, free=0, sizeof=200                                                                                                                                                |
| ndbcluster | NdbBranch             | created=0, free=0, sizeof=32                                                                                                                                                 |
| ndbcluster | NdbSubroutine         | created=0, free=0, sizeof=72                                                                                                                                                 |
| ndbcluster | NdbCall               | created=0, free=0, sizeof=24                                                                                                                                                 |
| ndbcluster | NdbBlob               | created=0, free=0, sizeof=496                                                                                                                                                |
| ndbcluster | NdbReceiver           | created=0, free=0, sizeof=128                                                                                                                                                |
| ndbcluster | NdbLockHandle         | created=0, free=0, sizeof=48                                                                                                                                                 |
| ndbcluster | binlog                | latest_epoch=159618164588546, latest_trans_epoch=153261612990483, latest_received_binlog_epoch=0, latest_handled_binlog_epoch=153192893513728, latest_applied_binlog_epoch=0 |
+------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)

Cette requête indique la prise en charge des moteurs par MySQL :

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Cette requête indique la prise en charge des moteurs NDB par MySQL :

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE LIKE 'NDB%';
+------------+---------+-------------------------------------------------+--------------+------+------------+
| ENGINE     | SUPPORT | COMMENT                                         | TRANSACTIONS | XA   | SAVEPOINTS |
+------------+---------+-------------------------------------------------+--------------+------+------------+
| ndbcluster | YES     | Clustered, fault-tolerant tables                | YES          | NO   | NO         |
| ndbinfo    | YES     | MySQL Cluster system information storage engine | NO           | NO   | NO         |
+------------+---------+-------------------------------------------------+--------------+------+------------+
2 rows in set (0.00 sec)

Cette requête indique les valeurs des variables concernant le moteur NDB :

mysql> SHOW VARIABLES LIKE 'NDB%'\G
*************************** 1. row ***************************
Variable_name: ndb_allow_copying_alter_table
        Value: ON
*************************** 2. row ***************************
Variable_name: ndb_autoincrement_prefetch_sz
        Value: 1
*************************** 3. row ***************************
Variable_name: ndb_batch_size
        Value: 32768
*************************** 4. row ***************************
Variable_name: ndb_blob_read_batch_bytes
        Value: 65536
*************************** 5. row ***************************
Variable_name: ndb_blob_write_batch_bytes
        Value: 65536
*************************** 6. row ***************************
Variable_name: ndb_cache_check_time
        Value: 0
*************************** 7. row ***************************
Variable_name: ndb_clear_apply_status
        Value: ON
*************************** 8. row ***************************
Variable_name: ndb_cluster_connection_pool
        Value: 1
*************************** 9. row ***************************
Variable_name: ndb_cluster_connection_pool_nodeids
        Value: 
*************************** 10. row ***************************
Variable_name: ndb_connectstring
        Value: 10.0.2.80:1186,
...

Cette requête indique les valeurs des variables concernant le moteur NDB :

mysql> SHOW STATUS LIKE 'NDB%' \G
*************************** 1. row ***************************
Variable_name: Ndb_api_wait_exec_complete_count
        Value: 262
*************************** 2. row ***************************
Variable_name: Ndb_api_wait_scan_result_count
        Value: 69
*************************** 3. row ***************************
Variable_name: Ndb_api_wait_meta_request_count
        Value: 571
*************************** 4. row ***************************
Variable_name: Ndb_api_wait_nanos_count
        Value: 33828168675516
*************************** 5. row ***************************
Variable_name: Ndb_api_bytes_sent_count
        Value: 110612
*************************** 6. row ***************************
Variable_name: Ndb_api_bytes_received_count
        Value: 708756
*************************** 7. row ***************************
Variable_name: Ndb_api_trans_start_count
        Value: 144
*************************** 8. row ***************************
Variable_name: Ndb_api_trans_commit_count
        Value: 124
*************************** 9. row ***************************
Variable_name: Ndb_api_trans_abort_count
        Value: 7
*************************** 10. row ***************************
Variable_name: Ndb_api_trans_close_count
        Value: 144
...

Pour pouvoir utiliser cette base de données, le plugin ndbinfo doit être activé :

mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL    | GPL     |
+----------------------------------+----------+--------------------+---------+---------+
47 rows in set (0.00 sec)

Cette base de données contient de tables munies d'informations spécifiques au moteur NDB :

mysql> USE ndbinfo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+---------------------------------+
| Tables_in_ndbinfo               |
+---------------------------------+
| arbitrator_validity_detail      |
| arbitrator_validity_summary     |
| blocks                          |
| cluster_locks                   |
| cluster_operations              |
| cluster_transactions            |
| config_nodes                    |
| config_params                   |
| config_values                   |
| counters                        |
| cpustat                         |
| cpustat_1sec                    |
| cpustat_20sec                   |
| cpustat_50ms                    |
| dict_obj_info                   |
| dict_obj_types                  |
| disk_write_speed_aggregate      |
| disk_write_speed_aggregate_node |
| disk_write_speed_base           |
| diskpagebuffer                  |
| locks_per_fragment              |
| logbuffers                      |
| logspaces                       |
| membership                      |
| memory_per_fragment             |
| memoryusage                     |
| nodes                           |
| operations_per_fragment         |
| processes                       |
| resources                       |
| restart_info                    |
| server_locks                    |
| server_operations               |
| server_transactions             |
| table_distribution_status       |
| table_fragments                 |
| table_info                      |
| table_replicas                  |
| tc_time_track_stats             |
| threadblocks                    |
| threads                         |
| threadstat                      |
| transporters                    |
+---------------------------------+
43 rows in set (0.00 sec)

mysql> 

mysql> SELECT * FROM memoryusage;
+---------+---------------------+--------+------------+----------+-------------+
| node_id | memory_type         | used   | used_pages | total    | total_pages |
+---------+---------------------+--------+------------+----------+-------------+
|       1 | Data memory         | 983040 |         30 |  1048576 |          32 |
|       1 | Index memory        | 237568 |         29 |  1310720 |         160 |
|       1 | Long message buffer | 393216 |       1536 | 33554432 |      131072 |
|       2 | Data memory         | 983040 |         30 |  1048576 |          32 |
|       2 | Index memory        | 237568 |         29 |  1310720 |         160 |
|       2 | Long message buffer | 262144 |       1024 | 33554432 |      131072 |
+---------+---------------------+--------+------------+----------+-------------+
6 rows in set (0.02 sec)

mysql> 

Ouvrez le fichier /opt/MySQL_Cluster/49/config.ini en édition et ajoutez les deux sections suivantes :

...
[NDBD]
NodeId=3
HostName=10.0.2.80

[NDBD]
NodeId=4
HostName=10.0.2.90
...

Vous obtiendrez :

root@ndb01:~# vi /opt/MySQL_Cluster/49/config.ini 
root@ndb01:~# cat /opt/MySQL_Cluster/49/config.ini 
#
# Configuration file for MyCluster
#

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=49
HostName=10.0.2.80
DataDir=/opt/MySQL_Cluster/49/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M

[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=1024M
IndexMemory=1512M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
DiskPageBufferMemory=64M
SharedGlobalMemory=20M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=16384
BatchSizePerLocalScan=512
FragmentLogFileSize=64M
NoOfFragmentLogFiles=16
RedoBuffer=32M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

[NDBD]
NodeId=1
HostName=10.0.2.100
DataDir=/opt/MySQL_Cluster/1/

[NDBD]
NodeId=2
HostName=10.0.2.110
DataDir=/opt/MySQL_Cluster/2/

[NDBD]
NodeId=3
HostName=10.0.2.80

[NDBD]
NodeId=4
HostName=10.0.2.90


[MYSQLD DEFAULT]

[MYSQLD]
NodeId=55
HostName=10.0.2.90

Re-démarrez le noeud de gestion :

root@ndb01:~# /opt/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> 49 STOP
Connected to Management Server at: localhost:1186
Node 49 has shutdown.
Disconnecting to allow Management Server to shutdown

ndb_mgm> exit
root@ndb01:~# /opt/mysql/bin/ndb_mgmd ndb_mgmd -f /opt/MySQL_Cluster/49/config.ini --config-dir=/opt/MySQL_Cluster/49 --reload
MySQL Cluster Management Server mysql-5.7.24 ndb-7.5.12

Constatez l'état du cluster :

root@ndb01:~# /opt/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)
id=3 (not connected, accepting connect from 10.0.2.80)
id=4 (not connected, accepting connect from 10.0.2.90)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

ndb_mgm> 

Procédez à un rolling restart :

ndb_mgm> 1 RESTART
Connected to Management Server at: localhost:1186
Node 1: Node shutdown initiated
Node 1: Node shutdown completed, restarting, no start.
Node 1 is being restarted

ndb_mgm> Node 1: Start initiated (version 7.5.12)
Node 1: Data usage increased to 93%(30 32K pages of total 32)
Node 1: Started (version 7.5.12)

ndb_mgm> 2 RESTART
Node 2: Node shutdown initiated
Node 2: Node shutdown completed, restarting, no start.
Node 2 is being restarted

ndb_mgm> Node 2: Start initiated (version 7.5.12)
Node 2: Data usage increased to 93%(30 32K pages of total 32)
Node 2: Started (version 7.5.12)

root@ndb02:~# /opt/mysql/bin/mysqladmin --socket="/opt/MySQL_Cluster/55/mysql.socket" -p shutdown
Enter password: fenestros
root@ndb02:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &
[3] 12710
[2]   Done                    /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init

Procédez à un démarrage initial des deux noeuds de données supplémentaires :

root@ndb01:~£ /opt/mysql/bin/ndbmtd --ndb-nodeid=3 --ndb-connectstring=10.0.2.80:1186 --initial
2019-01-07 15:25:42 [ndbd] INFO     -- Angel connected to '10.0.2.80:1186'
2019-01-07 15:25:42 [ndbd] INFO     -- Angel allocated nodeid: 3

root@ndb02:~# /opt/mysql/bin/ndbmtd --ndb-nodeid=4 --ndb-connectstring=10.0.2.80:1186 --initial
2018-12-30 15:17:07 [ndbd] INFO     -- Angel connected to '10.0.2.80:1186'
2018-12-30 15:17:07 [ndbd] INFO     -- Angel allocated nodeid: 4

Constatez l'état du cluster :

root@ndb01:~£ /opt/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)
id=3	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12, no nodegroup)
id=4	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12, no nodegroup)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

ndb_mgm> 

Créez le groupe de noeuds 1 :

ndb_mgm> CREATE NODEGROUP 3,4
Nodegroup 1 created

Constatez de nouveau l'état du cluster :

ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)
id=3	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 1)
id=4	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 1)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

ndb_mgm> 

Notez que les données et les index n'ont pas été redistribués auprès des nouveaux noeuds :

ndb_mgm> ALL REPORT MEMORY
Node 1: Data usage is 93%(30 32K pages of total 32)
Node 1: Index usage is 18%(29 8K pages of total 160)
Node 2: Data usage is 93%(30 32K pages of total 32)
Node 2: Index usage is 18%(29 8K pages of total 160)
Node 3: Data usage is 53%(17 32K pages of total 32)
Node 3: Index usage is 0%(0 8K pages of total 160)
Node 4: Data usage is 53%(17 32K pages of total 32)
Node 4: Index usage is 0%(0 8K pages of total 160)

ndb_mgm> 

La redistribution est effectuée à partir du noeud SQL :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| ctest          |
+----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE ctest ALGORITHM=INPLACE, REORGANIZE PARTITION;

Query OK, 0 rows affected (6.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

Constatez le résultat :

ndb_mgm> ALL REPORT MEMORY
Node 1: Data usage is 87%(28 32K pages of total 32)
Node 1: Index usage is 18%(29 8K pages of total 160)
Node 2: Data usage is 87%(28 32K pages of total 32)
Node 2: Index usage is 18%(29 8K pages of total 160)
Node 3: Data usage is 56%(18 32K pages of total 32)
Node 3: Index usage is 2%(4 8K pages of total 160)
Node 4: Data usage is 56%(18 32K pages of total 32)
Node 4: Index usage is 2%(4 8K pages of total 160)

ndb_mgm> 

Les tables des privilèges de chaque noeud SQL doivent utilisées le moteur MyISAM ce qui implique que les privilèges sur un noeud SQL ne sont pas disponibles dans un autre noeud SQL.

Pour convertir les privilèges au format NDBCLUSTER, importez le script ndb_dist_priv.sql dans chaque noeud SQL :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p < /opt/mysql/share/ndb_dist_priv.sql
Enter password: fenestros

Ce script importe 6 procédures et une fonction :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'mysql_cluster%'ORDER BY ROUTINE_TYPE;
+---------------------------------------------+----------------+--------------+
| ROUTINE_NAME                                | ROUTINE_SCHEMA | ROUTINE_TYPE |
+---------------------------------------------+----------------+--------------+
| mysql_cluster_privileges_are_distributed    | mysql          | FUNCTION     |
| mysql_cluster_backup_privileges             | mysql          | PROCEDURE    |
| mysql_cluster_move_grant_tables             | mysql          | PROCEDURE    |
| mysql_cluster_move_privileges               | mysql          | PROCEDURE    |
| mysql_cluster_restore_local_privileges      | mysql          | PROCEDURE    |
| mysql_cluster_restore_privileges            | mysql          | PROCEDURE    |
| mysql_cluster_restore_privileges_from_local | mysql          | PROCEDURE    |
+---------------------------------------------+----------------+--------------+
7 rows in set (0.05 sec)

mysql> 

Commencez par sauvegarder les tables des privilèges actuelles :

root@ndb02:~# /opt/mysql/bin/mysqldump --socket="/opt/MySQL_Cluster/55/mysql.socket" -p mysql user db tables_priv columns_priv procs_priv proxies_priv > /opt/backup_file
Enter password: fenestros
root@ndb02:~# cat /opt/backup_file 
-- MySQL dump 10.13  Distrib 5.7.24-ndb-7.5.12, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version	5.7.24-ndb-7.5.12-cluster-gpl

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('localhost','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*00269BA49BEC800F9CCF34C20C1FD83E0236B89A','N','2018-12-30 15:14:55',NULL,'N'),('localhost','mysql.session','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2018-12-29 15:08:23',NULL,'Y'),('localhost','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2018-12-29 15:08:23',NULL,'Y');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `db`
--

DROP TABLE IF EXISTS `db`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `db`
--

LOCK TABLES `db` WRITE;
/*!40000 ALTER TABLE `db` DISABLE KEYS */;
INSERT INTO `db` VALUES ('localhost','performance_schema','mysql.session','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'),('localhost','sys','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y');
/*!40000 ALTER TABLE `db` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tables_priv`
--

DROP TABLE IF EXISTS `tables_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tables_priv`
--

LOCK TABLES `tables_priv` WRITE;
/*!40000 ALTER TABLE `tables_priv` DISABLE KEYS */;
INSERT INTO `tables_priv` VALUES ('localhost','mysql','mysql.session','user','boot@connecting host','0000-00-00 00:00:00','Select',''),('localhost','sys','mysql.sys','sys_config','root@localhost','2018-12-29 15:08:23','Select','');
/*!40000 ALTER TABLE `tables_priv` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `columns_priv`
--

LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `procs_priv`
--

DROP TABLE IF EXISTS `procs_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `procs_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `procs_priv`
--

LOCK TABLES `procs_priv` WRITE;
/*!40000 ALTER TABLE `procs_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `procs_priv` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `proxies_priv`
--

DROP TABLE IF EXISTS `proxies_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proxies_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `proxies_priv`
--

LOCK TABLES `proxies_priv` WRITE;
/*!40000 ALTER TABLE `proxies_priv` DISABLE KEYS */;
INSERT INTO `proxies_priv` VALUES ('localhost','root','','',1,'boot@connecting host','0000-00-00 00:00:00');
/*!40000 ALTER TABLE `proxies_priv` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-12-30 16:02:13

Appelez la procédure mysql_cluster_move_privileges :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> CALL mysql.mysql_cluster_move_privileges();
Query OK, 0 rows affected (7.39 sec)

Vérifiez ensuite que la conversion a réussi :

mysql> SELECT CONCAT('Conversion ', IF(mysql.mysql_cluster_privileges_are_distributed(), 'succeeded', 'failed'), '.') AS Result;
+-----------------------+
| Result                |
+-----------------------+
| Conversion succeeded. |
+-----------------------+
1 row in set (0.00 sec)

La procédure a également créé des sauvegardes des privilèges :

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE '%backup' ORDER BY ENGINE;
+-------------------------+------------+
| TABLE_NAME              | ENGINE     |
+-------------------------+------------+
| db_backup               | MyISAM     |
| user_backup             | MyISAM     |
| columns_priv_backup     | MyISAM     |
| tables_priv_backup      | MyISAM     |
| proxies_priv_backup     | MyISAM     |
| procs_priv_backup       | MyISAM     |
| ndb_columns_priv_backup | ndbcluster |
| ndb_user_backup         | ndbcluster |
| ndb_tables_priv_backup  | ndbcluster |
| ndb_proxies_priv_backup | ndbcluster |
| ndb_procs_priv_backup   | ndbcluster |
| ndb_db_backup           | ndbcluster |
+-------------------------+------------+
12 rows in set (0.01 sec)

mysql> 

La réplication non-clusterisé a lieu en générale entre une machine maître et une machine esclave.

Quand la réplication est mise en place, deux threads sont démarrés sur l'esclave :

• IO_THREAD - copie les informations des journaux binaires du maître dans son relay log,
• SQL-THREAD - exécute les modifications sur la ou les bases de données de l'esclave.

Un troisième thread est ouvert sur le serveur maître par l'IO_THREAD pour le transfert des journaux.

Un esclave ne peut avoir qu'un seul maître mais un maître peut avoir plusieurs esclaves.

Dans une réplication Maître - esclave, ceci permet d'être évolutif en lecture mais pas en écriture. Trop de requêtes en écriture vont “inondées” le ou les esclaves.

Dans une réplication Maître-Maître il est possible d'avoir deux modes de fonctionnement :

• actif-passif,
  • un des maîtres est utilisé pour les écritures/lectures tandis que l'autre est un backup en cas de défaillance du premier,
• actif-actif,
  • les deux maîtres sont utilisés pour les écritures/lectures.

Il existe deux types de réplications :

• SBR - Statement Based Replication,
  • les requêtes SQL sont ré-exécutées sur l'esclave. Cette méthode crée des problèmes quand la requête est dynamique ( par exemple dans le cas d'une fonction ),
• RBR - Row Based Replication,
  • les modifications et les valeurs des enregistrements sont répliqués. Cette méthode est plus lente à cause de la quantité de données à répliquer.

A partir de la version 5.6 de MySQL, il est possible de choisir un mode mixed. Le serveur va choisir la méthode approprié pour chaque cas.

Il est possible de fixer la valeur de plusieurs variable pour choisir quelles bases de données vont être répliquées :

• binlog-do-db et binlog-ignore-db sur le maître,
• replicate-do-db et replicate-ignore-db sur l'esclave.

Dans le premier cas cela nécessite le re-démarrage du serveur maître. Il est donc préférable d'utiliser les variables sur l'esclave.

Arrêtez le cluster et éteignez les machines virtuelles. Créez 4 clones :

• NDB01 > NDB01Slave,
• NDB02 > NDB02Slave,
• NDB03 > NDB03Slave,
• NDB04 > NDB04Slave.

Modifiez les ports de redirection ainsi :

• NDB01Slave - 6022,
• NDB02Slave - 7022,
• NDB03Slave - 8022,
• NDB04Slave - 9022.

Démarrez la VM NDB01Slave et reconfigurez le profil réseau ip_fixe, le nom d'hôte et le fichier /etc/hosts :

root@ndb01:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
ip_fixe          e3c5902e-1524-49af-a3b3-b4149fde50a0  802-3-ethernet  eth1   
eth0             13819f4b-6365-486a-b9b7-f465e65c4456  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --  
   
root@ndb01:~# nmcli connection del ip_fixe

root@ndb01:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             13819f4b-6365-486a-b9b7-f465e65c4456  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     

root@ndb01:~# nmcli connection add con-name ip_fixe ifname eth1 type ethernet ip4 192.168.99.5/24 gw4 10.0.2.2
Connection 'ip_fixe' (410658ef-1aa9-4e06-9b1b-81cf917ace2c) successfully added.

root@ndb01:~# nmcli connection up ip_fixe
Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/3)

root@ndb01:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             13819f4b-6365-486a-b9b7-f465e65c4456  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
ip_fixe          410658ef-1aa9-4e06-9b1b-81cf917ace2c  802-3-ethernet  eth1   

root@ndb01:~# nmcli g hostname ndb05.i2tch.loc

root@ndb01:~# vi /etc/hosts
root@ndb01:~# cat /etc/hosts
127.0.0.1	localhost
127.0.1.1	ndb05.i2tch.loc	ndb05
10.0.2.80	ndb01.i2tch.loc
10.0.2.90	ndb02.i2tch.loc
10.0.2.100	ndb03.i2tch.loc
10.0.2.110	ndb04.i2tch.loc
192.168.99.5	ndb05.i2tch.loc
192.168.99.6	ndb06.i2tch.loc
192.168.99.7	ndb07.i2tch.loc
192.168.99.8	ndb08.i2tch.loc

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

root@ndb01:~# shutdown -r now

Démarrez la VM NDB02Slave et reconfigurez le profil réseau ip_fixe, le nom d'hôte et le fichier /etc/hosts :

root@ndb02:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
ip_fixe          b1759ca2-03a1-48fc-b37f-475882df964d  802-3-ethernet  eth1   
eth0             bd3f93b3-4ff7-441d-8483-9b4a9d27d55a  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
root@ndb02:~# nmcli connection del ip_fixe
root@ndb02:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             bd3f93b3-4ff7-441d-8483-9b4a9d27d55a  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
root@ndb02:~# nmcli connection add con-name ip_fixe ifname eth1 type ethernet ip4 192.168.99.6/24 gw4 10.0.2.2
Connection 'ip_fixe' (bf625cf9-c0cb-409b-a184-9ea20acff2a6) successfully added.
root@ndb02:~# nmcli connection up ip_fixe
Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/3)
root@ndb02:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             bd3f93b3-4ff7-441d-8483-9b4a9d27d55a  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
ip_fixe          bf625cf9-c0cb-409b-a184-9ea20acff2a6  802-3-ethernet  eth1   
root@ndb02:~# nmcli g hostname ndb06.i2tch.loc
root@ndb02:~# vi /etc/hosts
root@ndb02:~# cat /etc/hosts
127.0.0.1	localhost
127.0.1.1	ndb06.i2tch.loc	ndb06
10.0.2.80	ndb01.i2tch.loc
10.0.2.90	ndb02.i2tch.loc
10.0.2.100	ndb03.i2tch.loc
10.0.2.110	ndb04.i2tch.loc
192.168.99.5	ndb05.i2tch.loc
192.168.99.6	ndb06.i2tch.loc
192.168.99.7	ndb07.i2tch.loc
192.168.99.8	ndb08.i2tch.loc

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
root@ndb02:~# shutdown -r now

Démarrez la VM NDB03Slave et reconfigurez le profil réseau ip_fixe, le nom d'hôte et le fichier /etc/hosts :

root@ndb03:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
ip_fixe          f64f4364-5380-47e0-b06f-9a8828f4d2d3  802-3-ethernet  eth1   
eth0             e36b3a73-6972-407b-a794-c740bb415ce8  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
root@ndb03:~# nmcli connection del ip_fixe
root@ndb03:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             e36b3a73-6972-407b-a794-c740bb415ce8  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
root@ndb03:~# nmcli connection add con-name ip_fixe ifname eth1 type ethernet ip4 192.168.99.7/24 gw4 10.0.2.2
Connection 'ip_fixe' (8bd77c12-2ec9-4674-a106-10f3ed319fbe) successfully added.
root@ndb03:~# nmcli connection up ip_fixe
Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/3)
root@ndb03:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             e36b3a73-6972-407b-a794-c740bb415ce8  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
ip_fixe          8bd77c12-2ec9-4674-a106-10f3ed319fbe  802-3-ethernet  eth1   
root@ndb03:~# nmcli g hostname ndb07.i2tch.loc
root@ndb03:~# vi /etc/hosts
root@ndb03:~# cat /etc/hosts
127.0.0.1	localhost
127.0.1.1	ndb07.i2tch.loc	ndb07
10.0.2.80	ndb01.i2tch.loc
10.0.2.90	ndb02.i2tch.loc
10.0.2.100	ndb03.i2tch.loc
10.0.2.110	ndb04.i2tch.loc
192.168.99.5	ndb05.i2tch.loc
192.168.99.6	ndb06.i2tch.loc
192.168.99.7	ndb07.i2tch.loc
192.168.99.8	ndb08.i2tch.loc

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
root@ndb03:~# shutdown -r now

Démarrez la VM NDB04Slave et reconfigurez le profil réseau ip_fixe, le nom d'hôte et le fichier /etc/hosts :

root@ndb04:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
ip_fixe          2b8cd5a8-9966-477a-a94a-502566400c80  802-3-ethernet  eth1   
eth0             a909cf2b-6728-426b-91f2-6883f3f72d33  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
root@ndb04:~# nmcli connection del ip_fixe
root@ndb04:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             a909cf2b-6728-426b-91f2-6883f3f72d33  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
root@ndb04:~# nmcli connection add con-name ip_fixe ifname eth1 type ethernet ip4 192.168.99.8/24 gw4 10.0.2.2
Connection 'ip_fixe' (4edb50a4-f730-4fc7-969d-c86778b1240e) successfully added.
root@ndb04:~# nmcli connection up ip_fixe
Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/3)
root@ndb04:~# nmcli c show
NAME             UUID                                  TYPE            DEVICE 
eth0             a909cf2b-6728-426b-91f2-6883f3f72d33  802-3-ethernet  eth0   
Ifupdown (eth0)  681b428f-beaf-8932-dce4-687ed5bae28e  802-3-ethernet  --     
ip_fixe          4edb50a4-f730-4fc7-969d-c86778b1240e  802-3-ethernet  eth1   
root@ndb04:~# nmcli g hostname ndb08.i2tch.loc
root@ndb04:~# vi /etc/hosts
root@ndb04:~# cat /etc/hosts
127.0.0.1	localhost
127.0.1.1	ndb08.i2tch.loc	ndb08
10.0.2.80	ndb01.i2tch.loc
10.0.2.90	ndb02.i2tch.loc
10.0.2.100	ndb03.i2tch.loc
10.0.2.110	ndb04.i2tch.loc
192.168.99.5	ndb05.i2tch.loc
192.168.99.6	ndb06.i2tch.loc
192.168.99.7	ndb07.i2tch.loc
192.168.99.8	ndb08.i2tch.loc

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
root@ndb04:~# shutdown -r now

Dans la VM NDB01Slave, nettoyez la configuration précédente, videz le cache d'apt et re-créer le répertoire /opt/mysql :

root@ndb05:~# rm -rf /opt/mysql
root@ndb05:~# rm -rf /opt/MySQL_Cluster
root@ndb05:~# apt-get clean
root@ndb05:/opt# tar -xf mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
root@ndb05:/opt# mv mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64 mysql

Dans la VM NDB02Slave, nettoyez la configuration précédente, videz le cache d'apt et re-créer le répertoire /opt/mysql :

root@ndb06:~# rm -rf /opt/mysql
root@ndb06:~# rm -rf /opt/MySQL_Cluster
root@ndb06:~# apt-get clean
root@ndb06:/opt# tar -xf mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
root@ndb06:/opt# mv mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64 mysql

Dans la VM NDB03Slave, nettoyez la configuration précédente, videz le cache d'apt et re-créer le répertoire /opt/mysql :

root@ndb07:~# rm -rf /opt/mysql
root@ndb07:~# rm -rf /opt/MySQL_Cluster
root@ndb07:~# apt-get clean
root@ndb07:/opt# tar -xf mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
root@ndb07:/opt# mv mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64 mysql

Dans la VM NDB04Slave, nettoyez la configuration précédente, videz le cache d'apt et re-créer le répertoire /opt/mysql :

root@ndb08:~# rm -rf /opt/mysql
root@ndb08:~# rm -rf /opt/MySQL_Cluster
root@ndb08:~# apt-get clean
root@ndb08:/opt# tar -xf mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64.tar.gz
root@ndb08:/opt# mv mysql-cluster-gpl-7.5.12-linux-glibc2.12-x86_64 mysql

Ouvrez un terminal graphique sur ndb05 et tapez les commandes suivantes :

root@ndb05:~# rm -rf /root/mcc.pid 
root@ndb05:~# /opt/mysql/bin/ndb_setup.py 

Si le navigateur web ne se lance pas automatiquement, ouvrez-le et saisissez l'adresse suivante : http://localhost:8081/welcome.html.

Cliquez sur le lien de création d'un nouveau cluster et créez votre cluster esclave dénommé Cluster_Slave :

Lors du premier démarrage du cluster, le SQL node 1 ne démarrera pas. Démarrez donc ce service manuellement :

root@ndb06:~# vi /opt/mysql/bin/mysql.init
root@ndb06:~# cat /opt/mysql/bin/mysql.init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'fenestros';
root@ndb06:~# chown -R mysql:mysql /opt/MySQL_Cluster/55
root@ndb06:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &

Vérifiez que tous les noeuds sont en état de fonctionnement :

root@ndb05:~# /opt/mysql/bin/ndb_mgm -e SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@192.168.99.7  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@192.168.99.8  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@192.168.99.5  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@192.168.99.6  (mysql-5.7.24 ndb-7.5.12)

Vérifiez que vous pouvez vous connecter au noeud SQL :

root@ndb06:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql>

Démarrez les noeuds du premier cluster. Supprimez le cluster. Re-créez un cluster vierge :

Vérifiez que le cluster est en état de fonctionnement :

root@ndb01:~# /opt/mysql/bin/ndb_mgm -e SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	1 node(s)
id=55	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

Arrêtez le serveur MySQL sur NDB02 :

root@ndb02:~# ps aux | grep mysql
mysql    19660  1.4 12.0 1152508 186080 ?      Sl   16:42   0:56 /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init
root     30727  0.0  0.1  12728  2120 pts/1    S+   17:47   0:00 grep mysql
root@ndb02:~# kill 19660

Démarrez le serveur MySQL sur NDB02 en spécifiant la valeur de –serevr-id :

root@ndb02:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init --server-id=1 &
[1] 30895

Connectez-vous au serveur MySQL sur NDB02 et créez l'utilisateur replicant :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> GRANT REPLICATION SLAVE
    ->     ON *.*
    ->     TO 'replicant'@'%'
    ->     IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

Arrêtez le serveur MySQL sur NDB06 :

root@ndb06:~# ps aux | grep mysql
root     24235  0.0  0.1  12728  2052 pts/1    S+   13:51   0:00 grep mysql
mysql    24415  1.5 12.0 1152508 185676 ?      Sl   10:40   2:55 /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init
root@ndb06:~# kill 24415

Démarrez le serveur MySQL sur NDB06 en spécifiant la valeur de –serevr-id :

root@ndb06:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init --server-id=2 &

Connectez-vous au serveur MySQL sur NDB06 et indiquez les coordonnées de connexion au serveur maître :

root@ndb06:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> CHANGE MASTER TO MASTER_HOST='10.0.2.90', MASTER_PORT=3306, MASTER_USER='replicant', MASTER_PASSWORD='password';
Query OK, 0 rows affected, 2 warnings (2.10 sec)

mysql> 

Re-démarrez le serveur MySQL sur NDB02 en mode maître avec les options –ndbcluster, –server-id=1 et –log-bin :

root@ndb02:~# ps aux | grep mysql
mysql    31225  1.5 12.0 1152364 185056 pts/1  Sl   17:50   0:05 /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init --server-id=1
root     32185  0.0  0.1  12728  2156 pts/1    S+   17:56   0:00 grep mysql
root@ndb02:~# kill 31225

root@ndb02:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init --ndbcluster --server-id=1 --log-bin &
[1] 15908

Re-démarrez le serveur MySQL sur NDB06 en mode esclave avec les options –ndbcluster, –server-id=2 et –skip-slave-start :

root@ndb06:~# ps aux | grep mysql
mysql    25001  2.4 12.0 1152624 184980 pts/1  Sl   13:55   0:07 /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init --server-id=2
root     25944  0.0  0.1  12728  2124 pts/1    S+   14:01   0:00 grep mysql
root@ndb06:~# kill 25001

root@ndb06:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init --ndbcluster --server-id=2 --skip-slave-start &
[1] 9969

Synchronisez les bin logs de l'esclave et le maître :

root@ndb06:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=4;
Query OK, 0 rows affected (0.06 sec)

Démarrez l'esclave dans NDB06 et constatez la liste des bases de données :

mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

Connectez-vous au serveur MySQL de NDB02 et créez une base de données :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.24-ndb-7.5.12-cluster-gpl-log MySQL Cluster Community Server (GPL)

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

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

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

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.13 sec)

mysql> USE test1;
Database changed
mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.64 sec)

mysql> 
<code>

Retournez à NDB06 et vérifiez que la réplication fonctionne :

<code>
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

Arrêtez votre cluster esclave. Supprimez votre cluster maître. Mettez en place le cluster suivant :

Utilisez l'interface HTML uniquement pour déployer le cluster.

Démarrez le noeud de gestion sur NDB01 :

root@ndb01:~# /opt/mysql/bin/ndb_mgmd --initial --ndb-nodeid=49 --config-dir=/opt/MySQL_Cluster/49/ --config-file=/opt/MySQL_Cluster/49/config.ini
MySQL Cluster Management Server mysql-5.7.24 ndb-7.5.12
2019-01-10 13:21:12 [MgmtSrvr] WARNING  -- at line 28: [DB] BackupMemory is deprecated, use Total memory allocated for backups per node (in bytes) instead

Démarrez le noeud de gestion sur NDB02 :

root@ndb02:~# /opt/mysql/bin/ndb_mgmd --initial --ndb-nodeid=52 --config-dir=/opt/MySQL_Cluster/52/ --config-file=/opt/MySQL_Cluster/52/config.ini
MySQL Cluster Management Server mysql-5.7.24 ndb-7.5.12
2019-01-08 21:18:06 [MgmtSrvr] WARNING  -- at line 28: [DB] BackupMemory is deprecated, use Total memory allocated for backups per node (in bytes) instead

Démarrez le noeud de données sur NDB03 :

root@ndb03:~# /opt/mysql/bin/ndbmtd --ndb-nodeid=1 --ndb-connectstring=10.0.2.80:1186,10.0.2.90:1186
2019-01-08 21:23:19 [ndbd] INFO     -- Angel connected to '10.0.2.80:1186'
2019-01-08 21:23:19 [ndbd] INFO     -- Angel allocated nodeid: 1

Démarrez le noeud de données sur NDB04 :

root@ndb04:~# /opt/mysql/bin/ndbmtd --ndb-nodeid=2 --ndb-connectstring=10.0.2.80:1186,10.0.2.90:1186
2019-01-08 21:23:19 [ndbd] INFO     -- Angel connected to '10.0.2.80:1186'
2019-01-08 21:23:19 [ndbd] INFO     -- Angel allocated nodeid: 2

Vérifiez le démarrage des noeuds de données à partir du premier noeud de gestion :

root@ndb01:~# /opt/mysql/bin/ndb_mgm -e SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)

[ndb_mgmd(MGM)]	2 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)
id=52	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	2 node(s)
id=55 (not connected, accepting connect from 10.0.2.80)
id=56 (not connected, accepting connect from 10.0.2.90)

Vérifiez le démarrage des noeuds de données à partir du deuxième noeud de gestion :

root@ndb02:~# /opt/mysql/bin/ndb_mgm -e SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@10.0.2.100  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0, *)
id=2	@10.0.2.110  (mysql-5.7.24 ndb-7.5.12, Nodegroup: 0)

[ndb_mgmd(MGM)]	2 node(s)
id=49	@10.0.2.80  (mysql-5.7.24 ndb-7.5.12)
id=52	@10.0.2.90  (mysql-5.7.24 ndb-7.5.12)

[mysqld(API)]	2 node(s)
id=55 (not connected, accepting connect from 10.0.2.80)
id=56 (not connected, accepting connect from 10.0.2.90)

Préparez NDB01 pour le noeud SQL :

root@ndb01:~# apt-get install libaio1 libnuma1
root@ndb01:~# vi /opt/mysql/bin/mysql.init
root@ndb01:~# cat /opt/mysql/bin/mysql.init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'fenestros';
root@ndb01:~# groupadd mysql
root@ndb01:~# useradd -g mysql -s /bin/false mysql
root@ndb01:~# chown -R mysql:mysql /opt/MySQL_Cluster/55

Préparez NDB02 pour le noeud SQL :

root@ndb02:~# chown -R mysql:mysql /opt/MySQL_Cluster/56

Utilisez la commande mysql_install_db pour initialiser le noeud SQL sur NDB01 :

root@ndb01:~# /opt/mysql/bin/mysql_install_db --no-defaults --basedir=/opt/mysql/ --datadir=/opt/MySQL_Cluster/55/data/ --user=root
2019-01-08 21:51:25 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-01-08 21:51:33 [WARNING] The bootstrap log isn't empty:
2019-01-08 21:51:33 [WARNING] 2019-01-08T21:51:25.849889Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
root@ndb01:~# chown -R mysql:mysql /opt/MySQL_Cluster/55

Utilisez la commande mysql_install_db pour initialiser le noeud SQL sur NDB02 :

root@ndb02:~# /opt/mysql/bin/mysql_install_db --no-defaults --basedir=/opt/mysql/ --datadir=/opt/MySQL_Cluster/56/data/ --user=root
2019-01-08 21:51:25 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-01-08 21:51:33 [WARNING] The bootstrap log isn't empty:
2019-01-08 21:51:33 [WARNING] 2019-01-08T21:51:25.849889Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
root@ndb02:~# chown -R mysql:mysql /opt/MySQL_Cluster/56

Lancez mysqld sur NDB01 :

root@ndb01:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &
[1] 8637

Lancez mysqld sur NDB02 :

root@ndb02:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/56/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &
[1] 8637

Connectez-vous à la première instance de mysqld sur NDB01 :

root@ndb01:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> 

Créez la base de données cluster_test :

mysql> CREATE DATABASE cluster_test;
Query OK, 1 row affected (0.04 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster_test       |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>  

Connectez-vous à la deuxième instance de mysqld sur NDB02 et visualisez les bases de données :

root@ndb02:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/56/mysql.socket" -p
Enter password: fenestros
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cluster_test       |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

Dans NDB01, créez la table t1 et insérez des données :

mysql> USE cluster_test;
Database changed
mysql> CREATE TABLE t1 (a integer, b char(20), primary key (a)) ENGINE=NDB;
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO t1 VALUES (1, 'hello');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (2, 'hello');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (3, 'hello');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (4, 'hello');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+---+-------+
| a | b     |
+---+-------+
| 3 | hello |
| 1 | hello |
| 2 | hello |
| 4 | hello |
+---+-------+
4 rows in set (0.01 sec)

mysql> exit
Bye
<code>

Arrêtez le noeud MySQL sur NDB01 :

<code>
root@ndb01:~# ps aux | grep mysql
root     10600  1.8  0.4 591528  6972 ?        Ssl  13:21   1:36 /opt/mysql/bin/ndb_mgmd --initial --ndb-nodeid=49 --config-dir=/opt/MySQL_Cluster/49/ --config-file=/opt/MySQL_Cluster/49/config.ini
mysql    15898  1.5 12.7 1152744 195700 ?      Sl   13:55   0:50 /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init
root     16028  0.5  0.9 124844 15112 pts/1    S+   13:55   0:18 python /opt/mysql/bin/ndb_setup.py
root     27371  0.0  0.1  12728  2160 pts/0    S+   14:48   0:00 grep mysql
root@ndb01:~# kill -9 15898

Sur le neoud NDB02, modifiez la table t1 :

mysql> USE cluster_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM t1;
+---+-------+
| a | b     |
+---+-------+
| 3 | hello |
| 1 | hello |
| 2 | hello |
| 4 | hello |
+---+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (5, 'hello');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+---+-------+
| a | b     |
+---+-------+
| 5 | hello |
| 1 | hello |
| 2 | hello |
| 4 | hello |
| 3 | hello |
+---+-------+
5 rows in set (0.00 sec)

mysql> 

Démarrez le noeud MySQL sur NDB01 :

root@ndb01:~# /opt/mysql/bin/mysqld --defaults-file=/opt/MySQL_Cluster/55/my.cnf --user=mysql --explicit_defaults_for_timestamp --init-file=/opt/mysql/bin/mysql.init &
[1] 28134

Connectez-vous au serveur mysqld sur NDB01 et vérifiez que la modification est visible :

root@ndb01:~# /opt/mysql/bin/mysql --socket="/opt/MySQL_Cluster/55/mysql.socket" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-ndb-7.5.12-cluster-gpl MySQL Cluster Community Server (GPL)

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

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

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

mysql> use cluster_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM t1;
+---+-------+
| a | b     |
+---+-------+
| 3 | hello |
| 5 | hello |
| 1 | hello |
| 2 | hello |
| 4 | hello |
+---+-------+
5 rows in set (0.00 sec)

mysql>

<html> <center> Copyright © 2021 Hugh Norris.<br><br> </center> </html>