MYSQL建立dblink
mysql的dblink就是访问目标数据库,通过dblink连接的方式来访问到源数据库的数据。
这里源端为远程服务器,目标端为本地
查看本地mysql的有没有federated 引擎:
mysql> show engines;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | 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 |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
安装了federated,但没启动。(如果没有安装FEDERATED 引擎 执行install plugin federated soname 'ha_federated.so';)
在mysql目录下的my.ini文件里添加一行
federated
重启mysql服务,然后查看federated
mysql> show engines;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | 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 |
| CSV | YES | CSV storage engine
| 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 |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
看到已开启
在本地创建和远程数据库的表的表结构一致的表,以及远程数据库的连接:例如
CREATE TABLE dblink_view (
bank_name VARCHAR(50),
sys_bank_code VARCHAR(12),
PROV_INPUT_NAME VARCHAR(20),
NAME VARCHAR(10)
)ENGINE=FEDERATED CONNECTION='mysql://bankinfo:bankinfo@192.168.3.42:3307/t2_cpv2/view_bt_input_bank_info';
engine=federated connection = 'mysql://用户:密码@IP地址:端口/库名称/表名称';
完成后就可以本地数据库查到远程库数据:SELECT * FROM dblink_view;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库