我如何在2个MySQL数据库之间传输数据?[英] How can I transfer data between 2 MySQL databases?

本文是小编为大家收集整理的关于我如何在2个MySQL数据库之间传输数据?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想使用代码,而不是使用诸如" MySQL迁移工具包"之类的工具.我知道的最简单方法是打开连接(使用MySQL连接器)向DB1打开并读取其数据.打开与DB2的连接并将数据写入其中.有更好/最简单的方法吗?

推荐答案

首先,我将假设您不适合复制数据/目录,因为如果您正在使用现有快照/备份/恢复可能会足够(并测试您的备份/将程序还原成讨价还价).

在这种情况下,如果两个表具有相同的结构,则通常是最快的,具有讽刺意味其他.

参见 http://dev.mysql.com/doc.mysql.com/doc/refman/5.1/en/load-data.html 和.../select.html有关确定的详细信息.

对于微不足道的表格,以下可行:

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' ;

我们还使用了FIFO的效果,以避免实际写入磁盘的开销,或者,如果我们确实需要写入磁盘,以通过GZIP将其输送.

即.

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

基本上,您可以将表数据引导到FIFO,您可以将其压缩,munge或在网络上将其隧穿到您的心中.

其他推荐答案

联合存储引擎?不是一束中最快的,而是一次,偶然或少量数据.假设您是在谈论2个服务器.在一台和同一服务器上有2个数据库,它将仅是:

INSERT INTO databasename1.tablename SELECT * FROM databasename2.tablename;

其他推荐答案

您可以使用mysqldump和mysql(命令行客户端).这些是命令行工具,在您编写的问题中,您不想使用它们,但是仍然使用它们(即使是从代码中运行它们)是最简单的方法. mysqldump解决了很多问题.

您可以从一个数据库中制作select s,然后insert到另一个数据库,这很容易.但是,如果您还需要传输数据库架构(create table s等),它会变得更复杂,这就是我推荐mysqldump的原因.但是很多PHP-MYSQL-ADMIN工具也可以做到这一点,因此您可以使用它们或查看其代码.

或也许您可以使用MySQL复制.

本文地址:https://www.itbaoku.cn/post/597703.html

问题描述

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 ?

推荐答案

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.

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html and .../select.html for definitive details.

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.

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.

其他推荐答案

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:

INSERT INTO databasename1.tablename SELECT * FROM databasename2.tablename;

其他推荐答案

You can use mysqldump and 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; mysqldump solves a lot of problems.

You can make selects from one database and insert to 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.

Or maybe you can use MySQL replication.