mysql sync two tables from 2 databases

Question

I have a query . I have two tables on two different servers .Both the tables have the same structure . The master table on one server gets updated on a daily basis , so i want a cron job or a php script cron to update the second slave table on a different server . I have seen a lot of scripts but none resolved my requirements .

Answer 1

I can't believe you didn't find a suitable script to do this. Depending on server-to-server bandwidth and connectivity, and table data size, you can:

• directly transfer the whole table:

  mysqldump [options] sourcedatabase tablename \
    | mysql [options] --host remoteserver --user username ...
  

• transfer the table with MySQL compression

  # same as above, mysql has the "-C" flag
  

• transfer using SSH encryption and compression; mysql is executed remotely

  mysqldump [options] sourcedatabase tablename \
    | ssh -C user@remoteserver 'mysql [options]'
  

• transfer using intermediate SQL file and rsync to transfer only modifications

  mysqldump [options] sourcedb tbl > dump.sql
  rsync [-z] dump.sql user@remoteserver:/path/to/remote/dump.sql
  ssh user@remoteserver "mysql [options] < /path/to/remote/dump.sql"
  

The above are all simple table overwrites, remote data is LOST and replaced by the master copy. The mysqldump-plus-rsync-plus-ssh runs in a time roughly proportional to modifications, which means that if you have a 10-GB SQL dump and add a dozen INSERTS, the transfer stage will need at most a couple seconds to synchronize the two SQL files.

To optimize the insert stage too, either you go for full MySQL replication, or you will need a way to identify operations on the table in order to replicate them manually at sync time. This may require alterations to the table structure, e.g. adding "last-synced-on" and "needs-deleting" columns, or even introduction of ancillary tables.

Answer 2

You can use one simple solution - Data Synchronization tool in dbForge Studio for MySQL.

1. Create Data Comparison project that would compare and synchronize two tables on two different MySQL servers.
2. Run application in command line mode using created Data Comparison project document (*.dcomp file).