Moving data between identical database schema's in MySQL

Question

I have 2 databases with identical schemas. One is a master database serving production/marketing purposes (probably 95% SELECTs) and the other is a "slave" and runs SELECTs/INSERTs for orders, customers, etc for a specific micro-site.

The reason for the separation is not all micro-sites will run the same products/specials/coupons etc and an API is in the works, but it won't be completed for some time.

I need to move data from the slave/micro-site database to the master database and maintain key associations.

Example: Master database has orders #1 and #2 (on orders.id let's say) and I have several new orders on my micro-site database that I want to get migrated to the master database so production/manufacturing can begin processing those orders.

How can I move that data using in the easiest way to avoid conflicts/duplicate keys?

Thanks for your time.

Answer 1

You can temporarily disable foreign key checks during the copy.

Or do you need to copy them while keeping all constraints in place?

Answer 2

If you have 2 databases, and (auto-increment) primary keys should not overlap, the easiest way to achieve it is to set auto_increment_increment & auto_increment_offset.

Master:

auto_increment_increment = 2
autoincrement-offset = 1

Copy yet to be merged:

auto_increment_increment = 2
auto_increment_offset = 2

Now, all 'freshly' inserted records with an auto-increment key will guaranteed not overlap. Copying data from one to another (i.e: with a defined key/id) will just insert that id, making it easy to copy 'standalone' data like orders to the other database. If you are however altering data with the same primary keys (copy product#1 from master to copy, altering product#1 on copy, and then trying to copy product#1 back to the master as a distinctly different product), then you'll have to write a moderately sofisticated import script which knows all about you primary keys & relations and reassigns them. Possibly you can do this without to much script if it is about 2 databases on 1 server, with carefully crafted temporary tables etc, but there is no 'fire & forget' method out of the box I know of.