Mysql Database Migration / Synchronisation Script
June 24th, 2009Read More mysql
This is a nice little script I just knocked together that helps to synchronise databases when the table structures might not be exactly the same (for example different versions of the same system).
You would need to edit the top section to put the correct DB credentials etc.
This is definitely not to be used on a live DB, purely for aiding development and migration.
PHP:
-
<?php
-
/** Database Synchronisation / Migration Tool
-
*
-
* For synching up an old and a new DB schema.
-
*
-
* @author EdmondsCommerce.co.uk
-
*
-
*/
-
-
//do you want the system to empty the new table before inserting data from the old table?
-
$truncate=true;
-
-
/**
-
* DB Server Credentials - Needs to have full access to both databases
-
*/
-
$dbHost='localhost';
-
$dbUser='root';
-
$dbPass='password';
-
-
/** Tables to Synch
-
*/
-
'categories',
-
'categories_description',
-
'products',
-
'products_description',
-
'products_to_categories',
-
'manufacturers',
-
);
-
-
$dbOld = 'dbold';
-
-
$dbNew = 'dbnew';
-
-
-
/*********** CODE BELOW HERE - NO NEED TO EDIT UNLESS YOU WANT TO ***********/
-
-
$dbOldTables= fetch_tables($dbOld);
-
-
$dbNewTables = fetch_tables($dbNew);
-
-
-
foreach($tablesToSynch as $table){
-
-
//check for common tables
-
//now get column data
-
$dbOldTableCols = fetch_columns($dbOld,$table);
-
-
$dbNewTableCols = fetch_columns($dbNew,$table);
-
-
//now for the column comparison
-
-
//now emptying the new DB if set to do so
-
if($truncate){
-
db_query("TRUNCATE $dbNew.$table");
-
}
-
-
//copy old table to new DB so we can copy columns
-
$tempTable=copy_table($dbOld, $dbNew, $table);
-
-
//now build SQL and run
-
db_query($sql);
-
-
//now drop temp table
-
db_query("DROP TABLE $dbNew.$tempTable");
-
}
-
-
}
-
-
/****** FUNCTIONS ********/
-
-
function db_query($query){
-
<h1 style="color: red">Uh Oh......MySQL Error:</h1>
-
<h3>Query:</h3>
-
<h3>MySQL Error:</h3>
-
<hr /> <hr />'); return $output;
-
}
-
-
-
function fetch_tables($dbname){
-
$query=db_query("show tables from $dbname");
-
$return[]=$r["Tables_in_$dbname"];
-
}
-
return $return;
-
}
-
-
function fetch_columns($dbname, $table){
-
$query = db_query("SHOW COLUMNS from $dbname.$table");
-
$return[]=$r['Field'];
-
}
-
return $return;
-
}
-
-
function copy_table($fromDb, $toDb, $table){
-
db_query("DROP TABLE IF EXISTS $toDb.temp_$table");
-
db_query("CREATE TABLE $toDb.temp_$table LIKE $fromDb.$table");
-
db_query("ALTER TABLE $toDb.temp_$table DISABLE KEYS");
-
db_query("INSERT INTO $toDb.temp_$table SELECT * FROM $fromDb.$table");
-
db_query("ALTER TABLE $toDb.temp_$table ENABLE KEYS");
-
return "temp_$table";
-
}
