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:

  1. Test database is created with production data
  2. 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)
  3. 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
    NOT EXISTS (SELECT * from proddb.users WHERE proddb.users.id=testdb.users.id) AND
    NOT EXISTS (SELECT * from testdb.MetaDataRecords 
            testdb.MetaDataRecords.pk=testdb.users.pk AND
            testdb.MetaDataRecords.action='C' AND

I think you might find merge tables useful here. Simply keep test data in one table; and have daily snapshots from production in other table; and have merge table of those two.

  • 1
    Do read the problems section before implementing this. In particular, MERGE tables cannot guarantee that unique values are unique across both tables, and certain operations like REPLACE might not work as one would expect. – Jay Oct 9 '12 at 21:34

I have been here, and was willing to modify my workflow slightly in order to minimise the risk that I would have production data that should have been replaced, in my test database.

All I did was, simply:

  • mysqldump the production
  • Inject SQL to the end of the dump, and assert it's in the file (can never be too careful with file writes)
  • Restore this backup into the test database.

The SQL you inject should be maintained separately, perhaps in another script. These are generally called test fixtures. To abstract away from your DB implementation, and rely less on "magic" (i.e., in your case, diffing your test database with its previous restore to see what's been added, then seeing whats removed from live, cross-referencing primary/foreign keys and such) so that there's a much smaller chance it'll screw up, and you end up emailing your real users instead if your test ones.

One advantage (for me) is doing something like:

UPDATE users SET email = CONCAT('gmailusername+', users.name, '@gmail.com')`;

which is a mega-failsafe incase all other avenues fail (such as your mock SMTP server), and the mail slips through (again, can never be too careful with these things).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.