MySQL Replication 개요

MySQL Replication 개요

– 해당 글은 MySQL 4.0.x 버전에서 테스트후 작성된 글이며
공식적으로 Replication기능은 3.23 이상의 버전부터 지원되는것으로 알고 있습니다.
– 빠른 글 설명을 위해 대부분의 존칭은 생략합니다.

1. Replication 의 개요 & 필요성

나는 회사 서버에서 실제로 사용해본 데이터베이스는 MySQL과 MSSQL정도로만 압축되며,
현재 회사 서비스의 메인데이터베이스는 모두 MySQL로 이루어져있다.

좀 지난이야기이지만 데이터베이스가 엉켜(?)서 MySQL내의 특정 테이블이 말소되어버린적이 있다.
혹은 phpMyAdmin같은 웹 데이터베이스 관리 프로그램이나 MySQL-Front같은 어플리케이션 관리도구등을 사용한다면
버튼하나, 링크하나잘못 클릭해서 데이터베이스가 통째로 날라갈수가 있다.

그런데 일반 pc에서 하드포맷이나 데이터 삭제시
도스시절 unformat이나 undelete, finaldata 등등의 여러가지 방법으로 복구를 했던반면
데이터베이스는 한번 날라가면 끝이다.

데이터베이스의 보존방법은 크게 Dump를 이용한 방식과 Replication을 이용하는 방식이 있는데
두가지의 분명한 장단점이 존재한다!

ㄱ. Dump를 이용한 데이터베이스 백업의 장단점
– 문제가 생겼을때 특정 시점으로의 복원이 가능하다(덤프 받아놓은 날짜의 데이터베이스로 돌려놓을수 있다.
– 한번 덤프를 받을때 데이터가 많을수록 덤프 시간은 기하급수적으로 늘어난다.
– 한번 덤프를 받아놓은것들이 누적되면 용량이 장난아니게 불어난다.

ㄴ. Replication을 이용한 데이터베이스 백업의 장단점.
– 실시간으로 데이터를 옮겨적기때문에 원본 데이터베이스에 문제가 발생해서 서비스를 중단할 경우에도 리플리케이션서버로 대처가 가능하다.
– 특정시점으로의 복원은 불가능하다.
– Replication에 사용하는 Log를 주기적으로 비워주지 않으면 용량의압박이 상당하다.

위에서도 볼수있듯, 두가지의 가장큰 차이점은 특정시점으로의 복원여부, 그리고 실시간 백업이다.
그래서 나의 경우는 두가지 백업을 모두 애용하는 편이며, Replication은 이제 없으면 허전한 그런것이 되어버렸다.
리플리케이션의 구현시, 장점은 위에 있는게 전부가 아니다.
원본 데이터베이스(master)는 쓰기만 하고, 쿼리문 등으로 데이터베이스를 읽는 작업은 Slave에서 처리해도 가능한,
효율적인 분산작업을 위해서도 필요한것이다.

2. Replication의 설정

리플리케이션에서는 하드 IDE시절 수도없이 작업했던 Master, Slave의 개념만 잘 알고 있으면 된다.
Master는 원본데이터베이스, Slave는 백업받는 대상 데이터베이스라고 생각하면 되며, 일반적으로 알려진 리플리케이션의 설정은 다음과 같다.

(리눅스버전, MySQL 4.0 기준 설명)

ㄱ. Master의 설정
일단 처음부터 쉽게 생각하려면, Replication의 기본 구성은 다음과 같다.

a. Master에서는 데이터베이스에서 일어나는 모든 작업을 Log에 기록하게 되며, 해당 로그는 Postion 값을 가지고 있다.
b. Slave에서는 Master에 설정되어 있는 계정으로 접근하여 Master의 정보를 보게되며, Log의 Postion값을 읽어들여, 어디까지 가져왔는지를 알아내서
포지션값을 갱신하면서 데이터를 계속 긁어온다.

윗말을 정리하자면 Master는 값을 계속 기록만 하고, Slave에서는 Master로 접근후에 데이터를 계속 가져오기만 한다.
그럼 Slave에서 Master로 연결하기 위한 계정 설정은 Master에서 만들어주는것이며, 계정설정은 다음과 같은 방법으로 Master에 기록하게 된다.

GRANT file ON *.* TO 리플리케이션아이디@’아이피.아이피.아이피.아이피’ IDENTIFIED BY ‘패스워드’

권한 설정을 할때 file대신 All privileges 권한을 줘도 무방하긴하지만, 어짜피 file에 대해서만 긁어오는 작업을 수행하므로, 기타 권한에 대해 주는것은 불필요하다
또한 아이피부분 역시 %로 대처할수도 있으나, 대부분의 리플리케이션은 같은 네트워크안에 속해있는 서버끼리 작업하는게 보통이므로 아이피는 적어주는게 좋다.

권한 설정은 다 되었으므로, my.cnf에 대한 설정방법은 다음과 같다.
(윈도우 버전은 my.ini의 설정을 고치면 된다.)

——————-/etc/my.cnf————————–
[mysqld]
……

# MySQL 의 리플리케이션 기능을 사용하기 위한 binary log 설정
log-bin = /mysqltemp/log/replication.log
server-id   = 1
binlog-do-db = 데이터베이스1
binlog-do-db = 데이터베이스2
——————————————————–

log-bin = 파일경로/파일이름
입력하지 않는경우 기본적으로 Mysql의 Lib Directory에 기록이 된다.
해당 로그가 쌓이게될때 용량을 무시할 수준이 아니므로 기억하기 좋은 위치로 지정해서 사용하기 바란다.

server-id = 번호
해당 아이디의 숫자는 고유한 숫자를 입력해야되며 중요한건 slave와 id가 틀려야 한다.

binlog-do-db = 데이터베이스1
해당 MySQL내에서 여러 데이터베이스중 Replication을 할 데이터베이스를 적어준다.
데이터베이스가 다수인경우 해당 구절을 계속 추가해야되며,
binlog-do-db = 데이터베이스1,데이터베이스2 식으로 추가하는건 동작하지 않는다고 알려져있다.

ㄴ. Slave의 설정
Slave에서는 아까도 이야기 했듯 Master로 접근후에 데이터베이스를 가져오기만 하므로 별도의 계정생성이 필요없으며
my.cnf 설정파일을 수정하는것으로 작업이 완료된다.

——————-/etc/my.cnf————————–
[mysqld]

……

server-id       = 2
master-host     = 아이피.아이피.아이피.아이피
master-user     = 리플리케이션아이디
master-password = 패스워드
replicate-do-db = 가져올 데이터베이스1
replicate-do-db = 가져올 데이터베이스2
master-port     = 3306
——————————————————–

slave설정시 Master와 틀린건 가져올 마스터 데이터베이스의 아이피를 적어줘야한단것과(어디서 읽어올지는 알아야하니까)
접근에 필요한 계정(아까 GRANT file On…으로 생성한 계정의 아이디/패스워드)를 기록, 그리고 읽어들일 데이터베이스명
(Master의 binlog-do-db=데이터베이스1)을 기재해주어야 한다는것이다.

3. 동기화 팁
보통 리플리케이션은 백지상태에서 시작하기보다는 특정 데이터베이스가 돌아가고 있는 상황에서 리플리케이션 서버를 추가로 운영하는 경우로
발전되므로, 다음과 같은 두가지 처리방법이 있다.

※리플리케이션 동기화시에는 최대한 데이터베이스의 내용이 일치하는게 좋으므로, Master에서 더이상 DB의 입출력이 일어나지 않는 상태에서 작업하는것이 좋다.
즉. slave와 master의 mysql을 중단시켜놓고 작업을 하는것이 좋다.
(/etc/rc.d/init.d/mysql stop)

ㄱ. Dump를 이용하여 처리하는방법
– 데이터베이스를 덤프뜬후, Slave에서 Restore시키는 방식, 단점으론 시간이 굉장히 많이 소모되므로 추천하지 않는다.

ㄴ. MySQL 데이터 디렉토리를 통째로 옮기는 방법
– 권장하는 방식이며, 데이터베이스가 위치한곳 (/var/lib/mysql또는 /var/db같은…)을 통째로 압축해서 복사하거나, 아니면 그냥 복사하는 방식이다.
본인은 Linux(AnnyungLinux 1.2)서버에서 다른 리눅스서버(AnnyungLinux 1.2)로 옮길때 한번에 2기가 이상은 ftp에서 옮겨지지 않는 기현상(? 단일 파일 2G이상 생성 불가. 파티션형태…) 이 발생하여
다음과 같은 처리를 하였다.

a. tar cvfpz – sourcedirectory | split -b 1500m – databasebackup.tar.gz  (1.5G로 분할 압축) – Master에서 가져올 디렉토리를 압축
b. Slave에 접근후 ftp -> open 아이피.아이피.아이피.아이피, -> 압축파일 경로로 변경 -> get databasebackup.tar.gz
c. 가져온후 마스터측의 압축은 삭제, 복사작업등의 권한(chown,chmod)등은 적절하게 설정

4. 리플리케이션의 실제 동작명령

동기화가 완료되었다면 mysql에서 다음과 같은 명령어로 여러 동작들을 확인 가능하다.
(아이피는 임의처리하였다)

먼저 master 서버의 상태를 보자.

ㄱ. show processlist; – 현재 동작하고 있는 processlist를 확인가능하다.

mysql> show processlist;
+——–+———-+———————-+———+————-+——–+—————————————————————-+——————————————————————————————————+
| Id     | User     | Host                 | db      | Command     | Time   | State                                                          | Info                                                                                                 |
+——–+———-+———————-+———+————-+——–+—————————————————————-+——————————————————————————————————+
|      8 | replsvr  | ***.***.***.***:**** | NULL    | Binlog Dump | 747916 | Has sent all binlog to slave; waiting for binlog to be updated | NULL

ㄴ. show master status; – 리플리케이션 마스터의 동작 상태, 포지션값등을 알수있다.

mysql> show master status;
+—————–+———–+—————+——————+
| File            | Position  | Binlog_do_db  | Binlog_ignore_db |
+—————–+———–+—————+——————+
| replication.002 | 351161171 | test1, test2  |                  |
+—————–+———–+—————+——————+
1 row in set (0.00 sec)

여기서 중요한건 file에서 replication.002에 현재 기록중인것을 알고 있으며, slave측에서 001등을 다 가져온 상태라면 해당 로그(replication.001)는 지워도 무방하다.

slave의 상태는 다음과 같이 확인 가능하다.

mysql> show processlist;
+—-+————-+———–+——+———+——–+———————————————————————–+——————+
| Id | User        | Host      | db   | Command | Time   | State                                                                 | Info             |
+—-+————-+———–+——+———+——–+———————————————————————–+——————+
|  2 | system user |           | NULL | Connect | 748267 | Waiting for master to send event                                      | NULL             |
|  3 | system user |           | NULL | Connect | 8      | Has read all relay log; waiting for the I/O slave thread to update it | NULL             |

ㄷ. 동일하게 show slave status로 slave의 상태와 포지션값을 알수있다.(아이피, port 임의처리)

mysql> show slave status;
+—————-+————-+————-+—————+—————–+———————+——————————-+—————+———————–+——————+——————-+—————–+———————+————+————+————–+———————+—————–+
| Master_Host    | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space |
+—————-+————-+————-+—————+—————–+———————+——————————-+—————+———————–+——————+——————-+—————–+———————+————+————+————–+———————+—————–+
| ***.***.**.*** | replsvr     | ****        | 60            | replication.002 | 355000057           | wisereplication-relay-bin.003 | 355000141     | replication.002       | Yes              | Yes               | test1,test2     |                     | 0          |            | 0            | 355000057           | 355000141       |
+—————-+————-+————-+—————+—————–+———————+——————————-+—————+———————–+——————+——————-+—————–+———————+————+————+————–+———————+—————–+

이때 중요한것은 master status와 slave status를 봤을때 읽어들이는 파일명(위에서는 replication.002)와 Postion을 비교해서 차이가 나지 않으면 성공한것이다.

ㄹ. 그외 명령어

reset Master;
reset slave;
위의 명령어로 둘의 포지션값과 로그파일 생성을 초기화 시킬수 있다.

slave stop;
master stop;
위의 명령어로 리플리케이션 서버의 동작정지를 시킬수 있다.

slave start;
master start;
위의 명령어로 리플리케이션 서버의 정지를 해제하고 다시 가동시킬수 있다.

5. 그 외의 환경 실험.
예전 리플리케이션을 구현할때 4.0과 4.1에서도 서로 리플리케이션이 가능했다.
4.0과 5.0간에는 테스트 해보지 않았으나 충돌이 발생할 확률이 지대일듯 하다.
(가급적 같은 버전을 쓰자?)
또한 리눅스(master), 윈도우즈(slave)같은 환경에서도 제대로 동작했다.
(운영체제는 가리지 않는다. 단지 윈도우즈/MySQL 조합은 그리 권장하는 조합이 아니란건…)

6. 끝으로
얼마전 윈도우즈 서버 해킹으로 인해 윈도우즈의 스케쥴 관리 기능을 이용해 매일 특정시간 주기적으로 데이터베이스를 덤프했는데
둘다 리눅스 머신으로 바뀌면서 Crontab, 쉘스크립트등의 사용법을 몰라서 아직 매일 Dump작업을 못하고 있는 실정이다.

다음 강좌에서 저말고 누군가 리눅스에서 crontab을 쓰는방법과 쉘스크립트 작성법등을 강좌 해주면 캐감사하겠다.