[MySQL]Replicate 설정 및 장애대응[For example C-POS]

### MySQL Replication

 

[Master]
GRANT REPLICATION SLAVE ON *.* TO 'repmaster'@'172.27.31.96' IDENTIFIED BY 'dlslxpr123';
FLUSH TABLES WITH READ LOCK;
show master status \g;
unlock tables;

 

[Slave]
stop slave;
CHANGE MASTER TO MASTER_HOST='172.27.31.96', MASTER_USER='root', MASTER_PASSWORD='dlslxpr123', MASTER_LOG_FILE='mysql-bin.001039', MASTER_LOG_POS=17675561;
start slave;

 

 

 

[Knowledge]

mysql 리플리케이션 설정은 어렵지 않군.
장애대응이 어려운것 같다.

# 기본 mysql replication 설정.(마스터-슬레이브)
1. mysql config 설정
master , slave 각각 config 파일에 replication 설정을 해준다.
우분투의 경우 /etc/mysql/conf.d 에 replication_slave.cnf (파일명은 임의로 )
/etc/mysql/my.cnf 의 마지막줄에 이렇게 되어 있어서 불러오게 된다

!includedir /etc/mysql/conf.d/

my.cnf 의 [mysqld] 탭에 직접추가해줘도 된다.

내용은 이런식이다. (슬레이브쪽)

[mysqld]<br />server-id = 2<br />master-host = master<br />master-user = replication<br />master-password = slave<br />replicate-do-db = master_db<br />replicate-do-db = other_db<br />

마스터쪽은 다음과 같다.

[mysqld]<br />server-id = 1<br />log_bin = /var/log/mysql/mysql-bin.log<br />binlog_do_db = master_db<br />binlog_do_db = other_db<br />binlog_ignore_db = mysql<br />binlog_ignore_db = information_schema<br />

설정만 보면 이해할 수 있을 것이다.
binlog_do_db 등을 각자의 서버설정에 맞게 바꾸어 주면 된다.

2. replication 접근 권한설정
replication 은 slave 쪽에서 master 의 자료(bin_log)를 참조해서 데이터를 가져가는 것이다.
그래서 슬레이브쪽에서 마스터에 접근할 수 있어야 한다.

그 명령은 다음과 같다.(마스터쪽에서 mysql 명령을 내려준다)

mysql> grant replication slave on *.* to 'replication'@192.168.0.1 identified by 'slave';

진한부분이 conf 에서 설정한 master-user / master-host / master-password 에 해당한다.
IP 주소대신 /etc/hosts 에 설정한 호스트명을 적어줘도 된다. ( master-host = master 같은..)

3. 사용 및 장애대응
작동 중지/실행

start slave ;<br />stop slave ;

슬레이브 상태 보기

mysql> show slave status\G<br />*************************** 1. row ***************************<br /> Slave_IO_State: Waiting for master to send event<br /> Master_Host: master<br /> Master_User: replication<br /> Master_Port: 3306<br /> Connect_Retry: 60<br /> Master_Log_File: mysql-bin.000018<br /> Read_Master_Log_Pos:958292<br /> Relay_Log_File: slave-relay-bin.000271<br /> Relay_Log_Pos: 251<br /> Relay_Master_Log_File: mysql-bin.000018<br /> Slave_IO_Running: Yes<br /> Slave_SQL_Running: Yes<br /> Replicate_Do_DB: master_db<br /> Replicate_Ignore_DB:<br /> Replicate_Do_Table:<br /> Replicate_Ignore_Table:<br /> Replicate_Wild_Do_Table:<br /> Replicate_Wild_Ignore_Table:<br /> Last_Errno: 0<br /> Last_Error:<br /> Skip_Counter: 0<br /> Exec_Master_Log_Pos: 958292<br /> Relay_Log_Space: 556<br /> Until_Condition: None<br /> Until_Log_File:<br /> Until_Log_Pos: 0<br /> Master_SSL_Allowed: No<br /> Master_SSL_CA_File:<br /> Master_SSL_CA_Path:<br /> Master_SSL_Cert:<br /> Master_SSL_Cipher:<br /> Master_SSL_Key:<br /> Seconds_Behind_Master: 0<br />Master_SSL_Verify_Server_Cert: No<br /> Last_IO_Errno: 0<br /> Last_IO_Error:<br /> Last_SQL_Errno: 0<br /> Last_SQL_Error:<br />1 row in set (0.00 sec)

마스터의 상태를 보려면

mysql> show master status\G<br />*************************** 1. row ***************************<br /> File:mysql-bin.000018<br /> Position: 958292<br /> Binlog_Do_DB: master_db<br />Binlog_Ignore_DB: mysql<br />1 row in set (0.00 sec)

진하게 표시한 position 이 일치하는지 확인하면 된다.

slave 의 position 값이 일치하지 않는경우.
다음처럼 해준다.

mysql > stop slave;<br />mysql > reset slave;<br />mysql > start slave;

cron 등으로 주기적으로 확인해주도록 한다.

 

 

 

[Knowledge 2]

 

바로 지금 잘 서비스 되고 있는 MySQL DBMS Replication 구조를 단번에 깨뜨릴 수 있는 노하우(?)입니다.
DISK를 독립적으로 움직이는 구조이기 때문에, 데이터 충돌로 쉽게 깨질 수 있지만 복구는 참으로 난해한 MySQL Replication..
제가 알고 있는 방법은 세 가지가 있습니다. 혹시나 이것들 외에 다른 방법을 아시는 분은 꼭 sdchan1@kthcorp.com 으로 메일을 보내주시면 감사드리겠습니다.

1. replicate_ignore_db를 이용한 방법
   데이터 분산 처리를 위해 replicate_ignore_db(또는 table)과 같은 옵션을 Slave 서버에 필터링 설정을 적용하는 경우가 있습니다. (replicate_do_db도 마찬가지 입니다.)
   특정 계정이 Master에서 replicate_ignore_db에 해당하는 Database 접속 권한 보유 시 쉽게 Replication을 깨뜨릴 수 있습니다.
2. InnoDB Lock Wait Time-out을 이용한 방법
   Slave 서버를 Read-only 전용으로 사용하지 않는 경우(Stand-by 또는 Multi-master), 한 마디로 Configure에 Read-only 속성을 명시하지 않은 경우에도 쉽게 깨뜨릴 수 있습니다. 물론 1번보다는 조금 오래 걸리지만요..
3. BUG 취약점을 이용한 방법
   MySQL 5.1.57 또는 5.5.12 이전 버전 사용한다면 너무나도 쉽게 Replication을 깰 수 있습니다. 여기에 관한 내용은 얼마 전 블로그에MySQL 이중화 상태에서 Replication Fail Bug 해결법을 올린 바가 있습니다. 물론 최근 버전에서는 해결됐습니다.

 

Let’s Break!!

Replication을 깨기 전에 Slave 서버 쪽에 다음과 같은 사전 설정 작업이 필요합니다.

####SLAVE####
replicate_ignore_db = dba_ignore
#read-only

1) replicate_ignore_db를 이용한 방법

바로 아래와 같은 경우라면 몇 줄의 SQL로 아주 쉽게 Replication 오류를 발생시킬 수 있습니다.

시나리오는 다음과 같습니다. 물론 Master에서 모든 작업을 수행하고요. (하단 그림 참조)

1) Master의 binlog_format을 MIXED 또는 STATEMENT로 설정
2) 복제 되는 Database에 접속
3) 복제 안되는 Database에 있는 테이블에 직접 입력

Slave 쪽 가서서 show slave status 명령어로 확인을 해보면 아래와 같이 Replication이 깨진 것을 확인하실 수 있습니다.
참으로 허무하다 싶을 정도로 너무도 쉽게 깨집니다. -_-;;

 

2) InnoDB Lock Wait Time-out을 이용한 방법

만약 Slave에 Read-only 옵션이 걸려있지 않다면 또 쉽게 Replication을 깨뜨릴 수 있습니다.
1) Slave 에서 begin 명령어로 Transaction 시작
2) Slave에서 Select for update 명령문으로 Lock 수행
3) Master에서 Slave에 Lock이 걸려있는 데이터 변경 작업 수행

위에서 3번 과정 이후로는 Master/Slave 간 데이터가 다른 것을 확인할 수 있고, Seconds_Behind_Mater값이 계속해서 증가됩니다.
그리고 show processlist 로 현재 실행되는 Thread현황을 보시면 Updating 중으로 나오나 왜 Update 완료가 안되는지는 알려주지는 않습니다.
(Lock을 건 Process 번호는 77번이나 현재 아무것도 수행하지 않는 것으로 나옵니다.)

즉 Master에서는 여전히 트랜잭션이 수행되나, Slave에서는 여전히 2번 과정에서 발생한 Lock으로 인하여 Master의 트랜잭션이 수행되지 못함을 의미합니다.
총 Slave_Transaction_Retries회 시도 각각 innodb_lock_wait_timeout만큼 대기하고, 모두 실패하게 되면 아래와 같이 Slave SQL Thread가 Stop되면서 Replication이 깨지게 됩니다.

물론 Lock을 건 Session이 종료되고, start slave를 수행하면 Replication은 원래대로 복구됩니다.
그러나! 운영 중에 자꾸 Replication이 깨지게 된다면, 데이터 일관성 및 DB 관리 측면에서 참으로 난감한 상황이겠죠^^

 

3) MySQL BUG 취약점을 이용한 방법

예전에 MySQL 이중화 상태에서 Replication Fail Bug 해결법 라는 블로그를 통해서 소개드린 방법입니다.
간단하게 요약하자면 Binary Log Format이 Row 타입으로 데이터 동기화가 이루어지는 상태에서 PK가 없는 테이블의 NULL값을 수정하게 되면 Replication이 깨지는 버그입니다.
MySQL버전이 5.1.57 또는 5.5.12 이전 버전이라면 유용하게(?) 쉽게 Replication을 깨뜨릴 수 있는 방법이죠..^^;;
자세한 설명은 위 블로그를 참조하심 되겠습니다.

Conclusion

이 블로그를 포스팅하는 이유는 이 방법으로 Replication을 깨주자는 것이 절대 아닙니다. (설마 진짜로 실 서버에 위 내용을 시뮬레이션 해보시는 분은 안계시겠죠?)
애초에 발생 가능한 Replication Fail Error을 미연에 방지하고, 어이가 없는 실수로 Replication을 깨뜨리지 말고 MySQL Replication을 “잘 써보자”는 취지하에 적어보았습니다.

사전 대처 방법을 간단하게 적어봅니다.

1) replicate_ignore_db 사용 시 사전 대처 방법

- Binary Log Format을 Row로 설정 (세션 별로 변경 가능한 옵션이기 때문에 여전히 잠재적인 에러 발생 가능성은 존재)
- Replicate_ignore_db에 접근 가능한 MySQL계정은 오직 하나로만 제한하고 해당 계정 또한 타 DB에 접근 불가하도록 권한 설정 (insert into db.tabe로 데이터 입력 방지)

2) InnoDB Lock Wait Time-out을 이용한 방법 (택1)

- 트랜잭션에 포함되는 Select도 Master에서 수행
- 특정 데이터는 한 서버에서만 DML이 적용되도록 Application에서 구현()
- 모든 Slave에 READ-ONLY 옵션 추가

3) BUG 취약점을 이용한 방법 (택1)

- 테이블에 NULL속성을 제거
- 모든 테이블에 Primary Key 추가
- 버전 업그레이드 (권고)

Master/Slave의 역할을 명확이 구분하여 사용하고, 접속 계정의 권한을 엄격하게 적용한다면 적어도 1번과 2번같은 어이없는 Replication Fail Error는 발생하지 않겠죠?
MySQL Replication을 활용한 백업/부하분산 등 정말 좋은 기술입니다.

mysql replication 에서 동기화 문제가 발생했을 경우~

잘 쓰고 있던 서비스의 내용이 request 마다 다른 내용을 보여주었습니다. 

DB는 mysql replication을 이용해서 사용하고 있는데 말이죠. 테스트를 해보니 새롭게 입력하는 데이터가 slave DB에는 반영이 되지 않고 있는 것 이었습니다.

아~ 처음 만나는 문제에 당황했지만 열심히 관련자료를 찾아봤습니다. 생각보다 쉽게 답을 찾을 수 있었습니다. 문제는 slave에서 갱신된 데이터를 쓰려고 하니 에러가 발생한 것이었습니다. 아래는 발생한 에러입니다. 

(참고, 에러파일의 위치를 명시하지 않았다면 mysqld 실행 옵션중 -dataDir에 생성됩니다.)

101008 19:48:58 [ERROR] Slave: Error ‘Duplicate entry ’222′ for key 1′ on query. Default database: ‘M_SERVICE’. Query: ‘INSERT INTO      T_LOTTO_MY     (     MY_SEQ,USERNO,NUM1,NUM2,NUM3,NUM4,NUM5,NUM6,GAME_NUM,RANK,PRIZE,UPDATE_DATE     )   VALUES     (     null,’620013149754′,4,18,25,44,16,8,409,’0′,0,NOW()     )’, Error_code: 1062

1062라는 에러가 발생한 것입니다. slave에서는 에러가 발생하게 되면 더이상 master로부터의 갱신된 쿼리를 실행하지 않습니다. 그래서 이후에 생겼던 갱신 정보는 반영되지 않았던 것입니다. 

그래도 다행은 에러 이후 발생된 갱신 정보는 차곡차곡 쌓여 있다는 것입니다.

자 이제 그럼 문제를 풀었던 방법에 대해서 설명 하겠습니다. 방법은 간단합니다. 에러가 발생했던 쿼리를 넘어가자 라고 DB에 알려주면 됩니다. 

우선 슬레이브 상태를 멈춥니다. 

stop slave;

에러 쿼리를 하나 건너뛰자~ 라고 알려줍니다.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

다시 슬레이브를 시작합니다.

START SLAVE;

자 이제 에러 쿼리를 건너 뛰었으니 그동안 쌓여있던 쿼리가 slave에 반영될 것입니다. 

항상 복제되어 최신의 데이터를 보여주어야 하는 DB가 에러 하나 생겼다고 복제가 안된다면 큰 문제가 될 수 있습니다. 이런 서비스를 위한 DB 설정이 있습니다.

my.cnf에 다음과 같이 설정을 변경해주시면 됩니다.

앞으로 1062번 에러 코드 발생하면 그냥 skip 해주고 다음 쿼리를 처리해주렴~

slave-skip-errors = 1062  (여러개일 경우는 ,로 분리해서 쭈욱 나열해주면 됩니다.)