How to update a table automatically as another table is updated on different mysql server?

Question

Let's say I have two databases 'db1' & 'db2' on different mysql servers 'A' & 'B' respectively.

I want to check every 6 hours if there is any update found in 'table1' in 'db2', then the 'table1' in 'db1' will be automatically updated.

How can I do that with trigger or a cron job? and when it will be fired?

This kind of sounds like a case where MySQL replication is what you're looking for... — Charles, Dec 18 '12 at 10:33
You can check this post. There are som clients which would help you. [1]: http://stackoverflow.com/questions/7707859/mysql-database-sync-between-two-databases/14040878#14040878 — , Mar 20 '13 at 07:29
Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) — e4c5, Aug 16 '16 at 06:15

score 0 · Accepted Answer · answered Dec 18 '12 at 10:36

You could do with with a cron job, simply could run a php file every minute.

this file can check for a new row in a table1 in db2 (saving current count of rows in a text file for comparison, and if new count > old count, then this can then update table1 in db1.

easy

but mysql replication as @Charles said in the comment would be better.

score 0 · Answer 2 · answered Dec 18 '12 at 11:33

I accepted kutF's answer and after you run that program Using keyword CASCADE in table creation in mysql. So if you update the parent table then the child table also get updated

CREATE TABLE parent
(
par_id INT NOT NULL,
PRIMARY KEY (par_id)
) TYPE = INNODB;

CREATE TABLE child
(
par_id INT NOT NULL,
child_id INT NOT NULL,
PRIMARY KEY (child_id),
FOREIGN KEY (par_id) REFERENCES parent (par_id) ON DELETE CASCADE
) TYPE = INNODB;

How to update a table automatically as another table is updated on different mysql server?

2 Answers2