Synchronize identical tables in separate databases with php

Question

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 you may have to offer - even links to previous questions with the answer would be great if my search mojo was weak.

Frank

Answer 1

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).