Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a production database and a test database running on MySQL with MyISAM and InnoDB tables, which are updated independently.

The Production database is updated by system and sometimes by a user.

The Test database is mostly updated by a user, and most work is done on this database.

Then at the end of every week or a month, after all verifications that data looks good on test, these two databases need to be merged.

So test will need to have all changes/updates of its own + changes/updates of a production database. Or, the production database could be just a dump of this new test database (or vice-versa).

INSERT IGNORE and/or REPLACE INTO will not be useful as I don't want to lose any of the data, if for instance both production and test databases happened to have a record (different values) with the exact same primary key.

What I came up so far is:

Set up one way MySQL replication from a production (master) to test (slave).

If new records are added to a test database, write a MySQL trigger to update AUTO_INCREMENT value of the table in production database to be a next primary key of affected table in test database.

So the production database tables will have gaps in the primary key fields to make database merge possible later.

The only problem I see here is when MySQL server is restarted, I lose those set AUTO_INCREMENT values in a production database. (It's set to the next of the current biggest primary key)

I also can't insert just empty records in order to solve this last problem with AUTO_INCREMENT after MySQL restart. (at least it's not preferred) Because I will have to go through all queries in a web application and make sure they are not using pseudo "empty" records.

I'm wondering what the best solution would be for this kind of problem?

share|improve this question
    
And what should happen if a specific row has been updated in 2 databases, with different changes? Or row deleted in one database and kept in the other? –  ypercube Jun 8 '13 at 18:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.