[MYSQL] MySQL to MySQL DBLINK (FEDERATED 기능)

MySQL의  FEDERATED Engine을 사용한 방법인데, 타 DB에서 사용하는 DBLINK의 개념보다는 Table Mirroring에 가깝다.

 

1. FEDERATED 설정

1) show engines;

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

-. FEDERATED 가 YES 로 되어야한다.

 

2) FEDERATED Engines 존재하지않으면 설치

mysql> INSTALL PLUGIN federated SONAME 'ha_federated.so';

 

3) FEDERATED Support가 NO인 경우 

[mysqld] 아래에 federated 추가 federated

 

4) Link할 서버 접속 정보 생성

# 생성
CREATE SERVER link_test
FOREIGN DATA WRAPPER mysql
OPTIONS (
  USER '원본DB 계정',
  PASSWORD '원본DB 계정 패스워드',
  HOST '원본DB IP',
  PORT 3306,
  DATABASE '원본DB명'
);

# 수정
ALTER SERVER link_test
  OPTIONS (
  USER '수정 계정',
  PASSWORD '수정 계정 패스워드',
  HOST '수정DB IP',
  PORT 3306,
  DATABASE '수정DB명'
);

# 삭제
DROP SERVER [링크서버명]



# LINK SERVER 조회
use mysql;
select * from servers;
use TEST; -- 확인 후 돌아옴

 

2. Test

CREATE SERVER link_test
FOREIGN DATA WRAPPER mysql
OPTIONS (
  USER 'xxxxx',
  PASSWORD 'xxxxxx',
  HOST '2xx.x.xx.xx',
  PORT 5550,
  DATABASE 'xxxxx'
);

use mysql;

mysql> select * from servers;
+-------------+-------------+---------+-----------+-----------+------+--------+---------+-------+
| Server_name | Host        | Db      | Username  | Password  | Port | Socket | Wrapper | Owner |
+-------------+-------------+---------+-----------+-----------+------+--------+---------+-------+
| link_test   | 2xx.x.xx.xx | xxxxxxx | xxxxxxxxx | xxxxxxxxx | 5550 |        | mysql   |       |
+-------------+-------------+---------+-----------+-----------+------+--------+---------+-------+



mysql> CREATE TABLE testTable(                             
    ->   id INT(11) ,               
    ->   name VARCHAR(20),    
    ->   date  DATETIME           
    -> )ENGINE=FEDERATED 
    -> CONNECTION='mysql://xxxxx:xxxxx@2xx..xxx.xx:5550/xxxxx/testtable';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select count(*) from xxxxx.testtable;
+----------+
| count(*) |
+----------+
|      160 |
+----------+
1 row in set (0.08 sec)