MySQL Cluster 설치 가이드

MySQL Cluster Installation Guide

이번 블로그에서는  MySQL Cluster 설치에 대해서 설명을 할까합니다. MySQL 서버의 경우엔 혼자서 설치하고 테스트하시는데 문제가 없는 것 같은데 Cluster의 경우는 설치도 어려워하시는 분들이 계셔서요.  제가 참고한 자료가 있었는데 기억이 잘 안나서 나중에 원문은 첨부하겠습니다.
제 환경에서 테스트를 했는데요 1차는 보시듯이  mysql cluster 7.2.6 버전으로 설치했습니다. 그래서 자료에 보면 7.2.6이나 mysql 5.5.x로 표시된 부분이 있을 거예요.
얼마전 교육을 위해 7.4.6(현재 최신버전0으로 테스트했는데 잘 됩니다. 변경사항은 파일명 정도입니다.

그리고 맨 아래에는 Backup과 Restore부분을 추가했습니다.
테스트의 편의를 위해 2 data node와 1 mgm node, 1 sql 노드를 한서버에 구성했습니다. 혹시 sql 노드를 두개로 가져가시려고 하시면 포트번호를 따로해서 data dir을 별개로 두시면 되세요.
VM의 cluster를 올렸을때 2개만 올려도 테스트시에 버벅거리기 때문에 일단 하나의 서버의 모든 노드를 설치해서 기능테스트만 한다고 생각하고 진행한 것입니다. 그리고 편의를 위해이 테스트에서는 root로 설치 및 구동을 했습니다.

하지만 실환경에서는 만드시 mgm 노드를 분리하셔야 하고 data노드도 각각 별개의 물리서버의 위치하게 끔 구축하셔야 합니다. 사용자 역시 DBA 권한을 부여한 사용자로 구동을 하셔야 합니다.

설치 바이너리는 dev.mysql.com 의 community mysql cluster를 받으셔도 되구요. CGE를 테스트하시려면 edelivery.oracle.com에 들어가시면 30일 trial을 받으실 수 있습니다.

1.    Cluster binary 설치 및 심볼릭링크 걸기

$ mkdir mysql

$ tar xvf /mysql/mysql-cluster-gpl-7.2.6-linux-x86-glibc23.tar.gz

$ ln -s mysql-cluster-gpl-7.2.6-linux-x86-glibc23.tar.gz mysqlc

2.    Cluster를 위한 디렉토리 생성

Root(/) 디렉토리 아래에서 다음 명령어를 수행함.

$mkdir my_cluster my_cluster/mgm_data my_cluster/ndb_data my_cluster/sql_data my_cluster/conf

3.    Config 파일 생성 - /my_cluster/conf 아래
Cluster를 위해서는 아래 2개의 configuration 파일이 필요합니다. my.cnf는 아시겠지만 sql 노드에 위치해야 하구요. config.ini는 mgm 노드에 위치합니다.

my.cnf:

[mysqld]

ndbcluster

datadir=/my_cluster/sql_data

basedir=/mysql/mysqlc

port=5000

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster/conf# vi config.ini
[ndb_mgmd]
hostname=localhost
datadir=/my_cluster/mgm_data
NodeId=1

[ndbd default]
noofreplicas=2
datadir=/my_cluster/ndb_data

[ndbd]
hostname=localhost
NodeId=3

[ndbd]
hostname=localhost
NodeId=4

[mysqld]
NodeId=50

[mysqld]

NodeId=51

4.    Mysqld의 install_db 스크립트 실행

root@sumiryu-VirtualBox:/mysql/mysqlc# ./scripts/mysql_install_db --no-defaults --datadir=/my_cluster/sql_data/
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h sumiryu-VirtualBox password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

5.    mgmd 실행

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster#/mysql/mysqlc/bin/ndb_mgmd -f conf/config.ini --initial --configdir /my_cluster/conf/
MySQL Cluster Management Server mysql-5.5.22 ndb-7.2.6

6.    MGM 이용 노드 정상적으로 동작 중인지 확인

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster# /mysql/mysqlc/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=3 (not connected, accepting connect from localhost)
id=4 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6)

[mysqld(API)]    1 node(s)
id=50 (not connected, accepting connect from any host)

7.    ndbd 실행 (2개 데이타 노드)

root@sumiryu-VirtualBox:/mysql/mysqlc/bin# ./ndbd -c 127.0.0.1:1186
2012-08-03 17:13:04 [ndbd] INFO     -- Angel connected to '127.0.0.1:1186'
2012-08-03 17:13:04 [ndbd] INFO     -- Angel allocated nodeid: 3

root@sumiryu-VirtualBox:/mysql/mysqlc/bin# ./ndbd -c 127.0.0.1:1186
2012-08-03 17:16:04 [ndbd] INFO     -- Angel connected to '127.0.0.1:1186'
2012-08-03 17:16:04 [ndbd] INFO     -- Angel allocated nodeid: 4

8.    ndbd 정상 동작 확인

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster# /mysql/mysqlc/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=3    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0, *)
id=4    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6)

[mysqld(API)]    1 node(s)
id=50 (not connected, accepting connect from any host)

9.    mysqld실행 : mysqld를 제외한 모든 노드 정상동작 확인 후 진행해야 함

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster# /mysql/mysqlc/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=3    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6, starting, Nodegroup: 0, Master)
id=4    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6, starting, Nodegroup: 0)

[ndb_mgmd(MGM)]    1 node(s)
id=1    @127.0.0.1  (mysql-5.5.22 ndb-7.2.6)

[mysqld(API)]    1 node(s)
id=50 (not connected, accepting connect from any host)

10.  mysqld 실행

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster# ../bin/mysqld --defaults-file=/my_cluster/conf/my.cnf --user=root &

[1] 4042
root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster# 120803 17:25:49 [Note] Plugin 'FEDERATED' is disabled.
120803 17:25:49 [Note] NDB: NodeID is 50, management server 'localhost:1186'
120803 17:25:50 [Note] NDB[0]: NodeID: 50, all storage nodes connected
120803 17:25:50 [Warning] NDB: server id set to zero - changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
120803 17:25:50 [Note] Starting Cluster Binlog Thread
120803 17:25:50 InnoDB: The InnoDB memory heap is disabled
120803 17:25:50 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
120803 17:25:50 InnoDB: Compressed tables use zlib 1.2.3
120803 17:25:50 InnoDB: Using Linux native AIO
120803 17:25:50 InnoDB: Initializing buffer pool, size = 128.0M
120803 17:25:50 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
120803 17:25:50  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
120803 17:25:51  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120803 17:25:51  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
120803 17:25:54  InnoDB: Waiting for the background threads to start
120803 17:25:55 InnoDB: 1.1.8 started; log sequence number 0
120803 17:25:56 [Note] Event Scheduler: Loaded 0 events
120803 17:25:56 [Note] ../bin/mysqld: ready for connections.
Version: '5.5.22-ndb-7.2.6-enterprise-commercial-advanced'  socket: '/tmp/mysql.sock'  port: 5000  MySQL Enterprise Server - Advanced Edition (Commercial)
120803 17:25:56 [Note] NDB: Creating mysql.ndb_schema
120803 17:26:01 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema
120803 17:26:01 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
120803 17:26:01 [Note] NDB: Creating mysql.ndb_apply_status
120803 17:26:02 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_apply_status
120803 17:26:02 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
120803 17:26:03 [Note] NDB: missing frm for mysql.ndb_index_stat_sample, discovering...
120803 17:26:03 [Note] NDB: missing frm for mysql.ndb_index_stat_head, discovering...
2012-08-03 17:26:03 [NdbApi] INFO     -- Flushing incomplete GCI:s < 232/6
2012-08-03 17:26:03 [NdbApi] INFO     -- Flushing incomplete GCI:s < 232/6
120803 17:26:03 [Note] NDB Binlog: starting log at epoch 232/6
120803 17:26:03 [Note] NDB Binlog: ndb tables writable

11.  MySQL Client 를 이용해 정상적으로 동작되는지 확인

root@sumiryu-VirtualBox:/mysql/mysqlc/my_cluster# /mysql/mysqlc/bin/mysql -h 127.0.0.1 -P 5000 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.22-ndb-7.2.6-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2011, 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              |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.06 sec)
mysql> create database clusterdb;
Query OK, 1 row affected (0.26 sec)

mysql> use clusterdb;
Database changed

mysql> create table simples (id int not null primary key) engine=ndb;
120803 17:28:27 [Note] NDB Binlog: CREATE TABLE Event: REPL$clusterdb/simples
Query OK, 0 rows affected (2.08 sec)

mysql> insert into simples values (1), (2), (3), (4);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from simples;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  3 |
+----+
4 rows in set (0.03 sec)

12.  문제있을 때 로그보기

root@sumiryu-VirtualBox:/mysql/mysqlc/bin# tail -f ../my_cluster/ndb_data/ndb_1_cluster.log
2012-08-03 17:18:09 [MgmtSrvr] INFO     -- Node 3: Start phase 101 completed (initial start)
2012-08-03 17:18:09 [MgmtSrvr] INFO     -- Node 4: Start phase 101 completed (initial start)
2012-08-03 17:18:09 [MgmtSrvr] INFO     -- Node 3: Started (mysql-5.5.22 ndb-7.2.6)
2012-08-03 17:18:09 [MgmtSrvr] INFO     -- Node 4: Started (mysql-5.5.22 ndb-7.2.6)
2012-08-03 17:18:09 [MgmtSrvr] INFO     -- Node 3: Node 1: API mysql-5.5.22 ndb-7.2.6
2012-08-03 17:18:09 [MgmtSrvr] INFO     -- Node 4: Node 1: API mysql-5.5.22 ndb-7.2.6
2012-08-03 17:18:10 [MgmtSrvr] INFO     -- Node 3: Communication to Node 50 opened
2012-08-03 17:18:10 [MgmtSrvr] INFO     -- Node 4: Communication to Node 50 opened
2012-08-03 17:18:10 [MgmtSrvr] INFO     -- Node 4: Prepare arbitrator node 1 [ticket=0ec60001009b987c]
2012-08-03 17:18:10 [MgmtSrvr] INFO     -- Node 3: Started arbitrator node 1 [ticket=0ec60001009b987c]

13.  Safe shutdown

$/mysql/mysqlc/bin/ndb_mgm –e shutdown

Mysqld 는 아래 명령어를  stop

./mysqladmin shutdown –u root –h 127.0.0.1 –P 5000

14.                   Backup

ndb_mgm> start backup

ndb_mgm> Node 3: Backup 4 started from node 1

Node 3: Backup 4 started from node 1 completed

 StartGCP: 12865 StopGCP: 12868

 #Records: 2061 #LogRecords: 0

 Data: 51388 bytes Log: 0 bytes

15.                   Restore

Restore 전에 해야 할 일

- clusterdb를 mysql client> drop database clusterdb;

- 다시 clusterdb를 생성해줘야 합니다. 테이블들은 생성하실 필요가 없으며, database 만 생성해주시면 됩니다. database를 생성하지 않을 경우 restore가 정상적으로 동작하지 않습니다. -m옵션은 메타데이타를 restore하는 옵션으로 반드시 한번만 수행하셔야 합니다. 그리고 restore는 data 노드 수만큼 수행하셔야 합니다.

mysql client>create database clusterdb;

[root@smdomain bin]# ./ndb_restore -c 127.0.0.1:1186 –m -n 3 -b 4 -r --backup_path=/my_cluster/ndb_data//BACKUP/BACKUP-4/
Nodeid = 3
Backup Id = 4
backup path = /my_cluster/ndb_data//BACKUP/BACKUP-4/
Opening file '/my_cluster/ndb_data//BACKUP/BACKUP-4/BACKUP-4.3.ctl'
File size 21988 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.24 ndb-7.4.6
Stop GCP of Backup: 30780
Connected to ndb!!
Opening file '/my_cluster/ndb_data//BACKUP/BACKUP-4/BACKUP-4-0.3.Data'
File size 26000 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 0
_____________________________________________________
Processing data in table: clusterdb/def/simples(10) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 0
Opening file '/my_cluster/ndb_data//BACKUP/BACKUP-4/BACKUP-4.3.log'
File size 52 bytes
Restored 3 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@smdomain bin]# ./ndb_restore -c 127.0.0.1:1186 -n 4 -b 4 -r --backup_path=/my_cluster/ndb_data//BACKUP/BACKUP-4/
Nodeid = 4
Backup Id = 4
backup path = /my_cluster/ndb_data//BACKUP/BACKUP-4/
Opening file '/my_cluster/ndb_data//BACKUP/BACKUP-4/BACKUP-4.4.ctl'
File size 21988 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.24 ndb-7.4.6
Stop GCP of Backup: 30780
Connected to ndb!!
Opening file '/my_cluster/ndb_data//BACKUP/BACKUP-4/BACKUP-4-0.4.Data'
File size 26004 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 1
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 1
_____________________________________________________
Processing data in table: clusterdb/def/simples(10) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 1
Opening file '/my_cluster/ndb_data//BACKUP/BACKUP-4/BACKUP-4.4.log'
File size 52 bytes
Restored 5 tuples and 0 log entries

NDBT_ProgramExit: 0 – OK

참고자료 : https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-restore.html

16.  정상적으로 Restore 됐는지 확인

mysql> select * from simples;
+----+
| id |
+----+
|  7 |
|  3 |
|  6 |
+----+
3 rows in set (0.02 sec)