溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

MySQL下的DB link

發(fā)布時(shí)間：2020-06-26 18:27:00 來(lái)源：網(wǎng)絡(luò) 閱讀：300 作者：wangkunj 欄目：MySQL數(shù)據(jù)庫(kù)

前言：

在實(shí)際工作中，我們可能會(huì)遇到需要操作其他數(shù)據(jù)庫(kù)實(shí)例的部分表，但又不想系統(tǒng)連接多庫(kù)。此時(shí)我們就需要用到數(shù)據(jù)表映射。如同Oracle中的DBlink一般，使用過(guò)Oracle DBlink數(shù)據(jù)庫(kù)鏈接的人都知道可以跨實(shí)例來(lái)進(jìn)行數(shù)據(jù)查詢(xún)，同樣的，Mysql自帶的FEDERATED引擎完美的幫我們解決了該問(wèn)題。本篇文章介紹FEDERATED引擎的開(kāi)啟和使用。

1.開(kāi)啟FEDERATED引擎

若需要?jiǎng)?chuàng)建FEDERATED引擎表，則目標(biāo)端實(shí)例要開(kāi)啟FEDERATED引擎。從MySQL5.5開(kāi)始FEDERATED引擎默認(rèn)安裝 只是沒(méi)有啟用，進(jìn)入命令行輸入show engines; FEDERATED行狀態(tài)為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，然后重啟數(shù)據(jù)庫(kù)，F(xiàn)EDERATED引擎就開(kāi)啟了。

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創(chuàng)建FEDERATED表

使用CONNECTION創(chuàng)建FEDERATED引擎表通用模型：

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

簡(jiǎn)單創(chuàng)建測(cè)試：

# 源端表結(jié)構(gòu)及數(shù)據(jù)
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 '學(xué)號(hào)',
  `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
  PRIMARY KEY (`increment_id`),
  UNIQUE KEY `uk_stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='學(xué)生表'
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)

# 目標(biāo)端建表及查詢(xún)
# 注意ENGINE=FEDERATED CONNECTION后為源端地址 避免使用帶@的密碼
mysql> CREATE TABLE `test_table` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `stu_id` int(11) NOT NULL COMMENT '學(xué)號(hào)',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='學(xué)生表' 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創(chuàng)建FEDERATED表

如果要在同一服務(wù)器上創(chuàng)建多個(gè)FEDERATED表，或者想簡(jiǎn)化創(chuàng)建FEDERATED表的過(guò)程，則可以使用該CREATE SERVER語(yǔ)句定義服務(wù)器連接參數(shù)，這樣多個(gè)表可以使用同一個(gè)server。

CREATE SERVER創(chuàng)建的格式是：

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

之后創(chuàng)建FEDERATED表可采用如下格式：

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

示例演示：

# 目標(biāo)端創(chuàng)建指向源端的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)

# 目標(biāo)端創(chuàng)建FEDERATED表
mysql> CREATE TABLE `s1` (
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `stu_id` int(11) NOT NULL COMMENT '學(xué)號(hào)',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='學(xué)生表' 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 '學(xué)號(hào)',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
    ->   PRIMARY KEY (`increment_id`),
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='學(xué)生表' CONNECTION='test_link/s2';
Query OK, 0 rows affected (0.01 sec)

4.FEDERATED使用總結(jié)

基于MySQL5.7.23版本，筆者在源端及目標(biāo)端實(shí)驗(yàn)了多種DDL及DML，現(xiàn)簡(jiǎn)單總結(jié)如下，有興趣的同學(xué)可以試試看。

  • 目標(biāo)端建表結(jié)構(gòu)可以與源端不一樣 推薦與源端結(jié)構(gòu)一致
  • 源端DDL語(yǔ)句更改表結(jié)構(gòu) 目標(biāo)端不會(huì)變化
  • 源端DML語(yǔ)句目標(biāo)端查詢(xún)會(huì)同步
  • 源端drop表 目標(biāo)端結(jié)構(gòu)還在但無(wú)法查詢(xún)
  • 目標(biāo)端不能執(zhí)行DDL語(yǔ)句
  • 目標(biāo)端執(zhí)行DML語(yǔ)句 源端數(shù)據(jù)也會(huì)變化
  • 目標(biāo)端truncate表 源端表數(shù)據(jù)也會(huì)被清空
  • 目標(biāo)端drop表對(duì)源端無(wú)影響

5.FEDERATED引擎最佳實(shí)踐

目前FEDERATED引擎使用范圍還不多，若確實(shí)有跨實(shí)例訪問(wèn)的需求，建議做好規(guī)范，個(gè)人總結(jié)最佳實(shí)踐如下：

  1. 源端專(zhuān)門(mén)創(chuàng)建只讀權(quán)限的用戶(hù)來(lái)供目標(biāo)端使用。
  2. 目標(biāo)端建議用CREATE SERVER方式創(chuàng)建FEDERATED表。
  3. FEDERATED表不宜太多，遷移時(shí)要特別注意。
  4. 目標(biāo)端應(yīng)該只做查詢(xún)使用，禁止在目標(biāo)端更改FEDERATED表。
  5. 建議目標(biāo)端表名及結(jié)構(gòu)和源端保持一致。
  6. 源端表結(jié)構(gòu)變更后 目標(biāo)端要及時(shí)刪除重建。

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

MySQL下的DB link

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手