Merging Two Mysql Databases With Identical Schema And Different Data

[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