[SQL] Replication 동기화 작업하기 (마스터-슬래이브)

서비스가 작을 때는 WAS(Web Application Server)하나에 DB 하나를 연결하여 요청을 처리한다.

 

하지만, 서비스가 커지면서 WAS를 여러개 연결하여 LB(Load Balancer)를 사용해 요청을 적절히 분배하기 시작한다.

이때 연결된 DB는 SSD병렬, index 스키마 튜닝등을 통해 성능관리를 한다.

 

하지만, 서비스가 매우 커지게 되면서 하나의 DB에는 병목현상이 심하게 발생하게 되어 DB도 여러개 생성하게 된다.

즉, LB에 여러개의 WAS를 연결하고, 각각 DB를 생성하여 동기화하는 방식으로 관리하게 된다.

이때, Replication을 사용한다.

 

가장 흔하게 사용되는 Replication방식은 MASTER-SLAVE(마스터-슬래이브)방식인데,

MASTER는 주로 Insert를 담당하여 자료 수정을 맡고 SLAVE에게 동기화하여 뿌려주는 방식으로 사용하고,

SLAVE는 Select만을 담당하여 MASTER로부터 동기화받은 것을 조회시키는 담당을 한다.

주로, 뉴스기사같은 경우, 수정과 입력보다 출력이 훨씬 많은 경우에 이 방식을 사용한다.

 

MASTER-SLAVE Replication의 작동 방법은 다음과 같다.

 

1. AWS에서 MASTER 인스턴스(EC2)와 SLAVE 인스턴스(EC2)를 생성

2. MASTER서버와 SLAVE서버에 각각 mysql 설치하고 환경설정

3. MASTER서버를 세팅

4. SLAVE서버를 세팅

 

- 1. 와 2. 는 아래 포스팅의 방법과 똑같다.

https://ssongblog.tistory.com/134?category=902345

[AWS]AWS를 이용하여 MySQL 환경 구축하기

 

 

1. AWS에서 MASTER 인스턴스(EC2)와 SLAVE 인스턴스(EC2)를 생성

- AWS에서 MASTER 인스턴스와 SLAVE 인스턴스를 각각 생성한다.

- AMI, 인스턴스 유형, 보안그룹은 앞 포스팅과 같은 환경을 세팅했고, Key Pair는 기존에 생성한 key.pem을 사용하였다.

*AWS에서는 인스턴스를 여러개 실행시에 과금정책에 따라 과금이 발생될 수 있으니 유의한다.

*탄력적IP 역시 과금을 유발하기에 사용하지 않는 것은 꼭 릴리스한다.

 

2. MASTER서버와 SLAVE서버에 각각 mysql 설치하고 환경설정

- 각각의 터미널 환경을 열어 mysql을 설치해주고, 환경설정해준다.

- 환경설정 : 1. 내부pw설정   2. 외부접속허용   3. 외부pw설정

 

[터미널 1] for MASTER

 

- MASTER서버에 접속

ssh -i ~/Downloads/.ssh/key.pem ubuntu@MASTER의퍼블릭IP

- mysql 설치

sudo apt-get update -y
sudo apt-get upgrade -y
sudo apt-get install -y mysql-server mysql-client

- 환경설정 1. 내부pw설정

sudo mysql

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql > quit

- 환경설정 2. 외부접속허용

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

vi 에디터에서 /bind (bind글자찾기) 입력 후 i를 눌러 수정모드에서 bind-address = 127.0.0.1을 0.0.0.0으로 수정한다.

esc를 누르고, :wq를 입력하여 에디터를 빠져나온다.

 

 

- 환경설정 3. 외부pw설정

mysql -u root -ppassword

mysql > grant all privileges on *.* to root@'%' identified by 'password';
mysql > quit

서버 재시작

sudo systemctl restart mysql.service

 

 

[터미널 2] for SLAVE

 

- SLAVE서버에 접속

ssh -i ~/Downloads/.ssh/key.pem ubuntu@SLAVE의퍼블릭IP

- mysql 설치

sudo apt-get update -y
sudo apt-get upgrade -y
sudo apt-get install -y mysql-server mysql-client

- 환경설정 1. 내부pw설정

sudo mysql

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql > quit

- 환경설정 2. 외부접속허용

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

vi 에디터에서 /bind (bind글자찾기) 입력 후 i를 눌러 수정모드에서 bind-address = 127.0.0.1을 0.0.0.0으로 수정한다.

esc를 누르고, :wq를 입력하여 에디터를 빠져나온다.

 

- 환경설정 3. 외부pw설정

mysql -u root -ppassword

mysql > grant all privileges on *.* to root@'%' identified by 'password';
mysql > quit

서버 재시작

sudo systemctl restart mysql.service

 

 

3. MASTER서버를 세팅

[WorkBench] for MASTER

[WorkBench] for SLAVE

 

- WorkBench에 MASTER세션과 SLAVE세션을 추가한 후, 다시 터미널 1 (for MASTER)로 넘어온다.

 

[터미널 1] for MASTER

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

- vi 에디터에서 /server_id 검색을 하여 수정모드로 들어가서,

수정사항 :

1. sever_id를 주석해제하고

2. log_bin을 주석해제하고

3. binlog_do_db를 mydb1로 변경한다.

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb1

esc후 :wq

- 서버 재시작한다.

sudo systemctl restart mysql

- mysql로 들어가 SLAVE와의 동기화 설정을 진행해준다.

mysql -u root -ppassword

mysql > GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
mysql > quit

- 설정이 완료된 후 다시 WorkBench로 넘어간다.

 

[WorkBench] for MASTER

 

- mydb1 데이터 베이스를 생성한다.

create database mydb1;

- 생성이 잘되었는지 확인한다.

show databases;

- MASTER 세션에서 마스터 상태를 확인한다.

show master status;

여기서 File과 Position을 SLAVE에 연결할 때 사용한다.

File : mysql-bin.000002

Position : 154

 

4. SLAVE서버를 세팅

[터미널 1] for MASTER

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

- vi 에디터에서 /server_id 검색을 하여 수정모드로 들어가서,

수정사항 :

1. sever_id를 주석해제 후 2로 바꾸고

2. relay-log를 추가하고

3. log_bin을 주석해제하고

4.. binlog_do_db를 mydb1로 변경한다.

server_id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb1

esc후 :wq

- 서버 재시작한다.

sudo systemctl restart mysql

- 설정이 완료된 후 다시 WorkBench로 넘어간다.

 

[WorkBench] for SLAVE

 

- mydb1 데이터 베이스를 생성한다.

create database mydb1;

- 생성이 잘되었는지 확인한다.

show databases;

- 동기화 세팅을 진행해준다. (MASTER STATUS의 File과 Position활용)

change master to master_host='마스터퍼블릭IP'
, master_user='root'
, master_password='password'
, master_log_file='mysql-bin.000002'
, master_log_pos=154;

- 동기화를 시작해준다.

start slave;

- 동기화 상태를 확인한다.

show slave status;

- Slave_IO_Running이 Yes로 나오면 동기화가 잘 된 것이다.

 

이제 동기화가 모두 완료되었다. 정상적으로 작동하는지 확인해보자

[WorkBench] for MASTER

 

- 동기화 확인을 위해 MASTER의 mydb1에 테이블을 생성하고 데이터를 추가해준다.

use mydb1;

create table user(
	data varchar(20)
);
insert into user values ("data1"), ("data2");
select * from user;

 

[WorkBench] for SLAVE

- MASTER에서 생성, 수정한 테이블, 컬럼이 반영된 것을 확인할 수 있다.

use mydb1;
select * from user;