How do you sync a data-base from a test server to production server

Question

If you have a test server, and a production server. How do you sync the database?

Do you add the data in the test server first, just like you code in the test server?

How is that generally handled?

Answer 1

That depends on how coupled you can allow the two databases to be.

One configuration might be to have the two databases replicated with the production server in master configuration and the test server as slave. I would use caution with this method, unless you absolutely need the synchronization of live data with test infrastructure, don't go down this path.

Instead, if you want to keep the two instances separated, just replicate with mysqldumps.

Answer 2

I version control SQL dumps (as made by mysqldump). I find it's usually a nice portable format.

Some platforms have concepts like Django's Fixtures which dumps models data to a JSON or XML file so you can version control it to/from the server and load it up where you need it. Whether you have something like this available depends on your platform.

You're talking about getting a live version in your production environment. If I were you, I'd settle for near-live. Cron up a dump script on the server and either write a little script to pull it into your production environment or just rely on version control.

Anything more than near-live will likely be two-way and risk live data.

Answer 3

I use Navicat to sync my development and production servers. The Navicat product allows structure syncing (ie new fields) and data syncing (data). The main limitation is that you can't sync any tables that don't have a primary key. And its not always that fast if there are a lot of records to transfer.

Answer 4

WE do all database devvelopment inscripts including scripts to add records to lookup tables. these scripts are in source control and are versionied just like any other code. To send changes from dev to prod, we run the scripts for that version. This would include any changes needed to lookup type tables as well as table structure changes, sps, userdefined functions, views, etc. SInce it cannot be deplyed to prod without a script (we havea configuration managment team that does the deplying not developers), we have no trouble at all with people nort using scripts or source control.

To go back from prod to dev, we refesh last prod backup and then rerun the dev scripts which have not yet been promoted to prod.