MySQL Replication으로 DB 이중화하기 (feat. mysql replication 동작방식)

◈ 목차

• MySQL Replication 동작방식
• Master-Slave 기본 setup
• Master-Slave 실습

 

---

 

지금까지 MySQL 서버 한 대로 프로젝트를 진행해 왔습니다. 많은 사용자가 접속하여 대용량 트래픽을 받는다고 가정하면 한 서버에 부하가 집중될 것이고, 만약 장애가 발생하면 다운되어 서비스 운영이 불가능하게 될 것입니다. 이런 병목현상 또는 DB 서버 다운을 방지하기 위해 DB Replication을 사용하여 MySQL 서버 이중화를 구축하였습니다. 

 

해당 글은 Ubuntu 18.04 / MySQL 5.7.42 스펙을 기준으로 하며,

기존 서버에서 사용 중인 flea_market DB를 새로운 서버에 복제하는 내용입니다.

 

원본 데이터를 가진 서버는 source server 또는 Master, 복제 데이터를 가진 서버는 replica 또는 Slave라고 부릅니다.

여기서는 원본-복제 DB 서버 명칭을 Master-Slave로 통일하여 설명하겠습니다.

# MySQL Replication 동작방식

MySQL Replication 동작 방식 설명에 앞서,

Master 서버에 쓰기(Create/Update/Delete) 작업으로 변경된 내용들을 'Event'라고 하며, 이 event들은 Binary log에 저장됩니다. 이 로그 파일은 아래 그림 4번과 같이 Slave 서버에서 Master로의 요청을 통해서 비동기적으로 복사 전달됩니다. 변경된 event들이 Slave에 적용되기 전에 select 요청이 온다면 아직 반영되지 않았기 때문에, Master-Slave 간 데이터가 동기화되지 않아 데이터 정합성 문제가 발생할 수 있습니다. 

 

출처 : https://mblog.kr/_bbs/bbs_view.html?no=130&page=5&category=

위 그림에서 기억해야 할 부분

• Slave가 Master에 변경사항을 요청
• Master(Binary log dump thread)는 Binary log 파일을 Slave로 전송
• Slave(I/O thread)는 전달받은 Binary log 파일을 Relay log에 기록
• Slave(SQL thread)는 Relay log에 기록된 SQL 문장들을 읽어서 실행 

---

# Master-Slave 기본 setup

• Master setup
  
  1. unique server ID 설정 & binary logging 활성화
  2. 사용자 계정 생성 및 권한 부여
  3. Binary log 파일 name, position 조회/기록
  4. DB 백업 파일 생성 -> Slave로 전송

• Slave setup
  
  1. 백업 파일 확인 및 DB 복제
  2. unique server ID 설정
  3. Master와 연결
  4. Slave 실행

---

# Master 서버 setup 실습

NCP의 Mater 서버

1. unique server ID 설정 & binary logging 활성화

1-1. putty로 Master 서버 접속

Host Name : 서버 접속용 공인 IP / Port : 설정한 외부 포트

 

1-2. MySQL 설정 파일인 my.cnf 편집기 실행

# vi /etc/mysql/my.cnf

• OS마다 파일 경로가 다릅니다.  # find / -name my.cnf   명령어로 파일 위치를 확인하세요!

 

1-3. unique server ID = 1 설정, binary logging 활성화

수정 -> 알파벳 i , 저장 및 닫기 -> :wq

Q. server-id, log-bin 옵션을 추가해야 하는 이유는? 

• server-id를 명시적으로 지정하지 않을 경우 default server ID = 0이며, Master-Slave 서버는 서로 연결할 수 없게 됩니다.  MySQL Replication을 구성할 때, Master 서버와 Slave 서버가 서로를 식별하기 위해 각각 고유한 서버 식별자(server-id)를 가져야 합니다. 
• Slave 서버는 Master 서버의 Binary log를 읽어서 변경 사항을 복제합니다. log-bin 옵션으로 binary logging을 활성화해야 Replication을 수행할 수 있습니다.

 

1-4. MySQL 재시작

# sudo service mysql restart

 

2. 사용자 계정 생성 및 권한 부여

: 모든 Slave 서버는 CHANGE MASTER TO 명령어를 통해 Master 서버와 연결합니다. 이때, REPLICATION SLAVE 권한이 부여된 계정과 암호를 필요로 합니다. 사용자 계정과 암호를 'repl'로 통일하겠습니다. 

 

2-1. 사용자 계정 생성  ( CREATE USER '생성할 계정명'@'%' IDENTIFIED BY '비밀번호'; )

# mysql -u root -p

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';

2-2. 권한 부여  ( GRANT REPLICATION SLAVE ON *.* to '생성한 계정명'@'%' IDENTIFED BY '비밀번호'; )

mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' IDENTIFED BY 'repl';

 2-3. 확인

 

계정 확인

mysql> SELECT user, host FROM mysql.user WHERE user = 'repl';

확인 OK

권한 확인

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

확인 OK

Q. flush privileges를 사용하지 않는 이유는?

: INSERT, UPDATE, DELETE를 통해 grant 테이블을 직접 수정한다면, 해당 명령어를 사용하여 테이블 reload를 하거나 서버를 재시작해야 합니다. 하지만, GRANT/REVOKE 등의 명령어를 사용할 경우는 즉시 grant 테이블이 reload 되므로, flush privileges 명령어를 사용하지 않아도 됩니다. 

3. Binary log 파일 name, position 조회/기록

3-1. 읽기 전용 테이블로 Lock

mysql> FLUSH TABLES WITH READ LOCK;

: 위 명령어를 통해 해당 DB의 모든 테이블에 대한 쓰기 작업을 차단(block commit)하고 읽기 전용 테이블로 변경합니다. binary log file의 position을 기록하고 아래 4번에서의 일관성 있는 데이터 백업을 위한 선행 작업입니다.

 

 

3-2.  Binary log file, position 조회/기록

mysql> SHOW MASTER STATUS\G;

: Slave 서버에서 Master와 connection에 쓰이는 mysql-bin.00011 / 154 를 기록해 둡니다.

Q. Master setup에서 Binary log 파일 name, position을 기록하는 이유는?

: 우리는 Master가 특정 이벤트만을 로그 파일에 기록하도록 설정할 수 없습니다. Slave는 Binary log 파일 전체를 복사본으로 전달받고 실행해야 할 SQL 문장을 Slave가 직접 결정합니다. 그러므로 모든 Slave는 Binary log 파일의 좌표(name, position)을 알고 있어야 합니다. 

4. DB 백업 파일 생성 -> Slave로 전송

4-1. 특정 DB 백업 파일 생성  ( # mysqldump -u 'root' -p 특정 DB명 > 백업 파일명.sql )

mysql> exit

# mysqldump -u 'root' -p flea_market > flea_market_dump.sql

: 백업 작업은 모든 권한을 가지고 있는 root 계정을 사용했습니다.

 

4-2. 백업 파일 확인

: # ls -al  

생성 확인 OK

: # cat flea_market_dump.sql  

내부 확인 OK

 

4-2. Slave 서버로 백업 파일 전송 ( # scp 파일명 'Slave 서버 접속 ID'@'Slave 서버 비공인 IP주소':/root/ )

# scp flea_market_dump.sql 'root'@'xx.xx.xxx.xx':/root/

: scp는 secure copy protocol 축약어로, 원격으로 파일을 복사 전송하는 명령어입니다. Slave 서버의 루트 경로로 전송합니다.

 

4-3. 테이블 Lock 해제

# mysql -u root -p

mysql> UNLOCK TABLES;

---

# Slave 서버 setup 실습

NCP의 Slave 서버

1. 백업 파일 확인 및 DB 복제

1-1. putty로 Slave 서버 접속

1-2. 백업 파일 확인

루트 경로에 전달받은 백업 파일 확인 OK

1-3. 백업 파일 복제할 DB 생성

# mysql -u root -p

mysql> CREATE DATABASE flea_market;

mysql> exit

1-4. 데이터 복제

# mysql -u root -p flea_market < flea_market_dump.sql

: `<` 기호를 사용하여 scp 명령어로 복사 전달받은 flea_market_dump.sql 파일을 Slave 서버의 flea_market DB에 복제합니다.

 

2. unique server ID 설정

2-1. MySQL 설정 파일인 my.cnf 편집기 실행

# vi /etc/mysql/my.cnf

2-2. unique server ID = 2 설정

2-3. MySQL 재시작

# sudo service mysql restart

 

3. Master 서버와 연결

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='Master 공인IP',
    ->     MASTER_USER='위에서 생성한 계정',
    ->     MASTER_PASSWORD='위에서 생성한 계정 암호',
    ->     MASTER_LOG_FILE='binary log 파일명',
    ->     MASTER_LOG_POS='binary log 파일 position';

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='xx.xx.xxx.xx',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='repl',
    ->     MASTER_LOG_FILE='mysql-bin.000011',
    ->     MASTER_LOG_POS=154;

4. Slave 실행

4-1. 실행

mysql> START SLAVE;

4-2. 확인

mysql> show slave status\G;

실행 확인 OK

 

Master 서버에 접속하여 test DB를 생성합니다.

mysql> CREATE DATABASE test;

Slave 서버에 접속하여 Master의 새 event(test DB 생성)이 잘 반영되었는지 확인합니다.

확인 OK

 

 

 

참고 :

https://dev.mysql.com/doc/refman/5.7/en/replication.html