温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

mysql dblink如何链接mysql库

发布时间:2021-11-16 15:48:25 来源:亿速云 阅读:607 作者:柒染 栏目:MySQL数据库

今天就跟大家聊聊有关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_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';

  1. 注意:源端表结构 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库有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注亿速云行业资讯频道,感谢大家的支持。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI