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

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

i having application deployed on 2 separate regions us-west , eu, both application has own db. , want move european union part db us-west.

this lead primary key collision since both db has tables same primary auto increment id, can give me suggestion solve this.

scenario: user table db1(say us-west) has next entries

id name 1 rob 2 san 3 tulip

user table db2(say eu) has next entries

id name 1 john 2 michael 3 natasha

for every 1 of 2 original databases (say db0 , db1):

back db.

lock database utilize script only.

for tables in database have foreign keys defined without on update cascade, alter these foreign keys constraints option.

for every table auto_increment (or simple integer) primary key, run (the cascading updates create rest):

.

update tablex set pk = 2 * pk - 0 --- db0 order pk desc update tablex set pk = 2 * pk - 1 --- db1 order pk desc

export tables each database.

now merge 2 databases merging corresponding tables. info db0 have ids , db1 have odd ids. no collisions.

for tables without auto-incrementing primary keys or tables may have mutual rows, merging should different, off course.

unlock.

you can read auto_increment_increment , related scheme variables can alter point, 2 databases produce different auto incremented ids (one odd ids, other ones).

mysql mysql-management