如何使用php将所有的表从一个数据库复制到另一个数据库？
如何使用php将所有的表从一个数据库复制到另一个数据库？
提问于 2018-06-02 02:30:24
我需要以编程方式将表的所有值从一个数据库复制到另一个数据库。我非常喜欢使用php。我该如何实现这一点？
我发现了一个特殊的代码：
$sql1 = "DELETE FROM Kunthanahali.justshawarma_aauth_groups;";
$result1 = $conn->query($sql1);
$sql2 = "INSERT INTO Kunthanahali.justshawarma_aauth_groups SELECT * FROM justshawarmapos.justshawarma_aauth_groups;";
$result2 = $conn->query($sql2);这段代码工作正常，问题是我的database.Is中有大约50个表，有没有一种方法可以截断第二个数据库并从第一个数据库中创建表和复制值？
我知道在phpmyadmin里面有一个选项。但是我想通过编程来实现这一点。我想实现这一点，因为我正在创建一个销售点系统，其中销售点系统存在于本地主机中，分析是在网站上在线查看的。我需要定期将表及其数据复制到在线数据库中。
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-06-02 03:11:32
您可以使用以下代码来实现这一点-
<?php
$dblink1=mysql_connect('$ip1', '$user1', '$pass1'); // connect server 1
mysql_select_db('$database1',$dblink1); // select database 1
$dblink2=mysql_connect('$ip2', '$user2', '$pass2'); // connect server 2
mysql_select_db('$database2',$dblink2); // select database 2
$tables = mysql_fetch_array(mysql_query("SHOW TABLES ",$dblink1));
//$table='tabletest';
foreach($tables as $table){
$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ",$dblink1)); // get structure from table on server 1
mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2
$result = mysql_query("SELECT * FROM $table ",$dblink1); // select all content
while ($row = mysql_fetch_array($result, MYSQL_ASSOC) ) {
mysql_query("INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')",$dblink2); // insert one row into new table
}
}
mysql_close($dblink1);
mysql_close($dblink2);下面是mysqli版本-
<?php
$dblink1=mysqli_connect('127.0.0.1', 'root', ''); // connect server 1
mysqli_select_db($dblink1,'pdb1'); // select database 1
$dblink2=mysqli_connect('127.0.0.1', 'root', ''); // connect server 2
mysqli_select_db($dblink2,'pdb4'); // select database 2
$tables = mysqli_fetch_array(mysqli_query($dblink1,"SHOW TABLES "));
//$table='tabletest';
foreach($tables as $table){
$tableinfo = mysqli_fetch_array(mysqli_query($dblink1,"SHOW CREATE TABLE $table ")); // get structure from table on server 1
mysqli_query($dblink2," $tableinfo[1] "); // use found structure to make table on server 2
$result = mysqli_query($dblink1,"SELECT * FROM $table "); // select all content
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')"); // insert one row into new table
}
}
mysqli_close($dblink1);
mysqli_close($dblink2);Stack Overflow用户
发布于 2018-06-02 03:16:45
在我看来，这不是实现这一点的方法。完成整个数据库的方法是使用mysqldump命令编写脚本。要备份整个数据库，您需要执行以下操作：
mysqldump --databases yourdb --password=pw | mysql -u user --password=pw otherdb有您可能需要或想要的各种选项和设置，在this blog post中有一些详细介绍。例如，如果将远程主机的-h参数和主机详细信息添加到命令的命令行mysql部分，那么在mysql服务器之间复制数据库同样有效。
一旦编写了脚本，使用system或exec从PHP运行这种类型的脚本就足够简单了。
Stack Overflow用户
发布于 2020-04-07 04:07:17
下面是在两个数据库之间迁移数据代码，它检查表或列是否存在，否则创建它并插入或更新数据注意，这不适合大型数据库的解决方案，并确保您的目标数据库备份。
mysqli_select_db($dblink1,'db1'); // select database 1
$dblink2=mysqli_connect('127.0.0.1', 'root', ''); // connect server 2
mysqli_select_db($dblink2,'db2'); // select database 2
$result = (mysqli_query($dblink1,"SHOW TABLES "));
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
echo "Searching Table: ".$tables[0] ."<br>";
foreach ($tables as $i => $table){
$val = mysqli_query($dblink2,"select 1 from $table ");
if($val !== FALSE) /***** table exists */
{
$structure1= get_colums($dblink1, $table);
$structure2= get_colums($dblink2, $table);
compare_colums($dblink1,$dblink2, $structure1, $structure2, $table );
$result = mysqli_query($dblink1,"SELECT * FROM $table "); // select all content
echo $table." Updating table.... "."<br>";
$rowcount=mysqli_num_rows($result);
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
$update_string = update_query_format($row );
mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')
ON DUPLICATE KEY UPDATE $update_string "); // insert one row into new table
}
echo $table." Updating table Completed.... total updated: $rowcount"."<br><br><br>";
}
else
{
/**** table not exists */
echo $table." Table not found "."<br>";
echo $table." Creating table... "."<br>";
$tableinfo = mysqli_fetch_array(mysqli_query($dblink1,"SHOW CREATE TABLE $table ")); // get structure from table on server 1
mysqli_query($dblink2," $tableinfo[1] "); // use found structure to make table on server 2
$result = mysqli_query($dblink1,"SELECT * FROM $table "); // select all content
echo $table." Copying table.... "."<br><br><br>";
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')"); // insert one row into new table
}
}
}
mysqli_close($dblink1);
mysqli_close($dblink2);
function compare_structure($table, $dblink1 , $dblink2){
/*** Check if table exists */
$val = mysqli_query($dblink2,"select 1 from $table ");
if($val !== FALSE) /***** table exists */
{
$structure1= get_colums($dblink1, $table);
$structure2= get_colums($dblink2, $table);
compare_colums($dblink1,$dblink2, $structure1, $structure2, $table );
$result = mysqli_query($dblink1,"SELECT * FROM $table "); // select all content
echo $table." Updating table.... "."<br>";
$rowcount=mysqli_num_rows($result);
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
$update_string = update_query_format($row );
mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')
ON DUPLICATE KEY UPDATE $update_string "); // insert one row into new table
}
echo $table." Updating table Completed.... total updated: $rowcount"."<br><br><br>";
}
else
{
/**** table not exists */
echo $table." Table not found "."<br>";
echo $table." Creating table... "."<br>";
$tableinfo = mysqli_fetch_array(mysqli_query($dblink1,"SHOW CREATE TABLE $table ")); // get structure from table on server 1
mysqli_query($dblink2," $tableinfo[1] "); // use found structure to make table on server 2
$result = mysqli_query($dblink1,"SELECT * FROM $table "); // select all content
echo $table." Copying table.... "."<br><br><br>";
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')"); // insert one row into new table
}
}
}
function get_colums($dblink, $table){
$result = mysqli_query($dblink,"SHOW COLUMNS FROM $table ");
$columns = [];
if (!$result) {
echo 'Could not run query: ' . mysql_error();
return [];
}
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$columns[$row['Field']] = $row['Type'];
}
}
return $columns;
}
function compare_colums( $dblink1, $dblink2, $structure1, $structure2, $table ){
foreach ($structure2 as $field => $type){
if (!array_key_exists($field,$structure2)){
$result = mysqli_query($dblink2,"ALTER TABLE cus_tbl ADD $field $type NOT NULL ");
if ($result){
echo "Table: ". $table." New Field created ".$field. " ". $type. " .... "."<br>";
}
}
}
}
function update_query_format($row){
$query_format = "";
foreach ($row as $k => $v){
$query_format .= " ".$k. " = '". $v ."',";
}
// echo strlen($query_format);exit;
// echo substr($query_format, 0,strlen($query_format)-1); exit;
return substr($query_format, 0, strlen($query_format)-1);
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50649207
复制相关文章
相似问题