如何在 2 个 MySQL 数据库之间传输数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3242504/
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
How can I transfer data between 2 MySQL databases?
提问by Morano88
I want to do that using a code and not using a tool like "MySQL Migration Toolkit". The easiest way I know is to open a connection (using MySQL connectors) to DB1 and read its data. Open connection to DB2 and write the data to it. Is there a better/easiest way ?
我想使用代码而不是使用像“MySQL 迁移工具包”这样的工具来做到这一点。我知道的最简单的方法是打开一个到 DB1 的连接（使用 MySQL 连接器）并读取它的数据。打开与 DB2 的连接并将数据写入其中。有更好/最简单的方法吗？
采纳答案by Recurse
First I'm going to assume you aren't in a position to just copy the data/ directory, because if you are then using your existing snapshot/backup/restore will probably suffice (and test your backup/restore procedures into the bargain).
首先，我将假设您不能只复制数据/目录，因为如果您使用现有的快照/备份/恢复可能就足够了（并测试您的备份/恢复程序到讨价还价） .
In which case, if the two tables have the same structure generally the quickest, and ironically the easiest approach will be to use SELECT...INTO OUTFILE... on one end, and LOAD DATA INFILE... on the other.
在这种情况下，如果两个表具有相同的结构，通常最快，具有讽刺意味的是，最简单的方法是在一端使用 SELECT...INTO OUTFILE...，在另一端使用 LOAD DATA INFILE...。
See http://dev.mysql.com/doc/refman/5.1/en/load-data.htmland .../select.html for definitive details.
有关明确的详细信息，请参阅http://dev.mysql.com/doc/refman/5.1/en/load-data.html和 .../select.html。
For trivial tables the following will work:
对于普通表，以下将起作用：
SELECT * FROM mytable INTO OUTFILE '/tmp/mytable.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' ;
LOAD DATA INFILE '/tmp/mytable.csv' INTO TABLE mytable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' ;
We have also used FIFO's to great effect to avoid the overhead of actually writing to disk, or if we do need to write to disk for some reason, to pipe it through gzip.
我们还使用了 FIFO 来避免实际写入磁盘的开销，或者如果我们出于某种原因确实需要写入磁盘，则可以通过 gzip 将其通过管道传输。
ie.
IE。
mkfifo /tmp/myfifo
gzip -c /tmp/myfifo > /tmp/mytable.csv.gz &
... SEL
ECT... INTO OUTFILE '/tmp/myfifo' .....
wait
gunzip -c /tmp/mytable.csv.gz > /tmp/myfifo &
... LOAD DATA INFILE /tmp/myfifo .....
wait
Basically, one you direct the table data to a FIFO you can compress it, munge it, or tunnel it across a network to your hearts content.
基本上，您将表数据定向到 FIFO，您可以对其进行压缩、处理或通过网络将其传输到您的核心内容。
回答by Wrikken
The FEDERATED storage engine? Not the fastest one in the bunch, but for one time, incidental, or small amounts of data it'll do. That is assuming you're talking about 2 SERVERS. With 2 databases on one and the same server it'll simply be:
联邦存储引擎？不是最快的，但有一次，偶然的或少量的数据它会做。那是假设您在谈论 2 个服务器。在一台和同一台服务器上有 2 个数据库，它只是：
INSERT INTO databasename1.tablename SELECT * FROM databasename2.tablename;
回答by Messa
You can use mysqldumpand mysql(the command line client). These are command line tools and in the question you write you don't want to use them, but still using them (even by running them from your code) is the easiest way; mysqldumpsolves a lot of problems.
您可以使用mysqldump和mysql（命令行客户端）。这些是命令行工具，在您编写的问题中您不想使用它们，但仍然使用它们（即使通过从您的代码运行它们）是最简单的方法；mysqldump解决了很多问题。
You can make selects from one database and insertto the other, which is pretty easy. But if you need also to transfer the database schema (create tables etc.), it gets little bit more complicated, which is the reason I recommend mysqldump. But lot of PHP-MySQL-admin tools also does this, so you can use them or look at their code.
您可以将selects 从一个数据库创建到另一个数据库insert，这非常简单。但是如果您还需要传输数据库模式（create tables 等），它会变得稍微复杂一些，这就是我推荐mysqldump. 但是很多 PHP-MySQL-admin 工具也这样做，所以你可以使用它们或查看它们的代码。
Or maybe you can use MySQL replication.
或者您可以使用 MySQL 复制。
回答by ErichBSchulz
from http://dev.mysql.com/doc/refman/5.0/en/rename-table.html:
来自http://dev.mysql.com/doc/refman/5.0/en/rename-table.html：
As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:
只要两个数据库在同一个文件系统上，就可以使用 RENAME TABLE 将表从一个数据库移动到另一个数据库：
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
回答by desertwebdesigns
If you enabled binary logging on your current server (and have all the bin logs) you can setup replication for the second server
如果您在当前服务器上启用了二进制日志记录（并拥有所有二进制日志），您可以为第二台服务器设置复制
