Mysql Cluster

MySQL-CLuster-rizky.txt MYSQL CLUSTER - DATABASE Redundancy - REV 2. *** 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 ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# /etc/init.d/mysqld [root@localhost ~]# [root@localhost ~]#

tar -xvzf mysql-cluster-XXX-VERSION-OS.tar.gz mv mysql-cluster-XXX-VERSION-OS /usr/local ln -s /usr/local/mysql-cluster-XXX-VERSION-OS /usr/local/mysql groupadd mysql useradd -g mysql mysql cd /usr/local/mysql chown -R mysql . chgrp -R mysql . scripts/mysql_install_db --user=mysql chown -R root . chown -R mysql data cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf cp /usr/local/mysql/support-files/mysql.server chmod 755 /etc/init.d/mysqld 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 --pidfile=/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 --pidPage 1

MySQL-CLuster-rizky.txt 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 [root@localhost [root@localhost [root@localhost [root@localhost

~]# ~]# ~]# ~]# ~]#

cd /usr/local/mysql ln -s /usr/local/mysql/bin/ndb_mgm /sbin/ndb_mgm ln -s /usr/local/mysql/bin/ndb_mgmd /sbin/ndb_mgmd mkdir mysql-cluster 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 ; Page 2

MySQL-CLuster-rizky.txt [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 ,... Add a Nodegroup containing nodes DROP NODEGROUP Drop nodegroup with id NG START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED] START BACKUP [] [NOWAIT | WAIT STARTED | WAIT COMPLETED] START BACKUP [] [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED] Start backup (default WAIT COMPLETED,SNAPSHOTEND) ABORT BACKUP Abort backup SHUTDOWN Shutdown all processes in cluster CLUSTERLOG ON [<severity>] ... Enable Clus <severity> = ALERT | CRITICAL | ERROR | WARNING | INFO | DEBUG = STARTUP | SHUTDOWN | STATISTICS | CHECKPOINT | NODERESTART | CONNECTION | INFO | ERROR | CONGESTION | DEBUG | BACKUP = 0 - 15 = 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 [mysql_cluster] ndb-connectstring=192.168.24.10

# IP MANAGEMENT SERVER # 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 Page 3

MySQL-CLuster-rizky.txt Hentikan proses MySQL yang dalam kondisi aktif ; [root@localhost ~]# /etc/init.d/mysqld stop Jalankan CLUSTER DATABASE SERVER dan MySQL; [root@localhost [root@localhost [root@localhost [root@localhost

~]# ~]# ~]# ~]#

cd /usr/local/mysql/mysql-cluster ln -s /usr/local/mysql/bin/ndbd /sbin/ndbd /sbin/ndbd --initial /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] ) ; Page 4

MySQL-CLuster-rizky.txt 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 ('[email protected]'); Query OK, 1 row affected (0.03 sec) mysql> select * from rtest; +--------------------+ | author | +--------------------+ | [email protected] | +--------------------+ 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 | +--------------------+ | [email protected] | +--------------------+ 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 : Page 5

MySQL-CLuster-rizky.txt 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 Query OK, 0 rows affected (1.26 sec)

CHAR(50)) ENGINE=NDBCLUSTER;

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 | +-------------------+ Page 6

MySQL-CLuster-rizky.txt | 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 -Page 7

MySQL-CLuster-rizky.txt ndb_mgm> SHUTDOWN ndb_mgm> EXIT Kemudian lakukan 'RELOAD' pada tiap DATABASE SERVER ( 192.168.24.[20/30/40/50] ) ; [root@localhost [root@localhost [root@localhost [root@localhost

~]# ~]# ~]# ~]#

/etc/init.d/mysqld stop killall ndbd /sbin/ndbd /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 : [email protected] - http://spikecursed.blogsome.com Referensi : - http://dev.mysql.com - http://en.wikipedia.org/wiki/Mysql_cluster

Page 8