MySQL Replication 설정

ü MySQL Replicaton 은 DB 서버를 여러대 두고 웹서버로 부터의 접속(쿼리, select)을 분산 시켜 DB서버의 부하를 줄이는 방법 입니다.

ü slave 서버에서 master 서버의 바이너리 로그파일을 참조하여 업데이트 합니다.

ü CentOS 7 / mysql 5.6

1 my.conf 서버 설정

mysql 설정 파일을 엽니다.

vi /etc/my.conf                                                 

아래 두개 항목이 사용중인지 확인하고, 없으면 추가해 줍니다.

※ server-id 는 서버 고유 번호이므로 slave서버와 같으면 안됩니다.

[Master]

[mysqld]
log-bin=mysql-bin
server-id   = 1                                                 

[Slave]

[mysqld]
log-bin=mysql-bin
server-id   = 2                                                

설정 변경후 mysql을 재시작 합니다.

service mysqld restart                                           

2 계정 생성

GRANT REPLICATION SLAVE ON *.* TO 'ID'@'%' IDENTIFIED BY '비밀번호';
flush privileges;

 %부분은 접속 허용 대상 IP로, %는 모든 IP 허용을 뜻합니다.

아이디가 test , 패스워드가 test1234, Slave IP가 22.34.56.78 이라면

grant replication slave on *.* to 'test'@'22.34.56.78' identified by 'test1234';

3 백업

기본적으로 Replication은 Dump를 최소 1번 이상 받아야 합니다.

백업방법 1

[Master]

tar -cvf /tmp/mysqldata.tar .                                                              

[Slave]

tar -xvf /tmp/mysqldata.tar                                                                

백업방법 2

[Master]

mysqldump -u root -p world > dump_file.sql                                               

show master status;                                                                     

File명과 Positaion을 기록해 둡니다.

 

Master와 Slave DB가 이미 동일한 구조(스키마, 테이블, 컬럼 등)라면 dump파일을 전송하지 않아도 됩니다.

scp를 이용하여 slave에 dump 파일 전송합니다.

scp dump_file.sql root@SlaveIP:/root/                                                      

[Slave]

mysql -u root -p world < dump_file.sql                                                    

4 Master 접속 설정

[Slave]

CHANGE MASTER TO

-> master_host = 'MASTER IP',
 
-> master_port = 3306,                          ## MySQL 기본 포트. 포트 변경 시 변경한 Port 입력
 
-> master_user = 'ID',                          ## replication 용으로 생성했던 계정명
 
-> master_password = '비밀번호',
 
-> master_log_file = 'mysql-bin.000006',        ## 위에서 확인했던 file명
 
-> master_log_pos = 19609;                      ## 위에서 확인했던 Posiotion
 
start slave;

5 설정 확인

[Master]

SHOW MASTER STATUS\G                                                 

[Slave]

mysql

SHOW SLAVE STATUS\G

6 테스트

master서버에 테이블을 생성한 후, slave에서 테이블이 생성된것을 확인합니다.

[Master]

use test;
 
create table test ( no int(8), primary key (no) );                  
 
show tables;  

[Slave]

show tables;                                                        

같은 형식으로 여러대(1:N)의 replication 을 구성할 수 있습니다. (주의 : my.cnf 파일의 server-id 만 각각 다르게 설정)

7-1 c.f

참고로, 위와같이 설정 완료 후에도 replication 이 동작하지 않는다면 slave 서버에서 아래와 같이 재설정을 해봅니다.

[Slave]

slave stop;    
reset slave;                                                        

master_log_pos 에는 새로운 log번호로 수정해 줍니다.

change master to master_host='12.34.56.78', master_user='test', master_port=3306, master_password='test1234', master_log_file='mysql-bin.000010', master_log_pos=19610;

slave start;                                                        

7-2 Error Case

Replication까지는 문제 없지만 실제로 web server에서 DB조작시 

다음과 같은 에러가 났었다.

java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. 
InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

맨처음 설정했었던 my.conf파일에서 

binlog_format=row

출처

http://hys9958.tistory.com/entry/mysql-%EC%9D%B4%EC%A4%91%ED%99%94-master-slave%EA%B5%AC%EC%84%B1%ED%95%98%EA%B8%B0#

http://itgameworld.tistory.com/54

https://confluence.atlassian.com/confkb/confluence-will-not-start-using-mysql-5-1-5+-with-statement-binary-logging-251724630.html