Path to improvements

*** KEBUTUHAN PAKET :

- OS Linux dengan paket library yang memadai.
- mysql-cluster-XXX-VERSION-OS.tar.gz
- PC Hardware : (1 pc) Management Server (2 pc) Node Server
- Login setara root pada masing masing system.
- Konektivitas jaringan (TCP/IP).

*** DESAIN SYSTEM :

- SYS-A ~ MANAGEMENT SERVER ~ 192.168.24.10
- SYS-B ~ DATABASE SERVER ~ 192.168.24.20
- SYS-C ~ DATABASE SERVER ~ 192.168.24.30

*** INSTALASI & KONFIGURASI :

A. Installasi MySQL ( 192.168.24.[10/20/30] ) ;

Copy paket mysql-cluster-XXX-VERSION-OS.tar.gz ke /home directory anda, kemudian lakukan 'sudo -i' atau login setara 'root', selanjutnya :

[root@localhost ~]# tar -xvzf mysql-cluster-XXX-VERSION-OS.tar.gz
[root@localhost ~]# mv mysql-cluster-XXX-VERSION-OS /usr/local
[root@localhost ~]# ln -s /usr/local/mysql-cluster-XXX-VERSION-OS /usr/local/mysql
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -g mysql mysql
[root@localhost ~]# cd /usr/local/mysql
[root@localhost ~]# chown -R mysql .
[root@localhost ~]# chgrp -R mysql .
[root@localhost ~]# scripts/mysql_install_db --user=mysql
[root@localhost ~]# chown -R root .
[root@localhost ~]# chown -R mysql data
[root@localhost ~]# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# chmod 755 /etc/init.d/mysqld
[root@localhost ~]# bin/mysqld_safe --user=mysql &

Lakukan penggantian password dengan menjalankan perintah ;

[root@localhost ~]# bin/mysqladmin -u root password 'password-baru-anda'

Lakukan check terhadap proses installasi dan daemon mysql yang telah dijalankan ;

[root@localhost ~]# ps -ax | grep mysql
14815 pts/0    S+     0:00 grep mysql
23508 ?        S      0:00 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.pid
24632 ?        Sl     0:12 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/localhost.err --pid-file=/usr/local/mysql/data/localhost.pid --socket=/var/lib/mysql/mysql.sock --port=3306

Lakukan proses installasi diatas pada masing masing system.

B. Konfigurasi MYSQL CLUSTER MANAGEMENT SERVER ( 192.168.24.10 ) ;

Pada MANAGEMENT SERVER sebenarnya tidaklah memerlukan installasi paket MySQL, dan hanya membutuhkan PC dengan hardware requirement yang relatif rendah dibanding dengan DATABASE SERVER, MANAGEMENT SERVER dapat di shutdown setelah DATABASE CLUSTER aktif / berjalan dengan baik, namun hal ini mengakibatkan fungsi redudancy database tidak berjalan secara realtime, dan hilangnya fungsi control pada DATABASE CLUSTER.

Konfigurasi ;

[root@localhost ~]# cd /usr/local/mysql
[root@localhost ~]# ln -s /usr/local/mysql/bin/ndb_mgm /sbin/ndb_mgm
[root@localhost ~]# ln -s /usr/local/mysql/bin/ndb_mgmd /sbin/ndb_mgmd
[root@localhost ~]# mkdir mysql-cluster
[root@localhost ~]# ln -s /usr/local/mysql/mysql-cluster /var/lib/mysql-cluster

Edit file konfigurasi MANAGEMENT SERVER ;

[root@localhost ~]# vi /usr/local/mysql/mysql-cluster/config.ini

Isi file config.ini ;

[NDBD DEFAULT]
NoOfReplicas=2
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# MANAGEMENT SERVER
[NDB_MGMD]
HostName=192.168.24.10        # IP MANAGEMENT SERVER
# DATABASE SERVER
[NDBD]
HostName=192.168.24.20        # IP DATABASE SERVER 1
DataDir= /usr/local/mysql/mysql-cluster
[NDBD]
HostName=192.168.24.30        # IP DATABASE SERVER 2
DataDir=/usr/local/mysql/mysql-cluster
# 2 DATABASE SERVER
[MYSQLD]
[MYSQLD]

Jalankan MANAGEMENT SERVER ;

[root@localhost ~]# /sbin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini

Proses eksekusi perintah diatas tidak memberikan feedback apapun pada console.

Check proses MANAGEMENT SERVER ;

[root@localhost ~]# ps -ax | grep mysql-cluster
23487 ?        Ssl    2:42 ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini

Check akses MANAGEMENT CONSOLE ;

[root@localhost ~]# /sbin/ndb_mgm
ndb_mgm> HELP
---------------------------------------------------------------------------
 NDB Cluster -- Management Client -- Help
---------------------------------------------------------------------------
HELP                                   Print help text
HELP COMMAND                           Print detailed help for COMMAND(e.g. SHOW)
SHOW                                   Print information about cluster
CREATE NODEGROUP <id>,<id>...          Add a Nodegroup containing nodes
DROP NODEGROUP <NG>                    Drop nodegroup with id NG
START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [<backup id>] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [<backup id>] [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
                                       Start backup (default WAIT COMPLETED,SNAPSHOTEND)
ABORT BACKUP <backup id>               Abort backup
SHUTDOWN                               Shutdown all processes in cluster
CLUSTERLOG ON [<severity>] ...         Enable Clus
<severity> = ALERT | CRITICAL | ERROR | WARNING | INFO | DEBUG
<category> = STARTUP | SHUTDOWN | STATISTICS | CHECKPOINT | NODERESTART | CONNECTION | INFO | ERROR | CONGESTION | DEBUG | BACKUP
<level>    = 0 - 15
<id>       = ALL | Any database node id

For detailed help on COMMAND, use HELP COMMAND.
ndb_mgm>
ndb_mgm> exit
[root@localhost ~]#

C. Konfigurasi MYSQL CLUSTER DATABASE SERVER ( 192.168.24.[20/30] ) ;

CLUSTER berfungsi sebagai media penyimpanan database dimana fungsi cluster dijalankan, jalankan konfigurasi berikut ini pada kedua DATABASE SERVER.

Edit file konfigurasi CLUSTER DATABASE SERVER ;

[root@localhost ~]# vi /etc/my.cnf

Isi file my.cnf ;

[mysqld]
ndbcluster
ndb-connectstring=192.168.24.10    # IP MANAGEMENT SERVER
[mysql_cluster]
ndb-connectstring=192.168.24.10    # IP MANAGEMENT SERVER

Membuat directory data ;

[root@localhost ~]# cd /usr/local/mysql
[root@localhost ~]# mkdir mysql-cluster
[root@localhost ~]# ln -s /usr/local/mysql/mysql-cluster /var/lib/mysql-cluster

Hentikan proses MySQL yang dalam kondisi aktif ;

[root@localhost ~]# /etc/init.d/mysqld stop

Jalankan CLUSTER DATABASE SERVER dan MySQL;

[root@localhost ~]# cd /usr/local/mysql/mysql-cluster
[root@localhost ~]# ln -s /usr/local/mysql/bin/ndbd /sbin/ndbd
[root@localhost ~]# /sbin/ndbd --initial
[root@localhost ~]# /etc/init.d/mysqld start

Untuk eksekusi perintah "/sbin/ndbd --initial" hanya dilakukan pada proses awal inisialisasi saja, selanjutnya cukup menggunakan perintah "/sbin/ndbd".

*** CHECK KINERJA :

Untuk melakukan pemeriksaan pada kinerja MYSQL-CLUSTER, dimulai dari MANAGEMENT SERVER sebagai berikut ;

Check melalui MANAGEMENT CONSOLE ( 192.168.24.10 ) ;

[root@localhost ~]# /sbin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.24.20  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=3    @192.168.24.30  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.24.10  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]   2 node(s)
id=4    @192.168.24.30  (mysql-5.1.34 ndb-7.0.6)
id=5    @192.168.24.20  (mysql-5.1.34 ndb-7.0.6)

ndb_mgm>exit
[root@localhost ~]#

Fungsi CLUSTER berjalan dengan baik apabila indikator diatas terpenuhi, dimana seluruh node database (NDB) telah dikenali dan tersambung pada MANAGEMENT SERVER, bila anda menemukan informasi berikut ;

not connected, accepting connect from 192.168.24.20 atau,
not connected, accepting connect from 192.168.24.30

pada 2 baris awal (NDB) atau 2 baris akhir (API) menandakan adanya kendala pada proses CLUSTER, lakukan check pada konektivitas jaringan dan konfigurasi pada system tersebut.

Check melalui DATABASE SERVER ( 192.168.24.[20/30] ) ;

Akses pada MySQL Console SYS-B dan SYS-C dan lakukan test insert dan query pada database.

SYS-B ( 192.168.24.20 ) ;

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>use test;
mysql> CREATE TABLE rtest (author CHAR(50)) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (1.11 sec)

mysql> INSERT INTO rtest () VALUES ('rizky.md@gmail.com');
Query OK, 1 row affected (0.03 sec)

mysql> select * from rtest;
+--------------------+
| author             |
+--------------------+
| rizky.md@gmail.com |
+--------------------+
1 row in set (0.00 sec)

mysql>

SYS-C ( 192.168.24.30 ) ;

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> select * from rtest ;
+--------------------+
| author             |
+--------------------+
| rizky.md@gmail.com |
+--------------------+
1 row in set (0.00 sec)

mysql>

Bila database pada SYS-C terupdate sesuai dengan database pada SYS-B secara realtime (vice versa), maka fungsi cluster telah berjalan dengan baik, lakukan hal yang sama pada SYS-C untuk melakukan pemeriksaan terhadap fungsi CLUSTER. Anda dapat juga melakukan test redundancy dengan memutuskan koneksi jaringan pada salah satu DATABASE SERVER.

*** MENGGUNAKAN ENGINE CLUSTER :

Untuk membuat table baru dengan Engine NDBCLUSTER, perintah yang digunakan  ;

CREATE TABLE 'NAMA-TABLE' 'PARAMETER' ENGINE=NDBCLUSTER;

Contoh :

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE rmdtest ;
mysql> use rmdtest ;
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>  CREATE TABLE VPNUSER (nama CHAR(50), password  CHAR(50)) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (1.26 sec)

mysql> INSERT INTO VPNUSER () VALUES ('admin' ,'r1zky');
Query OK, 1 row affected (0.03 sec)

mysql> select * from VPNUSER;
+-------+----------+
| nama  | password |
+-------+----------+
| admin | r1zky    |
+-------+----------+
1 row in set (0.00 sec)

mysql>

Untuk menggunakan fungsi cluster pada table yang sudah ada diperlukan konversi dari Engine MyISAM ke Engine NDBCLUSTER, perintah yang digunakan ;

ALTER TABLE 'NAMA-TABLE' ENGINE=NDBCLUSTER;

Contoh :

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use rmdtest ;
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_rmdtest |
+-------------------+
| VPNUSER           |
+-------------------+
1 row in set (0.06 sec)

mysql> ALTER TABLE VPNUSER ENGINE=NDBCLUSTER;
Query OK, 1 row affected (2.85 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from VPNUSER;
+-------+----------+
| nama  | password |
+-------+----------+
| admin | r1zky    |
+-------+----------+
1 row in set (0.07 sec)

mysql>

*** INFORMASI TAMBAHAN :

Untuk menambahkan DATABASE SERVER (NDB) edit config.ini pada MANAGEMENT SERVER, sebagai contoh menambahkan 2 node NDB sehingga menjadi 4 DATABASE SERVER ;

[NDBD DEFAULT]
NoOfReplicas=4
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# MANAGEMENT SERVER
[NDB_MGMD]
HostName=192.168.24.10        # IP MANAGEMENT SERVER
# DATABASE SERVER
[NDBD]
HostName=192.168.24.20        # IP DATABASE SERVER 1
DataDir= /usr/local/mysql/mysql-cluster
[NDBD]
HostName=192.168.24.30        # IP DATABASE SERVER 2
DataDir=/usr/local/mysql/mysql-cluster
[NDBD]
HostName=192.168.24.40        # IP DATABASE SERVER 3
DataDir=/usr/local/mysql/mysql-cluster
[NDBD]
HostName=192.168.24.50        # IP DATABASE SERVER 4
DataDir=/usr/local/mysql/mysql-cluster
# 2 DATABASE SERVER
[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]

Kemudian lakukan 'RELOAD' proses MANAGEMENT SERVER ( 192.168.24.10 ) ;

[root@localhost ~]# killall ndb_mgmd
[root@localhost ~]# /sbin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini

Shutdown proses CLUSTER melalui MANAGEMENT SERVER CONSOLE ( 192.168.24.10 ) ;

[root@localhost ~]# /sbin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHUTDOWN
ndb_mgm> EXIT

Kemudian lakukan 'RELOAD' pada tiap DATABASE SERVER ( 192.168.24.[20/30/40/50] ) ;

[root@localhost ~]# /etc/init.d/mysqld stop
[root@localhost ~]# killall ndbd
[root@localhost ~]# /sbin/ndbd
[root@localhost ~]# /etc/init.d/mysqld start

Lakukan 'RELOAD' setiap kali terjadi perubahan pada 'config.ini' MANAGEMENT SERVER.

Implementasikan MYSQL CLUSTER pada kondisi Network yang aman dan tertutup, semisal melalui link VPN atau Internal network yang terlindungi firewall dan fungsi keamanan yang memadai.

Penulis : rizky.md@gmail.com

Referensi :