Synchronize identical tables in separate databases with php

I'm trying to set up a PHP script to read a handful of MySQL tables from database A and then insert them into databases B and C. It's a really simple scenario - the tables being copied over can even be emptied or dropped before they have values inserted into them. All the databases are on the same server, and I don't need to preserve the old data, just overwrite it.

The only thing I can't do is table replication; my hosting setup doesn't permit it.

I tried a few methods I came up with and read several questions and answers here on SO and elsewhere, but I'm not finding the answer - my question is probably too simple! This is something that I would want to call from a cron job or manually, maybe once a day maximum. It doesn't even have to be very efficient.

Thanks, I appreciate any help yo开发者_JAVA技巧u may have to offer - even links to previous questions with the answer would be great if my search mojo was weak.

Frank

I don't think you'd even have to use PHP to get this done.

Provided that the databases are under the same MySQL instance, you can just use a cross-database query to select data from one table and drop it into another. For instance, including your wanting to purge the table before hand:

TRUNCATE TABLE DatabaseA.Table1;
INSERT INTO DatabaseA.Table1A SELECT 
       ColumnFoo, ColumnBar, ColumnBaz 
       FROM DatabaseB.Table1B;

You could write this into a PHP script, which cron could then call. Any other scripting language with a MySQL adapter would probably work, really.

Doing this by loading data into a script and then redumping back into the DB would involve far more network bandwidth, storage, and processing than is necessary.

One last note, if you start using other databases later on, bare in mind that not all of them support cross-database queries (I know of at least PostgreSQL that doesn't).

You could just do this:

First, create 3 tables.

• A = table from database A
• B = table from database B
• C = table in any of the databases but it has to be one...

(Let's assume C is in database A)

TRUNCATE TABLE DatabaseA.C;
INSERT INTO DatabaseA.C SELECT 
   ColumnFoo, ColumnBar, ColumnBaz 
   FROM DatabaseB.B;
INSERT INTO DatabaseA.C SELECT 
   ColumnFoo, ColumnBar, ColumnBaz 
   FROM DatabaseA.A;
TRUNCATE TABLE DatabaseA.A;
TRUNCATE TABLE DatabaseB.B;
INSERT INTO DatabaseA.A SELECT 
   ColumnFoo, ColumnBar, ColumnBaz 
   FROM DatabaseA.C;
INSERT INTO DatabaseB.B SELECT 
   ColumnFoo, ColumnBar, ColumnBaz 
   FROM DatabaseA.C;

Now you have both databases in sync with one another and no data was lost.

If I understand the word synchronize correctly, it means to get data from both databases safely and mix them all together...

Of course there might be problems with IDs and stuff, but if the database index rates are synchronized too, then it might just do the trick...

Check out this link, as well: About Data Overwrite and Identical Keys

There are several solutions.

• Set up table replication. This feature of MySQL is intended for distributed data, but should work database to database (though I haven't tried it).

• Periodically drop the table and recreate with:

  use b;
  drop table table;
  create table table as
   select *
   from a.table;
  
  
  use c;
  drop table table;
  create table table as
   select *
   from a.table;
  

  This assumes that a contains the master.