Federated engine이란
FEDERATED 스토리지 엔진을 사용하면 복제 또는 클러스터 기술을 사용하지 않고도 원격 MySQL 데이터베이스의 데이터에 액세스 할 수 있습니다. 로컬 FEDERATED 테이블을 쿼리하면 원격 테이블에서 데이터를 자동으로 가져옵니다. 로컬 테이블에는 데이터가 저장되지 않습니다.
표준 스토리지 엔진 (예 : MyISAM, CSV 또는 InnoDB) 중 하나를 사용하여 테이블을 생성할 때 테이블은 테이블 정의 및 관련 데이터로 구성됩니다. FEDERATED 테이블을 생성할 때 테이블 정의는 동일하지만 데이터의 물리적 스토리지는 원격 서버에서 처리됩니다.
FEDERATED 스토리지 엔진은 더 이상 지원되지 않는 레거시 스토리지 엔진입니다.
MariaDB 10.0 부터 CONNECT 스토리지 엔진 은 MySQL 또는 ODBC 연결(테이블 유형: MYSQL , ODBC ) 을 통해 원격 데이터베이스에 액세스하는 것도 허용
단점 및 제약사항
FEDERATED 테이블은 일반적인 의미에서 인덱스를 지원하지 않습니다. 테이블 데이터에 대한 액세스는 원격으로 처리되기 때문에 실제로 인덱스를 사용하는 원격 테이블입니다. 즉, 인덱스를 사용할 수 없어 전체 테이블 스캔이 필요한 쿼리의 경우 서버가 원격 테이블에서 모든 행을 가져 와서 로컬로 필터링 합니다. 이 SELECT 문에 사용 된 WHERE 또는 LIMIT에 관계없이 발생합니다. 이러한 명령문은 반환된 행에 로컬로 적용됩니다.
따라서 인덱스를 사용하지 못하는 쿼리는 성능 저하와 네트워크 과부하를 유발할 수 있습니다. 또한 반환 된 행은 메모리에 저장되어야하므로 이러한 쿼리로 인해 로컬 서버가 스와핑되거나 중단 될 수도 있습니다.
FEDERATED 저장소 엔진은 SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE를 지원합니다. ALTER TABLE 또는 DROP TABLE 이외의 테이블 구조에 직접 영향을 미치는 데이터 정의 언어 문을 지원하지 않습니다
Federated 테이블을 사용할 때는 로컬에서 해당 테이블에 인덱스를 직접 생성할 수 없습니다. Federated 테이블에 대한 쿼리 최적화는 원격 서버에서 이루어지며, 원격 서버에서 적절한 인덱스가 설정되어 있는지에 따라 성능이 결정됩니다.
원본 디비에서 조회하는 쿼리를 그대로 사용 가능하나 수정/추가는 안되고 오로지 조회 용도로 사용 가능하다
ㅁ 요약
1. 원격 유저의 권한에 따라 Select, insert, update, delete가 가능
2. 인덱스 생성이 불가능
3. 테이블에 컬럼변경 속성 변경, 크기 변경등 DDL 불가
=> federated 테이블을 다시 drop하고 create 필요
4.원본테이블과 같이 컬럼명을 똑같이 맞춰줘야 한다.
테스트
■ 엔진 사용 여부 조회
show engines; 조회시 FEDERATED의 Support가 YES인지 확인 -> NO일 경우 아래와 같이 설치 필요
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.003 sec)
※ 설치 방법
■ FEDERATED 엔진 플러그인 설치
MariaDB [(none)]> install plugin federated soname 'ha_federated.so'; ■ 엔진 활성화
my.cnf 구문 추가
[mariadb@localhost mariadb]$ vi my.cnf
...
[mysqld]
federated
■ DB 재기동 이후 확인
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.001 sec)
■ 원격지 서버에 DBLink target table 확인
MariaDB [test]> create table kim (
num bigint(20) not null default '0',
regdate date not null,
context text not null,
primary key (num,regdate)
) engine=innodb DEFAULT CHARSET=utf8; ■ 소스 서버에 동일한 table 생성
1 engine=FEDERATED 로 설정
2 CONNECTION 구문 추가 필요
CONNECTION='mysql://계정ID:비밀번호@원격DB서버IP:포트/원격 DB서버 DATABASE 명/테이블명'
MariaDB [test]> create table federated_kim (
num bigint(20) not null default '0',
regdate date not null,
context text not null,
primary key (num,regdate)
) engine=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://kim:kim@192.168.152.131:3306/test/kim';
※ 패스워드에 @가 들어간 경우 계속 포맷이 맞지 않다고 아래와 같은 에러가 발생
Can’t create federated table. The data source connection string ‘mysql://~~’ is not in the correct format
앞에 \(역슬레시)를 붙여 이스케이프 처리하고 진행하면 된다.
■ 원격지 DB서버에서 데이터 insert
MariaDB [test]> insert into kim values (1,'2024-01-12','test');
Query OK, 1 row affected (0.002 sec)
■ 소스 DB에서 조회
데이터가 동기화되어 정상 조회됨
MariaDB [test]> select * from federated_kim;
+-----+------------+---------+
| num | regdate | context |
+-----+------------+---------+
| 1 | 2024-01-12 | test |
+-----+------------+---------+
1 row in set (0.113 sec)
DML 테스트
MariaDB [test]> select * from federated_kim;
+-----+------------+---------+---------------+------------+
| num | regdate | context | email | phone |
+-----+------------+---------+---------------+------------+
| 1 | 2024-01-12 | test | NULL | NULL |
| 2 | 2024-01-13 | test | asd@asdsa.com | 1012311231 |
+-----+------------+---------+---------------+------------+
2 rows in set (0.013 sec)
MariaDB [test]> delete from federated_kim where num=1;
Query OK, 1 row affected (0.009 sec)
MariaDB [test]> select * from federated_kim;
+-----+------------+---------+---------------+------------+
| num | regdate | context | email | phone |
+-----+------------+---------+---------------+------------+
| 2 | 2024-01-13 | test | asd@asdsa.com | 1012311231 |
+-----+------------+---------+---------------+------------+
1 row in set (0.006 sec)
## 원격지꺼도 삭제됨
MariaDB [test]> select * from kim;
+-----+------------+---------+---------------+------------+
| num | regdate | context | email | phone |
+-----+------------+---------+---------------+------------+
| 2 | 2024-01-13 | test | asd@asdsa.com | 1012311231 |
+-----+------------+---------+---------------+------------+
1 row in set (0.001 sec)
인덱스 생성 테스트
■ 소스 DB에서 인덱스 생성시 에러 발생
-> 소스 DB에서는 원격 테이블에 DDL불가
MariaDB [test]> create index test_idx on federated_kim(phone);
ERROR 1031 (HY000): Storage engine FEDERATED of the table `test`.`federated_kim` doesn't have this option
■ 원격지 DB에서 인덱스 생성
MariaDB [test]> create index test_id on kim(phone);
Query OK, 0 rows affected (0.015 sec)
MariaDB [test]> show index in kim;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| kim | 0 | PRIMARY | 1 | num | A | 1 | NULL | NULL | | BTREE | | |
| kim | 0 | PRIMARY | 2 | regdate | A | 1 | NULL | NULL | | BTREE | | |
| kim | 1 | test_id | 1 | phone | A | 1 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
■ 소스 DB 인덱스 확인
-> 미반영
MariaDB [test]> show indexes in federated_kim;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| federated_kim | 0 | PRIMARY | 1 | num | NULL | NULL | NULL | NULL | | REMOTE | | |
| federated_kim | 0 | PRIMARY | 2 | regdate | NULL | NULL | NULL | NULL | | REMOTE | | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
■ 소스 DB 테이블 재작성
## 원본 테이블 조회
MariaDB [test]> show create table kim
-> ;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| kim | CREATE TABLE `kim` (
`num` bigint(20) NOT NULL DEFAULT 0,
`regdate` date NOT NULL,
`context` text NOT NULL,
`email` varchar(25) DEFAULT NULL,
`phone` int(25) DEFAULT NULL,
PRIMARY KEY (`num`,`regdate`),
KEY `test_id` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
## 소스 DB 테이블 삭제 후 재생성
MariaDB [test]> drop table federated_kim;
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> CREATE TABLE `federated_kim` (
-> `num` bigint(20) NOT NULL DEFAULT 0,
-> `regdate` date NOT NULL,
-> `context` text NOT NULL,
-> `email` varchar(25) DEFAULT NULL,
-> `phone` int(25) DEFAULT NULL,
-> PRIMARY KEY (`num`,`regdate`),
-> KEY `test_id` (`phone`)
-> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8
-> CONNECTION='mysql://kim:kim@192.168.152.131:3306/test/kim';
Query OK, 0 rows affected (0.002 sec)
## 소스 DB에서 인덱스 조회시 반영 확인
MariaDB [test]> show index in federated_kim;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| federated_kim | 0 | PRIMARY | 1 | num | NULL | NULL | NULL | NULL | | REMOTE | | |
| federated_kim | 0 | PRIMARY | 2 | regdate | NULL | NULL | NULL | NULL | | REMOTE | | |
| federated_kim | 1 | test_id | 1 | phone | NULL | NULL | NULL | NULL | YES | REMOTE | | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
참고
'DataBase > MySQL & MariaDB' 카테고리의 다른 글
| [MySQL & MariaDB - sync_binlog 0 vs 1 트랜잭션 보장 테스트] (7) | 2024.02.28 |
|---|---|
| [MySQL & MariaDB - Replication 복제 에러 error_code : 1236] (2) | 2024.02.28 |
| [MySQL & MariaDB - Relay Log 변경 시 replication 복제 에러 해결방법] (0) | 2024.01.05 |
| [MySQL & MariaDB - Partition Exchange] (0) | 2024.01.04 |
| [MySQL - 로컬 컴퓨터 to MySQL 서버 데이터 이관] use.MySQL workbench (0) | 2023.02.10 |