如何在PHP MYSQL中将数据库表和每个记录从一个数据库服务器复制到另一数据库服务器? [英] How to copy database tables and each record from one database server to another database server in PHP MYSQL ?>

问题描述

我编写了一个代码，可以将数据库表从一个服务器复制到另一个服务器，但是每个表的记录都没有复制.如何编写一个可以将表和每个记录从一个数据库服务器复制到另一个服务器的函数?

Hi I have write a code that can copy database table from one sever to another but the record of each table did not copy how to write a function that can copy tables and each record from one db server to another?

这是我的示例代码:

<?php
$dbNewDB = 'newdb';
$dbNewUser = 'newroot';
$dbNewUserPswd = 'newpass';

$dbConnect = mysql_connect('localhost', 'root', 'mypassword') or die('Couldn\'t connect to MySql:'.mysql_error());
$dbNewConnect = mysql_connect('localhost', $dbNewUser, $dbNewUserPswd) or die('Couldn\'t connect to MySql:'.mysql_error());

$sqlStatement = "SHOW TABLES FROM olddb";
$result = mysql_query($sqlStatement,$dbConnect) or die('Unable to get tables: '.mysql_error());
while($row = mysql_fetch_row($result)) 
    {
        //Drop table if exist
        $sqlStatement = "DROP TABLE IF EXISTS " . $dbNewDB . "." . $row[0];
        mysql_query($sqlStatement,$dbNewConnect) or die("Failed to delete: " . mysql_error());
        //Create new table
        $sqlStatement = "CREATE TABLE " . $dbNewDB . "." . $row[0] . " LIKE olddb." . $row[0];
        echo "$sqlStatement [" . __METHOD__ . "]"; 
        mysql_query($sqlStatement,$dbNewConnect)or die("Failed to create: ". mysql_error());
        //Insert data
        $sqlStatement = "INSERT INTO " . $dbNewDB . "." . $row[0] . " SELECT * FROM " . $dbNewDB . "." . $row[0];
        echo "$sqlStatement [" . __METHOD__ . "]"; 
        mysql_query($sqlStatement,$dbNewConnect)or die("Table copy failed: ".mysql_error());
        echo "$row[0] copy done. [" . __METHOD__ . "]"; 
    }

mysql_free_result($result);
mysql_close($dbConnect);
mysql_close($dbNewConnect);

?> 

我的代码已经可以正常工作了，我想修复所有表的记录. 有任何想法或帮助吗?

my code is already functional All i want to fixed to copy the records of each tables. Any idea?or help?

谢谢！

推荐答案

您可以使用以下SQL格式转储整个数据库:

You can dump the whole database in SQL format like this:

mysqldump --user=root --password=whatever --databases dbtest --opt --quote-names --complete-insert > testbkup.sql

然后您可以像这样将其导入:

Then you can import it back like this:

mysql -u root -p whatever dbtest < testbkup.sql

(注意:用户= root，密码=任何密码，dbtest是您的数据库.)

(Note: user = root, password = whatever, dbtest is your database.)

只是说

这篇关于如何在PHP MYSQL中将数据库表和每个记录从一个数据库服务器复制到另一数据库服务器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！