MySQL中DBlink的使用
2021/4/18 19:55:14
本文主要是介绍MySQL中DBlink的使用,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
在实际工作中,我们可能会遇到需要操作其他数据库实例的部分表,但又不想系统连接多库。此时我们就需要用到数据表映射。如同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使用总结
基于MySQL 5.7.23版本,笔者在源端及目标端实验了多种DDL及DML,现简单总结如下,有兴趣的同学可以试试看。
- 目标端建表结构可以与源端不一样 推荐与源端结构一致
- 源端DDL(CREATE、ALTER、DROP等语句)语句更改表结构 目标端不会变化
- 源端DML(SELECT、UPDATE、INSERT、DELETEt等语句)语句目标端查询会同步
- 源端drop表 目标端结构还在但无法查询
- 目标端不能执行DDL语句
- 目标端执行DML语句 源端数据也会变化
- 目标端truncate表 源端表数据也会被清空
- 目标端drop表对源端无影响
5.FEDERATED引擎最佳实践
目前FEDERATED引擎使用范围还不多,若确实有跨实例访问的需求,建议做好规范,个人总结最佳实践如下:
- 源端专门创建只读权限的用户来供目标端使用。
- 目标端建议用CREATE SERVER方式创建FEDERATED表。
- FEDERATED表不宜太多,迁移时要特别注意。
- 目标端应该只做查询使用,禁止在目标端更改FEDERATED表。
- 建议目标端表名及结构和源端保持一致。
- 源端表结构变更后 目标端要及时删除重建。
本文转载自:http://www.spring4all.com/article/15223
这篇关于MySQL中DBlink的使用的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7
- 2024-02-18从 20 多套 MySQL 到 1 套 TiDB丨骏伯网络综合运营管理平台应用实践
- 2024-02-07mysql 外键索引入门介绍,为什么工作中很少有人使用?