Merging Two Mysql Databases With Identical Schema And Different Data

[rschlachter]

I had a database failure so I immediately switched to my hot backup database. Everything went fine. When the main production db was fixed, I forgot to copy the data from the backup db to the production db. So now I have 3 days worth of data that is only on the backup and not on production. I need to get that data into production. Where it gets messy is that because I didn't move the backup data over, I'm going to have PK conflicts because production started incrementing right where it left off. On top of that, I need to make sure all the FK constraints are correct as well. I found SQLyog on a stack overflow post and I was just wondering if if can help me out in this situation?

I see there is a Database synchronization tool, but from what I've read it sounds like this is geared more towards syncing (copying one db to another) than retaining and merging data between the two.

Any thoughts/suggestions will be greatly appreciated.

[vishal.pr]

production.table

id(PK)

---

1

2

3

4

5

backup.table

id(PK)

---

6

7

8

Now once the problem is solved you switched back to production db, so the current state of production.table became

production.table

id(PK)

---

1

2

3

4

5

6

7

8

Am I correct?

[peterlaursen]

While Vishal was writing I was framing another reply. I'll write that too. Please reply if we missed something!

I understand that you have two databases. They are 'synchroneous' from the beginning till 3 days ago. After that the two databases have data inserted independently. Hence the PK- and FK- conflicts. Do I understand corrrect?

If so SQLyog Data Sync will not be able to resolve this (inserting from one database to another and changing PK's and FK's to resolve all conflicts). I am sorry. Depending on the amount of data you would need a script/an application specialized for this or simply do it manually.

It is possible to have two databases evolve in parallel and sync them but it requires that the database is designed for it. The PK's of all tables must be defined in such a way the same PK-values are not created in different databses.

[peterlaursen]

Lets us take a simple example (omitting FK problems for simplicity): You have data (1st column is PK). The 3rd row in both databases were inserted whne the DB's got 'asynchroneous'

DB#1: (1, x), (2,y), (3,z)

DB#2: (1, x), (2,y), (3,a)

.. and you want to insert to DB#1 some that it becomes (1, x), (2,y), (3,z), (4,a) .. (and resolve FK-conflcits to other tables as well)

— Do I understand right?

[Author]

Posts