MySQL ndbcluster & Replicator 설정 - help text

[Mysql NDB Cluster 설정] - 5.1.44-ndb-7.1.4b

1. 소스 다운로드

http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/mysql-cluster-gpl-7.1.4b.tar.gz/from/http://mirror.khlug.org/mysql/

2. 압축 해제, 설치

(필수 패키지: curses*, termcap, gcc-c++)

# ./configure --prefix=/user/service/mysql --with-mysqld-user=mysql --sysconfdir=/user/service/mysql/conf --with-unix-socket-path=/tmp/mysqld/mysql.sock --with-charset=utf8 --with-extra-charsets=all --with-plugins=innobase,myisam,partition,heap,ndbcluster

# make && make install

5. cnf 복사, 설정

# cp /ForMysqlBaseDir/mysql/share/mysql/my-huge.cnf /ForMysqlBaseDir/mysql/conf/my.cnf

* cnf 편집, 디렉토리 설정

  또는 미리 준비한 my.cnf 복사(cp /home/myaccound/mysql554/my.cnf /ForMysqlBaseDir/mysql/conf)

* Sample 설정

------------------------------------------------------

[mysqld]

init_connect=SET collation_connection = utf8_general_ci

init_connect=SET NAMES utf8

character-set-server=utf8

collation-server=utf8_general_ci

# Storage engine

default-storage-engine = innodb

# no difference between upper and lowercases

lower_case_table_names = 1

user            = mysql

pid-file        = /tmp/mysqld/mysql.pid

socket          = /tmp/mysqld/mysql.sock

port            = 3306

basedir         = /user/service/mysql

datadir         = /user/service/mysql/data

tmpdir          = /tmp

------------------------------------------------------

6. 초기데이터베이스 생성

# cd /ForMysqlBaseDir/bin

# ./mysql_install_db --user=mysql

7. Directory 생성, 권한 설정

# mkdir -p /ForMysqlBaseDir/mysql/conf

# chown -R root.mysql /ForMysqlBaseDir/mysql

# chown -R mysql.mysql /ForMysqlBaseDir/mysql/data

8. 테스트 실행

> cd /ForMysqlBaseDir/mysql/bin

> ./mysqld_safe --user=mysql &

> ./mysqladmin shutdown

9. Daemon 등록 실행

> cp /ForMysqlBaseDir/mysql/share/mysql/mysql.server /etc/init.d/mysqld

> chkconfig --add mysqld

> chkconfig | grep mysql

> chkconfig mysqld on

> /etc/init.d/mysqld start

10. Root 암호 등록, Daemon  종료

> mysqladmin -u root password 'xxxx'

> /etc/init.d/mysqld stop

* 여기까지 기본 설치(모든 노드)

* 총 4대의 서버중 2대(10.10.0.7, 10.10.0.8)을 MGM+SQL node 로, 2대(10.10.0.9, 10.10.0.10)을 NDB node 로

   설정한다고 가정

11. MGM, Mysql node

mkdir /user/service/mysql-cluster

* Cluster configuration(vi cluster.ini)

[ndbd default]

NoOfReplicas= 2

DataMemory= 512M

IndexMemory= 128M

DataDir= /user/service/mysql-cluster

TimeBetweenWatchDogCheck= 30000

MaxNoOfConcurrentOperations= 10000

MaxNoOfOrderedIndexes = 10000 

MaxNoOfUniqueHashIndexes = 15000 

MaxNoOfTables= 250

[ndb_mgmd default]

DataDir= /user/service/mysql-cluster

[ndb_mgmd]

Id=1

HostName= 10.10.0.7

[ndb_mgmd]

Id=2

HostName= 10.10.0.8

[ndbd]

Id= 3

HostName= 10.10.0.9

[ndbd]

Id= 4

HostName= 10.10.0.10

[mysqld]

Id= 5

HostName= 10.10.0.7

[mysqld]

Id= 6

HostName= 10.10.0.8

[mysqld]

Id= 7

[mysqld]

Id= 8

[tcp default]

PortNumber= 63132

* MySQL configuration

vi /user/service/mysql/my.cnf

[mysqld]

ndbcluster

ndb-connectstring=10.10.0.7,10.10.0.8

[mysql_cluster]

ndb-connectstring=10.10.0.7,10.10.0.8

* Daemon 등록(ndb_mgmd)

echo '/user/service/mysql/libexec/ndb_mgmd -f /user/service/mysql-cluster/cluster.ini' > /etc/init.d/ndb_mgmd

chmod 755 /etc/init.d/ndb_mgmd

12. NDB node

* mkdir /user/service/mysql-cluster

* MySQL configuration

vi /user/service/mysql/my.cnf

[mysqld]

ndbcluster

ndb-connectstring=10.10.0.7,10.10.0.8

[mysql_cluster]

ndb-connectstring=10.10.0.7,10.10.0.8

* Daemon 등록(ndbd)

echo '/user/service/mysql/libexec/ndbd' > /etc/init.d/ndbd

chmod +x /etc/init.d/ndbd

chkconfig --add ndbd

chkconfig ndbd on

13. Service start

<MGM node>

/etc/init.d/ndb_mgmd

<NDB node>

/user/service/mysql/libexec/ndbd --initial => 처음에 한번, 설정 변경시 적용

/etc/init.d/mysqld start

* Remote connect Grant

# mysql -uroot -prootpass

> use mysql;

> grant all privileges on *.* to 'root'@'%' identified by 'rootpass' with grant option;

> flush privileges;

14. Cluster 적용 상태 확인

# ndb_mgm

[MySQL Replication 시 작업] - 앞서 빌드 했던 5.5.4-m3 사용

* Master측

vi my.cnf

[mysqld]

server-id=1

log-bin

/user/service/mysql/bin/mysql_install_db --user=mysql

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO root@"%" IDENTIFIED by 'qrwe1423';

flush privileges;

show master status;

/etc/init.d/mysqld restart

* Slave측

vi my.cnf

log-bin

server-id=2

log-slave-updates

log-warning

max_allow_packet=32M -- sqldump backup/restore시 중요

CHANGE MASTER TO MASTER_HOST='10.10.10.11', MASTER_USER='root', MASTER_PASSWORD='qrwe1423';

* Restart mysql !!!

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

slave start;

show slave status;

* Error 발생시(ERROR 1418 (HY000))

show global variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators = 1;

mysqld 실행시 옵션 추가:  --log-bin-trust-function-creators=1

* show slave status에서 Duplicate PRIMARY ...mysql.tmp_db... blahblah Erro 발생시

master에서 reset master, slave에서 slave stop; reset slave; change master...; DB restart, slave start...

* 동기화 오류로 sync가 동작하기 않을 때의 임시 조치

(1) 1차 조치

<Slave> slave stop ;
<Slave> set global sql_slave_skip_counter=1;
<Slave> slave start ;

(2) 1차 조치로 해결되지 않으면

<Slave> /etc/init.d/mysqld stop

<Slave> ./mysqld_safe --skip-slave

<Slave> MYSQL> stop slave;

<Master> MYSQL> reset master; -- Qery log 파일 초기화

<Slave> MYSQL> reset slave;

<Slave> MYSQL> CHANGE MASTER TO MASTER_HOST='10.10.0.9', MASTER_USER='root', MASTER_PASSWORD='qrwe1423';

<Slave> MYSQL> slave start;

<Slave> MYSQL> start slave;