MySQL Insert Into from one Database in another of two different Host

Question

I want to push some values from one database (DB1) from a host (HOST1) to another database (DB2) from another host (HOST2). The query structure should be like following.

insert into HOST2.DB2.TABLEA (COL1, COL2, COL3) 
select COL1, COL2, COL3 HOST1.DB1.TABLEB ..

I know this will work with two different dbs in a same host. But dont know about two different hosts.

I am using java to perform this action.

Thanks in advance.

It won't work unless you have Federation (dev.mysql.com/doc/refman/8.0/en/federated-storage-engine.html) between the two hosts, no, and even then you may have to modify the query a bit (I don't know the specifics, hence I am posting a comment and not an answer). Another solution might be to use Java to fetch the data from one host into a Java variable, and then generate an INSERT to place it into the table in the other host. — ADyson
– ADyson, Commented Jan 8, 2019 at 10:18
With consoles one would use mysqldump. One could export to a csv file but then has to copy that file too. So a java program seems simplest - if the databases are open to the net. Maybe there are admin sql "console" sites. — Joop Eggen
– Joop Eggen, Commented Jan 8, 2019 at 10:26

lonwolf · Accepted Answer · 2019-01-08 11:28:04Z

0

If it's a one time activity, you can do using mysql.

First perform below query in source DB then in result you will get Queries that you need to run in destination DB.

select CONCAT(" insert into test(user_id) values(", user_id, ");") from user_table;

If it's not one time operation, then you need to make both datasource's repository in code.

answered Jan 8, 2019 at 11:28

lonwolf

911 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

MySQL Insert Into from one Database in another of two different Host

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related