[ Database ] DB 이중화, MySQL Replication 설정

클라우드 환경에서의 이중화 (RDS 읽기 복제본)

테이블 잠금 및 자동 백업을 허용한 RDS를 하나 생성한 다음, 

 

RDS DB를 클릭하고 읽기 복제본 생성을 클릭한다.

(이후 플로팅 IP나 서브넷 옵션, 기타 설정 등은 알아서 지정해주자)

 

단일 탭을 클릭하면 복제본이 생성된 것을 볼 수 있다. (시간이 좀 걸린다!)

 

로그인 정보는 master쪽의 ID와 동일한 정보를 사용하고, '단일'탭에서 slave쪽 접속 정보에 대한 내용을 확인할 수 있다.

 

 

10줄만 조회

성공적으로 이중화가 성공된 것을 확인할 수 있다.

---

MySQL Replication

replication : 복제

2대 이상의 DBMS로 나눠서 데이터를 저장하는 방식으로, 최소한의 구성은 Master / Slave 이다.

(Slave라는 단어는 요즘 부적절한 단어라고 하니까 앞으로는 Replica라고 부르도록 하자 !!)

+ 실제로 MySQL의 GRANT시 slave라는 단어는 권장하지 않는다고 warning을 띄워주고 있다.

 

Master / Replica의 역할

Master DBMS : 웹 서버에서 보내는 요청중 수정/등록/삭제에 대한 요청 시 바이너리 로그를 생성하여 Replica 서버에 전송한다.

Replica DBMS : Master로부터 전달받은 바이너리 로그를 자신의 데이터로 반영하게 된다.(복제) / 주로 요청이 많은 읽기를 담당하게 된다.

 

MySQL Replication시 주의사항

• MySQL의 버전을 Master와 Replica를 동일하게 가져가는 게 좋다(호환성)
• 버전이 다른 경우, Replica 서버가 상위 버전이어야 한다.
• Replication을 구동 시, 무조건 Master -> Replica 순으로 진행해야 한다.

 

MySQL Replication 구현해보기

등록/수정/삭제는 Master 서버로, 읽기는 Replica서버가 담당하도록 구성해보자.

 

인스턴스에 MySQL 설치

sudo apt-get update
sudo apt-get install mysql-server

 

MySQL 접속

sudo mysql -u root -p

root 계정으로 접속한 뒤, 비밀번호 초기화를 진행한다.

초기 비밀번호를 설정하지 않았기 때문에 그냥 Enter를 입력하면 로그인 할 수 있다.

---

(Master 측) DB 생성 및 계정 생성

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

DB를 먼저 생성해주고, Replica 서버에서 Master 측에 접근할 때 사용할 계정을 생성해주자.

-> 추후 허용 IP로 수정하던가 하기

 

MySQL 설정

# sudo nano /etc/mysql/my.cnf
 
[mysqld]
log-bin=mysql-bin
server-id=1
 
# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1 -> bind-address=0.0.0.0(모든 아이피 접속 허용)

exit로 MySQL 환경을 나와서 my.cnf 파일을 수정해주자.

server-id는 DB의 고유 번호이며, Master가 1번을 담당하고, 그 외 DB는 아무 숫자나 지정해도 상관 없다.

 

MySQL 재시작

sudo service mysql restart

 

MySQL 서버 정보 확인

sudo mysql -u root -p
show master status;

File에 해당되는 로그파일명과 Position에 해당되는 로그 파일 내 읽을 위치를 기억해주자.

(위 이미지에서는 mysql-bin.000001과 157)

 

동기화 할 DB를 dump 파일로 추출

                  계정명       DB명       (테이블명) > 추출파일
sudo mysqldump -u root -p log_backup application_logs > application_logs.sql
scp -P 22 repl_db.sql ubuntu@192.168.0.80:/home/ubuntu/

생성된 dump 파일을 Replica DB가 있는 서버로 전송해주자.

 

(키 파일을 이용하여 전송)

나의 경우에는 DB를 적재 할 인스턴스 생성 시 키 파일을 설정해서, 키 파일이 있어야만 인스턴스 접속 및 파일 전송 등의 작업이 가능하도록 했었다.

이런 경우에는 위의 scp 명령을 이용하면 timeout 에러가 발생하기 떄문에 키 파일 이용해서 접속할 필요가 있다.

scp -i ./(키파일경로)/키파일명.pem -P 22 application_logs.sql ubuntu@192.168.3.45:/home/ubuntu/

이런 형식으로 scp로 전송해주자.

그럼에도 전송이 안 될 경우엔 DB 서브넷에 scp 포트를 열어뒀는지 확인해보자!

나의 경우에는, 22번 포트는 열려 있었으나 Bastion보안 그룹을 대상으로 열려 있었어서,

DB 인스턴스 내에서도 SCP로 파일 송수신이 가능하도록 추가적으로 열어주었다.

 

성공!

+ 3306 포트 접근을 Private로 열어 두어서 Replication 수행이 제대로 안 되었는데, DB 보안그룹에 대해 열어두면 성공적으로 수행 가능하다!

---

(Replica 측) Replica 설정

CREATE DATABASE repl_db DEFAULT CHARACTER SET utf8;

DB를 먼저 생성해주자. 이후 MySQL을 나와서, my.cnf 파일을 수정해준다.

 

my.cnf 설정

# sudo nano /etc/mysql/my.cnf
 
[mysqld]
server-id=2		# Master에게 부여한 1번을 제외한 아무 숫자나 가능

 

 

MySQL dump 파일 복원

#                       (DB명)       
sudo mysql -u root -p log_backup < application_logs.sql
sudo mysql -u root -p

 

Master 서버로 연결하기 위한 설정

mysql> change master to
MASTER_HOST='xx.xxx.xx.xxx', # Master측 내부 IP -> ex)192.168.3.62 
MASTER_USER='repl',
MASTER_PASSWORD='mysql1380',
MASTER_LOG_FILE='mysql-bin.000001', # 앞서 확인했던 FILE명
MASTER_LOG_POS=157, # 앞서 확인했던 번호
MASTER_PORT=3306,
MASTER_SSL=1;
 
# 혹은
 
mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST=#'source_host_name',
SOURCE_USER=#'replication_user_name',
SOURCE_PASSWORD=#'replication_password',
SOURCE_LOG_FILE=#'recorded_log_file_name',
SOURCE_LOG_POS=#recorded_log_position,
SOURCE_PORT=#오픈된 포트,
SOURCE_SSL=#SSL설정, 1은 설정함, 0은 설정안함;
 
 
# START
mysql> START SLAVE;
 
# EXIT 후 MySQL 재시작
sudo service mysql restart

 

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

만약 코드를 잘못 넣었다면 Replication DB인스턴스 측에서 위와 같이 입력한 다음 change master to 부터 다시 진행하면 된다.

Replication 확인해보기 

오른쪽 이미지 확인!

show slave status\G;를 통해 성공적으로 적용된 내용을 확인할 수 있다.

Slave_IO_Running과 Slave_SQL_Running이 모두 Yes가 출력되어야 한다.