Copy to temptable mysql_如何將一個mysql數據庫中的表復制到另一個mysql數據庫中

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.

我需要將表從一個數據庫復制到另一個數據庫。這將是一件很難辦的事。哪一種是最好的方法?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?

我應該只連接第一個DB獲取所有記錄並使用WHILE循環插入所有新數據庫，還是有更好的方法?

I prefer a shell script to do this instead of PHP script.

我更喜歡shell腳本，而不是PHP腳本。

Thanks

謝謝

10 个解决方案

#1

29

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參考:4.5.4。一個數據庫備份程序

#2

32

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.

這不是我的解決方案，但我發現它很有用。

#3

28

mysqldump -u user1 -ppassword1 databasename TblName | mysql -u user2 -ppassword2 anotherDatabase

It all can be done in a single command.

這一切都可以在一個命令中完成。

#4

5

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或export表，然后導入表。

#5

5

$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是表的主鍵

#6

3

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

使用移動或復制到數據庫功能

If you come across privilege problems, you can temp grant a user Global permissions or add the same user to both databases.

如果遇到特權問題，您可以臨時授予用戶全局權限或向兩個數據庫添加相同的用戶。

#7

1

One liner with different servers

一個帶有不同服務器的班輪

mysqldump -h host1 -u user1 -ppassword1 databasename TblName | mysql -h host2 -u user2 -ppassword2 anotherDatabase

#8

0

insert into dest.table select * from orginal.table;

#9

0

CREATE TABLE db_target.cloned_table

SELECT *

FROM db_source.source_table;

#10

0

use

使用來自數據庫> <

create table as (select * from