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.

  • 2
    You can use FEDERATED tables for this, or possibly pt-archiver from the Percona Toolkit. – Aaron Brown Apr 10 '12 at 10:38
  • FEDERATED isn't the option to me, because it is very slow when I need to select like ~200k+ records. – Minutis Apr 11 '12 at 13:30
  • Look at pt-archive. It may be suited for your purpose. – Aaron Brown Apr 12 '12 at 0:51

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.

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

  • 1
    I kind of find this article after some searching - sqlwithmanoj.wordpress.com/2010/11/10/…, gave this to my server admin, he said he will look into it and I will post how it went if no one has any other sugestion. – Minutis Apr 10 '12 at 10:06

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.