MySQL: Insert into a table from another HOST

Question

So I would like to copy some records from one table to another. But the trick is that the another table is in a different HOST. I will try to explain by giving you a mysql query pseudo code.

Another_host = "192.168.X.X";

INSERT INTO database_original.table_1( id, name, surname)
SELECT id, name, surname
FROM Another_host.database_another.table_2
WHERE Another_host.database_another.table_2.id > 1000;

I would probably have to declare the user for the "Another_host" somewhere. This is what I am trying to do..is this even possible like I imagine it?

Thx

My typical solution for this situation is to use a scripting language, like PHP. Would that be an option for you? — jlemley
– jlemley, Commented Jun 14, 2015 at 12:23

Vishal Zanzrukia · Accepted Answer · 2015-06-11 11:24:55Z

1

There is one workaround solution which will do the same what you want.

Step 1: Take dump of select query

mysql -e "select * from myTable" -h <<firsthost>> -u myuser -pxxxxxxxx mydatabase > mydumpfile.sql

Step 2: Restore the dump

mysql -h <<secondhost>> -u myuser -pxxxxxxxx < mydumpfile.sql

answered Jun 11, 2015 at 11:24

Vishal Zanzrukia

4,9835 gold badges41 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DJack Over a year ago

I was thinking of this workaround, but the table is 1Gb big. So for the first time I would export it and import in another HOST DB..but then I want just to update it..the user will have the power to update the table with one button click..I cannot make the user wait for 1Gb of export/import every time :/

Collectives™ on Stack Overflow

MySQL: Insert into a table from another HOST

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related