Linux 如何将表从一个mysql数据库复制到另一个mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3932608/
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 to copy a table from one mysql database to another mysql database
提问by Ergec
I need to copy a table from one database to another. This will be a cronjob. Which one is the best way to do it? PHP script or Shell Script. The problem with PHP, both databases has different usernames and passwords so I can't do it like this.
我需要将一个表从一个数据库复制到另一个数据库。这将是一个 cronjob。哪一种是最好的方法？PHP 脚本或 Shell 脚本。PHP 的问题，两个数据库都有不同的用户名和密码，所以我不能这样做。
CREATE TABLE db1.table1 SELECT * FROM db2.table1
Should I just connect first DB get all records and insert all to new database using WHILE loop or there is a better way?
我应该只连接第一个数据库获取所有记录并使用 WHILE 循环将所有记录插入新数据库还是有更好的方法？
I prefer a shell script to do this instead of PHP script.
我更喜欢使用 shell 脚本而不是 PHP 脚本来执行此操作。
Thanks
谢谢
采纳答案by Pekka
I'd dump it. Much less complicated than anything PHP based.
我会扔掉它。比基于 PHP 的任何东西复杂得多。
mysqldump -u user1 -ppassword1 databasename > dump.sql
mysql -u user2 -ppassword2 databasename < dump.sql
MySQL reference: 4.5.4. mysqldump — A Database Backup Program
MySQL 参考：4.5.4。mysqldump — 数据库备份程序
回答by gautamlakum
Phpmyadmin has inbuilt functionality to copy tables from one database to another. Otherwise you can go with Pekka or export table then import table.
phpmyadmin 具有将表从一个数据库复制到另一个数据库的内置功能。否则，您可以使用 Pekka 或导出表然后导入表。
回答by shantanuo
mysqldump -u user1 -ppassword1 databasename TblName | mysql -u user2 -ppassword2 anotherDatabase
It all can be done in a single command.
这一切都可以在一个命令中完成。
回答by Peter Eskandar
insert into dest.table select * from orginal.table;
回答by Radu Damian
If you need to copy the table on the same server you can use this code:
如果您需要在同一台服务器上复制表，您可以使用以下代码：
USE db2;
CREATE TABLE table2 LIKE db1.table1;
INSERT INTO table2
SELECT * FROM db1.table1;
It's copy+pasted from here: codingforums.com
这是从这里复制+粘贴的： codingforums.com
It's not my solution, but I find it useful.
这不是我的解决方案，但我觉得它很有用。
回答by user2597353
use <from database>
用 <from database>
create table <to database.new name> as (select * from <table to copy>);
回答by itschrishill
I'll put this answer up for anyone else looking for help.
我会为其他寻求帮助的人提供这个答案。
If you don't have access to SSH then you can use PhpMyAdmin.
如果您无权访问 SSH，则可以使用 PhpMyAdmin。
Simply:
简单地：
- browse to the table you want to move
- Click the Operations tab
- Use the MOVE or COPY to database function
- 浏览到要移动的表格
- 单击操作选项卡
- 使用 MOVE 或 COPY 到数据库函数
If you come across privilege problems, you can temp grant a user Global permissions or add the same user to both databases.
如果您遇到权限问题，您可以临时授予用户全局权限或将同一用户添加到两个数据库。
回答by Fury
CREATE TABLE db_target.cloned_table
SELECT *
FROM db_source.source_table;
回答by user3637009
$L1 = mysql_connect('localhost', 'user1', 'pass1');
$DB1 = mysql_select_db('database1', $L1);
$L2 = mysql_connect('localhost', 'user2', 'pass2');
$DB2 = mysql_select_db('database2', $L2);
$re=mysql_query("SELECT * FROM table1",$L1);
while($i=mysql_fetch_assoc($re))
{
$u=array();
foreach($i as $k=>$v) if($k!=$keyfield) $u[]="$k='$v'";
mysql_query("INSERT INTO table2 (".implode(',',array_keys($i)).") VALUES ('".implode("','",$i)."') ON DUPLICATE KEY UPDATE ".implode(',',$u),$L2) or die(mysql_error());
}
user1, pass1, database1, table1 reffers to initial table user2, pass2, database2, table2 reffers to copied table $keyfield is the primary key of table
user1, pass1, database1, table1 指向初始表 user2, pass2, database2, table2 指向复制的表 $keyfield 是表的主键
回答by Juergen
One liner with different servers
一个班轮与不同的服务器
mysqldump -h host1 -u user1 -ppassword1 databasename TblName | mysql -h host2 -u user2 -ppassword2 anotherDatabase
