MySQL 은 Federated Engine 이 기본적으로 탑재 되어 있다. 그리고 X 는 MariaDB에서 사용하는 확장 판이다.
또한 타겟에서 끌고 올때 소스의 버퍼에 올리기 때문에 통으로나 데이터량이 많으면 소스에 스와핑이 생기거나 OOM 이 생길 수 있다.
/**
MySQL or MariaDB FederatedX Storage Engine
( like dblink of oracle)
*/
--
-- 01. Plugin Install
--
INSTALL PLUGIN federated SONAME 'ha_federatedx.so';
# my.cnf
federated # 이부분 추가
--
-- 02. Configure
--
connection=scheme://username:password@hostname:port/database/tablename
connection=scheme://username@hostname/database/tablename
connection=scheme://username:password@hostname/database/tablename
connection=scheme://username:password@hostname/database/tablename
ex)
connection=mysql://username:password@hostname:port/database/tablename
onnection="connection_one"
connection="connection_one/table_foo"
ex)
# 사용시 table name change 안됨
create server 'server_one' foreign data wrapper 'mysql' options
(HOST '127.0.0.1',
DATABASE 'db1',
USER 'root',
PASSWORD '',
PORT 3306,
SOCKET '',
OWNER 'root');
ex)
CREATE TABLE federatedx.t1 (
`id` int(20) NOT NULL,
`name` varchar(64) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';
# table name change 가능
CREATE TABLE test_table ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:9306/federatedx/test_federatedx';
--
-- 03. explain
-- ( 원격 테이블도 인덱스를 사용할수 있다.)
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | ox_clients | const | PRIMARY | PRIMARY | 3 | const | 1 | 100.00 | |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
--
-- 99. 작업 절차
--
INSTALL PLUGIN federated SONAME 'ha_federatedx.so';
create database if not exists dblink;
grant all on dblink.* to `root`@`localhost`;
use dblink;
create server 'server_one' foreign data wrapper 'mysql' options
(
HOST '127.0.0.1',
DATABASE 'db1',
USER 'root',
PASSWORD '',
PORT 3306,
SOCKET '',
OWNER 'root'
);
create table if not exists dblink.tbl_link_01
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';
create table if not exists dblink.tbl_link_02
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root:aaaa@127.0.0.1:9306/federatedx/test_federatedx';
CREATE TABLE dblink.tbl_link_03
(
id int(20) NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
other int(20) NOT NULL default '0',
PRIMARY KEY (id),
KEY name (name),
KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:9306/federatedx/test_federatedx';
