Our blog

 

Mysql Database Migration / Synchronisation Script

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.

PLAIN TEXT
PHP:
  1. <?php
  2. /** Database Synchronisation / Migration Tool
  3. *
  4. * For synching up an old and a new DB schema.
  5. *
  6. * @author EdmondsCommerce.co.uk
  7. *
  8. */
  9.  
  10. //do you want the system to empty the new table before inserting data from the old table?
  11. $truncate=true;
  12.  
  13. /**
  14. * DB Server Credentials - Needs to have full access to both databases
  15. */
  16. $dbHost='localhost';
  17. $dbUser='root';
  18. $dbPass='password';
  19.  
  20. /** Tables to Synch
  21. */
  22. $tablesToSynch = array(
  23.     'categories',
  24.     'categories_description',
  25.     'products',
  26.     'products_description',
  27.     'products_to_categories',
  28.     'manufacturers',
  29. );
  30.  
  31. $dbOld = 'dbold';
  32.  
  33. $dbNew = 'dbnew';
  34.  
  35.  
  36. /*********** CODE BELOW HERE - NO NEED TO EDIT UNLESS YOU WANT TO ***********/
  37. $_conn = mysql_connect($dbHost,$dbUser,$dbPass) or die('DB Connection Failed');
  38.  
  39. $dbOldTables= fetch_tables($dbOld);
  40.  
  41. $dbNewTables = fetch_tables($dbNew);
  42.  
  43.  
  44. foreach($tablesToSynch as $table){
  45.  
  46.     echo '<h3>Doing '.$table.'</h3>';
  47.     //check for common tables
  48.     if(in_array($table, $dbOldTables) && in_array($table, $dbNewTables)){
  49.         //now get column data
  50.         $dbOldTableCols = fetch_columns($dbOld,$table);
  51.  
  52.         $dbNewTableCols = fetch_columns($dbNew,$table);
  53.  
  54.         //now for the column comparison
  55.         $cols=array_intersect($dbOldTableCols, $dbNewTableCols);
  56.  
  57.         //now emptying the new DB if set to do so
  58.         if($truncate){
  59.             db_query("TRUNCATE $dbNew.$table");
  60.         }
  61.  
  62.         //copy old table to new DB so we can copy columns
  63.         $tempTable=copy_table($dbOld, $dbNew, $table);
  64.  
  65.         //now build SQL and run
  66.         $sql = "insert into $dbNew.$table (" . implode(', ',$cols) . ") select " . implode(', ',$cols) . " from $dbNew.$tempTable";
  67.         db_query($sql);
  68.  
  69.         //now drop temp table
  70.         db_query("DROP TABLE $dbNew.$tempTable");
  71.     }
  72.    
  73. }
  74.  
  75. /****** FUNCTIONS ********/
  76.  
  77. function db_query($query){
  78. $output = mysql_query($query) or die('
  79. <h1 style="color: red">Uh Oh......MySQL Error:</h1>
  80. <h3>Query:</h3>
  81. <pre>' . htmlentities($query) . '</pre>
  82. <h3>MySQL Error:</h3>
  83. ' . mysql_error() . '
  84. <hr /> <hr />');    return $output;
  85. }
  86.  
  87.  
  88. function fetch_tables($dbname){
  89.     $query=db_query("show tables from $dbname");
  90.     while($r=mysql_fetch_assoc($query)){
  91.         $return[]=$r["Tables_in_$dbname"];
  92.     }
  93.     return $return;
  94. }
  95.  
  96. function fetch_columns($dbname, $table){
  97.     $query = db_query("SHOW COLUMNS from $dbname.$table");
  98.     while($r= mysql_fetch_assoc($query)){
  99.         $return[]=$r['Field'];
  100.     }
  101.     return $return;
  102. }
  103.  
  104. function copy_table($fromDb, $toDb, $table){
  105.     db_query("DROP TABLE IF EXISTS $toDb.temp_$table");
  106.     db_query("CREATE TABLE $toDb.temp_$table LIKE $fromDb.$table");
  107.     db_query("ALTER TABLE $toDb.temp_$table DISABLE KEYS");
  108.     db_query("INSERT INTO $toDb.temp_$table SELECT * FROM $fromDb.$table");
  109.     db_query("ALTER TABLE $toDb.temp_$table ENABLE KEYS");
  110.     return "temp_$table";
  111. }

More Reading:

 

Leave a Reply