MySQL下的DBlink--FEDERATED引擎

lhrbest發表於2019-07-04

MySQL下的DBlink--FEDERATED引擎

在實際工作中，我們可能會遇到需要操作其他資料庫例項的部分表，但又不想系統連線多庫。此時我們就需要用到資料表對映。如同Oracle中的DBlink一般，使用過Oracle DBlink資料庫連結的人都知道可以跨例項來進行資料查詢，同樣的，Mysql自帶的FEDERATED引擎完美的幫我們解決了該問題。本篇文章介紹FEDERATED引擎的開啟和使用。

1.開啟FEDERATED引擎

若需要建立FEDERATED引擎表，則目標端例項要開啟FEDERATED引擎。從MySQL5.5開始FEDERATED引擎預設安裝只是沒有啟用，進入命令列輸入 show engines ; FEDERATED行狀態為NO。

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

在配置檔案[mysqld]中加入一行：federated，然後重啟資料庫，FEDERATED引擎就開啟了。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV 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         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | 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        |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2.使用CONNECTION建立FEDERATED表

使用CONNECTION建立FEDERATED引擎表通用模型：

CREATE TABLE (......) 
ENGINE =FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename'

簡單建立測試：

# 源端表結構及資料
mysql> show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `stu_id` int(11) NOT NULL COMMENT '學號',
  `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  PRIMARY KEY (`increment_id`),
  UNIQUE KEY `uk_stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='學生表'
1 row in set (0.00 sec)
mysql> select * from test_table;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time         | update_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | wang     | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            2 |   1002 | dfsfd    | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            3 |   1003 | fdgfg    | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            4 |   1004 | sdfsdf   | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            5 |   1005 | dsfsdg   | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            6 |   1006 | fgd      | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
+--------------+--------+----------+---------------------+---------------------+
6 rows in set (0.00 sec)
# 目標端建表及查詢
# 注意ENGINE=FEDERATED CONNECTION後為源端地址 避免使用帶@的密碼
mysql> CREATE TABLE `test_table` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `stu_id` int(11) NOT NULL COMMENT '學號',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='學生表' CONNECTION='mysql://root:root@10.50.60.212:3306/source/test_table';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test_table;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time         | update_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | wang     | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            2 |   1002 | dfsfd    | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            3 |   1003 | fdgfg    | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            4 |   1004 | sdfsdf   | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            5 |   1005 | dsfsdg   | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
|            6 |   1006 | fgd      | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
+--------------+--------+----------+---------------------+---------------------+
6 rows in set (0.00 sec)

3.使用CREATE SERVER建立FEDERATED表

如果要在同一伺服器上建立多個FEDERATED表，或者想簡化建立FEDERATED表的過程，則可以使用該CREATE SERVER語句定義伺服器連線引數，這樣多個表可以使用同一個server。

CREATE SERVER建立的格式是：

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', PASSWORD '123456', HOST 'remote_host', PORT 3306, DATABASE 'federated');

之後建立FEDERATED表可採用如下格式：

CREATE TABLE (......) 
ENGINE =FEDERATED CONNECTION='test_link/tablename'

示例演示：

# 目標端建立指向源端的server
mysql> CREATE SERVER test_link
    ->   FOREIGN DATA WRAPPER mysql
    ->   OPTIONS (USER 'root', PASSWORD 'root',HOST '10.50.60.212',PORT 3306,DATABASE 'source');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql.servers\G
*************************** 1. row ***************************
Server_name: test_link
       Host: 10.50.60.212
         Db: source
   Username: root
   Password: root
       Port: 3306
     Socket: 
    Wrapper: mysql
      Owner: 
1 row in set (0.00 sec)
# 目標端建立FEDERATED表
mysql> CREATE TABLE `s1` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `stu_id` int(11) NOT NULL COMMENT '學號',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='學生表' CONNECTION='test_link/s1';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `s2` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `stu_id` int(11) NOT NULL COMMENT '學號',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='學生表' CONNECTION='test_link/s2';
Query OK, 0 rows affected (0.01 sec)

4.FEDERATED使用總結

基於MySQL5.7.23版本，筆者在源端及目標端實驗了多種DDL及DML，現簡單總結如下，有興趣的同學可以試試看。

目標端建表結構可以與源端不一樣推薦與源端結構一致
源端DDL語句更改表結構目標端不會變化
源端DML語句目標端查詢會同步
源端drop表目標端結構還在但無法查詢
目標端不能執行DDL語句
目標端執行DML語句源端資料也會變化
目標端truncate表源端表資料也會被清空
目標端drop表對源端無影響

5.FEDERATED引擎最佳實踐

目前FEDERATED引擎使用範圍還不多，若確實有跨例項訪問的需求，建議做好規範，個人總結最佳實踐如下：

源端專門建立只讀許可權的使用者來供目標端使用。
目標端建議用CREATE SERVER方式建立FEDERATED表。
FEDERATED表不宜太多，遷移時要特別注意。
目標端應該只做查詢使用，禁止在目標端更改FEDERATED表。
建議目標端表名及結構和源端保持一致。
源端表結構變更後目標端要及時刪除重建。

參考：

https://dev.mysql.com/doc/refman/5.7/en/federated-create.html

https://dev.mysql.com/doc/refman/5.7/en/create-server.html

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（

http://blog.itpub.net/26736162

）、部落格園（

http://www.cnblogs.com/lhrbest

）和個人weixin公眾號（

xiaomaimiaolhr

）上有同步更新

● 本文itpub地址：

http://blog.itpub.net/26736162

● 本文部落格園地址：

http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：

http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：

http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群號：

230161599

（滿）

、618766405

● weixin群：可加我weixin，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友

（

646634621

）

，註明新增緣由

● 於 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成

● 最新修改時間：2019-07-01 06:00 ~ 2019-07-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 

小麥苗的微店

：

https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 

小麥苗出版的資料庫類叢書

：

http://blog.itpub.net/26736162/viewspace-2142121/

● 

小麥苗OCP、OCM、高可用網路班

：

http://blog.itpub.net/26736162/viewspace-2148098/

● 

小麥苗騰訊課堂主頁

：

https://lhr.ke.qq.com/

........................................................................................................................

使用

weixin客戶端

掃描下面的二維碼來關注小麥苗的weixin公眾號（

xiaomaimiaolhr

）及QQ群（DBA寶典）、新增小麥苗weixin，

學習最實用的資料庫技術。 

........................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2649533/，如需轉載，請註明出處，否則將追究法律責任。

MYSQL引擎的鎖對比
2018-03-19
MySql
理解mysql的儲存引擎
2021-09-09
MySql儲存引擎
MySQL 儲存引擎
2020-08-29
MySql儲存引擎
MySQL儲存引擎
2024-08-23
MySql儲存引擎
如何選擇mysql的儲存引擎
2018-10-11
MySql儲存引擎
聊一聊MySQL的儲存引擎
2022-01-24
MySql儲存引擎
【Mysql】InnoDB 引擎中的頁目錄
2021-07-24
MySql
MySQL系列-儲存引擎
2018-12-03
MySql儲存引擎
Mysql innodb引擎（二）鎖
2018-09-05
MySql
MySQL InnoDB儲存引擎
2024-05-25
MySql儲存引擎
Mysql表引擎優化
2019-05-11
MySql優化
mysql關於memory引擎的表的總結
2018-09-30
MySql
怎麼檢視mysql的儲存引擎
2021-09-11
MySql儲存引擎
MySQL MyISAM引擎的讀鎖與寫鎖
2020-12-02
MySql
Mysql innodb引擎（三）事務
2018-09-05
MySql
MySQL-05.儲存引擎
2024-04-19
MySql儲存引擎
MySQL入門--儲存引擎
2019-06-28
MySql儲存引擎
ClickHouse-整合引擎（MySQL、HDFS）
2020-12-04
MySql
MySQL之四儲存引擎
2021-03-02
MySql儲存引擎
Tesseract引擎的下載和安裝
2023-11-29
【MySQL】MySQL（四）儲存引擎、索引、鎖、叢集
2021-10-21
MySql儲存引擎索引
Mysql兩種主要資料引擎的區別
2020-10-10
MySql
MySQL部分引擎的資料檔名稱作用
2020-03-31
MySql
MySQL中InnoDB引擎對索引的擴充套件
2020-03-24
MySql索引套件
MySQL innodb引擎的事務執行過程
2018-04-22
MySql
【Mysql】InnoDB 引擎中的資料頁結構
2021-07-23
MySql
MySQL儲存引擎：MyISAM和InnoDB的區別
2020-12-09
MySql儲存引擎
MySQL InnoDB 儲存引擎探祕
2019-02-21
MySql儲存引擎
2_mysql（索引、儲存引擎）
2020-11-15
MySql索引儲存引擎
MySQL federated儲存引擎測試
2023-01-13
MySql儲存引擎
MySQL的nnodb引擎表資料分割槽儲存
2018-06-01
MySql
mysql dba系統學習（20）mysql儲存引擎MyISAM
2021-09-09
MySql儲存引擎
Linux下的Mysql操作
2018-08-15
LinuxMySql
MySQL的JDBC下載
2019-07-22
MySqlJDBC
MySQL下的DB Link
2019-07-04
MySql
小談mysql儲存引擎優化
2018-07-16
MySql儲存引擎優化
Mysql innodb引擎（一）緩衝和索引
2018-09-05
MySql索引
MySQL儲存引擎入門介紹
2020-09-28
MySql儲存引擎

MySQL下的DBlink--FEDERATED引擎

MySQL下的DBlink--FEDERATED引擎

1.開啟FEDERATED引擎

2.使用CONNECTION建立FEDERATED表

3.使用CREATE SERVER建立FEDERATED表

4.FEDERATED使用總結

5.FEDERATED引擎最佳實踐

相關文章