Keeping development database in sync with production database

Question

Here is my process for developing, this is more of a MySQL question then Drupal.

1. Have a local dev environment. This includes a local mysql and a copy of the drupal site. all under version control.
2. When the site is ready, I dump my database and set up a production database, I then create a production instance of the site from my repository.

So the issue I am running into is a user is adding content to the production site. And I am creating database changes on the development version of my site. The two versions are now out of sync. One solution for this is every time I want to develop on my local version I dump the production's database and load it. So I am wondering how this problem is usually approached. Is there something like mysqldiff that will only load data that is not reflected in the database?? Are there any drush tools that will help with keeping drupal databases in sync?

Answer 1

It really depends on what you're changing in the development environment. If you're adding/removing modules, content types, custom fields, etc.. I can't think of a really good way to do this in any sort of trustworthy automated way.

Also I am SURE there must be modules for exporting Drupal content. A quick search turned this up: http://drupal.org/project/node_export