[MySQL] FEDERATED Storage Engine - 실무

Federated기능은 로컬 서버에서 원격 서버의 테이블을 연결해 마치 자신의 테이블인것처럼 조회하고 입력하고 삭제하는 기능입니다. 로컬에서 작업하지만 실제로는 원격의 서버 테이블에 실제 작업이 수행됩니다.

 

■ 사용방법

소스에서 MySQL을 빌드하는 경우 FEDERATED 스토리지 엔진을 포함하려면 -DWITH_FEDERATED_STORAGE_ENGINE 옵션을 사용하여 CMake를 실행합니다.

 

FEDERATED 스토리지 엔진은 실행중인 서버에서 기본적으로 활성화되지 않습니다. FEDERATED를 활성화하려면 다음과 같은 2가지 방법을 사용합니다.

1. shell> mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --federated &

2. 서버 파라미터인 my.cnf에 추가.

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
federated

특별한 속성없이 federated만 입력해주면 됩니다. 그리고 mysql서버를 재시작합니다.

 

참고로 FEDERATED 엔진의 소스를 조사하려면 MySQL 소스의 storage / federated 디렉토리를 확인합니다.

 

■ Federared table 생성방법

▶︎ 테스트 서버 환경

1. Local Server

IP : 10.30.224.100

2. Remote Server

IP : 10.30.224.110

 

▶︎ Federation Table을 생성하는 방법은 2가지가 있습니다.

1. Connection을 이용하는 방법

테이블에 직접 타겟 서버 정보를 입력하는 방법입니다. 사용법은 다음과 같습니다.

먼저 원격서버의 테이블 DDL을 복사한 후 로컬서버에서 DDL을 수행합니다. 대신 마지막에 Connection을 추가로 입력하고 Engine은 Federated로 입력합니다. 테이블 이름은 원격서버 이름이 아닌 다른 이름으로 만들어도 됩니다.

CREATE TABLE fed_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://feduser:fedpassword@10.30.224.110:3306/fed_db/src_table';

 

CONNECTION 연결 문자열의 형식은 다음과 같습니다.

scheme://user_name:password@host_name:port_num/db_name/tbl_name

 

그리고 각 속성들의 의미는 다음과 같습니다.

· schema : 인식된 연결 프로토콜. 스키마 값으로 mysql만 지원됩니다.

· user_name : 연결을 위한 원격 서버 사용자 이름. 이 사용자는 원격 서버에서 생성되어야 하며 원격 테이블에서 필요한 작업 (SELECT, INSERT, UPDATE 등)을 수행 할 수있는 적절한 권한이 있어야합니다.

· password : (선택 사항) user_name에 해당하는 암호입니다.

· host_name : 원격 서버의 호스트 이름 또는 IP 주소.

· port_num : (선택 사항) 원격 서버의 포트 번호. 기본값은 3306입니다.

· db_name : 원격 테이블을 보유하는 데이터베이스의 이름.

· tbl_name : 원격 테이블의 이름. 로컬 및 원격 테이블의 이름은 일치 할 필요가 없습니다.

 

 

2. CREATE SERVER를 사용하는 방법

Federated Table이 한두개만 있다면 관계없지만 여러개가 있을 시 테이블 생성시마다 Connection 정보를 일일히 넣어주기는 어려울 것입니다. 그래서 아예 원격서버 정보를 생성해 두고 그것을 이용하는 방식입니다.

• 사용방법

CREATE SERVER server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

server_name은 새 FEDERATED 테이블을 생성할 때 연결 문자열에 사용됩니다.

 

• 실예제

서버정보를 생성합니다.

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'feduser', PASSWORD 'fedpassword', HOST '10.30.224.110', PORT 3306, DATABASE 'fed_db');

mysql> select * from mysql.servers;
+-------------+---------------+--------+----------+-------------+------+--------+---------+-------+
| Server_name | Host          | Db     | Username | Password    | Port | Socket | Wrapper | Owner |
+-------------+---------------+--------+----------+-------------+------+--------+---------+-------+
| fedlink     | 10.30.224.110 | fed_db | feduser  | fedpassword | 3306 |        | mysql   |       |
+-------------+---------------+--------+----------+-------------+------+--------+---------+-------+
1 row in set (0.00 sec)

mysql.server를 조회시 다음과 같이 등록된 정보가 나옵니다. 참고로 접속정보(user, password, ip등)가 같이 나오기 때문에 보안에 신경쓰셔야 합니다.

 

 

 

이 연결을 사용하는 FEDERATED 테이블을 생성하려면 CREATE SERVER 문에서 사용한 이름을 지정합니다.

CREATE TABLE fed_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='fedlink/src_table';

이 예제의 연결 이름에는 슬래시로 구분된 연결 이름 (fedlink)과 연결할 테이블 이름 (src_table)이 포함됩니다. 테이블 이름없이 연결 이름만 지정하면 로컬 테이블의 테이블 이름(fed_table)이 사용됩니다. 대신 원격 서버의 테이블 이름도 같아야 생성됩니다. 만약 원격서버에 같은 이름이 없다면 다음과 같은 에러가 출력됩니다.

ERROR 1431 (HY000): The foreign data source you are trying to reference does not exist. Data source error:  error: 1146  'Table 'fed_db.fed_table' doesn't exist'

 

CREATE SERVER 문은 CONNECTION 문자열과 동일한 인수를 허용합니다. CREATE SERVER 문은 mysql.servers 테이블의 행을 업데이트합니다. 연결 문자열의 매개 변수, CREATE SERVER 문의 옵션 및 mysql.servers 테이블의 컬럼간의 대응에 대한 정보는 다음 표를 참조합니다. 참고로 CONNECTION 문자열의 형식은 다음과 같습니다.

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

 

설명	CONNECTION 문자열	CREATE SERVER 옵션	mysql.servers 컬럼
접속 스키마	scheme	wrapper_name	Wrapper
원격 유저	user_name	USER	Username
원격 암호	password	PASSWORD	Password
원격 서버 호스트	host_name	HOST	Host
원격 포트	port_num	PORT	Port
원격 데이터베이스	db_name	DATABASE	Db

 

 

▶︎ 로컬에서 Federated Table 사용시 유의사항.

1. 원격 유저의 권한에 따라 Select, insert, update, delete가 가능합니다.

2. 인덱스 생성이 불가능합니다.

3. 테이블에 컬럼변경 속성 변경, 크기 변경등 DDL에 대해 명령문이 허용되지 않습니다.

 

■ 테이블 조회 및 모니터링

▶︎ 원격 서버에서 세션 조회시

mysql> show processlist;
+----+------+---------------------+--------+---------+------+----------+------------------+
| Id | User | Host                | db     | Command | Time | State    | Info             |
+----+------+---------------------+--------+---------+------+----------+------------------+
|  7 | root | localhost           | fed_db | Query   |    0 | starting | show processlist |
|  9 | root | 10.30.224.100:40990 | fed_db | Sleep   |   76 |          | NULL             |
+----+------+---------------------+--------+---------+------+----------+------------------+
2 rows in set (0.01 sec)

로컬서버가 원격서버에 접속되어 사용되는 것으로 표시됩니다.

참고로 원격서버에서 로컬서버의 IP는 처음에 보이지 않습니다. 로컬에서 조회를 해야 원격서버에 접속을 하게 됩니다.

 

▶︎ 로컬서버에서 테이블 Drop 시

mysql> show processlist;
+----+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host      | db     | Command | Time | State    | Info             |
+----+------+-----------+--------+---------+------+----------+------------------+
|  7 | root | localhost | racoon | Query   |    0 | starting | show processlist |
+----+------+-----------+--------+---------+------+----------+------------------+
1 row in set (0.00 sec)

로컬 서버 세션은 종료됩니다.

 

▶︎ 로컬에서 테이블 정보 조회시

mysql> select table_schema, table_name, table_type, engine from information_schema.tables where table_name='fed_table';
+--------------+------------+------------+-----------+
| table_schema | table_name | table_type | engine    |
+--------------+------------+------------+-----------+
| fed_db       | fed_table  | BASE TABLE | FEDERATED |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

엔진에 FEDERATED로 표시가 됩니다.