Synchronize multiple MySQL Databases with PHP

Source : Bobby Handzhiev on Mon, 07/18/2005 - 07:32.

PIM Team Case Study

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

Az-Ert Idouz, 2013/12/16 22:01
pas de retour à la ligne pour les accolades (quand il y en a !!), chaînes nues pour accéder aux dimensions des tableaux, du SQL concaténé... Il a fait n'importe quoi, là, Bobby !
Fabrice, 2013/12/30 13:52
LOL, pour info, il y a la convention (celle qu'on connaît) et celle majoritaire à PHP, notamment pour les accolades... le retour à la ligne à l'ouverture de l'accolade c'est typique au C, pas à PHP, il suffit de voir la doc php.net et certains éditeurs comme Netbeans sont configurés ainsi par défaut ;)

Sinon, faut juste noter que la date de la source remonte à 2005 et que depuis il faut certainement procéder à quelques ajustements sur le code ;)
Saisissez votre commentaire:
Si vous ne pouvez pas lire le code, téléchargez ce fichier .wav pour l'écouter.
 

Si cet article vous a été utile ou simplement si vous appréciez ce site, n'hésitez pas à me soutenir via Paypal ou en utilisant la bannière Amazon1) ci-dessous pour vos futurs achats, ça ne vous coûtera pas plus cher et ça soutiendra un peu ce site ;-)


1)
Ou ajoutez ce lien https://amzn.to/2ZRiKrq vers Amazon dans vos favoris ;-)
  • de fabrice