我需要从其他数据库连接表，有时其他服务器 [英] I need to join table from other database and sometimes other server

问题描述

我的项目有自己的数据库。另外，我使用用户表，这是在其他数据库。两个办公室的数据在同一个服务器上，但第三个在其他服务器上有自己的用户表。

因此，在大量查询中，我需要加入表some_db .users或other_server.some_db.users

您会为此方案提供什么解决方案？

。

解决方案

有 MySQL中的联合表：

FEDERATED存储引擎允许您从远程MySQL
数据库访问数据，而无需使用复制或群集技术。查询
本地FEDERATED表自动从远程
（联合）表中提取数据。

首先，您必须在远程服务器上有一个要访问的表，方法是使用一个FEDERATED表。假设远程表在sakila数据库中，并且定义如下：

  CREATE TABLE test_table（
 id INT （20）NOT NULL AUTO_INCREMENT，
 name VARCHAR（32）NOT NULL 
 PRIMARY KEY（id）
）
 ENGINE = MyISAM 
 DEFAULT CHARSET = latin1; 
  

接下来，在本地服务器上创建一个用于访问远程表的FEDERATED表：

  CREATE TABLE federated_table（
 id INT（20）NOT NULL AUTO_INCREMENT，
 name VARCHAR（32）NOT NULL 
 PRIMARY KEY（id）
）
 ENGINE = FEDERATED 
 DEFAULT CHARSET = latin1 
 CONNECTION ='mysql：// fed_user：fed_user@197.186.1.199：3306 / sakila / test_table '; 
  

连接字符串示例：

  CONNECTION ='mysql：// username：password @ hostname：port / database / tablename'
 CONNECTION ='mysql：// username @ hostname / database / tablename'
 CONNECTION ='mysql：// username：password @ hostname / database / tablename'
  

表应与远程表的匹配，但ENGINE表选项应为FEDERATED。

执行：

 显示％federated％ 
  

以检查您的本地服务器上是否提供FEDERATED存储引擎。

localhost中的表 federated_table 成为远程服务器中的 test_table 的虚表。

现在，您可以在本地主机服务器的数据库中的表之间使用JOIN。如果在本地主机服务器中有一个名为 test 的表，并且希望使用远程服务器中的前sakila.test_table JOIN，请编写一个如显示的查询

  SELECT * FROM`federated_table` JOIN`test` 
  

查询中的 federated_table

>

默认情况下，运行的服务器中未启用FEDERATED存储引擎;要启用FEDERATED，必须使用 - federated 选项启动MySQL服务器二进制文件。

注意：

可选存储引擎需要权限，并且在 - skip-grant-tables 。

整个数据库将无法加载，日志中将显示以下错误：

  110318 21:37:23 [错误] / usr / local / libexec / mysqld：未知选项'--federated'
  

这意味着如果您具有联合表，则需要从两个步骤完成从5.x升级。一旦使用 - skip-grant-tables 且不使用 - federated --skip-grant-tables 和 - 联合。

FEDERATED存储引擎

My project has its own database. Also, I use table of users, which is on other database. Two offices have their data on the same server, but third one has its own user table on other server.

So, in lots of queries I need to join either table some_db.users or other_server.some_db.users

What solution would you advise for this scenario?

I use MySQL.

解决方案

There is Federated tables in MySQL:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the sakila database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user:fed_user@197.186.1.199:3306/sakila/test_table';

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

The basic structure of this table should match that of the remote table, except that the ENGINE table option should be FEDERATED.

Execute:

show variables like '%federated%'; 

to check if FEDERATED storage engine is available on your local server.

The table federated_table in localhost becomes virtual table of test_table in remote server.

Now you can use the JOIN between the tables in a DB in the localhost server. If there is a table called test in your localhost server, and you want to JOIN with the former sakila.test_table which is in the remote server, write a query like the one shown below:

SELECT * FROM `federated_table` JOIN `test`;

The federated_table in the query will actually refer to test_table in remote server.

---

On enabling FEDERATED Storage Engine

The FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option.

NOTE:
Optional storage engines require privileges and will fail to load when --skip-grant-tables is specified.

The result the entire db will fail to load and the following error will appear in the logs:

110318 21:37:23 [ERROR] /usr/local/libexec/mysqld: unknown option '--federated'

This in turn means that an upgrade from 5.x needs to be done in two steps if you have federated tables. Once with --skip-grant-tables and without --federated, the once without --skip-grant-tables and with --federated.

Source: The FEDERATED Storage Engine

这篇关于我需要从其他数据库连接表，有时其他服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！