如何使用php将所有表从一个数据库复制到另一个数据库？

laximzn5  于 2023-04-28  发布在  PHP
关注(0)|答案(4)|浏览(198)

我需要通过编程将一个表的所有值从一个数据库复制到另一个数据库。我更喜欢使用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);

这段代码运行良好。但问题是我的数据库中有大约50个表。有没有一种方法可以截断第二个数据库，并从第一个数据库创建表和复制值？

**我知道phpmyadmin中有一个选项。但我想用程序来做。**我想实现这一点，因为我正在创建一个销售点系统，其中销售点系统存在于本地主机中，并且可以在网站中在线查看分析。我需要定期将表及其数据复制到在线数据库。

php

4条答案

按热度按时间
ghg1uchk

ghg1uchk1#

你可以使用下面的代码来实现这一点-

<?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);
赞(0）分享  回复(0）举报  2023-04-28
2skhul33

2skhul332#

在我看来，这不是实现这一目标的方法。完成整个数据库的方法是使用mysqldump命令编写脚本。要备份整个数据库，您需要沿着以下操作：

mysqldump --databases yourdb --password=pw | mysql -u user --password=pw otherdb

您可能需要各种选项和设置，在this blog post中有详细介绍。例如，如果在命令行mysql部分添加-h参数和远程主机的主机详细信息，这也可以在mysql服务器之间复制数据库。
编写脚本后，使用system或exec从PHP运行这种类型的脚本就足够简单了。

赞(0）分享  回复(0）举报  2023-04-28
oaxa6hgo

oaxa6hgo3#

下面是在两个数据库之间迁移数据的代码，它检查表或列是否存在，否则创建它并插入或修改数据注意，这不是适合大型数据库的解决方案，请确保你的目标数据库。

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);

}
赞(0）分享  回复(0）举报  2023-04-28
ajsxfq5m

ajsxfq5m4#

///////////////////////////////////////////COPY DATABSE STRUCTURE TO NEW DATABASE/////////////////////////////////////////////////////////

                            /*After CREATING CONNECTIONS TO THE TWO DATABASES
                            AND THE TWO DATABASES MUST BE AT THE SAME SAVER*/

                            $old_database=$YOUR_FROM_DATABASE;
                            $new_dataBase=$DESTINATION_DATABASE;

                            $list = mysql_list_tables ("$old_database"); 
                            $i = 0;

                            while ($i < mysql_num_rows ($list)) {

                                $tb_names[$i] = mysql_tablename ($list, $i);  

                                $createTables="CREATE TABLE $new_dataBase.".$tb_names[$i]." LIKE $old_database.".$tb_names[$i];
                                $createTablesQry=mysql_query($createTables);

                                $i++;

                            }//clossing while ($i < mysql_num_rows ($list)) {
赞(0）分享  回复(0）举报  2023-04-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com