How to resolve primary key collision while merging one db with other

Question

I am having my application deployed on two separate regions say US-WEST and EU, both application has its own DB. And now I want to move the EU region DB to US-WEST.

This will lead to primary key collision since both the db has the tables with same primary auto increment id, can anybody give me suggestion to solve this.

Scenario: User Table from DB1(say from US-WEST) has the following entries

ID Name 
1  Rob
2  San
3 Tulip

User Table from DB2(say from EU) has the following entries

ID Name  
1  John
2  Michael
3  Natasha

Answer 1

• For every one of the two original databases (say db0 and db1):

  • Back up the db.

  • Lock database for use by this script only.

  • For all the tables in the database that have foreign keys defined without ON UPDATE CASCADE, change all these foreign keys constraints with this option.

  • For every table with an auto_increment (or a simple integer) Primary Key, run this (the cascading updates will make the rest):

.

           UPDATE TableX
           SET Pk = 2 * Pk - 0         --- for db0
           ORDER BY Pk DESC

           UPDATE TableX
           SET Pk = 2 * Pk - 1         --- for db1
           ORDER BY Pk DESC

• Export the tables from each database.

• Now merge the two databases by simply merging the corresponding tables. All data from db0 will have even ids and all from db1 will have odd ids. No collisions.

• For tables without auto-incrementing Primary Keys or for tables which may have common rows, the merging should be different, off course.

• Unlock.

---

You can read about auto_increment_increment and related system variables that you can change so from this point, the two databases produce different auto incremented ids (one odd ids, the other even ones).