Merging 2 mysql databases

[arzoo]

is it possible to merge the data of 2 mysql databases with the same db structure but with different data in them.

The problem i'm facing is that there are like 15 tables in the db (same set of fields, same set of tables), however, the tables contain different data and some tables are reference tables with prim keys where other tables use these keys as foreign keys. so when you just add one table over the other when putting the 2 dbs together the pks get all mixed up and some data will reference wrong information.

is there a script or a program that can merge similar dbs that synchronizes the prim keys at the same time so all the referencing is kept consistent after the merge?

thanks.

[chazzy]

I don't think that there's any program that will do it for you automatically, no. but obviously there needs to be some order to the way the data was loaded initially, right? in that case, you should be able to just insert the data in the same order to hold the constraints. typically, a real dba would write a script that would connect to both systems and move the data at a scheduled point, also alter the table to disable the constraints temporarily.

[NightShift58]

Could this be part of the solution?
http://dev.mysql.com/doc/refman/4.1/en/merge-storage-engine.html

[chazzy]

Could this be part of the solution?
http://dev.mysql.com/doc/refman/4.1/en/merge-storage-engine.html

MySQL is proud of their plug and play storage engine capability. Merge is one of them. It has nothing to do w/ merging database tables..

[NightShift58]

It may be a solution for the OP, especially because of the ways keys are handled, which seems to be his major issue.

I don't know what his application does nor what he intends to do with the data, but using the merge engine is definitely a real life option.