mysql dblink怎么做链接mysql库
发布时间：2021-12-18 13:54:25 所属栏目：MySql教程 来源：互联网
导读：今天就跟大家聊聊有关mysql dblink如何链接mysql库，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。 1、查看target端是否安装了FEDERATED存储引擎 mysql show engines ; +--------------------+--
|
今天就跟大家聊聊有关mysql dblink如何链接mysql库，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。 1、查看target端是否安装了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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 注意：如果没有安装FEDERATED 引擎 执行install plugin federated soname 'ha_federated.so'; 这里已经安装好了，只是没有启用 2、将federated添加到my.cnf 重启数据库 vi /etc/my.cnf [mysqld] federated service mysql restart mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | 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 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 这里FEDERATED 引擎已经启用。 源端： 目标的要dblink源端的表结构 show create table F_ORDERINFO_DETAILG; *************************** 1. row *************************** Table: F_ORDERINFO_DETAIL Create Table: CREATE TABLE `F_ORDERINFO_DETAIL` ( `SYSTEM_TYPE` varchar(100) DEFAULT NULL, `ORDER_DATE` int(6) DEFAULT NULL, `CUSTOMER_ID` varchar(40) DEFAULT NULL, `UBI_UID` varchar(40) DEFAULT NULL, `FOI_ORDERTIME` varchar(100) DEFAULT NULL, `FOI_ORDERNO` varchar(40) DEFAULT NULL, `FOI_KEY` varchar(2500) DEFAULT NULL, `FOI_KEYTYPE` varchar(40) DEFAULT NULL, `CODENAME` varchar(100) DEFAULT NULL, `ORDER_STATUS` varchar(20) DEFAULT NULL, `FOI_ORDERTYPE` varchar(60) DEFAULT NULL, `QYZM` int(11) DEFAULT NULL, `GDCZ` int(11) DEFAULT NULL, `GLRY` int(11) DEFAULT NULL, `QYDWTZ` int(11) DEFAULT NULL, `FRDWTZ` int(11) DEFAULT NULL, `FRZWRZ` int(11) DEFAULT NULL, `CXFR` int(11) DEFAULT NULL, `CXGD` int(11) DEFAULT NULL, `CXGG` int(11) DEFAULT NULL, `ORDER_TYPE` varchar(20) DEFAULT NULL, `DESC_INFO2` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select count(*) from F_ORDERINFO_DETAIL; +----------+ | count(*) | +----------+ | 2900 | +----------+ 目标端： CREATE TABLE `t` ( `SYSTEM_TYPE` varchar(100) DEFAULT NULL, `ORDER_DATE` int(6) DEFAULT NULL, `CUSTOMER_ID` varchar(40) DEFAULT NULL, `UBI_UID` varchar(40) DEFAULT NULL, `FOI_ORDERTIME` varchar(100) DEFAULT NULL, `FOI_ORDERNO` varchar(40) DEFAULT NULL, `FOI_KEY` varchar(2500) DEFAULT NULL, `FOI_KEYTYPE` varchar(40) DEFAULT NULL, `CODENAME` varchar(100) DEFAULT NULL, `ORDER_STATUS` varchar(20) DEFAULT NULL, `FOI_ORDERTYPE` varchar(60) DEFAULT NULL, `QYZM` int(11) DEFAULT NULL, `GDCZ` int(11) DEFAULT NULL, `GLRY` int(11) DEFAULT NULL, `QYDWTZ` int(11) DEFAULT NULL, `FRDWTZ` int(11) DEFAULT NULL, `FRZWRZ` int(11) DEFAULT NULL, `CXFR` int(11) DEFAULT NULL, `CXGD` int(11) DEFAULT NULL, `CXGG` int(11) DEFAULT NULL, `ORDER_TYPE` varchar(20) DEFAULT NULL, `DESC_INFO2` varchar(50) DEFAULT NULL ) ENGINE=federated connection = 'mysql://root:123456@192.168.1.5:3306/czb/F_ORDERINFO_DETAIL'; 注意：源端表结构 engine=federated connection = 'mysql://用户:密码@IP地址:端口/库名称/表名称'; 源端： mysql> insert into F_ORDERINFO_DETAIL select * from F_ORDERINFO_DETAIL; Query OK, 2900 rows affected (0.28 sec) Records: 2900 Duplicates: 0 Warnings: 0 目标端： mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 5800 | +----------+ 1 row in set (0.00 sec) 看完上述内容，你们对mysql dblink如何链接mysql库有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。 （编辑：52站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
