MySQL下的DB Link

来源:https://www.cnblogs.com/kunjian/archive/2019/07/04/11131592.html
-Advertisement-
Play Games

前言： 在實際工作中，我們可能會遇到需要操作其他資料庫實例的部分表，但又不想系統連接多庫。此時我們就需要用到數據表映射。如同Oracle中的DBlink一般，使用過Oracle DBlink資料庫鏈接的人都知道可以跨實例來進行數據查詢，同樣的，Mysql自帶的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:[email protected]: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引擎使用範圍還不多，若確實有跨實例訪問的需求，建議做好規範，個人總結最佳實踐如下：

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

參考：
https://dev.mysql.com/doc/refman/5.7/en/federated-create.html
https://dev.mysql.com/doc/refman/5.7/en/create-server.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SqlServer資料庫系列(1) ---游標
    寫在前面 在做項目的過程中。我發現有許多地方有用到游標的方式去實現功能效果的。所以，整理了有關常用的實現游標的方式。 一、什麼是游標 維基百科中事這樣定義游標的。游標是處理結果集的一種機制 ,而結果集就是select查詢返回的所有行數據的集合。 對於我而言，用通俗的話來講，就是把自己需要用到的數據先 ...
  • sql server 根據欄位去重
    使用 row_number() over (partition by 要去重的欄位 order by 排序欄位) 資料庫表結構 學生成績表 UserGrade Id int Checked 主鍵IdName varchar(50) Checked 學生名Course varchar(50) Chec ...
  • sql server union與unionALL區別
    兩種用法 一樣， 查詢欄位類型需要一致 union 會自動去重 union all 不會去重 ...
  • InfluxDB因修改預設數據目錄導致服務無法正常運行的問題（許可權問題）
    在實際的生產中，考慮的實際情況，我們會調整一些預設配置，例如，數據目錄。InfluxDB修改預設的Data目錄後，因許可權問題，服務無法正常運行。以下是具體的分析測試過程。 配置文件為 /etc/influxdb/influxdb.conf，關於數據存放的預設配置如下： 調整後的配置： 啟動influ ...
  • Oracle 資料庫表中已有重覆數據添加唯一鍵（唯一約束）
    Oracle 資料庫表中已有重覆數據添加唯一鍵（唯一約束） 問題描述 以 demo 舉例，模擬真實場景。 表 TEST_TABLE 有如下欄位和數據：id 是主鍵，code 沒有設置鍵和索引 ID | CODE | 1 | code1 2 | code2 3 | code2 4 | code2 5 ...
  • Mysql面試題
    https://juejin.im/post/5c91ac636fb9a071012a0c28 詳述MySQL主從複製原理及配置主從的完整步驟 innodb引擎的4大特性 事物的4種隔離級別 sql語句分類 事務是如何通過日誌來實現的 MySQL資料庫幾個基本的索引類型：普通索引、唯一索引、主鍵索引 ...
  • MYSQL實戰-1.mysql基本架構
    學習筆記：非原著 ...
  • oracle——學習之路（select檢索）
    select語法： select [distinct|all] 列名 from 表名 [where] [group by] [having] [order by] ps:[] 表示可以省略 舉幾個慄子： select * from emp; ps:* 表示所有欄位即把要查詢的表的所有欄位都顯示出來， ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類