[mysql] 리플리케이션

[mysql 리플리케이션 이란?]

MySQL Replication 설정을 해놓으면, 마스터 서버에 갱신되는 모든 자료를 슬레이브 서버에 저장하게 된다.

실시간 백업으로 사용할수 있지만 마스터 서버에서 자료를 삭제했다면 슬레이브 서버에서도 자료가 삭제되므로, 

하드디스크 문제같은 물리적인 문제에 대한 대처방안으로 생각하는것이 좋다.

또한 마스터서버에선 쓰기, 슬레이브 서버에선 읽기만 할경우 DB의 부하 분산에 도움이 된다.

[mysql 리플리케이션 설정방법]

[Master 서버에서 설정]

# vi /etc/my.cnf        -> 해당 파일을 열어 server-id =1 부분과 log-bin=mysql-bin 확인 및 설정완료후 mysql 재시작

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin       ->  (바이너리 로그 기준으로 슬레이브 서버에 리플리케이션하기때문에 반드시 생성이 되도록 설정이 되어있어야 합니다.)

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1           -> 보통 1이 기본값이며, 슬레이브서버와 다르게 설정하면 된다.

binlog_do_db = db명     ->  리플리케이션 할 DB명을 적어준다. (생략할경우 전체 DB를 리플리케이션함)

# mysql -u root -p         -> mysql 에 접속하여 슬레이브에서 접속할 사용자를 추가하거나 root 로 설정

Mysql > show master status;        -> 해당명령어 입력시 아래 내용이 나오며 mysql-bin 과 Position 번호 확인

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

| File                          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000040 |       106  |                          |                                |

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

1 row in set (0.00 sec)

# mysqldump -u root -p -A > mysql.dump        -> 리플리케이션 설정을 위해 마스터 서버의 데이터를 백업받아 슬레이브 서버에서 복구해야합니다.

# rsync -avzPog /usr/local/src/mysql.dump root@115.68.200.69:/usr/local/src/        -> 백업이후 슬레이브서버로 해당 백업파일을 보냅니다.

[Slave 서버에서 설정]

# vi /etc/my.cnf        -> 마스터 서버와 마찬가지로 해당파일을 수정해줍니다.

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin       ->  (바이너리 로그 기준으로 슬레이브 서버에 리플리케이션하기때문에 반드시 생성이 되도록 설정이 되어있어야 합니다.)

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 2        -> 마스터서버와 다르게 설정하여 줍니다.

binlog_do_db = db명        -> 특정 DB만 리플리케이션 할경우 추가, 생략시 전체 DB 리플리케이션

relay-log  = slave-relay-bin   

log-slave-updates

slave-skip-errors = all    -> 모든 에러를 무시하고 싱크를 맞춤 (해당옵션 안줄경우 에러발생시 리플리케이션 정상작동 안함)

read_only    -> 이 옵션을 줘야 슬레이브 서버에서 읽기만 가능. 슬레이브 서버에 데이터가 들어가면 안됨

# mysql -u root -p        -> mysql 에 접속하여 아래처럼 설정을 합니다.

Mysql > CHANGE MASTER TO MASTER_HOST='마스터 서버 IP', MASTER_USER='접속할 계정', MASTER_PASSWORD='패스워드', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000040', MASTER_LOG_POS=106;

Mysql > start slave;

Mysql > show master status\G        -> 해당명령어로 상태확인

slave 설정 하다가 실수하여 잘 안될경우 reset slave 명령어로 설정 초기화 하고 다시 change master .. 명령어로 다시 설정하면 잘됨.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

해당 설정값은 my.ini (즉 윈도우에서 설정한 값이며, server-id가 mysqld 부분에 들어가야 적용이 된다.)

[Replication]

log-bin = mysql-bin

max_binlog_size = 100M

expire-logs_days = 7

slave-skip-errors=1062[/code]

binlog_do_db = db_camperstory

binlog_do_db = db_camperstory_test

binlog_do_db = db_camperstory_shop

binlog_do_db = db_camperstory_shop_test

[mysql]   

default-character-set = euckr  

  

[mysqld]   

max_connections = 500

character-set-client-handshake=FALSE  

init_connect="SET collation_connection = euckr_korean_ci"  

init_connect="SET NAMES euckr"  

default-character-set = euckr  

character-set-server = euckr  

server-id = 59

 

collation-server = euckr_korean_ci  

  

[client]    

default-character-set = euckr  

  

[mysqldump]    

default-character-set = euckr

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

마스터 서버에 리플리케이션설정이 되어있고, 마스터서버 중단없이 slave 설정 하는 방법

마스터 서버 my.cnf 파일 수정

log-bin을 사용하지 않는다면 사용하도록 활성화

server-id = 1

마스터 서버 mysql 재시작

3. 슬레이브 서버에서 마스터 서버로 접근할 수 있는 계정 생성

# mysql -uroot -p

mysql> grant replication slave on *.* to 'root'@'서버ip' identified by '비밀번호';

mysql> flush privileghes;

 

--------------------------------------------------------------------------------

4. 모든 DB 덤프

mysqldump -uroot -p --master-data=2 --all-databases > all.sql DB를 sql파일로 만듭니다.(--master-data=2옵션은 sql파일에 pos번호를 남겨주는 옵션)

슬레이브서버로 덤프파일 넘기기

윈도우일 경우 CMD로 해당 파일이 있는 경로로 들어가서

find all.sql "MASTER_LOG_POS"

리눅스일 경우 head -n 30 0617all.sql

6. 슬레이브 서버 my.cnf 파일 수정

server-id = 2

7. 슬레이브 서버 mysql 재시작

8. 덤프한 파일 넣기

# mysql -uroot -p < all_dump.sql

mysql-bin.000026 , masterlog = 542466931

9. mysql 재시작

10. 슬레이브 서버에서 마스터 서버로 연결

mysql> CHANGE MASTER TO MASTER_HOST='서버ip', MASTER_USER='root', MASTER_PASSWORD='비밀번호', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=542466931;

mysql> salve start

 상태 확인

1) 마스터  서버 : show master status \G

2) 슬레이브  서버 : show slave status \G