Last Updated on 9월 13, 2021 by Jade(정현호)
Oracle Cloud 의 MySQL Database Service(MDS) 에 IDC 나 다른 클라우드에서 데이터를 이관(Migration) 하는 방법은 여러가지가 있을 것이고 통상적으로는 VPN 이나 전용망 그리고 오브젝트 스토리지 등이 필요 할 것 입니다.
포스팅에서는 VPN 이나 전용망, 오브젝트 스토리지 등이 없는 상황에서 타 클라우드나 IDC의 MySQL의 데이터를 이관(Migration) 하는 내용과 Oracle Cloud MDS 인스턴스를 Replica(Target) 으로 구성 하는 내용에 대해서 기술되어 있습니다.
[참조] 포스팅에서는 사전에 생성된 MDS 인스턴스를 사용합니다 MDS 의 생성 등은 이전의 포스팅에서 확인 하시면 됩니다
Contents
1. Master DB 설정
먼저 마스터DB 부터 설정을 진행 하겠습니다.
1-1 GTID 적용
MDS 인스턴스를 Replica 노드로 사용까지 할 계획임으로 먼저 GTID 적용 설정을 하겠습니다.
/etc/my.cnf 파일에 아래 파라미터를 추가 후 MySQL 재시작 합니다.
[mysqld] log-bin=binlog gtid-mode=ON enforce-gtid-consistency=ON
1-2 Replication 유저 생성
Master 노드(Source) 에서 유저를 생성 합니다.
mysql> create user 'repl_user'@'%' identified by 'Repl_user12#$'; mysql> grant replication slave,replication client on *.* to 'repl_user'@'%';
2. Data Migration
먼저 IDC 나 클라우드내 있는 MySQL 의 데이터를 Dump(export) 하고 Load(import) 를 하는 과정을 진행 하겠습니다.
2-1 dumpInstance
디렉토리를 생성 후 데이터를 dump 하도록 하겠습니다.
[root]# rm -rf /root/backup/inst_dump
[root]# mkdir -p /root/backup/inst_dump
[root]# mysqlsh root@localhost -e 'util.dumpInstance("/root/backup/inst_dump",
{ocimds: true, threads: 8, showProgress: true, compatibility:
["force_innodb", "strip_definers","strip_restricted_grants", "strip_tablespaces"]})'
# 가로 길이를 위해서 개행을 하였지만, 실제로는 한줄로 입력 해야 합니다.
[참고] MySQL Shell dump utility 는 아래 포스팅에서 내용을 확인하시면 되며, dumpInstance 에서는 대소문자를 구별해서 사용해야 합니다.
2-2 SSH Tunneling
이전 포스팅에서 언급한 SSH Tunneling 를 통해서 진행할 것입니다. 그림으로 표현 하면 아래와 같습니다.
관련된 자세한 내용은 아래 포스팅을 참조하시면 됩니다.
bastion host 와의 SSH Tunneling 은 아래 와 같이 실행 할 수 있습니다
[root]# ssh -f -N -L 3333:10.0.1.20:3306 ubuntu@배스천호스트IP [root]# netstat -antp | grep 3333 tcp 0 0 127.0.0.1:3333 0.0.0.0:* LISTEN 7447/ssh tcp6 0 0 ::1:3333 :::* LISTEN 7447/ssh
* 3306 포트는 MySQL 서버가 이미 사용중임으로 다른 포트(3333) 로 생성
[참고] 베스천 호스트의 SSH 의 보안 관련 해서는 아래 포스팅을 참조하시면 됩니다.
.
2-3 loadDump
위에서 터널링으로 베스천 호스트를 통해 MDS 인스턴스에 접속 할 수 있는 환경은 구성이 되었으므로 export 된 데이터를 Load 하도록 하겠습니다.
[root]# mysqlsh master@127.0.0.1 -P 3333 -e
'util.loadDump("/root/backup/inst_dump",
{threads: 8,ignoreExistingObjects:true,
resetProgress:true,updateGtidSet:"replace"})'
# 가로 길이를 위해서 개행을 하였지만, 실제로는 한줄로 입력 해야 합니다.
• util.loadDump 진행 로그 예시
Please provide the password for 'master@127.0.0.1:3333': ************ Save password for 'master@127.0.0.1:3333'? [Y]es/[N]o/Ne[v]er (default No): yes Loading DDL and Data from '/root/backup/inst_dump' using 8 threads. Opening dump... Target is MySQL 8.0.23-u2-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23 Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `xxxx` [Worker001] Executing DDL script for `xxxx`.`table1` [Worker002] Executing DDL script for `xxxx`.`table2` [Worker006] Executing DDL script for `xxxx`.`table3` [Worker005] Executing DDL script for `xxxx`.`table4` <....중략....> MySQL SQL > show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.0015 sec) MySQL SQL > show databases; +--------------------+ | Database | +--------------------+ | xxxx | <--- New Database | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.0014 sec)
입력 대상이 오라클 클라우드로 식별 되면 아래와 같이 메세지가 확인 됩니다.
Target is MySQL 8.0.23-u2-cloud (MySQL Database Service)
2-4 sys.get_gtid_purged
Load가 완료 되었다면 Replica 노드인 MDS 인스턴스에서 sys.set_gtid_purged 수행이 필요 합니다.
먼저 dump 된 파일에서 gtidEx 정보를 확인 합니다.
[root]# cd /root/backup/inst_dump [root]# cat @.json | grep gtidEx "gtidExecuted": "13abea0b-7b7b-11eb-b27b-08002761d7ed:1",
조회 후 MDS 인스턴스 에서 아래와 같이 sys.set_gtid_purged 를 수행 합니다.
mysql> show variables like '%gtid%';
+----------------------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_gtid_assignment_block_size | 1 |
| gtid_executed | dc6e2077-7f3f-11eb-84de-02001708c786:1-143 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------------------+--------------------------------------------+
10 rows in set (0.00 sec)
mysql> call sys.set_gtid_purged("13abea0b-7b7b-11eb-b27b-08002761d7ed:1-11");
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%gtid%';
+----------------------------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+---------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_gtid_assignment_block_size | 1 |
| gtid_executed | 13abea0b-7b7b-11eb-b27b-08002761d7ed:1-11, |
| | dc6e2077-7f3f-11eb-84de-02001708c786:1-143 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 13abea0b-7b7b-11eb-b27b-08002761d7ed:1-11 |
| session_track_gtids | OFF |
+----------------------------------------------+---------------------------------------------+
여기 까지 진행 되었다면 데이터 마이그레이션이 완료 되었습니다.
3. 접속 설정(방화벽/SSH 터널링)
Chapter3 에서 진행하는 내용은 Oracle Cloud -> IDC/타 클라우드 의 연결을 SSH 터널링으로 진행하기 위해서 MDS Instance 에서 베스천 호스트로 연결을 설정하는 내용입니다.
그렇기 때문에 구성하는 환경에 따라 Outbound Replication 설정에서 이 과정은 생략을 하셔도 됩니다.
3-1 VCN 설정
3-1-1 먼저 VCN 설정 -> 공용 서브넷 으로 이동 합니다.
3-1-2 Default 보안 목록 으로 이동 합니다.
3-1-3 수신 규칙 추가 를 클릭 합니다.
3-1-4 수신 규칙 에서의 소스 CIDR 은 전용 서브넷 CIDR 을 입력 하고, 포트는 SSH 터널링 으로 연결할 3333 포트를 입력 하겠습니다.
3-2 OS 방화벽 오픈
클라우드사 마다 설정이 약간씩은 다르며, 오라클 클라우드는 기본적으로 OS 방화벽이 설정되어 있습니다. CentOS 에서는 firewalld 를 Ubuntu 에서는 iptables 로 동작하고 있습니다.
• 우분투 기준 - iptables 설정
# iptables 설정에 필요한 패키지 설치 User$ sudo apt-get install iptables-persistent netfilter-persistent # 3333 포트 오픈 User$ iptables -I INPUT -p tcp -s 10.0.1.0/24 --dport 3333 -j ACCEPT # iptables 변경 내용 저장 User$ sudo netfilter-persistent save
.
3-3 SSH 터널링 설정
MDS 인스턴스에서 다른 위치의 Master DB 와의 연결을 위하여 베스천 호스트 에서 IDC 서버 호스트로의 SSH 터널링을 연결하도록 하겠습니다.
# 베스천 호스트에서 실행 ubuntu@mds-bastion:~$ ssh -f -N -L 0.0.0.0:3333:localhost:3306 opc@IDC서버IP ubuntu@mds-bastion:~$ sudo netstat -antp | grep 3333 tcp 0 0 0.0.0.0:3333 0.0.0.0:* LISTEN 26384/ssh
4. Outbound Replication
OracleCloud 의 MDS 에서는 Replication 은 채널 을 통해서 복제가 수행됩니다.
4-1 채널 생성
4-1-1 MDS 인스턴스 하단의 채널 -> 채널 생성 을 통해 진행 하거나 
채널 생성 시작은 DB 인스턴스 외 메뉴에서도 들어갈 수 있습니다
4-1-2 채널 이름을 입력 합니다.
4-1-3 소스 설정에 베스천 호스트 IP 를 입력하고, 터널링에 사용할 포트를 입력, 그리고 위에서 생성한 복제 유저 정보를 입력 합니다.
4-1-4 DB 시스템 선택에서 Replica 노드를 선택 후 채널 생성 을 클릭 합니다.
4-1-5 정상적으로 실행이 완료되었다면 아래와 같이 복제가 활성화 될 것 입니다.
5. Replication 확인
복제 설정이 완료 되었다면 먼저 Master 노드에서 테이블과 데이터를 입력하여 확인 및 Master 노드와 Replica 노드 에서 각각 상태를 조회 합니다.
5-1 데이터 복제 확인
# Source - Master
mysql> create database test;
mysql> use test;
# Target - Replica
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| redologs |
| sys |
| test | <-------
| var |
+--------------------+
# Source - Master
mysql> CREATE TABLE emp (
empno int NOT NULL AUTO_INCREMENT,
ename varchar(20),
job varchar(20),
mgr smallint,
hiredate date,
sal numeric(7,2),
comm numeric(7,2),
deptno int,
CONSTRAINT pk_emp PRIMARY KEY ( empno )
) engine=InnoDB;
insert into emp values( 7839, 'KING', 'PRESIDENT', null, STR_TO_DATE ('17-11-1981','%d-%m-%Y'), 5000, null, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('1-5-1981','%d-%m-%Y'), 2850, null, 30);
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('9-6-1981','%d-%m-%Y'), 2450, null, 10);
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('2-4-1981','%d-%m-%Y'), 2975, null, 20);
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, DATE_ADD(STR_TO_DATE('13-7-1987','%d-%m-%Y'),INTERVAL -85 DAY) , 3000, null, 20);
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('3-12-1981','%d-%m-%Y'), 3000, null, 20);
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980','%d-%m-%Y'), 800, null, 20);
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-2-1981','%d-%m-%Y'), 1600, 300, 30);
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-2-1981','%d-%m-%Y'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981','%d-%m-%Y'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('8-9-1981','%d-%m-%Y'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, DATE_ADD(STR_TO_DATE('13-7-1987', '%d-%m-%Y'),INTERVAL -51 DAY), 1100, null, 20);
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('3-12-1981','%d-%m-%Y'), 950, null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-1-1982','%d-%m-%Y'), 1300, null, 10);
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
# Target - Replica
mysql> use test;
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
.
5-2 복제 상태 확인
### Master 노드
mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000014
Position: 2392
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 13abea0b-7b7b-11eb-b27b-08002761d7ed:1-11
1 row in set (0.00 sec)
mysql> show replicas\G
*************************** 1. row ***************************
Server_Id: 678801625
Host:
Port: 3306
Source_Id: 1
Replica_UUID: dc6e2077-7f3f-11eb-84de-02001708c786
1 row in set (0.00 sec)
### Replica 노드
mysql> show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 10.0.0.2
Source_User: repl_user
Source_Port: 3333
Connect_Retry: 60
Source_Log_File: binlog.000014
Read_Source_Log_Pos: 2392
Relay_Log_File: relay-log-replication_channel.000002
Relay_Log_Pos: 409
Relay_Source_Log_File: binlog.000014
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 2392
Relay_Log_Space: 632
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 13abea0b-7b7b-11eb-b27b-08002761d7ed
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 0
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 13abea0b-7b7b-11eb-b27b-08002761d7ed:1-11,
dc6e2077-7f3f-11eb-84de-02001708c786:1-143
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: replication_channel
Source_TLS_Version: TLSv1.2,TLSv1.3
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace: mysql
mysql> SELECT
concat(conn_status.channel_name, ' (', worker_id,')') AS channel,
conn_status.service_state AS io_state,
applier_status.service_state AS sql_state,
format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0",
abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,
format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,
format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,
format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,
conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,
applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction
FROM
performance_schema.replication_connection_status AS conn_status
JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name;
+-------------------------+----------+-----------+---------+----------------+-------------------+------------+-------------------------+--------------------------+
| channel | io_state | sql_state | latency | transport_time | time_to_relay_log | apply_time | last_queued_transaction | last_applied_transaction |
+-------------------------+----------+-----------+---------+----------------+-------------------+------------+-------------------------+--------------------------+
| replication_channel (1) | ON | ON | 0 ps | 0 ps | 0 ps | 0 ps | | |
| replication_channel (2) | ON | ON | 0 ps | 0 ps | 0 ps | 0 ps | | |
| replication_channel (3) | ON | ON | 0 ps | 0 ps | 0 ps | 0 ps | | |
| replication_channel (4) | ON | ON | 0 ps | 0 ps | 0 ps | 0 ps | | |
+-------------------------+----------+-----------+---------+----------------+-------------------+------------+-------------------------+--------------------------+
6. Conclusion
Oracle Cloud의 SSH 터널링을 활용한 MDS 인스턴스에 데이터 마이그레이션 과 그리고 그 데이터를 이용한 Replication 을 구성하여 MDS 인스턴스를 Replica 로 구성하였습니다.
Read/Write 인 Master 노드는 기존의 IDC에서 사용 환경에서 백업이나 DR 형태로 클라우드에 복제 하는 형태로도 사용할 수 있거나 클라우드로 변경하기 위해서 데이터 복제를 구성 후 Application의 Connection 정보를 Replica 노드로 변경 하여 클라우드로 롤 체인지 하는 등으로 사용 할 수 있을 것 같습니다.
7. Reference
Reference link
dasini.net/replicate-mysql-database-service [Link]
docs.oracle.com/replication [Link]
docs.oracle.com/Replication/GUID-94149D15 [Link]
dev.mysql.com/mysql-shell-load-dump [Link]
lefred.be/using-mysql-database-service [Link]
관련된 다른글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
