Database Design: Separate Live and Test databases for PHP Website

Question

My organization is rewriting our database-driven website from scratch including a brand new database schema.

In the current setup both live and test websites use the exact same database.

We would like the next version to have a separate database for both live and test versions of the website.

Updating the live version of the database with new schema changes from test isn't a problem.

The problem is data on the live database is constantly being changed. For example, users uploading images, modifying meta data, creating new objects, etc.

What is the proper way of keeping the data on the test server in sync with user-entered data on the live server?

Answer 1

Generally, you don't keep it in sync. The test database is a separate system, and the data in it should be generated - so you can dump it and restore its state between different tests.

You should generate "migrations" (popularised by RoR, but they're a good idea) against the test database whenever you change the schema. Migrations are ALTER TABLE statements that update the layout of the database to the new schema. You can then run the migrations against the live database when you're upgrading it.

Of course, beforehand, it'd no doubt be a good idea to dump the real database into another test database to ensure the migration goes smoothly... so yeah.

I'd spin off a pair of databases, and look at beginning to unit test properly. If you're looking for a more off-the-cuff solution, I'd copy the live database to the test database and run your migrations nightly with a cron-job.

Answer 2

Good call on separating production and test.

This is a loaded question, and we might be able to point you in the right direction if you can tell us which database you are using and the amount of data you are expecting to migrate from production to test. For example, for a relatively small database you could just mysqldump out of production into a completely new database on your test database instance. Other solutions involve replication, but this has some overhead in terms of configuration, runtime and maintenance.

Do you really need to replicate production data for your test environment?