MySQL跨服务器选择查询 [英] MySQL Cross Server Select Query

问题描述

是否可以使用MySQL客户端编写跨服务器选择查询.基本上，设置如下所示.

Is it possible to write a cross server select query using MySQL Client. Basically the setup is like follows.

服务器IP.数据库
---------     --------
1.2.3.4    测试
a.b.c.d    测试

Server IP       Database
---------       --------
1.2.3.4       Test
a.b.c.d       Test

我想编写一个查询，该查询将从1.2.3.4的测试数据库"中的表中选择行，并将结果插入到a.b.c.d的测试数据库"中.
我的服务器相距数英里，因此我将打开SSH隧道来连接两者.

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.

有指针吗?

推荐答案

mysqldump可能是已经提到的解决方案，或者您可以尝试先使用SELECT ... INTO OUTFILE然后使用LOAD DATA INFILE ...命令.

mysqldump could be a solution as mentioned already or you could try using the SELECT ... INTO OUTFILE and then LOAD DATA INFILE ... commands.

MySQL确实具有联合存储引擎，这可能对您有用.这是有关它的更多文档 http://dev.mysql .com/doc/refman/5.0/en/federated-storage-engine.html 我不得不承认我并没有取得巨大的成功，但它可能对您有用.

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.html I have to confess that I've not had huge success with it but it might work for you.

第三个解决方案是在您的应用程序中完成工作.逐行读取SELECT查询的结果，并将INSERT的结果逐行读取到另一个服务器.但是，您可能会遇到数据类型和null处理这样的问题.

The third solution would be to do the work in your application. Read in the results of the SELECT query line by line and INSERT to the other server line by line. You might run into some issues with data types and null handling that way though.

这篇关于MySQL跨服务器选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！