用mysql实现类似于oracle dblink的功能

最新推荐文章于 2024-04-19 15:11:41 发布

weixin_33813128

最新推荐文章于 2024-04-19 15:11:41 发布

阅读量523

点赞数

文章标签：数据库运维操作系统

原文链接：https://my.oschina.net/90888/blog/1519409

版权

为什么80%的码农都做不了架构师？>>>

用mysql实现类似于oracle 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系统下】

[root@test01 mysql]# cat /etc/my.cnf 
[mysqld]
datadir=/mysql/data
socket=/mysql/data/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

lower_case_table_names=1
max_connections=1000
#max_user_connections=500
wait_timeout=2880

#增加开启federated引擎标识
federated

[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
pid-file=/var/lib/mysqld/mysqld.pid


[root@test01 mysql]#

配置my.cnf文件，在[mysqld]的最后增加 federated federated，然后重启mysql服务。

注意：在window版本中，如果是默认my.ini配置，在[mysqld]的最后增加 federated 。

[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;

本地库A的c_itcomp、def_comp、i_incident表：

DROP TABLE IF EXISTS `c_itcomp`;
CREATE TABLE `c_itcomp` (
  `P_Guid` varchar(40) NOT NULL COMMENT '主键，唯一标识',
  `AF_Pid` varchar(40) DEFAULT NULL COMMENT '部件的父设备',
  `D_AVA_STATE` int(11) DEFAULT NULL,
  `D_CAP_STATE` int(11) DEFAULT NULL,
  `Com_Duration` decimal(18,0) DEFAULT NULL COMMENT '本次持续时间，下一次变化维护，毫秒',
  `Com_StartTime` datetime DEFAULT NULL COMMENT '本次变化的开始时间',
  `Com_EndTime` datetime DEFAULT NULL COMMENT '下一次变化的开始时间，下一次变化时维护',
  `Com_Role` varchar(40) DEFAULT NULL,
  `Com_A_IsUserComfirmed` bit(1) DEFAULT NULL COMMENT '用户是否已确认',
  `A_CanManage` bit(1) DEFAULT NULL COMMENT '是否可网管',
  `A_CanMonitor` bit(1) NOT NULL COMMENT '是否可监视，有监视调度维护，无可连通凭证则不可监视',
  `A_IsStop` bit(1) DEFAULT NULL COMMENT '是否停止监视',
  `A_CIChanged` int(11) DEFAULT NULL COMMENT 'CI相对基线是否发生变化(1=未变更,2=已知变更,3=未知变更)',
  `A_IsEdge` bit(1) DEFAULT NULL COMMENT '是否是边缘交换机',
  `D_BusinessName` varchar(50) DEFAULT NULL COMMENT '业务名称',
  `D_Useage` varchar(500) DEFAULT NULL COMMENT '组件用途',
  `D_Descr` varchar(1000) DEFAULT NULL COMMENT '描述，snmp获取或用户自定义',
  `D_BusinessCode` varchar(200) DEFAULT NULL COMMENT '业务编码',
  `D_HostName` varchar(50) DEFAULT NULL COMMENT '设备名称，设备自身定义的名称',
  `D_ControlAdderss` varchar(200) DEFAULT NULL COMMENT '管理地址，设备Ip|数据库服务名|URI地址',
  `DE_UseState` int(11) DEFAULT NULL COMMENT '组件使用状态，枚举：ITCompState',
  `DF_DutyRole` varchar(1000) DEFAULT NULL COMMENT '责任角色，外键，角色唯一标识',
  `DF_DutyUser` varchar(2000) DEFAULT NULL COMMENT '责任人，外键，人员唯一标识',
  `DE_ITCatalog` int(11) DEFAULT NULL COMMENT '主类别，枚举：ITCompCatalog',
  `DE_ITFunction` int(11) DEFAULT NULL COMMENT '子类别，枚举：ITCompFunction',
  `DE_ITManufacturer` int(11) DEFAULT NULL COMMENT '厂商，枚举：ITCompManufacturer',
  `DE_ITProductSeries` int(11) DEFAULT NULL COMMENT '产品系列，枚举：ITCompProductSeries',
  `D_Version` varchar(100) DEFAULT NULL COMMENT '版本号',
  `DF_Path_Site` varchar(150) DEFAULT NULL COMMENT '外键，Site类别路径的唯一标识，格式：[id#id#id]',
  `DF_Path_Location` varchar(150) DEFAULT NULL COMMENT '外键，Location类别路径的唯一标识，格式：[id#id#id]',
  `DF_Path_Custom` varchar(150) DEFAULT NULL COMMENT '外键，Custom类别路径的唯一标识，格式：[id#id#id]',
  `DF_Right_R` varchar(800) NOT NULL COMMENT '权限读，角色唯一标识，格式：[''id'',''id'',''id'']|[all]|[none]',
  `DF_Right_W` varchar(800) NOT NULL COMMENT '权限写，角色唯一标识，格式：[''id'',''id'',''id'']|[all]|[none]',
  `D_CPU_Unilization` decimal(18,0) DEFAULT NULL COMMENT 'CPU使用率',
  `D_Mem_Unilization` decimal(18,0) DEFAULT NULL COMMENT '内存使用率',
  `D_CPU_State` int(11) DEFAULT NULL,
  `D_Mem_State` int(11) DEFAULT NULL,
  `D_CIP_Building` varchar(100) DEFAULT NULL COMMENT '建筑物',
  `D_CIP_Floor` varchar(50) DEFAULT NULL COMMENT '楼层',
  `D_CIP_OPSystem` varchar(50) DEFAULT NULL COMMENT '操作系统',
  `D_CIP_Bed` varchar(50) DEFAULT NULL COMMENT '机架',
  `D_CIP_SlotInBed` varchar(50) DEFAULT NULL COMMENT '机架上的槽位',
  `D_CIP_Room` varchar(50) DEFAULT NULL COMMENT '房间',
  `DF_CreateUserId` varchar(40) DEFAULT NULL COMMENT '创建人',
  `D_CreateTime` datetime DEFAULT NULL COMMENT '创建时间',
  `DF_UpdateUserId` varchar(40) DEFAULT NULL COMMENT '修改人',
  `D_UpdateTime` datetime DEFAULT NULL COMMENT '修改时间',
  `DF_CheckUserId` varchar(40) DEFAULT NULL COMMENT '检查人',
  `D_CheckTime` datetime DEFAULT NULL COMMENT '检查时间',
  `D_Price` decimal(18,2) DEFAULT NULL COMMENT '单价',
  `D_UsefulYear` decimal(18,2) DEFAULT NULL COMMENT '使用年限',
  `D_NetSalvageValue` decimal(18,2) DEFAULT NULL COMMENT '净残值',
  `D_ExternalSN` varchar(100) DEFAULT NULL COMMENT '资产_外部序列号',
  `D_ProductSN` varchar(100) DEFAULT NULL COMMENT '资产_产品序列号',
  `D_Brand` varchar(100) DEFAULT NULL COMMENT '资产_品牌',
  `D_BuyTime` datetime DEFAULT NULL COMMENT '购买日期',
  `D_BuyCost` decimal(18,2) DEFAULT NULL COMMENT '购买成本',
  `D_WarrantyTime` datetime DEFAULT NULL COMMENT '保修期',
  `DF_CostCustomer` varchar(200) DEFAULT NULL COMMENT '成本客户，从角色中选择，多个RoleID用逗号隔开',
  `DF_CostUser` varchar(200) DEFAULT NULL COMMENT '成本用户，从用户中选择，多个UserID用逗号隔开',
  `D_ImageUrl` varchar(100) DEFAULT NULL,
  `D_RunTime` decimal(18,0) DEFAULT NULL COMMENT '系统运行时间（秒）',
  `D_AssetCode` varchar(100) DEFAULT NULL COMMENT '资产编码,资产号',
  `D_ProviderOrg` varchar(40) DEFAULT NULL COMMENT '供应商',
  `D_BuyContract` varchar(40) DEFAULT NULL COMMENT '购买合同',
  `D_ServiceOrg` varchar(40) DEFAULT NULL COMMENT '服务商',
  `D_ServiceRole` varchar(40) DEFAULT NULL COMMENT '服务角色',
  `D_ServiceContract` varchar(40) DEFAULT NULL,
  `D_OldAssetValue` decimal(18,2) DEFAULT NULL COMMENT '原资产价值',
  `D_FixedAssetValue` decimal(18,2) DEFAULT NULL COMMENT '固定资产价值',
  `D_CIP_BusinessDepart` varchar(40) DEFAULT NULL COMMENT '归口业务部门',
  `D_CIP_Cabinets` varchar(50) DEFAULT NULL COMMENT '机柜',
  `D_CIP_Model` varchar(50) DEFAULT NULL COMMENT '设备型号',
  `D_CIP_CPU` varchar(50) DEFAULT NULL COMMENT 'CPU型号',
  `D_CIP_Memory` varchar(50) DEFAULT NULL COMMENT '内存',
  `D_CIP_HDD` varchar(50) DEFAULT NULL COMMENT '硬盘',
  `D_CIP_NIC` varchar(50) DEFAULT NULL COMMENT '网卡',
  `D_CIP_FiberCard` varchar(50) DEFAULT NULL COMMENT '光纤卡',
  `D_CIP_Power` varchar(50) DEFAULT NULL COMMENT '电源',
  `D_CIP_UsedPowerCount` varchar(50) DEFAULT NULL COMMENT '已通电电源数量',
  `D_CIP_DataBase` varchar(50) DEFAULT NULL COMMENT '数据库',
  `D_CIP_Middleware` varchar(50) DEFAULT NULL COMMENT '中间件',
  `D_CIP_Administrator` varchar(50) DEFAULT NULL COMMENT '管理员',
  `D_CIP_Developer` varchar(50) DEFAULT NULL COMMENT '开发人员',
  `D_CIP_StartUseTime` varchar(50) DEFAULT NULL COMMENT '开始使用时间',
  `D_CIP_ServiceStartTime` varchar(50) DEFAULT NULL COMMENT '维保开始时间',
  `D_CIP_ServiceEndTime` varchar(50) DEFAULT NULL COMMENT '维保终止时间',
  `D_CIP_ServiceProvider` varchar(150) DEFAULT NULL COMMENT '维保商',
  `D_CIP_Contact` varchar(150) DEFAULT NULL COMMENT '联系人',
  `D_CIP_ContactTel` varchar(150) DEFAULT NULL COMMENT '联系电话',
  `D_IPLong` decimal(18,0) DEFAULT NULL,
  `D_ControlUrl` varchar(200) DEFAULT NULL,
  `Com_AvaF` int(11) DEFAULT NULL COMMENT 'ava监视频率',
  `Com_CapF` int(11) DEFAULT NULL COMMENT 'Cap监视频率',
  `DF_LinkTopoID` varchar(50) DEFAULT NULL,
  `D_CIP_BatchNumber` varchar(50) DEFAULT NULL,
  `F_RUserID` varchar(1000) DEFAULT NULL,
  `F_RUserTEXT` varchar(1000) DEFAULT NULL,
  `F_RRoleID` varchar(40) DEFAULT NULL,
  `F_SUserID` varchar(500) DEFAULT NULL,
  `F_SUserTEXT` varchar(100) DEFAULT NULL,
  `F_SRoleID` varchar(40) DEFAULT NULL,
  `F_CUserID` varchar(500) DEFAULT NULL,
  `F_CUserTEXT` varchar(100) DEFAULT NULL,
  `F_CRoleID` varchar(40) DEFAULT NULL,
  `F_IUserID` varchar(500) DEFAULT NULL,
  `F_IUserTEXT` varchar(100) DEFAULT NULL,
  `F_IRoleID` varchar(40) DEFAULT NULL,
  `F_VUserID` varchar(500) DEFAULT NULL,
  `F_VUserTEXT` varchar(100) DEFAULT NULL,
  `F_VRoleID` varchar(40) DEFAULT NULL,
  `F_AUserID` varchar(500) DEFAULT NULL,
  `F_AUserTEXT` varchar(100) DEFAULT NULL,
  `F_ARoleID` varchar(40) DEFAULT NULL,
  `D_Affect` varchar(40) DEFAULT NULL,
  `D_Urgency` varchar(40) DEFAULT NULL,
  `DF_LifeCycle` varchar(40) DEFAULT NULL COMMENT '生命周期，对应d_itcomp_lifecycle',
  `D_SupportCompModel` varchar(40) DEFAULT NULL,
  `D_CIP_SlotCount` int(11) DEFAULT NULL COMMENT '网络设备的槽位数',
  `D_ExpiryDate` datetime DEFAULT NULL COMMENT '到期时间',
  `AF_SlaId` varchar(40) DEFAULT NULL,
  `D_SyslogEncoding` varchar(40) DEFAULT NULL,
  `D_IsReceiveLog` bit(1) DEFAULT NULL,
  `D_ReceiveLogParameter` varchar(100) DEFAULT NULL,
  `D_LastReceiveLogTime` varchar(40) DEFAULT NULL,
  `D_Barcode` varchar(50) DEFAULT NULL COMMENT '1D条形码',
  `A_IsBackup` bit(1) DEFAULT NULL COMMENT '是否是备件,true：备件；false：不是备件',
  `D_ClusterName` varchar(200) DEFAULT NULL COMMENT '集群名称（Vmware用）',
  `D_UID` varchar(100) DEFAULT NULL,
  `DE_VirtualType` int(11) DEFAULT NULL,
  PRIMARY KEY (`P_Guid`),
  UNIQUE KEY `c_itcomp_KEY` (`P_Guid`),
  KEY `x_A_CanManage` (`A_CanMonitor`),
  KEY `x_Com_A_IsUserComfirmed` (`Com_A_IsUserComfirmed`),
  KEY `x_Custom` (`DF_Path_Custom`),
  KEY `x_D_ControlAdderss` (`D_ControlAdderss`),
  KEY `x_DE_ITFunction` (`DE_ITFunction`),
  KEY `x_Loction` (`DF_Path_Location`),
  KEY `x_Site` (`DF_Path_Site`),
  KEY `IT_ITComp_D_Barcode` (`D_Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

DROP TABLE IF EXISTS `def_comp`;
CREATE TABLE `def_comp` (
  `P_Guid` varchar(40) NOT NULL COMMENT '主键，唯一标识',
  `AF_Pid` varchar(40) NOT NULL COMMENT '外键，父节点，如果是根节点则为 Root',
  `D_Key` varchar(20) DEFAULT NULL COMMENT '不是Id，是有意义的唯一标识',
  `D_Caption` varchar(20) DEFAULT NULL COMMENT '别名，默认为D_key',
  `DF_ITCatalog` int(11) DEFAULT NULL COMMENT '主类别，枚举：ITCompCatalog',
  `DF_ITFunction` int(11) DEFAULT NULL COMMENT '子类别，枚举：ITCompFunction',
  `DF_ITManufacturer` int(11) DEFAULT NULL COMMENT '厂商，枚举：ITCompManufacturer',
  `DF_ITProductSeries` int(11) DEFAULT NULL COMMENT '产品系列，枚举：ITCompProductSeries',
  `A_FacePlateId` varchar(50) DEFAULT NULL,
  `A_NeedParent` bit(1) DEFAULT NULL,
  `D_KeyWord` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`P_Guid`),
  UNIQUE KEY `def_comp_KEY` (`P_Guid`),
  KEY `x_Def_Comp_D_Key` (`D_Key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

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;

数据整合的脚本：

insert into inf_monitor_data
  (ID,
   IT_Catalog,
   IT_Function,
   Business_Name,
   Control_Address,
   Title,
   Detail,
   Start_Time,
   End_Time,
   BSM_Type,
   Status,
   Remarks)
  select i.P_Guid,
         case c.DE_ITCatalog
           when 1 then
            '应用'
           when 2 then
            '主机系统'
           when 3 then
            '主机硬件'
           when 4 then
            '网络设备'
           when 6 then
            '存储'
           else
            '其他'
         end,
         case c.DE_ITFunction
           when 101 then
            '网站'
           when 105 then
            '数据库'
           when 106 then
            '中间件'
           when 107 then
            'Web Server'
           when 201 then
            '服务器'
           when 402 then
            '交换机'
           when 403 then
            '防火墙'
           when 404 then
            '负载均衡'
           when 412 then
            '入侵防护'
           else
            '其他'
         end,
         c.D_BusinessName,
         c.D_ControlAdderss,
         i.D_Title,
         i.D_Detail,
         i.D_StartTime,
         i.D_EndTime,
         case i.AE_BSMType
           when 0 then
            'CI'
           when 1 then
            '可用性'
           when 2 then
            '容量'
           when 3 then
            '财务'
           else
            '其他'
         end,
         '0',
         ''
    from i_incident i
    Left outer join c_itcomp c
      on i.AF_SourceITCompId = c.P_Guid;

在远程库B中，创建用户s_monitor，并且将的monitor.inf_monitor_data表授权给s_monitor用户，然后提供给本地库A读写。

mysql> use 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)

#####################################################################下面的IP为B库的ip
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>

在本地库A中，创建用户link表，访问远程库B中的inf_monitor_data

mysql> use smartdb;
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>

脚本及说明如下：

#连接到远程库B中s_monitor用户下的inf_monitor_data
CONNECTION='mysql://s_monitor:s_monitor@192.168.12.***:3306/monitor/inf_monitor_data';

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 mysql;
Database changed
mysql>


DROP TRIGGER IF EXISTS t_afterinsert_on_i_incident;

CREATE TRIGGER t_afterinsert_on_i_incident 

AFTER INSERT ON i_incident

FOR EACH ROW

BEGIN
	 
  insert into link_inf_monitor_data
  (ID,
   IT_Catalog,
   IT_Function,
   Business_Name,
   Control_Address,
   Title,
   Detail,
   Start_Time,
   End_Time,
   BSM_Type,
   Status,
   Remarks)
  select i.P_Guid,
         case c.DE_ITCatalog
           when 1 then
            '应用'
           when 2 then
            '主机系统'
           when 3 then
            '主机硬件'
           when 4 then
            '网络设备'
           when 6 then
            '存储'
           else
            '其他'
         end,
         case c.DE_ITFunction
           when 101 then
            '网站'
           when 105 then
            '数据库'
           when 106 then
            '中间件'
           when 107 then
            'Web Server'
           when 201 then
            '服务器'
           when 402 then
            '交换机'
           when 403 then
            '防火墙'
           when 404 then
            '负载均衡'
           when 412 then
            '入侵防护'
           else
            '其他'
         end,
         c.D_BusinessName,
         c.D_ControlAdderss,
         i.D_Title,
         i.D_Detail,
         i.D_StartTime,
         i.D_EndTime,
         case i.AE_BSMType
           when 0 then
            'CI'
           when 1 then
            '可用性'
           when 2 then
            '容量'
           when 3 then
            '财务'
           else
            '其他'
         end,
         '0',
         ''
    from i_incident i
    Left outer join c_itcomp c
      on i.AF_SourceITCompId = c.P_Guid
	where i.P_Guid=new.P_Guid; 

END;

验证触发器

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 smartdb;
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>

--end--

转载于:https://my.oschina.net/90888/blog/1519409