CODEWITHSUNDEEP
phpmysqldatabasephpmyadmin
pal4life
pal4life

Reputation: 3388

Sync databases using PhpMyAdmin

I am looking for a way to sync MySql database across different environments (local and production) synced by using PhpMyAdmin. Thus looking for the ability to find diffs and then apply the one's we would like to.

Since the inbuilt Synchronize feature has been discontinued. What is the best way to do this in PhpMyAdmin?

Even if via sql queries would work.

Thanks.

Upvotes: 1

Views: 1156

Answers (1)

hendr1x
hendr1x

Reputation: 1501

You can do this within php pretty easily...here are the scripts I use

SYNCING

$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();
}

AND DIFF

$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>

Upvotes: 1

Related Questions