php - 使用 PhpMyAdmin 同步数据库

Question

我正在寻找一种使用 PhpMyAdmin 跨不同环境(本地和生产环境)同步 MySql 数据库的方法。因此寻找找到差异然后应用我们想要的差异的能力。

由于内置同步功能已停用。在 PhpMyAdmin 中执行此操作的最佳方法是什么？

即使通过 sql 查询也可以。

谢谢。

Answer 1

你可以在 php 中很容易地做到这一点......这是我使用的脚本

正在同步

$account_tables = $Db_stage->run("SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$Init->container["Config"]->dbName."' ORDER BY table_name");
$production_tables = $Db_production->run("SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."' ORDER BY table_name");

foreach ($account_tables as $account_table){        
    $dbDrop = "/usr/bin/mysql -u ".$Init->container["Config"]->dbUser." -p".$Init->container["Config"]->dbPassword." ".$Init->container["Config"]->dbName." -e 'drop table `".$account_table["table_name"]."`'";
    echo "DROP ".$account_table["table_name"]."<br/>";
    //echo $dbDrop."<br/>";
    exec($dbDrop);
    flush();
}

foreach ($production_tables as $production_table){
    echo "SETUP ".$production_table["table_name"].'<br/>';
    $dbDump = '/usr/bin/mysqldump --lock-tables=false --single-transaction --skip-add-locks -h '.$dbHost.' -u '.$dbUser.' -p'.$dbPassword.' '.$dbName.' '.$production_table["table_name"].' > '.$Init->container["Config"]->dirPublic.'system/sync/db/'.$production_table["table_name"].'.sql';
    //echo $dbDump."<br/>";
    exec($dbDump);
    exec('/usr/bin/mysql --user='.$Init->container["Config"]->dbUser.' --password='.$Init->container["Config"]->dbPassword.' '.$Init->container["Config"]->dbName.' < '.$Init->container["Config"]->dirPublic.'system/sync/db/'.$production_table["table_name"].'.sql');    
    flush();
}   

与差异

$account_tables = $Init->container["Db"]->run("SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$Init->container["Config"]->dbName."' ORDER BY table_name");
$production_tables = $Db_production->run("SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."' ORDER BY table_name");

$tables = array();
$table_rows = array();
foreach ($production_tables as $temp){
    $tables[] = $temp["table_name"];
    $table_rows[$temp["table_name"]]["production"] = $temp["table_rows"];
}
foreach ($account_tables as $temp){
    if (!in_array($temp["table_name"], $tables)){
        $tables[] = $temp["table_name"];
    }
    $table_rows[$temp["table_name"]]["account"] = $temp["table_rows"];
}

//
// Pull Down Column Data and output
//
$tables_with_discrepencies = array();
foreach ($tables as $table){
    $column_diff = "<div id='popup".$table."' style='display:none;'>";

    $account_columns = $Init->container["Db"]->run("select column_name, data_type from information_schema.columns where table_name = '".$table."'");
    $production_columns = $Db_production->run("select column_name, data_type from information_schema.columns where table_name = '".$table."'");

    $all_columns = array();
    $account_columns_verified = array();
    $production_columns_verified = array();
    $account_columns_verified_type = array();
    $production_columns_verified_type = array();

    foreach ($account_columns as $temp){
        $all_columns[] = $temp["column_name"];
        $account_columns_verified[] = $temp["column_name"];
        $account_columns_verified_type[$temp["column_name"]] = $temp["data_type"];
    }
    foreach ($production_columns as $temp){
        if (!in_array($temp["column_name"],$all_columns)){
            $all_columns[] = $temp["column_name"];            
        }
        $production_columns_verified[] = $temp["column_name"];
        $production_columns_verified_type[$temp["column_name"]] = $temp["data_type"];
    }

    $column_diff .= "<table border=1 cellpading='10'><tr><td>Column Name</td><td>Exists On Both</td><td>Data Type The Same</td></tr>";
    $difference_between_columns_found = false;
    foreach($all_columns as $column){
        $column_diff .= "<tr><td>".$column."</td>";

        if (in_array($column, $account_columns_verified) AND in_array($column, $production_columns_verified)){
            $column_diff .= "<td>YES</td>";
        } else if (in_array($column, $account_columns_verified)){
            $difference_between_columns_found = true;
            $column_diff .= "<td style='font-weight:bold;'>***Missing From Production***</td>";
        } else if (in_array($column, $production_columns_verified)){
            $column_diff .= "<td style='font-weight:bold;'>***Missing From Account***</td>";
            $difference_between_columns_found = true;
        }

        if ($account_columns_verified_type[$column] == $production_columns_verified_type[$column]){
            $column_diff .= "<td>YES</td>";
        } else {
            $column_diff .= "<td style='font-weight:bold;'>***Data Types Different***</td>";
            $difference_between_columns_found = true;
        } 
        $column_diff .= "</tr>";

    }
    $column_diff .= "</table>";   
    $column_diff .= "</div>";
    echo $column_diff;

    if ($difference_between_columns_found == true){
        $tables_with_discrepencies[] = $table;
    }
}

//
// Generate Table Report
// 
$table_diff = "<table border=1 cellpading='10'><tr><td>Table Name</td><td>Exists On Both</td><td>Column Differences</td><td>Rows on Production</td><td>Rows on Account</td></tr>";
$difference_between_tables_found = false;
foreach($tables as $table){
    $table_diff .= "<tr><td><span style='font-weight:bold;' onmouseover=\"this.style.cursor='pointer';\" onclick=\"tablePopup('".$table."');\">".$table."</span></td>";
    if (isset($table_rows[$table]["production"]) AND $table_rows[$table]["production"] != 0 AND isset($table_rows[$table]["account"]) AND $table_rows[$table]["account"] != 0){
        $table_diff .= "<td>EXISTS</td>";
    } else if ((!isset($table_rows[$table]["production"]) || $table_rows[$table]["production"] == 0) AND (!isset($table_rows[$table]["account"]) || $table_rows[$table]["account"] == 0)){
        $table_diff .= "<td style='font-weight:bold;'>***BOTH EMPTY***</td>";
    } else if (isset($table_rows[$table]["production"]) AND $table_rows[$table]["production"] != 0){
        $table_diff .= "<td style='font-weight:bold;'>***MISSING ON ACCOUNT***</td>";
        $difference_between_tables_found = true;
    } else {
        $table_diff .= "<td style='font-weight:bold;'>***MISSING ON PRODUCTION***</td>";
        $difference_between_tables_found = true;
    }
    if (in_array($table,$tables_with_discrepencies)){
        $table_diff .= "<td style='font-weight:bold;'>***YES***</td>";
    } else {
        $table_diff .= "<td>No</td>";
    }
    if ($table_rows[$table]["production"]){
        $table_diff .= "<td>".($table_rows[$table]["production"])."</td>";
    } else {
        $table_diff .= "<td>0</td>";
    }
    if ($table_rows[$table]["account"]){
        $table_diff .= "<td>".($table_rows[$table]["account"])."</td>";
    } else {
        $table_diff .= "<td>0</td>";
    }
}
$table_diff .= "</table>";

//
// Display report
//
echo "<h1>Database Comparision</h1>";
echo "Account Tables = ".count($account_tables)."<br/>";
echo "Production Tables = ".count($production_tables)."<br/>";
if ($difference_between_tables_found == true){
    echo "Table Differences Found : True<br/>";
} else {
    echo "Table Differences Found : False<br/>";
}
if (count($tables_with_discrepencies) > 0){
    echo "Column Differences Found : True<br/>";
} else {
    echo "Column Differences Found : False<br/>";
}
echo "<br/><a href='../index.php'>[ <- Back ]</a><br/><br/>";
echo $table_diff;
?>
<script>
    function tablePopup(tableName){
         $( "#popup"+tableName ).dialog({
            autoOpen: false,
            height: 600,
            width: 650,
            modal: false,
            Close: function() {
                $( this ).dialog( "close" );
            }            
        });
        $( "#popup"+tableName ).dialog( "open" );
    }
</script>