[project] DB DMS 실시간 동기화 (온프레미스 DB와)

 
 
공식 문서
https://docs.aws.amazon.com/dms/latest/sbs/chap-mariadb2auroramysql.html
고객이 필요로하는것을 먼저 생각함
- 라이브마이그레이션(다운 타임이 없는 서버 이주)
- 비용( 효율적인 비용 )
 
 
 

기존 DB (Origin DB)

• 기존 운영중인 DB서버 역할을 할vmware centos7 mysql 서버를 생성한다.

• DB서버는 보통 Private공간에서 운영되는데 DMS는 IP를 기반으로 Origin DB에 접근하기 때문에 DMS를 위해 잠시 공인 IP를 설정해준 것이다.

• DB서버에 공인 IP를 설정하지 않고 DB를 Migration하려면 AWS Direct Connect 서비스를 사용하거나 VPN을 구축해 진행하는 방법도 있다.

 
 
 
DMS 구성도

 
 
 
 
 
 
 
 
-1. vpc 전제
 
이전에 이렇게 제작했던 vpc를 그대로 쓴다. 이 vpc에 DMS서비스를 올리는게 내 목적이다.
근데 그냥 EIP 주고 디폴트 써도 아마 될거다.

 
 
 
0. VMware에 DB 생성
https://raid-1.tistory.com/101
 
티스토리 이전글과 같이 만들었다.
근데 버전은 주의해야한다. mysql 5.7 버전 처음에 설치했더니, DB 동기화에 실패했다.
그 이유는 오로라 mysql DB버전이 높아서 이전 mysql엔 있던 환경변수를 최신에선 못읽어서 그렇다. 
아래 마리아db 10.5에선 성공했다.

grant all privileges on *.* to 'root'@'%';  
권한 부여 (편의상 루트사용)

 

아니면 이렇게 create user 'roota'@'%'identified by 'It12345!';

모든 ip접속에 대한 계정을 하나 생성하고

grant all privileges on *.* to 'roota'@'%';

 권한도 줘보자.

GRANT ALL ON *.* TO 'was'@'localhost' IDENTIFIED BY 'Was12345!' WITH GRANT OPTION;

 

> flush privileges;

 

# systemctl restart mysqld

 

 
 
 
 
 
1.DMS 복제 instance를 위한 서브넷 그룹 생성
 
데이터베이스 마이그레이션에 사용할 네트워크의 일부로 가상 사설 클라우드 (VPC) 에서 사용할 서브넷을 지정한다.
서브넷은 지정된 가용 영역에 있는 VPC의 IP 주소 범위다.
이러한 서브넷은 VPC가 위치한AWS 지역의 가용 영역 간에 분산될 수 있다.
선택한 서브넷에 복제 인스턴스를 만들고,
AWS DMS 콘솔을 사용하여 소스 또는 대상 엔드포인트가 사용하는 서브넷을 관리할 수 있다.
 

• 서브넷은 꼭 IGW를 통해 외부와 통신할 수 있어야 한다.
• VPC는 Amazon VPC 서비스를 기반으로 해야 한다. 
• 서브넷 그룹은 동일한 AWS 리전에 있는 두 개의 서로 다른 가용 영역에 있는 두 개 이상의 서브넷을 포함해야 합니다.

그 후 해당 VPC의 서브넷을 선택하는데 꼭! Internet Gateway를 통해 외부로 나갈 수 있는 Public Subnet을 선택한다.
그렇지 않고 Private Subnet을 선택하면 외부로 갈 수 없기 때문에 이 후에 On-Premise DB에 접근하기위한 End Point에 접근이 불가하다.
서브넷들은 이전에 만든 vpc 참조.

 
 
2. DMS 가 쓸 보안그룹 생성

 
 
 
 
3. 복제 인스턴스 생성
 
이삿짐 센터를 꾸린다고 봐야한다.
센터의 스펙마다 다르게 시간당 요금이 부과되므로 DMS 완료 후 꼭 삭제해야 한다.
 
지속적으로 DMS를 통해 백업을 받으려고 하는데, 이럴 경우엔 시간당 계속 요금이 나간다고 보아야하는지 의문.
트레픽 양으로 요금 매기면 좋은데.
 

복제 서브넷 그룹, 보안그룹은 아까 만든 그룹들을 줬다.

AWS DMS는 복제 인스턴스와 엔드포인트 연결 정보에서 사용하는 스토리지를 암호화한다.

복제 인스턴스에서 사용하는 스토리지를 암호화하려면AWS 계정에 고유한AWS Key Management Service (AWS KMS) 키를AWS DMS 사용한다.
(보안대비책은 되어있군..) 
 
 
 
4. 엔드포인트 생성
 
엔드 포인트 연결 관련 공식 문서
https://docs.aws.amazon.com/ko_kr/dms/latest/userguide/CHAP_ReplicationInstance.VPC.html#CHAP_ReplicationInstance.VPC.Configurations.ScenarioDirect
 
다이렉트 커넥트 vpn
https://docs.aws.amazon.com/ko_kr/vpn/latest/s2svpn/private-ip-dx.html#private-ip-dx-prereq
 
내가 원하는 구성은 AWS상에 있는 EC2의 DB를 AWS상에 옮기는게 아닌,
사설망 온프레미스상에 있는 vmware의 DB를 옮기는 것이기 때문에 난이도가 좀 더 있다. (인터넷에 자료도 더 없다)
 
전자의 경우는 DB에 공인 IP(엔드포인트)가 그냥 제공돼서 갖다 붙여넣으면 끝이지만,
나의 경우는 내 VMware가 구동중인 사설망 PC 상단에 공인IP를 할당받는 공유기가 내 소유가 아니라 공공재라서 공인 ip를 손댈 수가 없었다. 심지어 공인 ip는 할당받는거니까 유동적일수밖에 없다.
 
그래서 사설끼리만 통신시키기위해 VPN이건 Direct connect건 InternetGW 연결이건 오픈스완이건 모든 문서를 시도해봤으나 결국 끝에가선 공인 IP (리얼IP 헤더) 가 필요했기에 하루종일 삽질했다.
결국엔 그냥 팀원이 일하던 IDC센터에서 공인 IP 하나 임시로 빌려왔다.
 

 
 
 
 
==================================================================================
 
Source Endpoint 생성
 
 
각설하고 공인 IP가 DB에 연결돼있다는 가정하에 진행한다.
만약 이런게 없다면 aws상에 EC2로 DB하나 만들고 거기에 있는 공인IP쓰는게 컨셉상 맞다.
(도메인 주소를 사용해서도 통신 할 수 있다고 하는데, 이건 가비아에서 300원짜리 도메인 사던지,
IPTIME 공유기의 DDNS기능 쓰던지 하는 복잡함이 더해진다.)
 

• 엔드포인트 식별자 = 엔드포인트 이름
• 데이터베이스 엑세스를 수동으로 액세스한다.
• 서버 이름에는 DB가 존재하는 Public IP를 입력한다.
• 포트는 MariaDB의 포트 3306 입력한다.
• 사용자 이름, 암호는 모든 IP가 접근할 수 있는 사용자로 입력한다. (root계정은 defualt로 localhost만 접근가능)

 

 
 
엔드포인트 연결테스트 진행 후 successful이 나온 경우 성공적으로 연결된 것이다
생성 정보창 하단에서도 테스트를 할수 있지만 만든 뒤 나와서도 가능하다.

 

• successful이 나오지 않은경우
  • DMS,EC2에 3306 포트가 열려있는지 확인
  • DB, IP정보가 잘 입력되었는지 확인
  • DB user 생성할때 create user 'dms'@'%' identified by 'dms'; 와 같이 '%'를 적용했는지 확인
  • 서브넷의 인터넷 통신성 확인

• successful이 나오지 않아도 테스트를 진행하는 순간 엔드포인트는 생성된다. 잘못 설정하여 생성된 경우 까먹지 말고 삭제하기

 
 
Target Endpoint 생성
 
Target은 이전에 RDS로 만든 Aurora DB다.
DMS는 동일 AWS 내에 존재하는 RDS를 수동 지정할 수 있어서 Source보다 편리하게 생성할 수 있다.
 
나의 경우엔 DB를 리더와 라이터 클러스터로 생성해서 DMS에 연결할수 있는 DB 인스턴스 목록이 2개 뜬다.

 
내 DMS 사용 목적은 온프레미스의 DB와 AWS상 DB의 싱크이므로
라이터 DB랑 연결하는 것이 맞다.

 
엔드포인트 연결 테스트

 

 
 
5. 데이터베이스 마이그레이션 태스크 생성
 
데이터베이스 마이그레이션 태스크를 생성해서 DMS를 활성화해보자.
내가 DMS를 채택한 목적은 지속적인 온프레 DB와 AWS DB의 Sync이기 때문에,
마이그레이션 유형에서 '지속적 변경사항 복제'를 선택한다.
 

 
 
 
테이블 메핑은 규칙을 사용하여 마이그레이션 태스크에 포함(또는 제외)할 스키마 및/또는 테이블을 결정하는 부분이다.
예를 들어 테이블 이름에 mem%라고 입력하면 mem로 시작하는 모든 테이블을 Migration한다는 옵션을 주는 것

 
태스크 생성 → 자동으로 Migration 진행

 
 
 
 
 
 
 
6. 트러블슈팅
 
 
테스크 시작 실패
 

 
DMS 사용시 MySQl 온프레 설정 공식 문서
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html
 
 
 
 
===========================해결 이론===========================================

 

 

제일 먼저 체크해야할 점

1. 온프레미스쪽 방화벽 3306 오픈
 
2. 온프레미스 앞에 공인IP가 3306 포트로 받는다 치면, 그 뒷단인 DB까지 가는 라우팅.
(특히 온프레쪽 DB가 여러개일때 어떤 놈을 어떻게 연결할지)
 
3. DB 버전따라 환경변수가 있고 없고 차이
 
 
 
 
 
 

MySQL Replication 사전 설정

우선 Replication을 사용하도록 source 서버를 구성하려면 두 가지 설정을 확인해야 합니다.

• 바이너리 로깅이 활성화 됐는가?
• 고유한 서버 ID가 설정 됐는가?

 

바이너리 로깅을 활성화하려면 MySQL my.ini (Windows) 또는 my.cnf(UNIX) 파일에 다음 매개변수를 구성해야 합니다.

모수값

server-id	이 매개변수를 1 이상의 값으로 설정하십시오.
log-bin	와 같은 바이너리 로그 파일의 경로를 설정합니다 log-bin=E:\MySql_Logs\BinLog. 파일 확장자를 포함하지 마십시오.
binlog_format	이 매개변수를 로 설정합니다 ROW. binlog_format가 로 설정된 경우 STATEMENT데이터를 대상에 복제할 때 불일치가 발생할 수 있으므로 복제 중에 이 설정을 사용하는 것이 좋습니다 . binlog_format또한 데이터베이스 엔진이 로 설정된 경우 일관성 없는 유사한 데이터를 대상에 씁니다 MIXED. 데이터베이스 엔진이 자동으로 STATEMENT기반 로깅으로 전환되어 대상 데이터베이스에 일관성 없는 데이터가 기록될 수 있기 때문입니다.
expire_logs_days	이 매개변수를 1 이상의 값으로 설정하십시오. 디스크 공간의 남용을 방지하려면 기본값인 0을 사용하지 않는 것이 좋습니다.
binlog_checksum	이 매개변수를 로 설정합니다 NONE.
binlog_row_image	이 매개변수를 로 설정합니다 FULL.
log_slave_updates	TRUEMySQL 또는 MariaDB 읽기 전용 복제본을 소스로 사용하는 경우 이 매개변수를 로 설정하십시오 .

 
소스에서 NDB(클러스터형) 데이터베이스 엔진을 사용하는 경우 해당 스토리지 엔진을 사용하는 테이블에서 CDC를 활성화하도록 다음 매개변수를 구성해야 합니다. MySQL my.ini(Windows) 또는 my.cnf(UNIX) 파일에 이러한 변경 사항을 추가합니다.

모수값

ndb_log_bin	이 매개변수를 로 설정합니다 ON. 이 값은 클러스터링된 테이블의 변경 사항이 이진 로그에 기록되도록 합니다.
ndb_log_update_as_write	이 매개변수를 로 설정합니다 OFF. 이 값은 바이너리 로그에서 UPDATE 문을 INSERT 문으로 쓰는 것을 방지합니다.
ndb_log_updated_only	이 매개변수를 로 설정합니다 OFF. 이 값은 이진 로그에 변경된 열만이 아니라 전체 행이 포함되도록 합니다.

 
 
 

바이너리 로깅이 활성화 됐는가?

mysql 서버에서 다음과 같은 명령어로 확인이 가능다.

show variables like '%log_bin%';

log_bin 의 value 가 ON으로 되있어야 바이너리 로깅이 활성화 된 상태
MariaDB의 경우 10.5버전 기준 기본적으로 비활성화 상태이니 확인
 
바이너리 로깅 상태는 my.cnf를 통해서도 확인이 가능하다.
 mysql의 my.cnf에는 해당 설정이 명시되있지 않고 default로 설정이 ON 되있는 것으로 보인다!
 
my.cnf 파일의 위치는 리눅스나 변수 따라 다른데, 다음 위치들중에 있다.
/etc/my.cnf , /etc/mysql/my.cnf, /opt/homebrew/etc/my.cnf, ~/.my.cnf 이 순서대로 my.cnf를 찾아서 읽는다.
 
my.cnf의 [mysqld] 설정

[mysqld]
log_bin = /var/log/mysql/binlog파일명.log
expire_logs_days = 10   # 바이너리 로그 만료 기간
max_binlog_size = 100M # 바이너리 로그의 최대 크기

 
 

고유한 서버 ID가 활성화 됐는가?

Replication 구성을 위해서는 source 서버와 replica 서버들의 server_id 가 달라야한다.
즉, 각각의 서버는 고유해야한다.
MySQL의 server_id는 기본값이 1이며 master 서버의 경우 기본값인 1을 유지해서 사용해도 된다.
(replica 서버에서 server_id를 다르게 설정할 수 있기때문!)
 
해당 설정값을 확인하는 명령어

show variables like 'server_id';

server_id는 다음 명령어로 mysql-cli에서 동적으로 변경이 가능하다.

SET GLOBAL server_id = 2

 
my.cnf 에서도 변경이 가능하다.
해당 설정값이 언더스코어('_')인지 하이픈('-') 인지는 MySQL의 버전에 따라 다르다.
최근 버전인 MySQL 8.0에서는 대부분의 환경변수 이름이 언더스코어('_')로 변경됐지만 이전에는 섞어서 사용했으므로 주의해야한다!
즉 최신버전에서는 server_id 이지만 예전 버전에서는 server-id 일 수 있다.

[mysqld]
server_id = 2

 
https://parkadd.tistory.com/127
 
 
===============================================================================
 
 
 
최종 결론
my.cnf를 다음과 같이 수정했다.

[mysqld]
log_bin = MYSQL-BIN.log
expire_logs_days = 10
server_id = 2
binlog_format = ROW
binlog_checksum = NONE
binlog_row_image = FULL
log_slave_updates = TRUE
max_binlog_size = 100M

systemctl restart mysqld

다음과 같이 잘 적용된 것을 확인

 
 
클라우드 워치 관찰결과
 

클라우드 워치를 본 결과 문제는 grant all privileges on 3zoDB.* to 'root'@'%';  부분이었다.
처음엔 특정 DB 권한만 줬는데, 그러면 쓰기 권한 등이 없나보다.
그 이상의 권한이 필요하다길래 그냥 grant all privileges on *.* to 'root'@'%';  날려버리니까 바로 진행률 100퍼 띄웠다.
 
 
 
7. 온프레 to AWS 결과 확인
 
아래와 같이 상시 복제 진행중으로 싱크상태이고,
원래 추가해놨던 3zoDB - member랑 dd - bb가 목록에 잘 들어가 있는것을 볼 수 있다.

 
온프레미스의 DB에 testDB를 추가하고 hohoho테이블도 넣어보니 바로 aws상 목록에 반영된다.

 
 
 
 
8. 쌍방 데이터 교환 테스트
 
위에서 테스트한 결과와 역으로 Aurora DB쪽에서 내용물을 추가해보고 온프레미스쪽 DB에 반영되는지 보고싶은데,
직접 오로라 DB기능을 못찾아서 EC2를 만들어 간접 접속해보려고 한다.
대충 테스트용으로 DB 쪽과 연결된 파블릭 서브넷에 EC2 한대 만들어주고 접속했다.
 
sudo yum -y install mysql

 
 
(오로라 DB 엔드포인트 주소 넣어주면 된다)
mysql -u admin -padmin123 -h mydbcluster-instance-1.cm4l5aeqvupc.ap-northeast-2.rds.amazonaws.com
 
직접 들어가서 보니, 온프레 쪽에서 받은 DB정보를 확실히 잘 갖고 있다.

 
그러나 오로라쪽에서 만든 정보들은 온프레미스로 안넘어간다.

어찌보면 타겟과 소스를 지정한 순간 일방통행이라는 것을 직감하긴 했다.
 
 
 
 
 
9. 온프레미스 from AWS 역방향 DMS 설정
 
여기서 포기할순 없으니까 인터넷에서 자료를 찾아봤다.
그런데 AWS의 상술때문인지 온프레미스에서 AWS상으로 마이그레이션 목적으로 DMS를 쓰는 자료는 좀 있어도
반대로 AWS상에서 온프레미스쪽으로 마이그레이션 하는 자료는 코빼기도 없었다.
영문자료도 거의다 뒤져보다가 포기할 때 쯤 문득 아이디어 하나가 떠올랐다.
그냥 실패 한번 해보자는 식으로 내가 떠오른 아이디어를 어떤 참고자료도 없이 실행해 보기로 했다.
 
DMS 테스크에서 타겟 엔드포인트와 소스 엔드포인트를 반대로 설정해서 시도해보는 것이다!
 
그래서 DMS 테스크에서 완전 거꾸로된 테스크를 하나 더 생성해봤다.
근데 구동해보니 실패했다.
클라우드 워치를 열어봤더니 아래와같이 또 그 환경변수들 설정 해달란다.

 
아까 6.트러블슈팅 목차에서 설정한 Mysql DMS 선행 필요 환경설정 문서를 다시 꺼내왔다.
이 가이드대로 아까 온프레미스 설정을 다 바꿔줘서 잘 됐다.
근데 aws상의 DB에도 해야한다니 좀 막막해진다.
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html
 
 
근데 오로라 DB에 접속해서 my.cnf파일을 편집을 할수가 없는데 어떻게 할까 알아봤더니,
오로라 DB 페이지의 파라미터 그룹 편집을 통해서 아까의 환경변수를 바꿔줘야 한다는 것을 배웠다.
 
아래 문서는 공식 RDS 파라미터 사전 문서다.
환경변수별로 어떻게 설정할지 주석이 달려있어서 파라미터 그룹을 편집할 때 매우 유용하다.
https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html

Amazon Aurora MySQL 참조 - Amazon Aurora

 
log-bin은 특히 gui로 파라미터로 수정이 불가능이라 이 문서가 없었다면 진짜 헤멜뻔했다.

 
클러스터 파라미터 그룹을 하나 만들어서 환경변수를 맞게 수정해줬다.
(나같은 경우는 DB가 리더랑 라이터로 나뉜 클러스터 그룹이라 그냥 파라미터 그룹엔 원하는 환경변수가 없다!
꼭 클러스터 파라미터 그룹으로 해야한다.)
제일 중요한 bin-log값은 EC2로 오로라DB에 접근해서 보면 off 상태였는데 (show variables like '%log_bin%';)
아래와 같이 binlog_format값을 off말고 다른 값으로 바꿔주고, Aurora DB를 재부팅해주면 on이 된다.
아래 값 말고 다른 변수들도 6.트러블슈팅 목록 참고해서 가이드랑 대조한뒤 다 바꿔주자.

 

만든 클러스터 파라미터그룹은 바로 AWS DB 수정 눌러서 그룹을 만든걸로 바꿔끼워준다.

그리고 클러스터 전체를 재부팅 해야한다.

 

 

 ec2 - aurora db로 다시 접속.
log-bin이 on으로 바뀌었다.

 
다시 시도해보니 DMS 테스크도 잘 올라갔다.

 
테스트삼아 successfulEND라는 DB를 AuroraDB쪽에 생성하고 온프레쪽 DB에서 확인하니 바로 업로드 돼있다.
자료 하나도 못찾아서 멋대로 한번 시도해 본 건데 해결이 되다니
운이 좋다.
 

 
온프레미스 DB <----> AWS DB 양방향 실시간 싱크 성공.
 
 

 

 

 

 

 

======================================================================

 

DMS를 다른 환경에도 계속 적용중인데,

 

테스크가 이런 오류 띄웠다. (진행률은 100퍼센트지만 오류가 1개 있는건 찜찜하다)

2023-03-12T06:27:44 [TARGET_LOAD     ]E:  RetCode: SQL_ERROR  SqlState: HY000 NativeError: 1142 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.12]TRIGGER command denied to user 'mysql.sys'@'localhost' for table 'sys_config' [1022502]  (ar_odbc_stmt.c:4828)

 

 

mysql.sys ?? 이건 원래 있는 유저인가보다.

그래서 해당 테이블 (혹시모르니까 그냥 전체테이블) 에 대한 권한을 줘버렸다.

GRANT TRIGGER ON *.* TO 'mysql.sys'@'localhost';