Set Up a High-Availability MySQL Cluster

kali ini gw coba memaparkan langkah apa aja yang dilakukan bila ingin meng-implementasikan MySQL Cluster, cukup mudah dan gak sesulit yang lo bayangkan bro…

Dengan meng-install MySQL-clustermanagement, MySQL-clusterstorage, MySQL-clustertools, MySQL-clusterextra, dan membuat file konfigurasi untuk cluster lo udah bisa testing mysql cluster nih… seperti biasa lab mysql cluster ini gw lakuin di 2 OS Centos yang running diatas VMware Server Console (centos1: 192.168.216.129 dan centos2: 192.168.216.128)
Instalasi MySQL server dan client RPMs dan set password untuk user root di mesin centos1:

[root@centos1 src]# rpm -i MySQL-server-community-5.0.51a-0.rhel5.i386.rpm
[root@centos1 src]# rpm -i MySQL-client-community-5.0.51a-0.rhel5.i386.rpm

[root@centos1 src]# mysqladmin -u root password ‘password’
[root@centos1 src]# mysqladmin -u root -p -h centos1 password ‘password’
Enter password:

Instalasi MySQL cluster RPMs, cluster storage, management, tools, extra :

[root@centos1 ~]# cd /home/david/src/cluster/
[root@centos1 cluster]# ls -al
total 13492
drwxr-xr-x 2 root root 4096 Jun 26 01:35 .
drwxrwxrwx 5 root root 4096 Jun 26 01:35 ..
-rw-r–r– 1 root root 3682652 May 16 09:17 MySQL-clusterextra-community-5.0.51a -0.rhel5.i386.rpm
-rw-r–r– 1 root root 1097338 May 16 09:17 MySQL-clustermanagement-community-5. 0.51a-0.rhel5.i386.rpm
-rw-r–r– 1 root root 1534373 May 16 09:17 MySQL-clusterstorage-community-5.0.5 1a-0.rhel5.i386.rpm
-rw-r–r– 1 root root 7030278 May 16 09:17 MySQL-clustertools-community-5.0.51a -0.rhel5.i386.rpm
-rw-r–r– 1 root root 396159 Jun 20 09:16 world.sql

[root@centos1 cluster]# rpm -iv MySQL*storage*
Preparing packages for installation…
MySQL-clusterstorage-community-5.0.51a-0.rhel5

[root@centos1 cluster]# rpm -iv MySQL*management*
Preparing packages for installation…
MySQL-clustermanagement-community-5.0.51a-0.rhel5

[root@centos1 cluster]# rpm -iv MySQL*tools*
Preparing packages for installation…
MySQL-clustertools-community-5.0.51a-0.rhel5

[root@centos1 cluster]# rpm -iv MySQL*extra*
Preparing packages for installation…
MySQL-clusterextra-community-5.0.51a-0.rhel5

[root@centos1 cluster]# cd /usr/sbin/

[root@centos1 sbin]# ls -al ndb*
-rwxr-xr-x 1 root root 2085580 Jan 14 2008 ndb_cpcd
-rwxr-xr-x 1 root root 3563944 Jan 14 2008 ndbd
-rwxr-xr-x 1 root root 2449172 Jan 14 2008 ndb_mgmd

langkah selanjutnya kita buat file konfigurasi my.cnf seperti berikut:

[root@centos1 sbin]# vi /etc/my.cnf

# Options for mysqld process:
[mysqld]
ndbcluster # run NDB storage engine
ndb-connectstring=192.168.216.129 # location of management server
port=3306

# Options for ndbd process:
[mysql_cluster]
ndb-connectstring=192.168.216.129 # location of management server

selanjutnya kita buat derektori data untuk mysql /usr/local/mysql/data:
[root@centos1 sbin]# mkdir /usr/local/mysql
[root@centos1 sbin]# mkdir /usr/local/mysql/data

kemudian kita buat file konfigurasi untuk MySQL cluster /var/lib/mysql-cluster/config.ini:
[root@centos1 sbin]# vi /var/lib/mysql-cluster/config.ini

# Options affecting ndbd processes on all data nodes:
[ndbd default]
NoOfReplicas=1 # Number of replicas
DataMemory=80M # How much memory to allocate for data storage
IndexMemory=18M # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the “world” database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.

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

# Options for data node “A”:
[ndbd]
# (one [ndbd] section per data node)
hostname=192.168.216.129 # Hostname or IP address
datadir=/usr/local/mysql/data # Directory for this data node’s data files

# Options for data node “B”:
#[ndbd]
#hostname=192.168.216.128 # Hostname or IP address
#datadir=/usr/local/mysql/data # Directory for this data node’s data files

# SQL node options:
[mysqld]
hostname=192.168.216.129 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)

kemudian start-up cluster dan check status dengan command show seperti berikut:

[root@centos1 sbin]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
[root@centos1 sbin]# ndbd

[root@centos1 sbin]# /usr/share/mysql/mysql.server stop
Shutting down MySQL. SUCCESS!
[root@centos1 sbin]# /usr/share/mysql/mysql.server start
Starting MySQL. SUCCESS!

[root@centos1 sbin]# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: 192.168.216.129:1186
Cluster Configuration
———————
[ndbd(NDB)] 1 node(s)
id=2 @192.168.216.129 (Version: 5.0.51, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.216.129 (Version: 5.0.51)

[mysqld(API)] 1 node(s)
id=3 @192.168.216.129 (Version: 5.0.51)

ndb_mgm>exit

kemudian kita rubah file konfigurasi MySQL cluster /var/lib/mysql-cluster/config.ini:
[root@centos1 sbin]# vi /var/lib/mysql-cluster/config.ini

edit  NoOfReplicas=1
jadi  NoOfReplicas=2

edit:   # Options for data node "B":
        #[ndbd]
        #hostname=192.168.216.128 # Hostname or IP address
        #datadir=/usr/local/mysql/data # Directory for this data node's data files

jadi:
	[ndbd]
	hostname=192.168.216.128
	datadir=/usr/local/mysql/data

lalu kita restart management node dengan command berikut:
[root@centos1 sbin]# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> shutdown
Connected to Management Server at: 192.168.216.129:1186
Node 2: Cluster shutdown initiated
1 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
Node 2: Node shutdown completed.
ndb_mgm> quit

[root@centos1 sbin]# ps axuw |grep ndb
root 2377 3.0 0.1 3892 680 pts/0 R+ 02:45 0:00 grep ndb

setelah cluster di shutdown, kemudian start up kembali:
[root@centos1 sbin]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Warning line 20: Cluster configuration warning:
arbitrator with id 1 and db node with id 2 on same host 192.168.216.129
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.

[root@centos1 sbin]# ndbd –initial
[root@centos1 sbin]# /usr/share/mysql/mysql.server start
Starting MySQL SUCCESS!

Sekarang kita beralih ke mesin centos2 untuk men set-up data node pada file konfigurasi /etc/my.cnf seperti berikut:

[root@centos2 tmp]# vi /etc/my.cnf
[mysql_cluster]
ndb-connectstring=192.168.216.129

kemudian kita install cluster storage dan buat direktori data di centos2:
[root@centos2 ~]# rpm -i /home/david/src/cluster/MySQL-clusterstorage-community-5.0.51a-0.rhel5.i386.rpm

[root@centos2 ~]# mkdir -p /usr/local/mysql
[root@centos2 ~]# mkdir -p /usr/local/mysql/data

start data node dengan command berikut:
[root@centos2 ~]# ndbd

Kembali ke mesin centos1 dan check apakah data node kedua sudah ter-register di cluster:
[root@centos1 sbin]# ndb_mgm
ndb_mgm> show
Connected to Management Server at: 192.168.216.129:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 @192.168.216.129 (Version: 5.0.51, Nodegroup: 0)
id=3 @192.168.216.128 (Version: 5.0.51, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.216.129 (Version: 5.0.51)

[mysqld(API)] 1 node(s)
id=4 @192.168.216.129 (Version: 5.0.51)

ndb_mgm> quit

Kemudian kita buat database dengan query CREATE DATABASE world dan bila sudah ada, maka hapus terlebih dahulu dengan query DROP DATABASE world:
[root@centos1 sbin]# cd /tmp
[root@centos1 tmp]# 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.0.51a-community MySQL Community Edition (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database world;

Load table pada file world.sql dengan command SOURCE:
mysql> use world;
mysql> source world.sql;

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+
3 rows in set (0.02 sec)

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> select count(*) from City;
+----------+
| count(*) |
+----------+
|     4080 |
+----------+
1 row in set (0.01 sec)

mysql> \q

gunakan ndb-mgm client, untuk men-stop second data node dengan command berikut: <id> STOP
[root@centos1 tmp]# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: 192.168.216.129:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 @192.168.216.129 (Version: 5.0.51, Nodegroup: 0)
id=3 @192.168.216.128 (Version: 5.0.51, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.216.129 (Version: 5.0.51)

[mysqld(API)] 1 node(s)
id=4 @192.168.216.129 (Version: 5.0.51)

ndb_mgm> 3 stop
Node 3: Node shutdown initiated
Node 3: Node shutdown completed.
Node 3 has shutdown.

ndb_mgm> quit

Coba jalankan kembali statement SELECT COUNT, SQL node akan tetap melayani request query dengan 1 node yang running
[root@centos1 tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a-community MySQL Community Edition (GPL)

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

mysql> use world
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 count(*) from City;
+----------+
| count(*) |
+----------+
|     4080 |
+----------+
1 row in set (0.01 sec)

mysql> \q

force kill data node dengan menggunakan command Linux kill -9
dan coba jalankan kembali statement SELECT COUNT, pastinya request query akan ditolak! karena kedua data node tidak running.

[root@centos1 tmp]# ps -ef | grep ndbd
root 2396 1 0 02:47 ? 00:00:00 ndbd –initial
root 2397 2396 1 02:47 ? 00:00:42 ndbd –initial
root 2576 1910 1 03:33 pts/0 00:00:00 grep ndbd
[root@centos1 tmp]# kill -9 2396 2397

[root@centos1 tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.51a-community MySQL Community Edition (GPL)

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

mysql> use world
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 count(*) from City;
ERROR 1015 (HY000): Can't lock file (errno: 157)
mysql> \q
Bye

berikut dapat terlihat data node pada centos1(192.168.216.129) id=2 dan centos2(192.168.216.128) id=3 tidak terkoneksi

[root@centos1 tmp]# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: 192.168.216.129:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.216.129)
id=3 (not connected, accepting connect from 192.168.216.128)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.216.129 (Version: 5.0.51)

[mysqld(API)] 1 node(s)
id=4 (not connected, accepting connect from 192.168.216.129)

ndb_mgm> \q

okay bro…. sampe sini dulu lab MySQL cluster kita,
semoga bermanfaat!

Referensi:
http://dev.mysql.com/doc/refman/5.0/en/ … uster.html
http://www.oreillynet.com/pub/a/databas … uster.html

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: