首先看看有没有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系统下】
- [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
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
- federated
配置好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;
本地库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;
在本地库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';
- <!--
- s_monitor:s_monitor=username:password
- 192.168.12.***=source ip
- 3306=source mysql server listener port
- monitor=source database name
- inf_monitor_data=source table name
- -->
创建触发器
- mysql> use s_monitor;
- 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 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>