copy data from a mysql database to other mysql database with java

Question

I have developed a small swing desktop application. This app needs data from other database, so for that I've created an small process using java that allows to get the info (using jdbc) from remote db and copy (using jpa) it to the local database, the problem is that this process take a lot of time. is there other way to do it in order to make faster this task ?

Please let me know if I am not clear, I'm not a native speaker.

Thanks

Diego

Hi Diego, the question is to broad, try to give us more information. Could you answer this: how big is the remote database, size in MB, nums of tables, how much time (secs) does it take to complete the import and what would be the acceptable time, how fast is the connection between the remote and the local host??? — Igor
– Igor, Commented Sep 23, 2012 at 23:47
Are you in control of the remote database as well, and if so are they both in a secure network. The speed issue will be mainly sucking the data down in to your client and then pushing it up to your local server. There's not a lot you can do to speed that up. — Tony Hopkinson
– Tony Hopkinson, Commented Sep 23, 2012 at 23:51
Thanks, for the answer. I did not have experience doing this kind of tasks. — Diego Arena
– Diego Arena, Commented Sep 24, 2012 at 0:01
Read this answer. It's a similar scenario but the source database is Oracle. Also, you can reuse another tool to handle this work like keetle. — Luiggi Mendoza
– Luiggi Mendoza, Commented Sep 24, 2012 at 0:10

RGO · Accepted Answer · 2012-09-23 23:48:10Z

1

One good option is to use the Replication feature in MySQL. Please refer to the MySQL manual here for more information.

answered Sep 23, 2012 at 23:48

RGO

4,7774 gold badges30 silver badges41 bronze badges

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

2 Comments

Diego Arena Over a year ago

Thank for the help. I'm not using the replication because the tables don't have the same structures, local tables have at least 4 more columns.

RGO Over a year ago

Then, i think you should try another option which is "The FEDERATED Storage Engine". It is a storage engine that accesses data in tables of remote databases rather than in local tables. Please see here[ dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html]

Joop Eggen · Accepted Answer · 2012-09-23 23:50:30Z

0

JPA is less suited here, as object-relational-mapping is costly, and this is bulk data transfer. Here you probably also do not need data base replication.

Maybe backup is a solution: several different approaches listed there.

In general one can also do a mysqldump (on a table for instance) on a cron task compress the dump, and retrieve it.

answered Sep 23, 2012 at 23:50

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Collectives™ on Stack Overflow

copy data from a mysql database to other mysql database with java

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related