[MySQL] Federated Engine (Table)

Oracle 엔지니어겸 DBA 롤을 진행하다 보니 DB Link로 인해 종종 문제가 생긴 적이 많았다.

scn 이 Max치가 되어서 장애가 생긴적, 동일 DB 내에서 DB Link를 사용해서 불필요한 컨넥션 증가 등등...

어쩔때에는 db link 갯수가 한 DB내에 80개나 되는 경우도 있어서 당황한 적도 많았다.

그래서 DB Link만 생각해도 짜증나는 경우가 더 많았다.

하지만, MySQL DBA로 업무를 진행하다 보니 오히려 여기서는 DB Link 기능 없이 

Python 으로 일일이 개발해서 진행하는 경우가 허다하게 많았다.

처음부터 Python 개발을 했었다면 모르겠지만 Python을 익히면서 DBA 업무를 하다보니 괜히 개발이 더더더 싫어졌다.

(컴퓨터과에서 공부하며 DBA 가 되고 싶었지만 개발이 싫어서 DBA가 하고 싶은 부분도 상당히 많았기에...)

어쨋든 MySQL 에서도 Federated storage engine 이라는 것이 있어서 이 기술을 사용하면 db link 역활을 할 수 있다는 것을 찾아서 

이에 대해 간략한 설명과 테스트한 부분을 소개하고자 한다.

---

Federated Storage Engine 이란?

FEDERATED 스토리지 엔진을 사용하면 리플리케이션이나 클러스터 기술을 이용하지 않고도 원격의 MySQL 데이터베이스에 접근할 수 있다. 서비스 운영을 위해 여러 데이터베이스를 사용하는데, 한 데이터베이스의 데이터를 다른 데이터베이스들이 참조하여 사용할 때 활용할 수 있다. Oracle의 DBLink와 유사한 기능이라고 한다.

FEDERATED 엔진은 MySQL에 기본으로 설정되어 있지 않기 때문에 이를 사용하기 위해서는 별도의 작업이 필요하다. 검색을 해보면 처음에 설치할 때 부터 –with-federated-storage-engine 옵션을 주어야 한다는 등의 이야기 등이 있지만, 실제로는 재설치를 하거나 할 필요는 없다.

참조 : http://blog.weirdx.io/post/3503

그럼 나의 테스트 환경이다.

현재 회사 테스트 DB 장비와 나의 Local DB 를 연결하였다.

회사 테스트 DB는 5.0.37 이다

나의 local DB 는 5.7.12 이다

테스트는 개발장비의 데이터를 내 pc 에서 select 하고자 한다.

1. Engine 이 설치되어 있는지 확인해 본다. (Local DB)

 - 보통은 아래와 같이 Federated engine 이 NO 라고 되어 있는 것을 확인할 수 있다.

이 부분을 YES 로 변경해 보자.

my.cnf 또는 my.ini 파일에서 아래와 같이 추가해 보자

FEDERATED    만 추가한 후 저장하자.

(내 PC는 윈도우 이기에, my.ini 파일을 수정하였다.)

이 후, 적용을 위해서 DB를 재 기동 한다.

다시 Engine을 확인해 본다.

Federated가 YES 로 되어 있는 것을 확인 할 수 있다.

2. Engine을 이용하여 테이블을 추가해 보자.

 - Federated 엔진을 이용한 테이블은 쉽게 생각해 보면 원격 테이블을 가져와서 보여주는 것을 의미하며, 

   DML 작업을 진행하게 되면 원격지의 테이블 또한 변경이 되기에 굉장히 조심하자!!

* 원격지의 Table과 동일한 형태로 만들자

* 기본적으로 원격지 DB로 접속이 되는지 확인하자.(IP / Port 허용 여부)

* Oracle DB Link와 같이 원격지로 접근할 수 있도록 ID와 패스워드를 알아야 한다.

기본 문구는 아래와 같다.

CREATE TABLE `테이블명` (

  `컬럼1` 속성, 

  `컬럼2` 속성,

  `컬럼3` 속성

) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://원격지접속ID:패스워드@원격지DB의IP:포트/DB명/테이블명'

;

ex) 

CREATE TABLE `federated_table` (

  `nID` int(11) NOT NULL auto_increment,

  `PlayerID` varchar(36) NOT NULL default '',

  `Password` varchar(32) NOT NULL default '',

  `Name` varchar(20) NOT NULL default ''

) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:password@192.168.1.70:3306/TestDB/Player'

;

3. 이렇게 추가가 정상적으로 되면 테스트 진행해 보자.

local 에서 federated_table 를 select 해 보자.

워낙 데이터가 많아 이렇게 먼저 비교를 해 보았다.

이번에는 데이터 한건에 대해서 DML 작업을 진행해 보자.

이 한건을 이용하여 Remote에서 지워지면 local 에서는 어떻게 되는지 보자.

지워져서 검색이 안되는 것을 확인할 수 있다.

다시 Insert를 진행해 보자.

local 에서 보여 지는 것을 확인할 수 있다.

이번에는 반대로 테스트 해 보자.

Local 에서 작업을 진행하면 원격지 DB에서는 어떻게 진행되는지 Delete 작업 하나만 해 보자.

Local 에서 삭제 작업을 진행 하였더니, 원격지에서 삭제 된 것을 확인할 수 있다.

DB Link를 대신 할 수 있는 분명 좋은 기능인 것을 확인할 수 있다.

이 작업을 통해서 파이썬을 이용한 작업을 대체할 수 있지 않을까 생각해 본다.

원격지의 필요한 것에 대해서 미리 테이블을 생성한 후 프로시저 등으로 작업을 진행하고, 스케줄러를 통해 주기적으로 한다면,

crontab 에 걸어서 사용을 하고 등의 필요성이 줄어들고, 동시에 보안도 해결되지 않을까 고민해 본다.

---

추가로 아래 사이트를 참조해도 좋을 듯 싶다.

http://blackbull.tistory.com/31

[HELP FEDERATED ENGINE]
길다.. 생략..... -0-;;;;;;;

[FEDERATED ENGINE 설명]
Federated Engine은,
실제적으로 물리적인 저장공간을 확보하여 데이터를 복사해서 갖는 것이 아니다.
무슨 말이냐 하면... 흠...

마치 C에서의 포인터나, C++, Java에서의 참조형 처럼,
단순히 틀에 대한 정보만을 지니고, 
그것을 원거리에서 MySQL의 API를 이용하여 접근하는 방식이다.

그리하여, 물리적으로 격리되어 있는 다른 MySQL 서버의 테이블로의 접근이 
로컬 내에서 가능하게 만들어준다.
(5.0 에서는 MySQL 서버간에만 사용가능하며, 추후에 이기종 DB 와의 연계도 개발할꺼라는데...
언제나 될런지 -0-;;;)

물론, 
DBLink와 마찬가지로 물리적 거리감은 실행 속도의 하락과
DDL(Data Definition Language) 같은 쿼리는 적용 되지 않는 부효과를 발생시킨다.

그런 이유로, 
만약 로컬에서 DROP TABLE 명령어를 사용하여 테이블을 DROP 시킨다면,
로컬만 DROP되고, 원거리의 TABLE에는 영향을 미치지 않는다.


[FEDERATED ENGINE 사용방법]
먼저, 원본 테이블이 어딘가 존재해야 한다.

# 원본 테이블
USE testdb;
CREATE TABLE testtbl (
 id INT AUTO_INCREMENT PRIMARY KEY,
 col1 VARCHAR(100) DEFAULT NULL,
 col2 VARCHAR(200) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

그리고 난 후에야, 로컬에 동일한 구조로 테이블을 생성한다.
단, 이번에는, 
****************************************************************************************
1. ENIGINE :  ENGINE=FEDERATED
2. CONNECTION 정보 : 'mysql://아이디:패스워드@IP또는DOMAIN:PORT/DB명/TABLE명'
****************************************************************************************

# FEDERATED 된 테이블
CREATE TABLE testtbl (
 id INT AUTO_INCREMENT PRIMARY KEY,
 col1 VARCHAR(100) DEFAULT NULL,
 col2 VARCHAR(200) DEFAULT NULL
) ENGINE=FEDERATED 
  DEFAULT CHARSET=UTF8
  CONNECTION='mysql://scott:tiger@192.168.10.2:3306/testdb/testtbl;

라고 만들면,
그 때부터 INSERT, DELETE, UPDATE, SELECT 가 가능해지며, 
INDEX를 사용할 수 있게 된다.

[FEDERATED 주의할 점]
1. 반드시 #원본테이블 처럼, 대상이 되는 테이블이 먼저 존재해야 한다.
2. Transaction은 지원되지 않는다.
3. #원본테이블의 데이터를 제외한 필드명/타입/구조 등의 변경 사항은 
   #FEDERATED 된 테이블에 적용이 되지 않기 때문에,
   수동으로 변경해 주어야 한다.
4. Query Cache는 이용할 수 없다.
5. !!!!! 중요 !!!!! SHOW CREATE TABLE #FEDERATED된 테이블; 
   입력시 #원본테이블로의 접속정보가 고스란히 노출 되므로, 주의가 요구된다.