Usando Linux para muchas cosas y disfrutando de videojuegos, en dispositivos moviles, consolas y pc. Using Linux for many things and enjoying video games, mobile devices, consoles and pc

Cluster MySQL

Como un nuevo proyecto debo crear un cluster de email y para ello debo usar MySQL en cluster para que no sea un punto de fallo.
Trabajaremos en servidores Centos 5.4 i386 actualizados y todos los comandos seran ejecutados desde root. La configuracion basica es de 4 servers, 2 para data, 1 para consola de manejo y 1 para SQL.
Los servers de data deben contar con buena cantidad de memoria y el de consola y SQL con la cantidad basica minima.

Nodo A-192.168.0.19
Nodo B-192.168.0.21
MGM-192.168.0.20
MySQL Server SQL-192.168.0.22

A la fecha la version es MySQL Cluster 7.0.13

-Descargamos los rpm a uno de los servers
wget -c http://mysql.mirrors.hoobly.com/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-client-7.0.13-1.rhel5.i386.rpm

wget -c http://mysql.mirrors.hoobly.com/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-server-7.0.13-1.rhel5.i386.rpm

wget -c http://mysql.mirrors.hoobly.com/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-shared-7.0.13-1.rhel5.i386.rpm

wget -c http://mysql.mirrors.hoobly.com/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-storage-7.0.13-1.rhel5.i386.rpm

wget -c http://mysql.mirrors.hoobly.com/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-tools-7.0.13-1.rhel5.i386.rpm

-Los copiamos a los demás servers y con eso evitamos tener que descargarlos en todos.

scp *.rpm ip-del-server:/root

-Eliminamos versión normal de mysql en caso de estar instalado

rpm -qa|grep mysql
mysql-server-5.0.77-4.el5_4.2
mysql-devel-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2

rpm -e mysql-server-5.0.77-4.el5_4.2 mysql-devel-5.0.77-4.el5_4.2 mysql-5.0.77-4.el5_4.2
error: Failed dependencies:
    libmysqlclient.so.15 is needed by (installed) perl-DBD-MySQL-3.0007-2.el5.i386
    libmysqlclient.so.15(libmysqlclient_15) is needed by (installed) perl-DBD-MySQL-3.0007-2.el5.i386

rpm -e mysql-server-5.0.77-4.el5_4.2 mysql-devel-5.0.77-4.el5_4.2 mysql-5.0.77-4.el5_4.2 perl-DBD-MySQL-3.0007-2.el5.i386

-Instalamos nodo A

rpm -vi MySQL-Cluster-gpl-tools-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-client-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-storage-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-server-7.0.13-1.rhel5.i386.rpm

mkdir /var/lib/mysql-cluster
mkdir /var/lib/mysql-cluster/backup

-Creamos el my.cnf

vi /etc/my.cnf
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.0.20 # ip del MGM

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.20 # ip del MGM

-Instalamos nodo B

rpm -vi MySQL-Cluster-gpl-tools-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-client-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-storage-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-server-7.0.13-1.rhel5.i386.rpm


mkdir /var/lib/mysql-cluster
mkdir /var/lib/mysql-cluster/backup

-Creamos el my.cnf


vi /etc/my.cnf
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.0.20 # ip del MGM

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.20 # ip del MGM

-Instalamos MGM

rpm -vi MySQL-Cluster-gpl-management-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-tools-7.0.13-1.rhel5.i386.rpm

mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster

vi config.ini
[NDBD DEFAULT]
NoOfReplicas=2

[TCP DEFAULT]

[NDB_MGMD]
hostname=192.168.0.20 # Hostname o IP MGM
datadir=/var/lib/mysql-cluster # Directorio de MGM logfiles

# node A
[NDBD]
hostname=192.168.0.19 # Hostname o IP
DataDir=/var/lib/mysql-cluster # Directorio para data
BackupDataDir=/var/lib/mysql-cluster/backup
DataMemory=512M # Depende del hardware


# node B
[NDBD]
hostname=192.168.0.21 # Hostname o IP
DataDir= /var/lib/mysql-cluster # Directorio para data
BackupDataDir=/var/lib/mysql-cluster/backup
DataMemory = 512M # Depende del hardware

# SQL node
[MYSQLD]
hostname=192.168.0.22 # Hostname o IP

[MYSQLD]
[MYSQLD]
[MYSQLD]


-Instalamos SQL

rpm -vi MySQL-Cluster-gpl-server-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-shared-7.0.13-1.rhel5.i386.rpm MySQL-Cluster-gpl-client-7.0.13-1.rhel5.i386.rpm


-Creamos el my.cnf


vi /etc/my.cnf
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.0.20 # ip del MGM

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.20 # ip del MGM

-Hora de iniciar el cluster, para ello debe tenerse en cuenta que el orden es, primero el MGM, después los Node y por ultimo el SQL

-Iniciamos el MGM

ndb_mgmd -f /var/lib/mysql-cluster/config.ini


Si todo va bien debe devolvernos algo asi

2010-04-07 15:38:42 [MgmtSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.41 ndb-7.0.13
2010-04-07 15:38:42 [MgmtSrvr] INFO     -- The default config directory '//mysql-cluster' does not exist. Trying to create it...
2010-04-07 15:38:42 [MgmtSrvr] INFO     -- Sucessfully created config directory
2010-04-07 15:38:43 [MgmtSrvr] INFO     -- Reading cluster configuration from '/var/lib/mysql-cluster/config.ini'

-Iniciamos Nodo A

ndbd --initial

Y debe devolvernos algo asi
2010-04-07 15:41:59 [ndbd] INFO     -- Configuration fetched from '192.168.0.20:1186', generation: 1

-Iniciamos Nodo B


ndbd --initial
Y debe devolvernos algo asi
2010-04-07 15:41:59 [ndbd] INFO     -- Configuration fetched from '192.168.0.20:1186', generation: 1

-Iniciamos SQL

service mysql start
Starting MySQL.. SUCCESS!

-Verificamos este ejecutando correctamente

mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.41-ndb-7.0.13-cluster-gpl MySQL Cluster Server (GPL)

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

mysql>

-Ejecutamos

SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: ndbcluster
     Support: YES
     Comment: Clustered, fault-tolerant tables
Transactions: YES
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: ndbinfo
     Support: YES
     Comment: MySQL Cluster system information storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
10 rows in set (0.00 sec)

-Salimos con

exit;

-Verifiquemos el MGM

ndb_mgm
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=2    @192.168.0.19  (mysql-5.1.41 ndb-7.0.13, Nodegroup: 0, Master)
id=3    @192.168.0.21  (mysql-5.1.41 ndb-7.0.13, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @192.168.0.20  (mysql-5.1.41 ndb-7.0.13)

[mysqld(API)]    4 node(s)
id=4    @192.168.0.22  (mysql-5.1.41 ndb-7.0.13)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
id=7 (not connected, accepting connect from any host)

ndb_mgm>exit;

-Otra verificacion de SQL

mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.41-ndb-7.0.13-cluster-gpl MySQL Cluster Server (GPL)

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

mysql> SHOW STATUS LIKE 'ndb%';
+--------------------------------+--------------+
| Variable_name                  | Value        |
+--------------------------------+--------------+
| Ndb_cluster_node_id            | 4            |
| Ndb_config_from_host           | 192.168.0.20 |
| Ndb_config_from_port           | 1186         |
| Ndb_number_of_data_nodes       | 2            |
| Ndb_number_of_ready_data_nodes | 0            |
| Ndb_connect_count              | 0            |
| Ndb_execute_count              | 0            |
| Ndb_scan_count                 | 0            |
| Ndb_pruned_scan_count          | 0            |
| Ndb_cluster_connection_pool    | 1            |
| Ndb_conflict_fn_max            | 0            |
| Ndb_conflict_fn_old            | 0            |
+--------------------------------+--------------+
12 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Daemon
   Time: 725
  State: Waiting for ndbcluster to start
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)

mysql>exit;

-Para apagar el cluster ejecutamos en MGM

ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
0 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

-Para iniciarlo ejecutamos en MGM

ndb_mgmd -f /var/lib/mysql-cluster/config.ini
 2010-04-07 16:07:42 [MgmtSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.41 ndb-7.0.13
2010-04-07 16:07:42 [MgmtSrvr] INFO     -- Loaded config from '//mysql-cluster/ndb_1_config.bin.1'

-Ejecutamos en Nodo A y B. OJO, esta vez sin el parametro --initial

ndbd
2010-04-07 16:08:41 [ndbd] INFO     -- Configuration fetched from '192.168.0.20:1186', generation: 1

-Detenemos el servicio en SQL

service mysql stop

-Lo iniciamos

service mysql start
Starting MySQL.. SUCCESS!

Y listo, ya tenemos nuestro cluster MySQL, las conexiones y demas procesos normales de http o algun software que usemos los dirigimos a SQL
En un próximo articulo mostrare la instalación y configuración del email server con este cluster MySQL
Share/Bookmark