Création d'un Cluster MySQL avec une IP flottante gérée par KeepAlive Daemon.

1. Avant-propos
1.1. Présentation
1.1.1 Schéma d'architecture
1.1.2 Schéma fonctionnel
1.2. Introduction
2. Installation des binaires
2.1. Installation du serveur MySQL Cluster
2.2. Démarrage du serveur MySQL Cluster
2.3. Installation du client MySQL Cluster
2.2. Test du client et du serveur MySQL Cluster
3. Configuration du serveur MySQL Cluster
3.1. Création du fichier de configuration MySQL Cluster (SERVICES)
3.2. Démarrage des services MySQL Cluster
3.2.1 Démarrage du service MySQL MGM
3.2.2 Démarrage du service MySQL NDB
3.3. Création de la configuration MySQL Cluster (BASE)
3.3.1 Création fichier de configuration (ENGINE CLUSTER)
3.3.2 Démarrage de la base MySQL en mode Cluster
3.3.3 Vérification de la base MySQL en mode Cluster
4. Installation/Configuration du KeepAlived
4.1. Installation des binaires du KeepAlived
4.2. Configuration du KeepAlived
4.3. Démarrage du KeepAlived
4.4. Etat du KeepAlived
4.5. Arrêt du KeepAlived

1. Avant-propos


1.1 - Présentation.


Afin de mieux comprendre le mecanisme d'une base de donnée en mode Cluster, je vais vous présenter les différentes étapes.
Les différents services sont le MGMd, le NDBd et le MySQLd.

1.1.1 - Schema d'architecture


Nous voyons dans le schéma technique ci-dessous, que nous n'avons que deux serveurs.
Normalement, il faudrait séparer les services MGM des services NDB et MySQL.
Malheureusement, je n'ai que deux serveurs, donc j'ai mis tous les services sur deux serveurs.

           +---------------------------------------+
           | Serveur Apache                        |
           |                                       |
           | Hostname : mercure                    |
           | OS : Linux                            |
           |                                       |
           | IP : 172.21.3.65                      |
           |                                       |
           | +-----------------------------------+ |
           | |                                   | |
           | | PHP-MYSQL-CONNECTEUR              | |
           | |                                   | |
           | |               |                   | |
           | +-----------------------------------+ |
           |                 |                     |
           |                 |                     |
           +---------------------------------------+
                             |
                             |
                             V
        _____________________|_ _ _ _ _ _ _ _ _ _  _ _
       |                                              |
       |  IP Virtuel : 172.21.3.73                    |
       |  VIP : srv-system-solaire                    |
       |                                              |
+-------------------------------------------------------------------+
|      |                                              |             |
|      V                                              V             |
|  +-----------------------+             +-----------------------+  |
|  | Serveur MGM           |             | Serveur MGM           |  |
|  | Version :             |   Failover  | Version :             |  |
|  |                 <=====|=============|=====>                 |  |
|  | IP : 172.21.3.71      |             | IP : 172.21.3.72      |  |
|  | Hostname : mars       |             | Hostname : venus      |  |
|  +-----------------------+             +-----------------------+  |
|      |                                              |             |
|      |                                              |             |
+-------------------------------------------------------------------+
       |                                              |
       |______________________ _ _ _ _ _ _ _ _ _ _ _ _|
                              |
                              |
                              V
+-----------------------------------------------------------------------------+ 
| Serveur Cluster MySQL                                                       | 
| Version : Ver 5.6.22-ndb-7.3.8-cluster-gpl                                  |
|           for Linux on x86_64 (MySQL CluCluster Community Server (GPL))     | 
|                                                                             | 
| VIP : srv-system-solaire                                                    |  
| IP : 172.21.3.73                                                            |  
|                                                                             | 
| +-------------------------------------------------------------------------+ | 
| |  +---------------------------+             +--------------------------+ | |
| |  |  Serveur : MySQLd         |             |   Serveur : MySQLd       | | |
| |  |  Version : 5.6.22         |             |   Version : 5.6.22       | | |
| |  |                           |             |                          | | |
| |  |  Serveur : NDBd           |             |   Serveur : NDBd         | | |
| |  |  Version : 7.3.8          |             |   Version : 7.3.8        | | |
| |  |                           |             |                          | | |
| |  |  Hostname : mars        --|-------------|-> Hostname : venus       | | |
| |  |                           |             |                          | | |
| |  |  I.P : 172.21.3.71        |             | I.P : 172.21.3.72        | | |
| |  |  Port : 3306              |             | Port : 3306              | | |
| |  |  Port : 1186              |             | Port : 1186              | | |
| |  +---------------------------+             +--------------------------+ | |
| |                                                                         | |
| +-------------------------------------------------------------------------+ |
|                                                                             |
+-----------------------------------------------------------------------------+



2. Installation des binaires


2.1 - Installation du serveur MySQL Cluster.


Installation des binaires du serveur MySQL Cluster (MariaDB Cluster).
[root] # rpm -Uvh MySQL-Cluster-server-gpl-7.3.8-1.el6.x86_64.rpm
[root] # rpm -Uvh MySQL-Cluster-server-gpl-7.3.8-1.el6.x86_64.rpm
Préparation...              ########################################### [100%]
   1:MySQL-Cluster-server-gp########################################### [100%]
[root] # 

2.2 - Démarrage du serveur MySQL Cluster.


Démarrage de la base de donnée.
[root] # /etc/init.d/mysql start
[root] # /etc/init.d/mysql start
Starting MySQL. SUCCESS! 
[root] # 

2.3 - Installation du client MySQL Cluster.


Installation des bianires du client MySQL Cluster (MariaDB Cluster).
[root] # rpm -Uvh MySQL-Cluster-client-gpl-7.3.8-1.el6.x86_64.rpm
[root] # rpm -Uvh MySQL-Cluster-client-gpl-7.3.8-1.el6.x86_64.rpm
Préparation...              ########################################### [100%]
   1:MySQL-Cluster-client-gp########################################### [100%]
-(jeu. janv. 14 16:37:01)--(PROD venus:~)-
[root] # 

2.4 - Test du client et du serveur MySQL Cluster.


Test de l'installation des binaires du client et du serveur MySQL Cluster .
[root] # mysql
[root] # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-ndb-7.3.8-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2015, 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              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0,00 sec)

mysql> quit

3. Configuration du serveur MySQL Cluster


3.1 - Création du fichier de configuration MySQL Cluster (services).


Voici le fichier de configuration complet, mais je vais expliquer les lignes une à une pour chaque process.
Allez dans le répertoire d'installation par défaut du serveur MySQL Cluster.
[root] # cd /var/lib/mysql
[root] # cd /var/lib/mysql
Puis, vous devez créer le fichier config.ini (qui n'existe pas).
[root] # vi config.ini
[root] # vi config.ini
Rentrer les paramètres suivants afin de configurer les services (NDB,MGM et MYSQL).
## Created by : TURLAN Franck
## Email : franckturlan@yahoo.com
## In : 2016
## Phone : 0618704346


[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[tcp default]

[ndb_mgmd]
hostname=172.21.3.73 # Hostname or IP address of MGM node
#hostname=172.21.3.72 # Hostname or IP address of MGM node
datadir=/var/lib/mysql # Directory for MGM node log files

[ndbd]
hostname=172.21.3.71 # Hostname or IP address
datadir=/var/lib/mysql # Directory for MGM node log files

[ndbd]
hostname=172.21.3.72 # Hostname or IP address
datadir=/var/lib/mysql # Directory for MGM node log files

[mysqld]
hostname=172.21.3.71 # Hostname or IP address

[mysqld]
hostname=172.21.3.72 # Hostname or IP address

3.2 - Démarrage des services MySQL Cluster.


3.2.1 - Démarrage du service MGM


Démarrage du service MGM.
[root] # ndb_mgmd -f /var/lib/mysql/config.ini –configdir=/var/lib/mysql
[root] # ndb_mgmd -f /var/lib/mysql/config.ini –configdir=/var/lib/mysql
MySQL Cluster Management Server mysql-5.6.22 ndb-7.3.8
[root] # 
Visualisation du service MGM.
[root] # ndb_mgm -e show
[root] # ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2 (not connected, accepting connect from 172.21.3.71)
id=3 (not connected, accepting connect from 172.21.3.72)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@172.21.3.72  (mysql-5.6.22 ndb-7.3.8)

[mysqld(API)]	2 node(s)
id=4 (not connected, accepting connect from 172.21.3.71)
id=5 (not connected, accepting connect from 172.21.3.72)

[root] # 

3.2.2 - Démarrage du service NDB


Démarrage du service NDB sur un des noeuds.
[root] # ndbd -c 172.21.3.72

[root] # ndbd -c 172.21.3.72
2016-01-15 12:34:49 [ndbd] INFO     -- Angel connected to '172.21.3.72:1186'
2016-01-15 12:34:49 [ndbd] INFO     -- Angel allocated nodeid: 3
[root] # 
Visualisation du démarrage du service NDB.
[root] # ndb_mgm -e show
[root] # ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2 (not connected, accepting connect from 172.21.3.71)
id=3	@172.21.3.72  (mysql-5.6.22 ndb-7.3.8, starting, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@172.21.3.72  (mysql-5.6.22 ndb-7.3.8)

[mysqld(API)]	2 node(s)
id=4 (not connected, accepting connect from 172.21.3.71)
id=5 (not connected, accepting connect from 172.21.3.72)

[root] # 

On vérifi le port 1186.
[root] # lsof -i tcp:1186
[root] # lsof -i tcp:1186
COMMAND    PID USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
ndb_mgmd 18234 root    8u  IPv4 15082469      0t0  TCP *:mysql-cluster (LISTEN)
ndb_mgmd 18234 root    9u  IPv4 15082470      0t0  TCP localhost:35894->localhost:mysql-cluster (ESTABLISHED)
ndb_mgmd 18234 root   10u  IPv4 15082474      0t0  TCP localhost:mysql-cluster->localhost:35894 (ESTABLISHED)
ndb_mgmd 18234 root   11u  IPv4 15083052      0t0  TCP venus-d.nua:mysql-cluster->venus-d.nua:38720 (ESTABLISHED)
ndb_mgmd 18234 root   12u  IPv4 15083069      0t0  TCP venus-d.nua:mysql-cluster->venus-d.nua:38721 (ESTABLISHED)
ndb_mgmd 18234 root   13u  IPv4 15083090      0t0  TCP venus-d.nua:mysql-cluster->venus-d.nua:38722 (ESTABLISHED)
ndbd     18254 root    3u  IPv4 15083050      0t0  TCP venus-d.nua:38720->venus-d.nua:mysql-cluster (ESTABLISHED)
ndbd     18255 root    3u  IPv4 15083050      0t0  TCP venus-d.nua:38720->venus-d.nua:mysql-cluster (ESTABLISHED)
ndbd     18255 root    7u  IPv4 15083067      0t0  TCP venus-d.nua:38721->venus-d.nua:mysql-cluster (ESTABLISHED)
ndbd     18255 root   13u  IPv4 15083088      0t0  TCP venus-d.nua:38722->venus-d.nua:mysql-cluster (ESTABLISHED)
[root] # 

3.3 - Création de la configuration MySQL Cluster (BASE).


3.3.1 - Création fichier de configuration (ENGINE CLUSTER)


Allez dans le répertoire "cd /var/lib/mysql/".
[root] # cd /var/lib/mysql/
[root] # cd /var/lib/mysql/
[root] # 
Edit: [root] # vi my.cnf
[root] # vi my.cnf
Voici ce que vous devez mettre dans votre fichier au minimum.
[mysqld]
ndbcluster

3.3.2 - Démarrage de la base MySQL en mode Cluster


Regarder les process en cours.
[root] # ps -ef | grep mysql
root     18084     1  0 11:56 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/venus.pid
mysql    18173 18084  0 11:56 pts/0    00:00:08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/venus.err --pid-file=/var/lib/mysql/venus.pid
root     18234     1  0 12:31 ?        00:01:20 ndb_mgmd -f /var/lib/mysql/config.ini –configdir=/var/lib/mysql
root     18364 18040  0 15:28 pts/0    00:00:00 grep mysql
-(ven. janv. 15 15:28:35)--(PROD venus:/etc/my.cnf.d)-
Arrêt/relance de la base MySQL.
[root] # /etc/init.d/mysql restart
Shutting down MySQL..160115 16:18:28 mysqld_safe mysqld from pid file /var/lib/mysql/venus.pid ended
 SUCCESS! 
Starting MySQL. SUCCESS! 
[1]+  Done                    mysqld_safe --user=mysql
[root] # 

3.3.3 - Vérification de la base MySQL en mode Cluster


SI LA BASE N'EST PAS En MODE CLUSTER pour "Engines" à ndbcluster on trouve "Support" à NO.
 
[root] # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-ndb-7.3.8-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2015, 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 ENGINES\G
*************************** 1. row ***************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
Création d'une table NDB dans la base test.

[root] # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22-ndb-7.3.8-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2015, 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;
Database changed
mysql> CREATE TABLE `toto` (
    ->   `id` int(11) default NULL
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
    -> ;
ERROR 1286 (42000): Unknown storage engine 'ndbcluster'
mysql> 


SI LA BASE EST PAS en MODE CLUSTER pour "Engines" à ndbcluster on trouve "Support" à YES.
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: ndbcluster
     Support: YES
     Comment: Clustered, fault-tolerant tables
Transactions: YES
          XA: NO
  Savepoints: NO

Autre erreur :157
C'est que vous n'avez pas mis la chaine "ndb-connectstring".
mysql> use test ;
Database changed
mysql> CREATE TABLE `toto` ( `id` int(11) default NULL) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ;
ERROR 157 (HY000): Could not connect to storage engine
mysql> 
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
| Warning | 1296 | Got error 4009 'Cluster Failure' from NDB                                       |
| Error   |  157 | Could not connect to storage engine                                             |
| Error   | 1499 | Too many partitions (including subpartitions) were defined                      |
+---------+------+---------------------------------------------------------------------------------+
4 rows in set (0,00 sec)

mysql> 

FONCTIONNEMENT NORMALE :



4. Installation/Configuration du KeepAlived


Le démon KeepAlived permet de créer une adresse ip flottante entre deux serveurs.

4.1 - Installation des binaires KeepAlived


Pour installer les binaires du démon KeepAlived, saisisser la commande suivante :
[root] # yum install keepalived
[root] # yum install keepalived
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package keepalived.x86_64 0:1.2.7-3.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================
 Package                       Arch                  Version                          Repository                                          Size
==============================================================================================================================================
Installing:
 keepalived                    x86_64                1.2.7-3.el6                      socle-2016-1.15-redhat-x86_64                      174 k

Transaction Summary
==============================================================================================================================================
Install       1 Package(s)

Total download size: 174 k
Installed size: 526 k
Is this ok [y/N]: y
Downloading Packages:
keepalived-1.2.7-3.el6.x86_64.rpm                                                                                          | 174 kB     00:00     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : keepalived-1.2.7-3.el6.x86_64                                                                                              1/1 
  Verifying  : keepalived-1.2.7-3.el6.x86_64                                                                                              1/1 

Installed:
  keepalived.x86_64 0:1.2.7-3.el6                                                                                                                  

Complete!
[root] # 

4.2 - Configuration du KeepAlived


Sur le serveur venus placer la configuration suivante dans le fichier "/etc/keepalived/keepalived.conf".
Faite la même chose sur le serveur mars et modifier la ligne router_id venus par router_id mars.
Vous l'avez compris votre adresse ip "172.21.3.73" correspond à l'adresse ip flottante entre les deux serveurs.

global_defs {
  notification_email_from franckturlan@yahoo.com
  smtp_connect_timeout 10
  router_id venus
}

vrrp_sync_group srv-system-solaire {
  group {
    VIP_MYSQL
  }

  # notify scripts and alerts are optional
  #
  # filenames of scripts to run on transitions
  # can be unquoted (if just filename)
  # or quoted (if has parameters)


  # Send email notifcation during state transition,
  # using addresses in global_defs above.
}

vrrp_instance VIP_MYSQL {
  interface                 bond1
  state                     MASTER
  virtual_router_id         81
  priority                  100
  advert_int                1
  garp_master_delay         5


  # notify scripts and alerts are optional
  #
  # filenames of scripts to run on transitions
  # can be unquoted (if just filename)
  # or quoted (if has parameters)



  authentication {
    auth_type PASS
    auth_pass 
  }


  virtual_ipaddress {
    172.21.3.73/32 dev bond1 
  }


}

4.3 - Démarrage du KeepAlived


[root] # /etc/init.d/keepalived start
[root] # /etc/init.d/keepalived start
Démarrage de keepalived :                                  [  OK  ]
[root] #

4.4 - Etat du KeepAlived


[root] # /etc/init.d/keepalived status
[root] # /etc/init.d/keepalived status
keepalived (pid  28802) en cours d'exécution...
[root] # 

4.5 - Arrêt du KeepAlived


[root] # /etc/init.d/keepalived stop
[root] # /etc/init.d/keepalived stop
Arrêt de keepalived :                                      [  OK  ]
[root] #