Synchronize multiple MySQL Databases with PHP

This article could be very usefull for the owers of web service businesses. If you are offering e-commerce, hosting, live support or ticketing web based services you have probably met the discussed problem many times in your work. My intend with this article is to help you solving this trouble and save your valued time.

Supporting multiple instanses of a web based application can be very time consuming and frustrating, especially if the app is in development stage or is being updated often. Here in PIM Team we met such a problem, trying to perform constant upgrades on an application running simultanteously on about 50 different domains.

You know, if you make just one change and don't transfer it on the other instanses it can cause big errors and stop your scripts from working. But (as in our case) opening 50 control panels and going to the MySQL administration and running manually these ALTER TABLE or CREATE TABLE statements was a cumbersome task, taking all of our time.

All the instances of our app were running on one physical server, which definitely was a facilitation. But you can implement similar solution even if your ap is running on different servers - you just need to allow connection to the master host - the one which will run the Synhronizer - the script i will describe below. Our Synchronizer is actually a simple PHP script which is started manually and have one only purpose - to synchronize all 50 databases with one “master” database. In our case we needed that script to synchronize only the DB structure, but not the content. But if you understand the simple logic of the script, you can easy extend it to copy/synchronize your content if this is you case.

First, you need to select all the tables and their fields from the master database:

<?php
//select tables from the master
$q="SHOW TABLES FROM master_database";
$tabs=$DB->aq($q); //$DB is a database fetching object, you can use the
built PHP functions to select from mysql if you prefer
 
$tables=array();
 
foreach($tabs as $tab) {
	//select fields
	$q="SHOW FIELDS FROM $tab[0]";
	$fields=$DB->aq($q);
	array_push($tables,array("name"=>$tab[0],"fields"=>$fields));
}
 
?>

You see how our script fills an array $tables with all the table names and itself containing another array - with the table fields.

Secondly, you need a list with the databases or domains where the instances of the synchronized application are running. Once having that list, you can browse thru it with “foreach” or another cycle.

Now we are going to select all the tables in the database on each target domain. (Of course you need to connect to its database, and disconnect from master one! We already did our job in selecting the tables from the master database :)

In the same way as above, you need to select the tables from the target domain.

Then below, just compare the tables:

<?php
foreach($tables as $table) { //browse thru master tables
	$found=false;
	foreach($dtables as $dtable) {
	   if($dtable[name]==$table[name]) $found=$dtable;
	}
 
	if(is_array($found)) {
		//table exists, check fields
		foreach($table[fields] as $field) {
			$ffound=false;
	      		foreach($found[fields] as $dfield) {
		  		if($field[Field]==$dfield[Field]) $ffound=true;
	      		}
 
	      		if(!$ffound) {
		  		//alter table add field
		  		if($field[Key]=='PRI') $primary=" PRIMARY KEY ";
		  		else $primary='';
 
				$q="ALTER TABLE `$table[name]` ADD `$field[Field]` $field[Type] NOT NULL
				$field[Extra] $primary";
				$DB->q($q);
	   		}
	   	}
	   	else {
	      		//table does not exists, create
	      		$q="CREATE TABLE `$table[name]`(";
 
	      		foreach($table[fields] as $cnt=>$field) {
		  		if($field[Key]=='PRI') $primary=" PRIMARY KEY ";
		  		else $primary='';
 
		  		$q.="`$field[Field]` $field[Type] NOT NULL $field[Extra] $primary ";
		  		if($cnt<(sizeof($table[fields])-1)) $q.=", ";
	      		}
 
	      		$q.=")";
	      		$DB->q($q);
	      	}
	}
}
?>

And that's all! You may need to work a little on this code, but the logic is here provided for your needs. Feel free to use the ideas for your own applications.

Bobby Handzhiev is a senior developer in PIM Team Bulgaria

2776