Synchronising databases

Databases
1

Hi guys,

I’ve recently create a script that synchronises 2 databases on the same host.

The script, written in PHP works like this;

  • Query databases for information
  • If table exists on DB1 but not DB2, create DB1 table on DB2, similar to deletion, if table exists on DB2 but not DB1 delete on DB2.
  • Create ‘hash’ values based on various pieces of table data and compare.
  • If ‘hash’ values do not match, truncate DB2 table and insert values using:
    INSERT INTO DB2.table1 SELECT * FROM DB1.table1;
  • If ‘hash’ values match, do nothing.

As you can tell this only works when the DBs are on the same host.
What would you recommend as a good method for doing similar when DBs are on different hosts? Loop each row individually?

Cheers,
Michael

2

You should use mysql replication, not php. Look into a master/slave or master/master configuration.