I'd like to create a test database that each day is refreshed with data from the production database.
BUT, I'd like to be able to create records in the test database and retain them rather than having them be overwritten.
I'm wondering if there is a simple straightforward way to do this.
Both databases run on the same server, so apparently that rules out replication?
For clarification, here is what I would like to happen:
Test database is created with production data
I create some test records that I want to keep running on the test server (basically so I can have example records that I can play with)
Next day, the database is completely refreshed, but the records I created that day are retained. Records that were untouched that day are replaced with records from the production database.
The complication is if a record in the production database is deleted, I want it to be deleted on the test database too, so I do want to get rid of records in the test database that no longer exist in the production database, unless those records were created within the test database.
Seems like the only way to do this would be to have some sort of table storing metadata about the records being created? So for example, something like this:
CREATE TABLE MetaDataRecords (
id integer not null primary key auto_increment,
tablename varchar(100),
action char(1),
pk varchar(100)
);
DELETE FROM testdb.users
WHERE
NOT EXISTS (SELECT * from proddb.users WHERE proddb.users.id=testdb.users.id) AND
NOT EXISTS (SELECT * from testdb.MetaDataRecords
WHERE
testdb.MetaDataRecords.pk=testdb.users.pk AND
testdb.MetaDataRecords.action='C' AND
testdb.MetaDataRecords.tablename='users'
);
