본문 바로가기

Technical/DBMS

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;