RDS 활용 DB 이중화 (마스터 = Onpremiss 슬레이브 = AWS)

목표 : AWS의 RDS기능을 사용하여 RDS DB와 내 컴퓨터의 DB간의 Master -Slave 관계를 맺어서 이중화를 하려고 한다. 

 

RDS 란 ? 아마존 관계형 데이터베이스 서비스 또는 아마존 RDS는 아마존 웹 서비스가 서비스하는 분산 관계형 데이터베이스이다. 애플리케이션 내에서 관계형 데이터베이스의 설정, 운영, 스케일링을 단순케 하도록 설계된 클라우드 내에서 동작하는 웹 서비스이다.

 

EC2 인스턴스 하나를 생성해서, 온프레미스 환경의 Database라 가정하고, RDS와 (master-slave 관계) 이중화를 실습 해보려고 한다.

 

구성 : EC2 인스턴스(Centos7)mysql 5.7.33 version , AWS_RDS(mysql 8 version), Workbench(mysql client 프로그램) 

주의 : Slave 서버의 mysql 버전은 마스터 서버의 DB 버전과 같거나, 더 높아야 한다.

---

마스터 설정

1) /etc/my.cnf 에서 설정파일 수정. [mysqld] 아래에 설정 추가.

my.cnf 설정에 수동으로 추가.

2) 테스트 할 DB 생성. CREATE DATABASE khg;

3) 해당 DB에 테이블 생성. CREATE TABLE FRIENDS(name VARCHAR(10), age INT);

4) Slave에서 접속할 수 있도록 계정생성

GRANT REPLICATION SLAVE ON *.* TO '[아이디]'@'[RDS의 endpoint]' IDENTIFIED BY '[패스워드]';
select * from mysql.user where user='[아이디]'\G Repl_slave_priv 명령어로 역할 잘 주어졌나 확인.

slave_priv:y 체크 돼있다.

* 내 db에 접근할 수 있거나 내가 생성한 유저들 목록 보는 법.

use mysql;
select user, host from user;

 

5) show master status로 현재 마스터의 file, position 확인. (재접속 할 때마다 파일명, 포지션 번호가 변경될 수 있으니  유의)

6) mysqldump로 RDS DB 인스턴스로 dump 파일 전송

mysqldump -u [local db user] \
    --databases [db name]\
    --single-transaction \
    --compress \
    --order-by-primary  \
    -p[local_password] | mysql -u [RDS_user] \
        --port=[3306] \
        --host=[RDS endpoint] \
        -p[RDS password]

---

슬레이브 생성 및 설정

1)RDS 생성

RDS를 생성할 때

마스터 사용자 이름이 곧, DB에 접속할 ID 이며 암호는 그 패스워드이다.  

퍼블릭 액세스 가능을 허용해주고 호스트 컴퓨터에서 워크벤치(Mysql client 프로그램)을 통해 접속해서 설정 할 것이다.

2) RDS 원격접속(client 프로그램 사용) gui 환경에 사용하기 편하다고 느낀 mysql workbench를 사용하였다.

RDS의 퍼블릭 ip로 접속하는 것이 아닌, 엔드포인트 주소를 활용하여 접속하면 된다.

워크벤치로 접속.

3) Slave 설정.

RDS가 아닌 자신이 직접 다운받아서 하는 경우 /etc/my.cnf 설정파일을 수정하면 되지만 RDS는 그렇게 설정 할 수가 없다. RDS에 파라미터 그룹을 들어가서.

 

수정해야 하는 값중 하나인 server_id를 검색해보면 값 유형에 수정 불가 라고 써있다.

RDS는 Super 권한을 제한해한다 그로인해서 할 수 없는 것들이.

 

# SUPER권한이 없어서 불가능한 항목들

* Replication 명령 : CHANGE MASTER TO, CHANGE REPLICATION FILTER, STOP SLAVE, START SLAVE ...

* BINLOG관련 명령 : PURGE BINARY LOGS, BINLOG 명령등 불가

* KILL 명령 : 오동작중인 thread를 죽여야 하기 때문에 필요

* GRANT 명령 : USER 추가

* 추가 접속 : max connection 까지 모두 차더라도 super 권한을 가진 1개의 USER는 접속이 가능했었다.

* Variable 변경 : 설정 변경은 parameter group 변경을 통해 가능합니다.

-> sql_log_bin=OFF 같은 명령이 안되기 때문에 슬레이브 먼저 컬럼 추가하고 마스터 적용하는 식의 ALTER가 안된다.

* init_connect : 접속시 캐릭터셋을 변경하거나, login trigger 같은 동작을 수행시킬 수 있다. 이것도 parameter group에서 가능하다.

* DEFINER 지정 : stored procedure 에서 definer 지정은 super권한이 필요하다.

* Stored procedure 생성 : super권한이 필요했었다.

출처 :https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sory1008&logNo=220952534094

 

이러한 명령들을 RDS 에서는 모두 프로시저 형태로 제공한다.

mysql.rds_set_external_master (RDS 외부에 있는 마스터에 대해) Replication을 위한 명령이다.

기존 명령어로는 CHANGE MASTER 명령을 대체한다고 보면 된다.

 

mysql.rds_start_replication

Replication Slave를 시작하거나 stop 하는 기능을 제공.

 

4) database, 테이블 생성

create database khg;
use khg;
CREATE TABLE FRIENDS(name VARCHAR(10), age INT);

5) 마스터 설정. (프로시저 호출)

CALL mysql.rds_set_external_master (
'[마스터 ip]'
, 3306
, '[마스터에 접속할 ID]'
, '[password]'
, '[master log file]'
, [position]
, 0);

입력 후 실행 했으나

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.031 sec 

에러 발생. 

 

워크벤치에서 실행 할 시 한번에 많은 값을 업데이트하거나 할 경우 안전모드 때문에 그렇다 하니 안전모드를 꺼줌.

SET SQL_SAFE_UPDATES = 0;

실행 성공.

설정이 끝났으니 마스터에서 데이터를 insert 했을 때 slave 에서도 조회가 되나 확인해보려고 한다.

 

왼쪽 - 마스터 db friends 테이블 데이터, 오른쪽 슬레이브 RDS friends 테이블 데이터

마스터 db에서 데이터 insert 후 조회

마스터 db에서만 insert를 진행 했음에도, slave(rds) 에서도 데이터가 들어간 것을 조회 할 수 있다.