建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
用MySQL實現(xiàn)類似于Oracledblink的功能
首先看看有沒有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
看到?jīng)]有安裝federated存儲引擎(目標(biāo)端可以是任何的存儲引擎比如說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
默認(rèn)mysql是沒有開啟federated存儲引擎【linux系統(tǒng)下】
- 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文件，我這里直接搜索的?！緇inux系統(tǒng)下】
- [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 就可以?！緇inux系統(tǒng)下】
- [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服務(wù)。
- [root@test01 ~]# service mysql restart
- mysql: 未被識別的服務(wù)
- You have new mail in /var/spool/mail/root
- [root@test01 ~]#
重啟的時候提示：未被識別的服務(wù)，解決方法如下：
- [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 服務(wù)是否啟動了。
- 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>
好了，F(xiàn)EDERATED值為YES了。
目標(biāo)：本地庫A的三張表，整合數(shù)據(jù)后，寫入遠(yuǎn)程庫B的一張表中。
下面我們來建表
遠(yuǎn)程庫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 '業(yè)務(wù)名稱',
- `Control_Address` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '管理地址，設(shè)備Ip|數(shù)據(jù)庫服務(wù)名|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 '采集查詢結(jié)束時間',
- `BSM_Type` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '對應(yīng)其是哪類監(jiān)視器的事件',
- `Status` varchar(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '處理標(biāo)記',
- `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 '主鍵，唯一標(biāo)識',
- `AF_Pid` varchar(40) DEFAULT NULL COMMENT '部件的父設(shè)備',
- `D_AVA_STATE` int(11) DEFAULT NULL,
- `D_CAP_STATE` int(11) DEFAULT NULL,
- `Com_Duration` decimal(18,0) DEFAULT NULL COMMENT '本次持續(xù)時間，下一次變化維護(hù)，毫秒',
- `Com_StartTime` datetime DEFAULT NULL COMMENT '本次變化的開始時間',
- `Com_EndTime` datetime DEFAULT NULL COMMENT '下一次變化的開始時間，下一次變化時維護(hù)',
- `Com_Role` varchar(40) DEFAULT NULL,
- `Com_A_IsUserComfirmed` bit(1) DEFAULT NULL COMMENT '用戶是否已確認(rèn)',
- `A_CanManage` bit(1) DEFAULT NULL COMMENT '是否可網(wǎng)管',
- `A_CanMonitor` bit(1) NOT NULL COMMENT '是否可監(jiān)視，有監(jiān)視調(diào)度維護(hù)，無可連通憑證則不可監(jiān)視',
- `A_IsStop` bit(1) DEFAULT NULL COMMENT '是否停止監(jiān)視',
- `A_CIChanged` int(11) DEFAULT NULL COMMENT 'CI相對基線是否發(fā)生變化(1=未變更,2=已知變更,3=未知變更)',
- `A_IsEdge` bit(1) DEFAULT NULL COMMENT '是否是邊緣交換機(jī)',
- `D_BusinessName` varchar(50) DEFAULT NULL COMMENT '業(yè)務(wù)名稱',
- `D_Useage` varchar(500) DEFAULT NULL COMMENT '組件用途',
- `D_Descr` varchar(1000) DEFAULT NULL COMMENT '描述，snmp獲取或用戶自定義',
- `D_BusinessCode` varchar(200) DEFAULT NULL COMMENT '業(yè)務(wù)編碼',
- `D_HostName` varchar(50) DEFAULT NULL COMMENT '設(shè)備名稱，設(shè)備自身定義的名稱',
- `D_ControlAdderss` varchar(200) DEFAULT NULL COMMENT '管理地址，設(shè)備Ip|數(shù)據(jù)庫服務(wù)名|URI地址',
- `DE_UseState` int(11) DEFAULT NULL COMMENT '組件使用狀態(tài)，枚舉：ITCompState',
- `DF_DutyRole` varchar(1000) DEFAULT NULL COMMENT '責(zé)任角色，外鍵，角色唯一標(biāo)識',
- `DF_DutyUser` varchar(2000) DEFAULT NULL COMMENT '責(zé)任人，外鍵，人員唯一標(biāo)識',
- `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 '產(chǎn)品系列，枚舉：ITCompProductSeries',
- `D_Version` varchar(100) DEFAULT NULL COMMENT '版本號',
- `DF_Path_Site` varchar(150) DEFAULT NULL COMMENT '外鍵，Site類別路徑的唯一標(biāo)識，格式：[id#id#id]',
- `DF_Path_Location` varchar(150) DEFAULT NULL COMMENT '外鍵，Location類別路徑的唯一標(biāo)識，格式：[id#id#id]',
- `DF_Path_Custom` varchar(150) DEFAULT NULL COMMENT '外鍵，Custom類別路徑的唯一標(biāo)識，格式：[id#id#id]',
- `DF_Right_R` varchar(800) NOT NULL COMMENT '權(quán)限讀，角色唯一標(biāo)識，格式：[''id'',''id'',''id'']|[all]|[none]',
- `DF_Right_W` varchar(800) NOT NULL COMMENT '權(quán)限寫，角色唯一標(biāo)識，格式：[''id'',''id'',''id'']|[all]|[none]',
- `D_CPU_Unilization` decimal(18,0) DEFAULT NULL COMMENT 'CPU使用率',
- `D_Mem_Unilization` decimal(18,0) DEFAULT NULL COMMENT '內(nèi)存使用率',
- `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 '操作系統(tǒng)',
- `D_CIP_Bed` varchar(50) DEFAULT NULL COMMENT '機(jī)架',
- `D_CIP_SlotInBed` varchar(50) DEFAULT NULL COMMENT '機(jī)架上的槽位',
- `D_CIP_Room` varchar(50) DEFAULT NULL COMMENT '房間',
- `DF_CreateUserId` varchar(40) DEFAULT NULL COMMENT '創(chuàng)建人',
- `D_CreateTime` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',
- `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 '資產(chǎn)_外部序列號',
- `D_ProductSN` varchar(100) DEFAULT NULL COMMENT '資產(chǎn)_產(chǎn)品序列號',
- `D_Brand` varchar(100) DEFAULT NULL COMMENT '資產(chǎn)_品牌',
- `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 '系統(tǒng)運行時間（秒）',
- `D_AssetCode` varchar(100) DEFAULT NULL COMMENT '資產(chǎn)編碼,資產(chǎn)號',
- `D_ProviderOrg` varchar(40) DEFAULT NULL COMMENT '供應(yīng)商',
- `D_BuyContract` varchar(40) DEFAULT NULL COMMENT '購買合同',
- `D_ServiceOrg` varchar(40) DEFAULT NULL COMMENT '服務(wù)商',
- `D_ServiceRole` varchar(40) DEFAULT NULL COMMENT '服務(wù)角色',
- `D_ServiceContract` varchar(40) DEFAULT NULL,
- `D_OldAssetValue` decimal(18,2) DEFAULT NULL COMMENT '原資產(chǎn)價值',
- `D_FixedAssetValue` decimal(18,2) DEFAULT NULL COMMENT '固定資產(chǎn)價值',
- `D_CIP_BusinessDepart` varchar(40) DEFAULT NULL COMMENT '歸口業(yè)務(wù)部門',
- `D_CIP_Cabinets` varchar(50) DEFAULT NULL COMMENT '機(jī)柜',
- `D_CIP_Model` varchar(50) DEFAULT NULL COMMENT '設(shè)備型號',
- `D_CIP_CPU` varchar(50) DEFAULT NULL COMMENT 'CPU型號',
- `D_CIP_Memory` varchar(50) DEFAULT NULL COMMENT '內(nèi)存',
- `D_CIP_HDD` varchar(50) DEFAULT NULL COMMENT '硬盤',
- `D_CIP_NIC` varchar(50) DEFAULT NULL COMMENT '網(wǎng)卡',
- `D_CIP_FiberCard` varchar(50) DEFAULT NULL COMMENT '光纖卡',
- `D_CIP_Power` varchar(50) DEFAULT NULL COMMENT '電源',
- `D_CIP_UsedPowerCount` varchar(50) DEFAULT NULL COMMENT '已通電電源數(shù)量',
- `D_CIP_DataBase` varchar(50) DEFAULT NULL COMMENT '數(shù)據(jù)庫',
- `D_CIP_Middleware` varchar(50) DEFAULT NULL COMMENT '中間件',
- `D_CIP_Administrator` varchar(50) DEFAULT NULL COMMENT '管理員',
- `D_CIP_Developer` varchar(50) DEFAULT NULL COMMENT '開發(fā)人員',
- `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 '聯(lián)系人',
- `D_CIP_ContactTel` varchar(150) DEFAULT NULL COMMENT '聯(lián)系電話',
- `D_IPLong` decimal(18,0) DEFAULT NULL,
- `D_ControlUrl` varchar(200) DEFAULT NULL,
- `Com_AvaF` int(11) DEFAULT NULL COMMENT 'ava監(jiān)視頻率',
- `Com_CapF` int(11) DEFAULT NULL COMMENT 'Cap監(jiān)視頻率',
- `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 '生命周期，對應(yīng)d_itcomp_lifecycle',
- `D_SupportCompModel` varchar(40) DEFAULT NULL,
- `D_CIP_SlotCount` int(11) DEFAULT NULL COMMENT '網(wǎng)絡(luò)設(shè)備的槽位數(shù)',
- `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 '主鍵，唯一標(biāo)識',
- `AF_Pid` varchar(40) NOT NULL COMMENT '外鍵，父節(jié)點，如果是根節(jié)點則為 Root',
- `D_Key` varchar(20) DEFAULT NULL COMMENT '不是Id，是有意義的唯一標(biāo)識',
- `D_Caption` varchar(20) DEFAULT NULL COMMENT '別名，默認(rèn)為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 '產(chǎn)品系列，枚舉：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 '主鍵，唯一標(biāo)識',
- `D_Owner` varchar(40) DEFAULT NULL COMMENT '所有者，單選一個用戶（不是角色）',
- `D_Title` varchar(300) NOT NULL COMMENT '摘要',
- `D_Title_Confirm` varchar(300) DEFAULT NULL COMMENT '摘要,工程師確認(rèn)',
- `AE_ChannelModule` int(11) NOT NULL COMMENT '來源模塊，枚舉ChannelModule：1=Monitor,2=NetWork,3=Syslo
標(biāo)題名稱：用MySQL實現(xiàn)類似于Oracledblink的功能
URL網(wǎng)址：http://www.5511xx.com/article/cocgogi.html
