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?