Jireh程序猿的那些事 Jireh程序猿的那些事

记录分享生活、程序、信息的精彩人生

目录
如何在 MySQL 中使用 FEDERATED 引擎实现DB Link能力
/  

如何在 MySQL 中使用 FEDERATED 引擎实现DB Link能力

数据库的 DB Link

如同Oracle的 DB Link 一下,Mysql自带的FEDERATED引擎可以完美的帮我们解决问题。

本篇文章介绍FEDERATED引擎的开启和使用。

开启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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

在配置文件[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         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

使用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 |
+--------------+--------+----------+---------------------+---------------------+

使用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)

转自:https://www.modb.pro/db/366026


如果觉得这篇文章不错的话,请我喝一杯 咖啡☕吧
标题:如何在 MySQL 中使用 FEDERATED 引擎实现DB Link能力
作者:Jireh
地址:https://jireh.xyz/articles/2024/05/20/1716187033599.html
本作品由 Jireh 采用 署名 – 非商业性使用 – 禁止演绎 4.0 国际许可协议进行许可,转载请注明出处。