用MySQL实现类似于Oracle dblink的功能-PingCAP

用MySQL实现类似于Oracle dblink的功能

网友投稿 1245 2023-05-03

用MySQL实现类似于*** dblink的功能

首先看看有没有federated 引擎：

mysql> show engines; +--------------------+---------+------------------------------------------------------------+--------------+-----+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+------------------------------------------------------------+--------------+-----+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | 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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+------------------------------------------------------------+--------------+-----+------------+ 6 rows in set

看到没有安装federated存储引擎(目标端可以是任何的存储引擎比如说myisam或者innodb)

安装federated存储引擎：

mysql> install plugin federated soname 'ha_federated.so'; Query OK, 0 rows affected mysql>

测试一下是否可以

[root@test01 bin]# ./mysqld_safe --federated & [1] 5418 You have new mail in /var/spool/mail/root [root@test01 bin]# 170823 16:24:17 mysqld_safe Logging to '/var/log/mysqld.log'. 170823 16:24:17 mysqld_safe Starting mysqld daemon with databases from /mysql/data 170823 16:25:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

默认mysql是没有开启federated存储引擎【linux系统下】

mysql> show engines ; +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | 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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ 7 rows in set mysql>

先找到my.cnf文件，我这里直接搜索的。【linux系统下】

[root@test01 mysql]# find / -name my.cnf /mysql/mysql-5.5.47/packaging/rpm-sles/my.cnf /mysql/mysql-5.5.47/packaging/rpm-docker/my.cnf /mysql/mysql-5.5.47/packaging/rpm-oel/my.cnf /mysql/mysql-5.5.47/mysql-test/suite/federated/my.cnf /mysql/mysql-5.5.47/mysql-test/suite/rpl/my.cnf /mysql/mysql-test/suite/federated/my.cnf /mysql/mysql-test/suite/rpl/my.cnf /etc/my.cnf [root@test01 mysql]#

开启federated存储引擎

只需要在my.cnf文件中增加 federated 就可以。【linux系统下】

配置好my.cnf文件的federated，然后重启mysql服务。

[root@test01 ~]# service mysql restart mysql: 未被识别的服务 You have new mail in /var/spool/mail/root [root@test01 ~]#

重启的时候提示：未被识别的服务，解决方法如下：

[root@test01 support-files]# pwd /mysql/support-files [root@test01 support-files]# cp ./mysql.server /etc/init.d/mysqld [root@test01 support-files]# chkconfig --add mysqld [root@test01 support-files]# chkconfig --level 2345 mysqld on [root@test01 support-files]#

使用service mysqld restart --federated 命令重启mysql

[root@test01 ~]# service mysqld restart --federated Shutting down MySQL... [确定] Starting MySQL.. [确定] [root@test01 ~]#

查看 federated 服务是否启动了。

mysql> show engines ; +--------------------+---------+------------------------------------------------------------+--------------+-----+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+------------------------------------------------------------+--------------+-----+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | 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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+------------------------------------------------------------+--------------+-----+------------+ 7 rows in set mysql>

好了，FEDERATED值为YES了。

目标：本地库A的三张表，整合数据后，写入远程库B的一张表中。

下面我们来建表

远程库B的inf_monitor_data表：

DROP TABLE IF EXISTS `inf_monitor_data`; CREATE TABLE `inf_monitor_data` ( `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '编号', `IT_Catalog` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '主类别', `IT_Function` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '子类别', `Business_Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '业务名称', `Control_Address` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '管理地址，设备Ip|数据库服务名|URI地址', `Title` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '摘要', `Detail` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '描述', `Start_Time` datetime NOT NULL COMMENT '采集查询起始时间', `End_Time` datetime DEFAULT NULL COMMENT '采集查询结束时间', `BSM_Type` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '对应其是哪类监视器的事件', `Status` varchar(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '处理标记', `Remarks` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '备注', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `i_incident`; CREATE TABLE `i_incident` ( `P_Guid` varchar(40) NOT NULL COMMENT '主键，唯一标识', `D_Owner` varchar(40) DEFAULT NULL COMMENT '所有者，单选一个用户（不是角色）', `D_Title` varchar(300) NOT NULL COMMENT '摘要', `D_Title_Confirm` varchar(300) DEFAULT NULL COMMENT '摘要,工程师确认', `AE_ChannelModule` int(11) NOT NULL COMMENT '来源模块，枚举ChannelModule：1=Monitor,2=NetWork,3=Syslog,4=SnmpTrap,5=Netflow,6=HelpDesk,9=Custom', `AE_BSMType` int(11) DEFAULT NULL COMMENT '对应其是哪类监视器的事件枚举：MonitorBSMType 0=CI,1=可用性,2=容量,3=财务', `D_StartTime` datetime NOT NULL COMMENT '开始时间', `D_UpdateTime` datetime DEFAULT NULL COMMENT '最后一次修改时间', `D_EndTime` datetime DEFAULT NULL COMMENT '关闭时间，如果没有关闭，则为Null', `D_Affect` int(11) NOT NULL COMMENT '影响度', `D_Urgency` int(11) NOT NULL COMMENT '紧急度', `D_PRI` int(11) NOT NULL COMMENT '优先级=影响度×紧急度', `D_OPTimes` int(11) DEFAULT NULL COMMENT '被处理过几次', `DF_DutyRoleId` varchar(1000) NOT NULL COMMENT 'R-角色1 R-角色2,U-用户1,M-用户2,U-用户3 R-角色3,U-用户4,U-用户5（R-：角色ID，U-：用户ID，M-：主用户ID）', `DF_DutyRoleText` varchar(2000) DEFAULT NULL, `DF_DutyUserId` varchar(1000) NOT NULL COMMENT '责任人', `DF_ApplyUserId` varchar(40) NOT NULL COMMENT '申请事件的用户，如是自动事件，则为【系统】', `DF_CreateUserId` varchar(40) NOT NULL COMMENT '创建事件的用户，如是自动事件，则为【系统】', `DF_UpdateUserId` varchar(40) DEFAULT NULL COMMENT '最后一次修改用户Id', `D_FlowState` int(11) NOT NULL COMMENT '流程状态，枚举FlowState:待确认=1,处理中=2,重打开=3,挂起=4,已处理待验证=5,关闭=6', `D_AcceptTime` datetime DEFAULT NULL, `D_Detail` varchar(1000) DEFAULT NULL COMMENT '描述', `D_Detail_Confirm` varchar(1000) DEFAULT NULL COMMENT '描述,工程师确认', `A_AlarmCount` int(11) DEFAULT NULL, `DE_ITManufacturer` int(11) DEFAULT NULL COMMENT '厂商，枚举：ITCompManufacturer', `DE_ITCatalog` int(11) DEFAULT NULL COMMENT '主类别，枚举：ITCompCatalog', `DE_ITFunction` int(11) DEFAULT NULL COMMENT '子类别，枚举：ITCompFunction', `DE_ITProductSeries` int(11) DEFAULT NULL COMMENT '产品系列，枚举：ITCompProductSeries', `DE_CITypeKey` varchar(40) DEFAULT NULL COMMENT '设备CI的Key', `A_RelatvieServiceId` varchar(500) DEFAULT NULL COMMENT '相关的服务id，由逗号分隔', `DF_Path_Custom` varchar(150) DEFAULT NULL COMMENT '外键，关联I_Incident_Catalog.A_Path，自定义类别', `A_IsReportToHigerLevel` bit(1) DEFAULT NULL COMMENT '是否上报给了上级', `A_ReportToHigerLevelObjectId` bigint(20) DEFAULT NULL COMMENT '上报给上级的单据的id', `A_ReportDateTime` datetime DEFAULT NULL, `A_RelaMainService` varchar(40) DEFAULT NULL COMMENT '影响的主服务', `AF_LastHistoryId` varchar(40) DEFAULT NULL COMMENT '最后一次处理历史的id', `D_CustomerDegree` int(11) DEFAULT NULL COMMENT '用户满意度,枚举：CustomerDegree', `D_DegreeDetail` varchar(500) DEFAULT NULL COMMENT '客户满意度描述', `AF_SLAId` varchar(40) DEFAULT NULL COMMENT '相关sla级别的id', `D_Number` bigint(20) NOT NULL AUTO_INCREMENT, `D_HopeEndTime` datetime DEFAULT NULL COMMENT '期望的解决时间', `CDDY` int(11) NOT NULL, `AF_SourceITCompId` varchar(40) DEFAULT NULL, `AF_SourceObjectId` varchar(40) DEFAULT NULL, `D_MState` int(11) DEFAULT NULL, `IsChangeToOK` bit(1) DEFAULT NULL, PRIMARY KEY (`D_Number`), UNIQUE KEY `i_incident_KEY` (`D_Number`), KEY `IX_PF_Inc_Datetime` (`D_StartTime`), KEY `x_AE_ChannelModule` (`AE_ChannelModule`), KEY `x_AF_SourceITCompId` (`AF_SourceITCompId`), KEY `x_AF_SourceObjectId` (`AF_SourceObjectId`), KEY `x_D_FlowState` (`D_FlowState`), KEY `x_DF_CreateUserId` (`DF_CreateUserId`), KEY `IX_Inc_Id` (`P_Guid`), KEY `IX_IsChangeToOK` (`IsChangeToOK`), KEY `IX_AlarmCount` (`A_AlarmCount`) ) ENGINE=InnoDB AUTO_INCREMENT=56989 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

数据整合的脚本：

在本地库A中，创建用户s_monitor，并且将远程库B中的inf_monitor_data表授权给s_monitor

mysql> CREATE USER 's_monitor'@'%' IDENTIFIED BY 's_monitor'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON monitor.inf_monitor_data TO s_monitor IDENTIFIED BY 's_monitor'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON monitor.inf_monitor_data TO s_monitor@192.168.12.*** IDENTIFIED BY 's_monitor'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR s_monitor; +----------------------------------------------------------------------------------------------------------+ | Grants for s_monitor@% | +----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 's_monitor'@'%' IDENTIFIED BY PASSWORD '*9BCC44AF82455FA4B8F72DC435F1FE3BEEA8E5C9' | | GRANT ALL PRIVILEGES ON `monitor`.`inf_monitor_data` TO 's_monitor'@'%' | +----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>

mysql> use s_monitor; Database changed mysql> CREATE TABLE `link_inf_monitor_data` ( -> `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '编号', -> `IT_Catalog` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '主类别', -> `IT_Function` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '子类别', -> `Business_Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '业务名称', -> `Control_Address` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '管理地址，设备Ip|数据库服务名|URI地址', -> `Title` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '摘要', -> `Detail` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '描述', -> `Start_Time` datetime NOT NULL COMMENT '采集查询起始时间', -> `End_Time` datetime DEFAULT NULL COMMENT '采集查询结束时间', -> `BSM_Type` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '对应其是哪类监视器的事件', -> `Status` varchar(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '处理标记', -> `Remarks` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '备注', -> PRIMARY KEY (`id`) -> ) -> ENGINE=FEDERATED DEFAULT CHARSET=utf8 -> CONNECTION='mysql://s_monitor:s_monitor@192.168.12.***:3306/monitor/inf_monitor_data'; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from link_inf_monitor_data; +----------+ | count(*) | +----------+ | 56740 | +----------+ 1 row in set (0.24 sec) mysql>

脚本及说明如下：

DROP TABLE IF EXISTS `link_inf_monitor_data`; CREATE TABLE `link_inf_monitor_data` ( `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '编号', `IT_Catalog` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '主类别', `IT_Function` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '子类别', `Business_Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '业务名称', `Control_Address` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '管理地址，设备Ip|数据库服务名|URI地址', `Title` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '摘要', `Detail` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '描述', `Start_Time` datetime NOT NULL COMMENT '采集查询起始时间', `End_Time` datetime DEFAULT NULL COMMENT '采集查询结束时间', `BSM_Type` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '对应其是哪类监视器的事件', `Status` varchar(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '处理标记', `Remarks` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '备注', PRIMARY KEY (`id`) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://s_monitor:s_monitor@192.168.12.***:3306/monitor/inf_monitor_data';

创建触发器

mysql> use s_monitor; Database changed mysql>

验证触发器

mysql> INSERT INTO `i_incident` VALUES ('9df5f3fa-df53-4f8e-8111-135ebf48be54', '', '192.168.10.186的[JVM]容量异常', '', '1', '2', '2017-03-03 09:55:29', '2017-03-03 09:55:29', null, '1', '1', '1', '0', '1635f6d8-f14f-41cb-987d-30f9f7f0d8d5', '', 'f5a03252-6944-49b1-a61c-15d09230f590,64015112-f056-4f32-93f3-c9d59997a967,5d6beb49-c573-4588-b46e-3299a9566de8', 'System', 'System', 'System', '1', null, '当前：使用率=[94.39%]；剩余内存=[4.93MB]；内存总量=[87.89MB]\r\n阀值：使用率(%) > 90', '', '0', '900', '1', '106', '2302', 'WSJVM', '', '', null, null, null, '', 'd767e13a-cd13-44ba-a2f7-3e5e4334a327', '0', '', '', '56988', null, '201703', '911be2ac-3db6-4abb-96ac-910a5e6779d1', '8ba66b5d-9ee2-464c-bda6-13f5e1673434', '2', '');

mysql> use s_monitor; Database changed mysql> select * from link_inf_monitor_data where id = '9df5f3fa-df53-4f8e-8111-135ebf48be54'; +--------------------------------------+------------+-------------+---------------+-----------------+------------------------------------+----------------------------------------------------------------------------------------------------------+---------------------+----------+----------+--------+---------+ | id | IT_Catalog | IT_Function | Business_Name | Control_Address | Title | Detail | Start_Time | End_Time | BSM_Type | Status | Remarks | +--------------------------------------+------------+-------------+---------------+-----------------+------------------------------------+----------------------------------------------------------------------------------------------------------+---------------------+----------+----------+--------+---------+ | 9df5f3fa-df53-4f8e-8111-135ebf48be54 | 应用 | 中间件 | | 192.168.10.186 | 192.168.10.186的[JVM]容量异常 | 当前：使用率=[94.39%]；剩余内存=[4.93MB]；内存总量=[87.89MB]阀值：使用率(%) > 90 | 2017-03-03 09:55:29 | NULL | 容量 | 0 | | +--------------------------------------+------------+-------------+---------------+-----------------+------------------------------------+----------------------------------------------------------------------------------------------------------+---------------------+----------+----------+--------+---------+ 1 row in set (0.00 sec) mysql>

麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践

1245 2023-05-03

用MySQL实现类似于Oracle dblink的功能

麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践

高成本云服务？TiDB 帮你省钱

零售业数据库选型与迁移ToC系统实践大规模场景应用

推荐文章

HTAP 还可以这么玩？丨TiDB 在 IoT 智慧园区的应用

新特性解析丨TiDB 资源管控的设计思路与场景解析

TiDB赋能保险业-首个全栈自主核心保单系统成功投产

首个云原生、分布式、全栈国产化银行核心业务系统投产上线丨TiDB × 杭州银行

TiDB 在社交场景的解决方案实践

电商数据技术栈，在海量数据增长下如何实现实时与全量兼得？

金融行业数据库的选择

TiDB 在智能制造中的应用实践

TiDB 在全球头部物流企业计费管理系统的应用实践

PingCAP与教育部教育管理信息中心合作，推动普惠教育数字化转型

友情链接

热评文章

TiDB 中标杭州银行核心系统数据库项目

TiDB 首批通过信通院 HTAP 数据库基础能力评

PingCAP 与 Wisconsin-Madiso

PingCAP 成为中国唯一入选 Forrester

TiDB 走进东软集团，共建医疗数字化基石

共享开源技术，共建开放生态丨平凯星辰余梦杰出席 20