热门标签 | HotTags
当前位置:  开发笔记 > 后端 > 正文

mysql使用federated实现dblink远程表访问_MySQL

mysql使用federated实现dblink远程表访问
bitsCN.com 1. source 端创建测试表
create table s select * from mysql.user ;

2. source 端查看测试表的建表语句
show create table s ;

<CREATE TABLE `s` (
`Host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;,
`User` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;,
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT &#39;&#39;,
`Select_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Insert_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Update_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Delete_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Drop_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Reload_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Shutdown_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Process_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`File_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Grant_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`References_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Index_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Alter_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Show_db_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Super_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_tmp_table_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Lock_tables_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Execute_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Repl_slave_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Repl_client_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Show_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Alter_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_user_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Event_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Trigger_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_tablespace_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`ssl_type` enum(&#39;&#39;,&#39;ANY&#39;,&#39;X509&#39;,&#39;SPECIFIED&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;&#39;,
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`max_updates` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`max_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`max_user_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`plugin` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT &#39;&#39;,
`authentication_string` text CHARACTER SET utf8 COLLATE utf8_bin,
`password_expired` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;
) ENGINE=InnoDB DEFAULT CHARSET=latin1

EOF

3. source端创建用户并且授权

GRANT ALL PRIVILEGES ON sources.s TO dex IDENTIFIED BY &#39;xiaojun&#39;;
GRANT ALL PRIVILEGES ON sources.s TO dex@192.168.100.42 IDENTIFIED BY &#39;xiaojun&#39;;
SHOW GRANTS FOR dex;

mysql> GRANT ALL PRIVILEGES ON sources.s TO dex IDENTIFIED BY &#39;xiaojun&#39;;
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON sources.s TO dex@192.168.100.42 IDENTIFIED BY &#39;xiaojun&#39;;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR dex;
+----------------------------------------------------------------------------------------------------+
| Grants for dex@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO &#39;dex&#39;@&#39;%&#39; IDENTIFIED BY PASSWORD &#39;*8FDE30312222738F1CD8AC8AF0EE515A9DB8180E&#39; |
| GRANT ALL PRIVILEGES ON `sources`.`s` TO &#39;dex&#39;@&#39;%&#39; |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4. 查看target端是否安装了FEDERATED存储引擎

mysql> 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 |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

看到没有安装federated存储引擎(目标端可以是任何的存储引擎比如说myisam或者innodb)

4.1 先来安装federated存储引擎

install plugin federated soname &#39;ha_federated.so&#39;;

mysql> install plugin federated soname &#39;ha_federated.so&#39;;
ERROR 1125 (HY000): Function &#39;federated&#39; already exists

已经安装好了,只是没有启用

测试一下是否可以

[root@rhel6Mysql02 ~]# mysqld_safe --federated &
[1] 2194
[root@rhel6Mysql02 ~]# 130620 18:12:28 mysqld_safe Logging to &#39;/var/lib/mysql/rhel6Mysql02.err&#39;.
130620 18:12:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

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 | YES | Federated MySQL storage engine | NO | NO | NO |
| 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 |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

好的修改一下my.conf文件

federated

重启一下mysql server
service mysql restart

恩,已经支持了。
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 | YES | Federated MySQL storage engine | NO | NO | NO |
| 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 |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

5. 在target端定义基于federated存储引擎的表links

CREATE TABLE `links` (
`Host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;,
`User` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;,
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT &#39;&#39;,
`Select_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Insert_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Update_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Delete_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Drop_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Reload_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Shutdown_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Process_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`File_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Grant_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`References_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Index_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Alter_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Show_db_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Super_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_tmp_table_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Lock_tables_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Execute_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Repl_slave_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Repl_client_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Show_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Alter_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_user_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Event_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Trigger_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`Create_tablespace_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;,
`ssl_type` enum(&#39;&#39;,&#39;ANY&#39;,&#39;X509&#39;,&#39;SPECIFIED&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;&#39;,
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`max_updates` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`max_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`max_user_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;,
`plugin` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT &#39;&#39;,
`authentication_string` text CHARACTER SET utf8 COLLATE utf8_bin,
`password_expired` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COnNECTION=&#39;mysql://dex:xiaojun@192.168.100.41:3306/sources/s&#39;;



Query OK, 0 rows affected (1.10 sec)

mysql> select count(*) from links;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec) bitsCN.com
推荐阅读
  • 本文详细介绍了如何通过JDBC连接Hive进行数据操作,包括Hive服务的启动、相关依赖的配置以及具体代码示例,适合对Hive和JDBC有一定了解的开发者阅读。 ... [详细]
  • 本文探讨了使用Python实现监控信息收集的方法,涵盖从基础的日志记录到复杂的系统运维解决方案,旨在帮助开发者和运维人员提升工作效率。 ... [详细]
  • binlog2sql,你该知道的数据恢复工具
    binlog2sql,你该知道的数据恢复工具 ... [详细]
  • 本文详细介绍了MySQL InnoDB存储引擎中的Redo Log和Undo Log,探讨了它们的工作原理、存储方式及其在事务处理中的关键作用。 ... [详细]
  • 本文介绍如何使用R语言中的相关包来解析和转换搜狗细胞词库(.scel格式),并将其导出为CSV文件,以便于后续的数据分析和文本挖掘任务。 ... [详细]
  • 本文介绍了Tomcat的基本操作,包括启动、关闭及首次访问的方法,并详细讲解了如何在IDEA中创建Web项目,配置Servlet及其映射,以及如何将项目部署到Tomcat。 ... [详细]
  • 数据输入验证与控件绑定方法
    本文提供了多种数据输入验证函数及控件绑定方法的实现代码,包括电话号码、数字、传真、邮政编码、电子邮件和网址的验证,以及报表绑定和自动编号等功能。 ... [详细]
  • 深入解析Unity3D游戏开发中的音频播放技术
    在游戏开发中,音频播放是提升玩家沉浸感的关键因素之一。本文将探讨如何在Unity3D中高效地管理和播放不同类型的游戏音频,包括背景音乐和效果音效,并介绍实现这些功能的具体步骤。 ... [详细]
  • 在开发过程中,有时需要提供用户创建数据库的功能。本文介绍了如何利用 .NET 和 ADOX 在应用程序中实现创建 Access 数据库,并详细说明了创建数据库及表的具体步骤。 ... [详细]
  • 过去我习惯使用百度空间来记录个人的生活琐事,但随着需求的增长,我发现它的功能略显不足,特别是在代码分享和图片管理方面存在诸多不便。因此,我决定寻找一个更适合技术分享的平台,最终选择了博客园。 ... [详细]
  • ASP.NET 进度条实现详解
    本文介绍了如何在ASP.NET中使用HTML和JavaScript创建一个动态更新的进度条,并通过Default.aspx页面进行展示。 ... [详细]
  • 本文探讨了如何在 Spring MVC 框架下,通过自定义注解和拦截器机制来实现细粒度的权限管理功能。 ... [详细]
  • 本文详细介绍了如何在Oracle数据库中使用SQL进行分页查询,通过嵌套查询和ROWNUM函数的应用,实现数据的高效分页展示。 ... [详细]
  • 在Android应用开发过程中,开发者经常遇到诸如CPU使用率过高、内存泄漏等问题。本文将介绍几种常用的命令及其应用场景,帮助开发者有效定位并解决问题。 ... [详细]
  • hlg_oj_1116_选美大赛这题是最长子序列,然后再求出路径就可以了。开始写的比较乱,用数组什么的,后来用了指针就好办了。现在把代码贴 ... [详细]
author-avatar
彭彭宅男丶
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有