mysql 将数据从一个数据库迁移到另一个数据库

Migrating data from one database to another database in mysql

提问by Belmark Caday

Is it possible to migrate related data but with different column names from one database to another database? and im talking about very large amount of data here. someone has an idea? I have tried exporting it to CSV and importing to the otherdatabase but i get errors saying:

是否可以将具有不同列名的相关数据从一个数据库迁移到另一个数据库？我在这里谈论的是非常大量的数据。有人有想法吗？我试过将它导出到 CSV 并导入到其他数据库，但我收到错误消息：

invalid column count in CSV input in line 1

So if anyone has an efficient and effective way of doing this please share. Or if anyone can guide mo through this CSV mapping in excell on how to properly do it, i would really appreciate

因此，如果有人有有效的方法来做到这一点，请分享。或者，如果有人可以通过 Excel 中的 CSV 映射指导 mo 如何正确执行此操作，我将不胜感激

回答by Barranka

There are two cases in this:

这其中有两种情况：

1. The databases are stored in the same server
2. The databases are stored in different servers

1. 数据库存储在同一台服务器上
2. 数据库存储在不同的服务器上

Solution 1: Databases in the same server

解决方案 1：同一服务器中的数据库

You just need to insert the data in the destination table:

您只需要在目标表中插入数据：

insert into dbDestination.tblDestination (field1, field2, ...)
select ...
from dbSource.tblSource

Notes

笔记

1. The selectstatement must include the fields you need to copy to the destination table.
2. The fields in the selectstatement must be in the same order as the fields specified in the field list in the insertportion

1. 该select语句必须包括您需要复制到目标表的字段。
2. select语句中的字段必须与insert部分中字段列表中指定的字段的顺序相同

Solution 2: Databases in different servers

解决方案2：不同服务器上的数据库

I would export the data to a plain text file, and then import it. I personally prefer .csv files, but it's up to you.

我会将数据导出到纯文本文件，然后导入。我个人更喜欢 .csv 文件，但这取决于您。

You have two possibilities: To use select... into outfileor to use the system terminal (command window).

您有两种可能性： 使​​用select... into outfile或使用系统终端（命令窗口）。

a. Using select... into outfileand load data

一种。使用select... into outfile和load data

1. In the server where dbSourceis:

   select ...
   from dbSource.tblSource
   into outfile [your destination file]
   ...
   

2. Copy the file to the destination server.

3. In the server where dbDestinationis:

   load data local infile [your file] ...

1. 在服务器中dbSource：

   select ...
   from dbSource.tblSource
   into outfile [your destination file]
   ...
   

2. 将文件复制到目标服务器。

3. 在服务器中dbDestination：

   加载数据本地 infile [您的文件] ...

Notes

笔记

1. The fields in the selectstatement must be in the same order as the fields specified in the field list in the insertportion
2. Check MySQL reference manual for the appropriate usage of select... into outfileand load data...

1. select语句中的字段必须与insert部分中字段列表中指定的字段的顺序相同
2. 检查 MySQL 参考手册以了解select... into outfile和的适当用法load data...

b. Using the terminal

湾 使用终端

1. In Linux (or other Unix systems), open a terminal window and enter the following command:

   $ mysql -h [sourceServer] -u [yourUser] -p[yourPassword] dbSource -e"select ..." | sed 's/\t/,/g' > yourDestinationFile.csv
   

2. Copy the file to the destination server

3. Start MySQL console, and use `load data ...``

1. 在 Linux（或其他 Unix 系统）中，打开终端窗口并输入以下命令：

   $ mysql -h [sourceServer] -u [yourUser] -p[yourPassword] dbSource -e"select ..." | sed 's/\t/,/g' > yourDestinationFile.csv
   

2. 将文件复制到目标服务器

3. 启动 MySQL 控制台，并使用 `load data ...`

Notes

笔记

1. The fields in the selectstatement must be in the same order as the fields specified in the field list in the insertportion
2. The | sed 's/\t/,/g'part converts the output of the mysql query to a .csv file. You can use another separator instead of ,. For further reference about sedcheck http://lowfatlinux.com/linux-sed.html
3. The destination file will have row headers, so you will need to ignore them when you import the data. Simply add ignore 1 linesat the end of the load data...sentence.

1. select语句中的字段必须与insert部分中字段列表中指定的字段的顺序相同
2. 该| sed 's/\t/,/g'部分将 mysql 查询的输出转换为 .csv 文件。您可以使用另一个分隔符代替,. 有关sed检查的进一步参考http://lowfatlinux.com/linux-sed.html
3. 目标文件将具有行标题，因此您在导入数据时需要忽略它们。只需ignore 1 lines在load data...句子末尾添加即可。

---

---

To copy data from one database to another is a very simple task. Hope this points you in the right direction.

将数据从一个数据库复制到另一个数据库是一项非常简单的任务。希望这为您指明了正确的方向。

A word of advice: Download the reference manual for your MySQL version and keep it at hand. You can find most of your solutions there.

忠告：下载您的 MySQL 版本的参考手册并随身携带。您可以在那里找到大部分解决方案。

回答by Osiris93

A simple way to do this is with the following syntax:

一种简单的方法是使用以下语法：

INSERT INTO Database.Table(field 1, field 2,...)
SELECT * FROM Database2.Table2;

回答by faloye sogo

Use this:

用这个：

INSERT INTO dbname.tablename(fields...);
SELECT * FROM olddatabase.oldtable;

回答by Whisper

Beware!!!

谨防！！！

INSERT INTO dbname.tablename(fields...);
SELECT * FROM olddatabase.oldtable;

will only work if id's follow.

仅当 id 跟随时才有效。

Select fields ommitting ID so that auto-increment can give a new ID to the imported rows.

选择省略 ID 的字段，以便自动增量可以为导入的行提供新 ID。