MySQL MySQL跨服务器选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/810349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Cross Server Select Query
提问by Anand Shah
Is it possible to write a cross server select query using MySQL Client. Basically the setup is like follows.
是否可以使用 MySQL 客户端编写跨服务器选择查询。基本上设置如下。
Server IP Database
--------- --------
1.2.3.4 Test
a.b.c.d Test
服务器 IP 数据库
--------- --------
1.2.3.4 测试
abcd 测试
I want to write a query that will select rows from a table in the Test Database on 1.2.3.4 and insert the result in a table into the Test Database on a.b.c.d
My servers are located miles apart so I will be opening a SSH tunnel to connect the two.
我想编写一个查询，该查询将从 1.2.3.4 上测试数据库中的表中选择行，并将结果插入到表中 abcd 上的测试数据库中
我的服务器相距数英里，因此我将打开 SSH 隧道进行连接他们俩。
Any pointers?
任何指针？
采纳答案by James C
mysqldumpcould be a solution as mentioned already or you could try using the SELECT ... INTO OUTFILEand then LOAD DATA INFILE ...commands.
mysqldump可能是已经提到的解决方案，或者您可以尝试使用SELECT ... INTO OUTFILE然后LOAD DATA INFILE ...命令。
MySQL does have the federated storage engine which might be useful to you. Here's some more documentation on it http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.htmlI have to confess that I've not had huge success with it but it might work for you.
MySQL 确实有可能对您有用的联合存储引擎。这里有一些关于它的更多文档http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html我不得不承认我没有取得巨大的成功，但它可能适用于你。
The third solution would be to do the work in your application. Read in the results of the SELECTquery line by line and INSERTto the other server line by line. You might run into some issues with data types and null handling that way though.
第三种解决方案是在您的应用程序中完成工作。SELECT逐行读入查询结果并逐行读入INSERT其他服务器。尽管如此，您可能会遇到数据类型和空值处理方面的一些问题。
回答by Peter Carrero
how about using federated tables on one of the servers? create the federated tables based on the remote tables you will use in the query and just run your query as if your database was all local. example below from MySQL site
在其中一台服务器上使用联合表怎么样？根据您将在查询中使用的远程表创建联合表，然后运行您的查询，就好像您的数据库都是本地的一样。以下示例来自MySQL 站点
The procedure for using FEDERATED tables is very simple. Normally, you have two servers running, either both on the same host or on different hosts. (It is possible for a FEDERATED table to use another table that is managed by the same server, although there is little point in doing so.)
使用 FEDERATED 表的过程非常简单。通常，您有两台服务器在运行，它们要么在同一台主机上，要么在不同的主机上。（一个 FEDERATED 表可以使用由同一服务器管理的另一个表，尽管这样做没什么意义。）
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 federated database and is defined like this:
首先，您必须在要使用 FEDERATED 表访问的远程服务器上有一个表。假设远程表在联合数据库中并且定义如下：
CREATE TABLE test_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=MyISAM
CHARSET=latin1;
The example uses a MyISAM table, but the table could use any storage engine.
该示例使用 MyISAM 表，但该表可以使用任何存储引擎。
Next, create a FEDERATED table on the local server for accessing the remote table:
接下来，在本地服务器上创建一个 FEDERATED 表用于访问远程表：
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://fed_user@remote_host:9306/federated/test_table';
(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)
（在 MySQL 5.0.13 之前，使用 COMMENT 而不是 CONNECTION。）
回答by staticsan
Since the mysql client can only connect to one server at a time, the short answer is No. But there is always a way...
由于 mysql 客户端一次只能连接到一个服务器，所以简短的回答是否定的。但总有办法......
Recent versions of mysqldumpsupport a --whereparameter that can be used to limit the data dumped. This means you have a way of running a simple SELECT(i.e. all columns on one table) and producing valid SQL to INSERTit. You can then pipe the output of such a mysqldumpcommand for the source server to a mysqlcommand to the destination server.
最新版本mysqldump支持--where可用于限制转储数据的参数。这意味着您有一种方法可以运行一个简单的SELECT（即一个表上的所有列）并为其生成有效的 SQL INSERT。然后，您可以mysqldump将源服务器的此类命令的输出通过管道mysql传输到目标服务器的命令。
You probably want to include a few options like --no-create-infoand --no-add-lockson the mysqldumpcommand. Test it until the output is exactly what you want.
您可能希望在命令中包含一些选项，例如--no-create-info和。测试它，直到输出正是您想要的。--no-add-locksmysqldump
