MySQL 存储过程查询远程数据库的方法

这几天有个任务，需要在存储过程中读取远程数据库一个表的数据存到本地表，然后进行计算。 在网上查找了资料，Oracle和SQL Server查询远程数据库是比较方便的，MySQL麻烦一些，需要在本地建立一个需要查询的远程的表结构完全一样的表，并且是Federate引擎。

1、首先，用show ENGINES语句查看是否安装Federate引擎。 如果有Federate记录但是Support字段是no就需要配置，修改my.ini（如果是linux就是my.cnf），在[mysqld]下面添加一行federated，保存退出后重启mysql服务。 如果没有记录，就需要编译安装federated 引擎，就不说了

2、在本地数据库中建表，结构和需要读取的远程数据库中的表完全一样，但是需要在最后指定Federate引擎：

CREATE TABLE federated_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://username:password@remote_host:port/federated/test_table';

对CONNECTION后面的字符串解释一下 mysql：支持的连接协议，在这里只支持mysql username：本地数据库连接远程数据库的用户名 password：密码 remote_host：远程数据库地址 port：端口号，例如3306，3936 federated：远程数据库实例 test_table：需要查询的数据库表名

3、在远程数据库上赋予本地数据库查询权限 在本地建立Federate表后是无法直接读取的，会出现如下错误：

1429 - Unable to connect to foreign data source: Can't connect to MySQL server on 'hostname'

这是因为本地数据库没有权限查询远程数据库的表，需要在远程数据库中添加本地username的查询权限 在远程数据库的mysql实例中赋予本地数据库查询权限：

GRANT SELECT ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

其中： SELECT是赋予的权限，也可以是UPDATE，DELETE等，用逗号分隔开 username是需要赋予的本地数据库用户名 localhost是本地数据库地址 password是本地数据库用户密码

这样就可以像查询本地表一样查询远程数据库表了