One query two MySQL servers

Question

I have two MySQL servers on different server computers. Is it possible to execute single query like this:

INSERT INTO database1.xxxxx SELECT table1.field1, table2.field1 FROM database2.xxxxx INNER JOIN ....

EDIT: I will make cron job witch will run this once a day. I don't need synchronization.

Answer 1

First of all in this scenario you have two remote MySQL server you need to have two connections. MySQL does not support such a query wherein it connects to other server and get details, in my understanding no other RDBMS does this kind of thing.

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Conclusion: NO. You can't write such query on Two Mysql servers.

Answer 2

This is cron job I ended up with:

I had to create database, where I store temp data:

mysqldump -uuser -ppass database table1 -hhost | mysql -uuser -ppass database_copy && mysqldump -uuser -ppass database table2 -hhost | mysql -uuser -ppass database_copy

and etc

and after copying all the needed data I run MySQL queries to process and insert data depending on my needs:

mysql -e "INSERT INTO table3 SELECT fields... FROM database_copy INNER JOIN database_copy WHERE ...; QUERY 2; ..." -hhost -uuser -ppass

P.S. I will confirm my own answer as correct, but feel free to add any easier or less complex way to do this, I will recheck your answer for sure.