Mysql federated (oracle dblink 와 같은 기능) - 물리적 다른 DB 연결

 

https://jang8584.tistory.com/245

Mysql federated (oracle dblink 와 같은 기능) - 물리적 다른 DB 연결

 

1. FEDERATED 를 사용하는 이유

 

 - mysql 에서 물리적으로 같은 서버면, 조회 권한만 주면 alias를 앞에 붙여서 다른 db 끼리 join 및 조회를 할수가 있다

 

ex) select a.a1 from db1.db_test1 a inner join db2.a1 b on a.a1=b.test1

 

 하지만, 물리적으로 다르면, 같이 조회를 못하고 각각 할 수 밖에 없다.

 

어쩔수 없이 다른 물리적 DB를 같이 조회를 하고 싶으면, 메인이 되는 DB 서버에 원격으로 붙는 형식을 사용할 수 있게 하는게 federated 기능이다.

 

* oracle에서 dblink와 유사한 기능이다

 

 

2. 방법

 

- DB1 : 원본 데이터 DB

- DB2 : 원격으로 원본 데이터를 가져올 DB

 

우선 DB2(원격) 에만 federated 기능을 키면 된다.(원본 DB에는 필요없음)

 

A. DB2에 root로 접속을 하여, 

 

SHOW ENGINES 명령으로 확인

 

-> FEDERATED 가 있으면 쉽게 사용

-> 없으면  install plugin federated soname 'ha_federated.so' 로 설치

 

B. 설치(또는 이미 설치 되어있으면) 후 DB2 의 my.cnf 수정

 -> [mysqld] 아래에 federated 라는 단어를 추가한다.

 

C. mysql 재시작 후, 다시 SHOW ENGINES 로 확인

 -> Support 컬럼만 'YES' 로 바뀌어 있으면 준비가 완료 된 것이다.

 

3. 사용 및 테스트

 

DB1 에서 우선 테이블 생성(일반적인 create 문, 물론 이미 생성된 table을 사용해도 된다. 테스트를 위해 새로 create)

 

CREATE TABLE `db_test1` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL,
  `a3` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

DB2 에서 federated 테이블을 생성해야 한다.

 

CREATE TABLE `db_test1` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL,
  `a3` varchar(11) DEFAULT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8

CONNECTION='mysql://접속ID:패스워드@도메인(또는 IP):포트/datebase명(ex:DB1)/테이블명(ex:db_test1)';

 

주의)

1. 컬럼명을 똑같이 맞춰줘야 한다.

2. 원본 데이터 db에 스키마가 바뀌면, federated 테이블을 drop하고 다시 생성해야 한다.

 -> federated 테이블은 스키마 변경 불가. 드랍하고 다시 create해야함

3. 패스워드에 '@' 골뱅이가 들어가면 안된다. 패스워드 변경해야함

 

 

장점)

1. 원본 데이터 DB- 원격 데이터 DB 간에 delay가 없다.

2. 원격 데이터DB 에 select,insert,delete,update 모두 가능하다. 바로바로 원본 db에 적용됨

3. 원격 데이터 DB를 drop 해도, 원본DB는 drop이 안된다. 한마디로 DB에 DDL은 불가능 하고, DML은 가능하다(장점이자 단점이다)

 

 

**) 실무에서, DB 용량과 구조가 늘어나면서 DB를 추가적으로 늘려야 하는 상황이 왔다. 서로 다른 물리적 DB를 각각 connection 맺으면서 개발하기엔 고칠게 너무 많았다. 하지만 federated를 알게 되서, 한방에 해결이 되었다. 

오늘도 새로운 걸 배우고, 공유한다...

 

 

 

 

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

일반적으로 위에서 언급한대로 하면, 리모트 하는 서버에서 원본 데이터의 db 접속 정보가 다 노출 된다.

그래서 원격지에서 server를 추가 해서, 접속 정보를 숨길수 있다.

 

A 서버(원본 서버) -> B서버(원격으로 접속할 서버)

 

라고 하면,

 

B서버에 

 

CREATE SERVER 서버명칭

FOREIGN DATA WRAPPER mysql

OPTIONS(

USER '접속유저명'

,DATABASE '접속할 DB'

,PASSWORD '비밀번호'

,HOST 'A서버 HOST'

,PORT A서버 DB PORT);

 

EX)

CREATE SERVER test_db

FOREIGN DATA WRAPPER mysql

OPTIONS(

USER 'test_user'

,DATABASE 'test_database'

,PASSWORD 'aa%^%aa'

,HOST '10.10.10.111'

,PORT 3306);

 

라고 하면 use mysql; 한 후에 

SELECT * FROM servers; 으로 확인

 

이 후에 

 

CREATE TABLE `db_test1` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL,
  `a3` varchar(11) DEFAULT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8

CONNECTION='서버명칭/접속할테이블명'; 으로 하면 된다

 

ex) ... CONNECTION='test_db/test_table'; 

출처: https://jang8584.tistory.com/245 [개발자의 길:티스토리]