Mysql - Replication (부하분산)

1.1 Replication 이란? ¶

Replication은 3.23.15부터 지원되기 시작한 기능으로 ‘복제’라는 사전적 의미에 맞게 마스터의 MySQL 서버의 데이터를 여러 대의 슬레이브 MySQL 서버의 데이터와 동기화 시켜주는 기능이다. 주로, MySQL의 데이터를 실시간으로 백업하거나, 데이터 서버의 부하분산을 하고자 할 때 많이 사용된다.

Dual-Master Replication을 구축하기 위해, 먼저 Master-Slave로 구성된 Replication 상태를 만들어야 한다.

[edit]

1.2 How to Set Up Replication ¶

[edit]

1.2.1 MASTER 와 SLAVE 설치 ¶

MySQL을 master 와 slave 서버에 설치한다. 안정성을 위해 두 서버의 버전을 맞춰주는 것이 좋다. Replication 기능은 3.23.15부터 지원되기 시작하였으나 3.23.32부터 안정화되었다고 알려져 있으므로, 그 이상 혹은 최신 버전의 MySQL 을 설치하길 권장한다.

[edit]

1.2.2 MASTER 계정생성 ¶

slave 서버에서 master 서버에 접속할 수 있도록, master 서버에 계정을 만든다. 사용자를 추가해 주어야 한다는 말이다. 이 계정에 REPLICATION SLAVE 권한을 주어야 한다. replication에만 사용할 계정이라면 추가적인 권한은 주지 않아도 된다. slave 서버에서master 서버에 접속할 계정과 패스워드에 권한을 부여하는 명령은 다음과 같다. master mysql > GRANT REPLICATION SLAVE ON *.* -> TO 'user_name'@'user_host' IDENTIFIED BY 'user_password';

여기서 user_name은 중복되지 않는 이름이면 되며, user_host 는 slave로 만들 서버의 주소 혹은 도메인 네임을 적어준다. 이 주소의 slave 유저만 master 서버로 접속할 수 있다. 4.0.2 이전 버전의 MySQL에서는, REPLICATION SLAVE 권한이 없으므로, 다음과 같이 FILE 권한으로 대신한다. master mysql > GRANT FILE ON *.* -> TO 'user_name'@'user_host' IDENTIFIED BY 'user_password';

[edit]

1.2.3 MASTER 데이터 SLAVE 에 복사 ¶

master 서버의 기본 데이터를 백업 받아, slave 서버의 데이터베이스에 복사한 후, 데이터 디렉토리에서 압축을 푼다.

HOT 백업

master mysql > FLUSH TABLES WITH READ LOCK; master shell > tar -cvf /tmp/mysql-snapshot.tar . slave shell > tar -xvf /tmp/mysql-snapshot.tar master mysql > UNLOCK TABLES;

mysqldump 이용 백업

master Shell > mysqldump -u root -p ‘password’ -B db_name > dump_file.sql

[edit]

1.2.4 MASTER 환경설정 ¶

Master 와 Slave 의 데이터 베이스 환경을 설정한다. 우선 master 서버를 설정하도록 한다. master shell> vi /etc/my.cnf

master 서버는 디폴트로 구성이 되어 있을 것이므로, mysqld 섹션에 log-bin이 있는 지 확인한다. [mysqld] log-bin server-id = 1

[edit]

1.2.5 SLAVE 환경설정 ¶

다음은 slave 서버의 환경설정이다. slave shell> vi /etc/my.cnf

mysqld 섹션으로 가서 server-id를 master 서버의 server-id와 다르게 설정한다. 본 문서에서는 2로 설정하도록 하겠다. slave 서버를 여러 대로 구축하고자 할 때에 각 slave 서버의 server-id는 각각 달라야 한다는 것에 주의하자. 2^32-1까지 가능하다. [mysqld] server-id = 2 master-host = xxx.xxx.xxx.xxx(user_host) master-port = 3306 master-user = user_name master-password = user_password

master 서버의 데이터를 백업 받았다면, slave 서버를 시작하기 전에 slave 서버의 데이터 디렉토리에 master 서버의 데이터를 복사해 둔다. mysqldump를 사용했다면, 다음으로 가서 먼저, slave 서버를 스타트한다.

[edit]

1.2.6 SLAVE 서버 스타트 ¶

slave 서버를 스타트한다. slave shell > /etc/init.d/mysqld start

[edit]

1.2.7 SLAVE 덤프라일 LOAD ¶

mysqldump를 사용해 백업 파일을 만들었다면, slave 서버에 덤프 파일을 로드시킨다. slave shell > mysql -u root -p < dump_file.sql

[edit]

1.2.8 MASTER 계정 설정 ¶

slave 서버에서 master-host, master-user, master-password 등의 설정을 다음과 같이 바꿀 수도 있다. 물론 /etc/my.cnf에서 설정하지 않았을 경우에도 쓸 수 있다. slave mysql > CHANGE MASTER TO -> MASTER_HOST='master_host_name', -> MASTER_USER='replication_user_name', -> MASTER_PASSWORD='replication_password', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position;

각 옵션의 최대 길이는 다음과 같다. MASTER_HOST 60 MASTER_USER 16 MASTER_PASSWORD 32 MASTER_LOG_FILE 255

[edit]

1.2.9 SLAVE 쓰레드 스타트 ¶

slave 쓰레드를 스타트한다. slave mysql > START SLAVE;

[edit]

1.2.10 SUCCESS CERTIFICATION ¶

mysql/data/slave.err을 확인하여 다음과 같은 메시지가 있으면 성공적으로 설정된 것이다. Slave I/O thread: connected to master 'user_name@user_host:3306', replication started in log 'FIRST' at position 4

[edit]

1.3 How to Set Up Dual-Master Replication ¶

우선 이후에서는 지금까지 master 라고 칭했던 서버를 mysql1 서버라고 하고, slave라 칭했던 서버를 mysql2 서버라 하겠다. 듀얼 마스터 리플리케이션을 구축할 두 대의 서버에는 동일 버전의 최신 MySQL이 설치되어 있으며, Master-Slave 리플리케이션이 구축된 상태에 있다고 간주한다.

이미 앞에서 리플리케이션 구축에 대해 자세히 설명하였으므로, 과정에 대해서만 기술하기로 하겠다.

[edit]

1.3.1 SLAVE STOP ¶

mysql2 서버로 이동한 후, mysql2 서버의 mysql 구동을 멈춘다. mysql2 shell > /etc/init.d/mysqld stop

[edit]

1.3.2 SLAVE LOG DELETE ¶

mysql2 서버의 -bin log를 삭제한다.

[edit]

1.3.3 SLAVE RESTER ¶

mysql2 서버의 mysql을 구동시킨다. mysql2 shell > /etc/init.d/mysqld start

[edit]

1.3.4 GRANT REPLICATION SLAVE ¶

d. mysql2 서버에서 GRANT REPLICATION SLAVE명령을 실행한다. Dual-Master란 것이 서로가 서로의 master이자 slave가 되는 것이므로, 이전의 설치에서 slave였던 mysql2가 mysql1 서버의 유저를 slave 유저로 갖게 된다. mysql2 mysql > GRANT REPLICATION SLAVE ON *.* -> TO 'users_name'@'users_host' IDENTIFIED BY 'users_password';

[edit]

1.3.5 MASTER SETUP ¶

이제 mysql1 서버로 이동하여, 설정을 계속한다. 우선, mysql1 서버의 mysql 구동을 멈춘다. mysql1 shell > /etc/init.d/mysqld stop

[edit]

1.3.6 MASTER CONFIGURATION ¶

mysql1 서버의 /etc/my.cnf 파일을 수정한다. mysqld 섹션으로 가서 mysql2 서버를 마스터로 간주하도록 정보를 추가한다. [mysqld] server-id = 1 <= 그대로 두고, 아래 내용을 추가한다. master-host = users_host master-port = 3306 master-user = users_name master-password = users_password

[edit]

1.3.7 MASTER START ¶

mysql1 서버의 mysql을 구동시킨다. mysql1 shell > /etc/init.d/mysqld start

[edit]

1.3.8 SUCCESS CERTIFICATION ¶

mysql/data/mysql1.err을 확인하여 다음과 같은 메시지가 있으면 성공적으로 설정된 것이다. Slave I/O thread: connected to master 'ccotti@222.112.137.172:3306', replication started in log 'FIRST' at position 4

지금까지 별다른 문제없이 설치를 진행하였다면, 각 서버의 mysql 모니터에서 데이터를 입력하고, 두 서버가 서로 연동이 되는 것을 확인할 수 있을 것이다.

[edit]

1.4 장애복구 ¶

위의 설정에서 두 대의 서버 중 한 대가 장애를 일으키는 경우 한 서버를 리부팅한다고 가정할 때, 별도의 설정이 없다면 기존의 MySQL 리플리케이션 구성에서는 두 서버 간의 동기화가 원활히 일어나지 않았다. 그런 경우 다음을 순서대로 진행하며, 장애를 복구할 수 있다. 우선 mysql1 서버를 재시작해야 한다고 가정하자. 1. mysql1의 mysql/data/ 의 mysql1-bin.*를 지운다.

2. mysql1의 mysqld를 시작한다. mysql1 shell > /etc/init.d/mysqld start

3. mysql2의 mysql 모니터에서 다음 명령어를 실행한다. mysql2 mysql > slave stop; mysql2 mysql > slave reset; mysql2 mysql > slave start;

[edit]

1.5 참고 ¶

● master와 slave 데이터 일치 방법

- master mysql을 정지시키고 대상 파일들을 백업(복사) - master mysql을 구동

-> 이 후 변경사항들이 bin-log에 기록됨

- slave에 백업한 DB 파일들을 복사 후 구동

-> master의 bin-log를 참고하여 데이터 일치됨 ※ 이 때, 복사한 파일의 소유자(mysql인지?) 확인 철저 ※ my.cnf 설정에서 특정 DB를 선택한 경우 master와 slave 모두 동일하게 설정해야 함

(한 쪽은 설정하지 않고 한 쪽은 설정한 경우 오동작)

※ my.cnf 주의사항 : mysql_safe 실행 시 DB_DIR 옵션에 따라 불러오는 위치 달라짐

● slave에서 'LOAD TABLE FROM MASTER' 나 'LOAD DATA FROM MASTER' 명령을

사용하기 위해서는 replication 계정에 다음은 권한 추가 필요

- SUPER, RELOAD, SELECT 권한을 replication 계정에 부여 ● 다음 명령을 통해 mysql의 내부cache를 clear시키고 쓰기 방지 가능

※ mysql 기본 테이블인 MyISAM 테이블을 사용할 경우 - mysql> FLUSH TABLES WITH READ LOCK;

● 쓰기 방지 해제 명령

- mysql> UNLOCK TABLES;

● slave의 mysql을 replication 미적용하고 구동 방법

- /usr/local/bin/mysqld_safe --skip-slave-start ● slave 동작 구동 방법 - mysql> start slave;

※ slave 설정 미인식 등의 문제 발생 시

mysql> change master to 명령을 사용하여 설정

● replication 정상동작 확인

- mysql> show processlist;

또는 mysql> show processlist\G ; 상세한 내용 확인

- mysql> show slave status;

또는 mysql> show slave status\G ; 상세한 내용 확인 또는 mysql> show master status;

- error 로그 확인

 

 

참고!!! 젠장 설정에 리플리케이션으로 묶을 DB 세팅방법이 안나와서 밤새 고생했음.'

 

replicate-do-db= 리플리케이션 할 DB명

 

 

 

show master status;

show slave status;

 

stop
 /data/mysql/bin/mysqladmin -u root -p shutdown

start
 /bin/sh /data/mysql/bin/mysqld_safe &

 

 

CHANGE MASTER TO MASTER_HOST='xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;

 

로그를 잘라주기위해 mysqladmin flush-logs 실행 필요

출처 : http://wiki.kldp.org/wiki.php/MySQL%B8%AE%C7%C3%B8%AE%C4%C9%C0%CC%BC%C7