Sync databases using PhpMyAdmin

Question

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.

There isn't. phpMyAdmin is a simple web based interface for small level management of a Database. You would need to look into some type of larger scale system like Doctrine for using mapping, validation and migration tools. apigen.juzna.cz/doc/doctrine/doctrine2.git — Rottingham
– Rottingham, Commented Sep 10, 2013 at 18:23
I Use SQLyog for this. This has an option Visual data compare. — Sathish D
– Sathish D, Commented Sep 11, 2013 at 11:32

hendr1x · Accepted Answer · 2013-09-10 19:16:11Z

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>

Thanks.Just trying to run the diff part. How do you connect to mysql in here to interact with the db's? I get this error when I try to run the code Fatal error: Call to a member function run() on a non-object in /Applications/XAMPP/xamppfiles/htdocs/mydev/dbsync.php on line 3
yeah...so my setup is a little skwank because I'm upgrading the site piece by piece...so this isn't a cut and paste deal but its 98% done for you... You need to setup and create two db connections (Mine are $Init->container["Db"] (or $Db_stage) and $Db_production....then replace all my calls with your connection...that should do it however if you have trouble post your stuff in pastebin and I can look at it (make sure to remove credentials of course)

Collectives™ on Stack Overflow

Sync databases using PhpMyAdmin

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related