Quantcast

Synchronize multiple MySQL Databases with PHP

Get the WebProNews Newsletter:


[ Business]

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.

Problem

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.

Solution

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.

Implementation

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

//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)
{
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp //select fields
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $q="SHOW FIELDS FROM $tab[0]";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $fields=$DB->aq($q);

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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:

foreach($tables as $table) //browse thru master tables
{
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $found=false;

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp foreach($dtables as $dtable)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if($dtable[name]==$table[name]) $found=$dtable;
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if(is_array($found))
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp //table exists, check fields
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp foreach($table[fields] as $field)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $ffound=false;
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp foreach($found[fields] as $dfield)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if($field[Field]==$dfield[Field]) $ffound=true;
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if(!$ffound)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp //alter table add field
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if($field[Key]=='PRI') $primary=" PRIMARY KEY ";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp else $primary='';

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $q="ALTER TABLE `$table[name]` ADD `$field[Field]` $field[Type] NOT NULL
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $field[Extra] $primary";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $DB->q($q);
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp else
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp //table does not exists, create
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $q="CREATE TABLE `$table[name]`(";

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp foreach($table[fields] as $cnt=>$field)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if($field[Key]=='PRI') $primary=" PRIMARY KEY ";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp else $primary='';

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $q.="`$field[Field]` $field[Type] NOT NULL $field[Extra] $primary ";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp if($cnt<(sizeof($table[fields])-1)) $q.=", ";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $q.=")";
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp $DB->q($q);
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }
}

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

http://pimteam.net

admin@pimteam.net

Synchronize multiple MySQL Databases with PHP
Top Rated White Papers and Resources
  • anitha

      pls help me and give the coding of database synchronizer in PHP.So kindly sent the coding ….

       "How to convert MySql to  sql  "pls    sent the codings……………

     

     

                                                                   Thanks

     

     

                                     

  • Guest

    you missed it and that messed up the page

  • http://news.answers.tw Tom Anderson


    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);
                 }
           }
    }

  • http://news.answers.tw Tom Anderson

    //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));
    }

  • Guest

    fail

  • http://CyMeP.com Thilo

    Implementation

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

    //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:

    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.

    • http://cozaki.net Kozak

      do you have a working excample, so that i understand better the script?
      Thx 4 help

  • Kozak

    I have several webshops. All with the same database structure & hosted on the same server. I’m looking for a way to copy several tables from each shop to 1 shop. This must happen several times each day. Is there somebody that can wright the needed php page for me? You can contact me by mail : kozak@hotmail.be.
    Thx4support

  • Join for Access to Our Exclusive Web Tools
  • Sidebar Top
  • Sidebar Middle
  • Sign Up For The Free Newsletter
  • Sidebar Bottom