mysql访问远程视图_mysql远程表/视图-应用 - @Jin

Date :20140213

Auth: Jin

参考

http://blog.sina.com.cn/s/blog_757b0e130101erl5.html

http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#federated-storage-engine

一、问题描述

1.目前应用情况

在mysql 3304实例中有phpcms，anquanzuo两个库，

phpcms有数据写入，anquanzu没有数据写入

anquanzu有一个视图表carseats_article 为phpcms库的三个表v9_news，v9_news_data，v9_category组合的视图

3304-anquanzuo>show create table carseats_article\G

*************************** 1. row ***************************

View: carseats_article

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`dbselect`@`192.168.201.201` SQL SECURITY DEFINER VIEW `carseats_article` AS select `a`.`id` AS `id`,`a`.`title` AS `title`,`a`.`catid` AS `module_id`,`a`.`thumb` AS `img`,`c`.`catname` AS `module_name`,`b`.`content` AS `content`,`a`.`username` AS `publish_user`,`a`.`status` AS `is_del`,`a`.`listorder` AS `order`,`a`.`updatetime` AS `update_time`,`a`.`inputtime` AS `add_time`,`b`.`copyfrom` AS `copyfrom`,`a`.`keywords` AS `keywords`,`a`.`islink` AS `islink`,`a`.`url` AS `url` from ((`phpcms`.`v9_news` `a` join `phpcms`.`v9_news_data` `b`) join `phpcms`.`v9_category` `c`) where ((`a`.`catid` = `c`.`catid`) and (`a`.`id` = `b`.`id`))

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

2、数据迁移

phpcms 迁移到mmm集群

anquanzuo 迁移至NDB Cluster (两台数据节点245,246，管理节点247)

3,问题

迁移anquanzuo库时无法创建carseats_article，因为phpcms库不在一个实例了

4、解决办法

以前SQL Server工作有用到DB link远程库

而mysql支持远程表

1)在phpcms创建需要的视图

2)在anquanzuo上创建远程表

二、创建mmm上phpcms的视图

到mmm的master服务器上

$ jump 121

# mysql -h 127.0.0.1 -P 3304 -u root -pjpasswd2011 phpcms

1、创建视图

DROP VIEW IF EXISTS `v_carseats_article`;

CREATE VIEW `v_carseats_article` AS

SELECT

`a`.`id` AS `id`,

`a`.`title` AS `title`,

`a`.`catid` AS `module_id`,

`a`.`thumb` AS `img`,

`c`.`catname` AS `module_name`,

`b`.`content` AS `content`,

`a`.`username` AS `publish_user`,

`a`.`status` AS