mysql Replication (mirror 백업)

안녕하세요. 디망쉬입니다.

mysql Replication 기능에 대해 잘 아실 겁니다. 좋은 dbms 라면 거의 지원하는 기능이고 mysql 에서도 지원하는 기능인데, mysql 에서는 Replication 기능 자체가 매우 단순하고 좀 허약합니다. 단지 실시간 db 미러링 정도지요. (이게 주제(?)기도 하지만)

mysql Replication 기능을 통해 A 서버에서는 insert 만 시키고, B 서버에서는 select 시키는 작업을 할 수 있습니다. 물론 두 DB 서버의 값은 거의 실시간으로 동일한 데이터를 갖고 있지요. 그럼 이러한 mysql Replication 기능을 쓰려면 어찌 하면 될까요? 예제를 통해 알아보겠습니다.

A 서버
- insert, update, delete, select 작업하는 main db (master)
- ip : 111.111.111.111

B 서버
- select 만 하는 sub db (slave)
- ip : 111.111.111.222

자 이제 A 서버의 /etc 에 my.cnf 를 작성해주세요. my.cnf 샘플은 mysql.com 등에 있을 겁니다. 하나 받으셔서 추가하시고 my.cnf 를 열어보시면 [mysqld] 라는 섹션이 보입니다. 여기에

log-bin
server-id=1

를 추가해줍니다. 그리고 B 서버의 /etc/my.cnf 에서 [mysqld] 섹션에

master-host=111.111.111.111 (master db ip)
master-user=미러링 유저 id
master-password=미러링 유저 pw
master-port= (이건 걍 놔두시거나 만일 port 번호가 다르면 그 값을 넣어주세요)
server-id=2 (2의 32승 값의 범위내에서 적당한 숫자를 넣어주세요)

를 추가해줍니다. 이제 A 서버와 B 서버의 mysql 를 restart 해줍니다. 그런 뒤 B 서버에 mysql 로그인을 해서 show slave status; 라고 하면 뭔가 출력되는데 Slave_Running 이 YES 가 나와야합니다. 그리고 A 서버에서 mysql 로그인 한 뒤 show processlist 라고 했을 때 State: Slave connection: wating for binlog update 가 있으면 서로 붙은 겁니다.

여기까지는 사용하기 위함이고, 관리하실 때 주의점 등을 알아보겠습니다.

mysql 의 Replication 이 좀 안좋습니다. -_-; 특히 가장 당황스러운게 쿼리 에러문으로 Replication 이 끊기는 겁니다(당연?). 즉 master 에서는 query 에 error 가 있어도 mirroring 은 살아있지만, slave 쪽에 error 가 발생되면 mirroring 이 끊깁니다. 정확히는 slave running 이 중단됩니다. 예를 들어 에러가 있는 query 가 있을 경우 master 에서는 에러나며 입력이 안되고 끝입니다. 그런데 slave 에서는 Replication 가 끊기는 겁니다.

이때는 master 서버에 남겨진 bin-log (binary log file) 에서 에러 나는 위치(position)를 강제로 건너띄우면 됩니다. 예를 들어 bin-log.003 파일에서 10000 위치의 query 에서 에러가 난다면 아마도 slave 는 bin-log.003 파일을 사용하고 있고(Replication 이 끊겼으니까), master 는 계속 bin-log 를 작성하기 때문에 big-log.004 나 그 이후일 수도 있고, 혹은 bin-log.003 일지라도 position 위치는 더 뒤쪽입니다. 이럴 때는 다음 명령어로 강제로 slave 의 Replication position 을 이동시켜주면 됩니다.

CHANGE MASTER TO MASTER_HOST='111.111.111.111(즉 A서버 ip)', MASTER_USER='미러링 유저 id', MASTER_PASSWORD='미러링 유저 pw', MASTER_LOG_FILE='참조할 로그파일(A서버에 있음)', MASTER_LOG_POS=참조할 포지션;

이라고 mysql 상에서 입력하면 됩니다. 그런 뒤 slave start; 라고 명령을 내려주면 다시 작동합니다.

그럼 참조할 포지션 값은 어찌 알아낼까. A 서버의 mysql 상에서 show master status; 라고 하면 현재 master server(A 서버)가 진행하고 있는 bin-log 파일 이름과 position 값이 나옵니다.



mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| wt144-bin.009 | 15216210 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)


이런 식으로요. 이 값들을 참고하시면 됩니다.

두번째. master 와 slave 의 character (언어)는 같아야합니다. 그렇지 않으면 slave 쪽에서 키 중복 에러가 납니다. (물론 slave 쪽에 에러나면 Replication 은 끊깁니다).

이정도는 필히 고려해야할 내용이고 보다 자세한 문제점들은 http://www.mysql.com/doc/en/Replication_Features.html 이곳을 참조하시길.

p.s : 위의 예제는 DB 전체를 백업하는 것입니다. 특정 DB 만 미러링하거나 특정 DB 는 미러링 하지 않을 수 있으며, 이는 http://www.easypos.co.kr/sipak/MySQLReplication 이곳을 참조하세요. (안광해님 의견 참조)