Manually Port Content Updated/Added After Copy To Test DB
  1. Set live site in offline mode
  2. Backup DB and file system
  3. On the live site; Modify all content types (node-types) to include "Create new revision" which is found under publishing options
  4. Place live site in online mode
  5. Complete modifications to test site
  6. Add view to live site which displays all revisions added after time you placed the site back online
  7. Use view to identify updated/added items and port these to test server
  8. Overwrite file system on live server (ensure you have an additional backup) with files from test server (only needed if file system changes were made)
  9. Overwrite DB on live server with test server DB (ensure you have a backup of live servers DB)
SQL Sync
  1. Backup live database
  2. Copy live database to test server
  3. Use SQLyog to sync database tables.
  4. Use MySQLdiff verify database structure is correct between the test database and live database.
SQLyog
Software works via connection with database server(s) to test and live database. Server will overwrite selected tables with modified data or structure from test database. Note: You will most likely want to create a second test database (using the latest live database) to verify working condition.
MySQLdiff
This application will verify the structure of the second test database is matching the test database prior to overwriting the live database.
DAST
Drupal Automated Staging Toolkit, referred herein as DAST, is an application requiring root server access for installation. The applications provides useful build files which allow deployment of the test site as the live site.
Installation Profiles
Dave Cohen described a method to sync installation profiles. This method works by beginning drupals nid's at 1000, reserving the previous 1000 specifically for the profile information. On the above linked page, Cohen also provides a database script to dump database configuration changes to allow quick syncing between the test (where the database changes are dumped from) and the live (overwritten with the database dump).

Comments

keff’s picture

keff commented

Hi everyone, I described the problem here: http://drupal.org/node/243423
MySQL, without special mode set on, expects all autoincremented fields to begin at 1, but drupal uses for example user with uid = 0 as anonymous user (all content people post without logging in is set to belong to this user).
This can cause problems, because in many cases when some program duplicates database (between test and live server), it doesn't set that special mode for the connection, and MySQL will change uid of item with uid = 0 to the (highest number set so far)+ 1. That will always change uid of anonymous user, and then all the content from anonymous user will be orphaned (will have uid, which doesn't exist in user table). As such, most sql queries in drupal will ignore such comment (because they use JOIN statement to select only content from existing users).

So, If you find out that anonymously posted comment completely disappeared, check the users table, find the anonymous user, and set its uid back to 0.

JamesV33’s picture

JamesV33 commented

Be sure to take a good look at the 'sequences' table. Instead of auto_increment, drupal keeps track of id's using the sequences table. When merging databases this can cause problems if it's out of synch, possibly overwriting data.

cels’s picture

cels commented

MySQLdiff are very outdated (1.5.0 - 2004), I found other software for database sync:

dbForge Schema Compare for MySQL ($79.95)
dbForge Data Compare for MySQL ($79.95)

Devart dbForge Studio for MySQL Professional edition includes the schema and data compare ($199.95).

There are 30 days free trial versions. Not the cheapest solution but it looks good solutions.