Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
add comment

2 Answers 2

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.

share|improve this answer
    
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
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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