mysqldblink如何链接mysql库-古蔺大橙子建站

新闻中心

这里有您想知道的互联网营销解决方案

mysqldblink如何链接mysql库

今天就跟大家聊聊有关MySQL dblink如何链接mysql库，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

城北网站制作公司哪家好，找成都创新互联公司！从网页设计、网站建设、微信开发、APP开发、成都响应式网站建设公司等网站项目制作，到程序开发，运营维护。成都创新互联公司于2013年开始到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选成都创新互联公司。

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_DETAIL\G;
*************************** 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> select count(*) from F_ORDERINFO_DETAIL;
+----------+
| count(*) |
+----------+
| 2900 |
+----------+

源端：

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> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from F_ORDERINFO_DETAIL;
+----------+
| count(*) |
+----------+
| 5800 |
+----------+
1 row in set (0.00 sec)

目标端：

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 5800 |
+----------+
1 row in set (0.00 sec)

看完上述内容，你们对mysql dblink如何链接mysql库有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注创新互联行业资讯频道，感谢大家的支持。

当前标题：mysqldblink如何链接mysql库
网站网址：http://scgulin.cn/article/pccpgh.html

新闻中心

其他资讯