FEDERATED 엔진을 이용한 DB Link

1. FEDERATED 엔진 설치 여부 확인

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

* 위와 같이 목록에 FEDERATED 엔진이 있다면 설치 되어 있는 것이다.

 

2. FEDERATED 엔진이 설치 되어 있지 않으면 설치

mysql> install plugin federated soname 'ha_federated.so';

 

3. 위 목록에 FEDERATED 엔진이 있지만 Support 필드 값이 NO라면 엔진 활성화

& vi my.cnf
...
[mysqld]
federated
...
	

위와 같이 my.cnf 파일에 저장 후 mysql을 재시작 한다.

이제 FEDERATED 엔진을 사용하기 위한 준비가 끝났으니 DB 링크를 만들어 보자

 

4. 원격 DB 서버에 아래와 같은 테이블이 있다면

CREATE TABLE `users` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL,
    `password` varchar(32) NOT NULL,
    `created_at` timestamp NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	

 

5. 원격 DB 서버에 연결할 DB서에도 동일한 테이블이 있어야 한다.

CREATE TABLE `federated_users` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL,
    `password` varchar(32) NOT NULL,
    `created_at` timestamp NULL,
    PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://계정ID:비밀번호@원격DB서버IP:포트/원격 DB서버 DATABASE 명/테이블명'
	

* 테이블 생성 시 ENGINE을 FEDERATED 하고 CONNECTION 에 원격 DB서버의 정보를 설정한다.

* CONNECTION 예시) CONNECTION='mysql://root:12345!@#$%@10.1.2.3:3306/remote_db/users

* 위와 같이 비밀번호에 '@'이 포하되어 있다면 오류가 발생한다.

* CONNECTION에 비밀번호가 노출되므로 mysql에 server 연결정보를 생성하고 사용한다.

 

6. 원격 DB 서버 정보 생성

CREATE SERVER 서버명
FOREIGN DATA WRAPPER mysql
OPTIONS (USER '계정ID', PASSWORD '비밀번호', HOST '192.168.197.128', PORT 3306, DATABASE '연결할 원격 DB명');
	

 

7. CONNECTION에 6번에서 등록한 서버 정보사용 한다.

CREATE TABLE `federated_users` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL,
    `password` varchar(32) NOT NULL,
    `created_at` timestamp NULL,
	PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='서버명/테이블명'
	

 

 

이제 부터는 로컬 DB에서 원격 DB의 users 테이블을 조회할 수 있다.

-- 원격 DB의 `users` 테이블을 조회
SELECT * FROM `federated_users` LIMIT 10

-- 로컬 DB의 `users` 테이블을 조회
SELECT * FROM `users` LIMIT 10

-- 원격 DB의 `users` 테이블의 데이터를 로컬 DB `users`테이블에 삽입
INSERT INTO `users`
SELECT * FROM `federated_users` LIMIT 10
	

 

※ FEDERATED를 엔진을 이용하여 원격 DB에 쿼리를 요청할 경우 세션이 종료되지 않는 문제가 있다.

원격 DB에 접속해 show processlist 쿼리를 실행해 보면 아래와 같이 Command가 Sleep인 세션이 있다.

로컬 DB에서 조회와 같은 쿼리 실행 후 flush local tables 테이블명 쿼리를 실행하면 해당 문제가 발생하지 않는 듯 하다. (문제가 해결되지 않는다 ㅠㅠ)

mysql> show processlist;
+----+------+-----------------------+-----------+---------+------+-------+-------------------+
| Id | User | Host                  | db        | Command | Time | State | Info              |
+----+------+-----------------------+-----------+---------+------+-------+-------------------+
| 1  | user | 123.123.123.123:12345 | testdb    |  Query  | 0    | init  | SHOW PROCESSLIST  |
| 2  | user | 123.123.123.123:12345 | testdb    |  Sleep  | 7    |       |                   |
+----+------+-----------------------+-----------+---------+------+-------+-------------------+