Mysql dblink

1 Mysql federated[편집]

oracle dblink 와 같은 기능
물리적 다른 DB 연결

1.1 개요[편집]

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 기능.

1.2 사용 방법[편집]

DB1(소스) : 원본 데이터 DB
DB2(타겟) : 원격으로 원본 데이터를 가져올 DB

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

1.3 federated 기능 설치 확인[편집]

DB2(타겟)에 root로 접속을 하여 SHOW ENGINES 명령으로 확인

FEDERATED 가 있으면 쉽게 사용
없으면 install plugin federated soname 'ha_federated.so' 로 설치
1. 설치(또는 이미 설치 되어있으면) 후 DB2 의 my.cnf 수정
2. [mysqld] 아래에 federated 라는 단어를 추가한다.
3. mysql 재시작 후, 다시 SHOW ENGINES 로 확인

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

1.4 사용 및 테스트[편집]

DB1(소스) 에 테이블 생성 or 생성된 table

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 테이블을 생성.

생성된 DB1(소스) table 에서 sql문을 추출 하여 타겟에 똑같은 테이블 생성하면 간편.

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.4.1 생성시 주의 사항[편집]

1. 컬럼명을 똑같이 맞춰줘야 한다. 2. 원본 데이터 db에 스키마가 바뀌면, federated 테이블을 drop하고 다시 생성해야 한다.

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

3. 패스워드에 '@' 문자가 들어가면 절대 안됨.

1.5 사용시 장/단점[편집]

원본 데이터 DB- 원격 데이터 DB 간에 delay가 없다.
원격 데이터DB 에 select,insert,delete,update 모두 가능하다. 바로바로 원본 db에 적용됨
원격 데이터 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';

DB CAFE

목차