Mysql Replication 으로 분산처리 하기

Posted at 2011. 1. 26. 14:15
Posted by 루지메이앙
Filed under MYSQL/펌글

MySQL Replication(복제)은 한 개나 2개 이상의 MySQL database server(slave)가 하나의 MySQL database server(master)로 부터 데이터를 복제해 갈 수 있는 기능을 제공한다.

MySQL Replication은 비동기 방식으로 처리된다. 즉 slave는 master로부터 데이터를 받아 복제하기 위해 항상 master에 연결되어 있을 필요가 없다.

MySQL Replication은 Binary logging mechanism을 사용하여 이뤄진다.

Master 서버는(MySQL 인스턴스)는 binary log에 변경된 데이터 정보를 기록하며 이 log를 slave가 읽어서 실행함으로써 복제가 된다.

Master에서 binary logging이 활성화되면 Master의 모든 데이터 구문이 bindary log에 저장되며 slave는 bindary log의 모든 내용을 복사해서 읽어온다.

따라서 slave는 log 파일내의 position을 유지할 필요가 있다. 그래야 로그파일 전체를 처음

부터 읽지 않고 효과적으로 로그 파일을 운영할 수 있다.

여기서 position은 로그파일내 위치를 의미하며 어느 부분부터 읽겠다는 것을 의미 한다.

Configuration에 따라 다음과 같은 단위로 복제가 이뤄질 수 있다.

l all database

l selected database

l selected tables within a database

Replication 구성 예 – Appendix A.

Replication Configuration

[ Master Configuration ]

1. Replication User 생성
slave는 master에 접속하여 데이터를 복제하기 위한 MySQL 계정이 필요하다. root를 사용해도 상관 없지만 slave에 Replication 설정을 하면(slave configureation 참조) 계정 정보가 암호화되지 않은 텍스트 형태로 slave 서버의 master.info(mysql\data) 파일에 기록이 되기 때문에 보안상 root나 기타 계정을 사용하는 것을 권하지 않는다. 따라서 복제를 위한 계정을 하나 생성한다.
이 계정은 단지 REPLICATION SLAVE Privilege만 있으면 되므로 다음과 같이 계정을 생성한다.(REPLICATION SLAVE Privilege만 있으면 된다는 의미는 INSERT/UPDATE 등과 같은 Privilege는 필요 없다는 의미이다. 따라서 복제 계정은 mysql query실행을할 수 없다.)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';

è repl이 계정이며 slavepass가 계정의 비밀번호 이다.

è %대신 IP주소를 넣으면 그 IP로부터 접속하는 slave에 대해서만 접속을 허용하겠다는 의미(그냥 %를 사용하자..!)

n 예) ….. ON *.* TO 'repl'@'1.1.1.2' IDENTIFIED BY 'slavepass';

2. Configuration 설정(my.ini 또는 my.cfg)

[mysqld]

log-bin=mysql-bin

server-id=1

è server-id는 1~(2^32)-1내의 숫자중 아무것이나 설정해도 된다.

è log-bin=mysql-bin는 바이너리 로그파일의 생성경로이며 log-bin만 기입시 기본 디렉토리에 생성된다.

3. MySQL 데몬 재시작

4. Master 정보 보기

mysql> FLSUSH TABLES WITH READ LOCCK;

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 98 | | |

+------------------+----------+--------------+------------------+

è File : 로그 파일을 의미한다.

è Position : 로그 파일내 읽을 위치

è Binlog_Do_DB : binary log 파일(변경된 이벤트 정보가 쌓이는 파일)

è Binlog_Ignore_DB : 복제 제외 정보

n Binlog_Do_DB와 Binlog_Ignore_DB는 Slave 시작하기 전까지는 나타나지 않는다.

[ Slave Configuration ]

1. Configuration 설정

[mysqld]

server-id=2

replicate-do-db=’database name’

# master-host=xxx.xxx.xxx.xxx --> 마스터의 IP

master-user=xxxx --> 리플리케이션용 ID

#
master-password=xxxx --> 패스워드

#
master-port=3306 --> 접속할 포트번호 (일반적인 포트 3306)

è slave의 server-id를 정의한다. 이 1~(2^32)-1내의 숫자중 아무것이나 설정해도 되나 Master와는 다르게 한다.

è replicate-do-db: 복제할 데이터베이스를 의미한다.

n 2개 이상의 데이터 베이스 복제를 원하면 replicate-do-db를 더 추가한다.

2. database dump

복제할 데이터베이스를 master로부터 dump하여 넣는다.

3. CHANGE MASTER TO

Master로 연결하기 위한 정보를 다음과 같이 설정한다.

mysql> CHANGE MASTER TO

MASTER_HOST='Master server host name or Master server IP',

MASTER_USER='replication user',

MASTER_PASSWORD='replication password',

MASTER_LOG_FILE='Log File name',

MASTER_LOG_POS=position;

è MASTER_HOST: Master 서버의 정보를 입력한다.

è MASTER_USER: replication을 위해 생성한 계정 ID

è MASTER_PASSWORD: replication을 위해 생성한 계정 비밀번호

è MASTER_LOG_FILE: SHOW MASTER STATUS에서 보이는 로그 파일 명

è MASTER_LOG_POS: SHOW MASTER STATUS에서 보이는 position값

n SHOW MASTER STATUS는 master에서 실행해야 한다.(master 설정 참고)

4. MySQL 데몬 재시작

※ Slave가 실행이 되면(MySQL 데몬 시작 또는 slave start) master에 접속하기 위한 정보를 master.info(mysql\data)에서 읽어 온다. 만일 master.info에 아무런 정보가 없으면 my.ini를 참고하여 master.info에 연결정보를 기록한다.

여기서 주의할 점은 이미 master.info에 정보가 있으면 my.ini를 참조하지 않으므로 my.ini정보를 수정해도 master에 연결시 반영되지 않는다.

그러나 CHANGE MASTER TO를 이용하면 master.info를 바로 변경한다.

따라서 master 연결정보는 my.ini에 설정하는 것 보다는 CHANGE MASTER TO를 이용하여 설정하는게 낫다.

다음과 같은 option이 CHANGE MASTER TO에서 사용된다.

master-host

master-user

master-password

master-port

master-connect-retry

master-ssl

master-ssl-ca

master-ssl-capath

master-ssl-cert

master-ssl-cipher

master-ssl-key

[양방향 동기화 처리]

Master서버에서 Slave도 구현하고자 한다면 다음과 같은 방법으로 처리

1. 현재 Slave서버에 replication 계정 생성

2. 현재 Slave의 my.ini 변경

log-bin=mysql-bin 추가

3. 현재 SLAVE 서버 데몬 재시작

4. 현재 Master의 my.ini 변경

replicate-do-db=’database name’추가

4. 현재 Master에서 CHANGE MASTER TO 실행

5. 현재 Master 서버 데몬 재시작

Replication Monitoring

//ON Master

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 11

User: repl

Host: 192.168.1.22:3556

db: NULL

Command: Binlog Dump

Time: 21960

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Slave인 192.168.1.22가 repl계정으로 thread11에 연결되어 있음을 보여준다.

//On Slave

mysql> show processlist\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 23049

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 4294967289

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

Id 1: Master 서버와 통신하기 위한 I/O Thread

Id 2: update된 내용을 처리하기 위한 SQL Thread

위 2개의 Thread에 오류가 발생하면 안된다.

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.14

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 9187563

Relay_Log_File: shin-relay-bin.000013

Relay_Log_Pos: 9187700

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: ipm3

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 9187563

Relay_Log_Space: 9187700

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Slave_IO_State: 현재 Slave의 상태를 나타낸다.(Appendix B 참고)

Slave_IO_Running: I/O Thread 상태

Slave_SQL_Running: SQL Thread 상태

Last Error: 최근에 발생한 오류, 정상인 경우 이 값은 없다.

Seconds_BeHind_Master: 이 값이 크면 클수록 Master로부터 복제할 수 없는 데이터가 많음을 나타낸다.

mysql> stop slave

mysql> start slave

MySQL 데몬(서비스)를 재 시작하면 slave는 자동으로 시작된다.(my.ini에 옵션 skip-slave-start이 있으면 자동 시작 안한다.)

[기타 알아두어야 할 내용]

1. 안정성을 위해서 두대의 DB서버의 버전을 동일하게 맞추어 주는것이 좋다.

2. 버전이 다른경우 높은 버전은 Slave 만 가능하다.

3. 서버는 Master 를 먼저 시작한 후 Slave를 시작시킨다.

4. Master의 status 에 지정된 File 이외의 로그파일은 삭제해도 무방하다.

5. Master의 status의 Position과 Slave status의 Read_Master_Log_Pos는 동일해야 한다.

6. 서버환경, 계정이 바뀐후에는 데이터 디렉토리안의 master.info를 변경하거나 제거한 후 재시작을 해야한다.

7. Master의 Replication로그나 Slave의 relay-bin로그는 vi로는 읽을수 없으며 아래와 같은 방법으로 변환후에 읽을 수 있다.

# mysqlbinlog KRDAC1FLD001-relay-bin.000002 > log.sql

8. Insert 이후 Slave 동기화 도중 데이터 조회가 이루어 져야 하는 상황에는 해당 경우만 Master DB에서 보게 하거나 process 종료 후 sleep 타임을 잠시주어 해결하면 된다

1. 구성도

MySQL 리플리케이션은 이중화의 역할 보다는 부하 분산의 역할을 한다고 볼 수 있다. 이 구성은 데이터베이스의 데이터를 갱신(입력/수정/삭제 등) 및 조회(검색 등)하는 비율에 따라 도입 유무를 판단 할 수 있다. 물론, MySQL 리플리케이션 도입에 가장 이상적인 서비스는 대형 포털 사이트와 같은 조회형 서비스에 적합하다.

2. 슬레이브 서버 별로 데이터 분산

MySQL 리플리케이션에서 슬레이브 서버마다 데이터를 동일하게 가져갈 수 있지만 이처럼 각각의 데이타를 가지고 있을 수 있다. 이와 같이 데이터를 나눠 주는 이유는 부하 분산의 차원이다.

3. 이중마스터 서버

MySQL 리플리케이션에 이중 마스터 서버 구성으로 하는 이유는 슬레이브 서버로 인한 마스터의 서버의 부하를 분산하는 차원이다. 여기서 마스터2 서버는 각 슬레이브에 대해서 마스터 서버의 역할을 수행하긴 하나 마스터1 서버에 대해서는 슬레이블 서버의 역할을 하고 있다.

4. 장애 대처