Merge Content and Structure Across DBs

Specs

Version
Drupal 7
Tools
Batch Processes
Field API
Created
19 Mar 2016

Summary

Under ideal circumstances, you can use the Features module to migrate structure, such as CCK fields, menu items, taxonomy vocabularies, or system variables, to your production database.

But if you're put in to circumstances where this is not possible, this snippet will help you migrate your content from your production database back to your development environment. You would then dump your development database and import it in to production.

It does this using a Drush command in the following steps:

  1. Modify the $databases variable in settings.php to allow for multiple databases on your Drupal site. Then use db_set_active('db_name') to switch back and forth
  2. Loop through an array of databases tables that store content, taxonomy terms, webform submissions, and managed files
  3. Truncate each of those tables in development
  4. Using a MySQL command, move all data from the production tables in to your development tables
Drush Command Example
drush --user=1 example-migration

Code

1. settings.php modification

settings.php
MODULE_PATH/sites/default
Modify the $databases variable in your settings.php to allow for multiple databases. You can then use db_set_active('default') and db_set_active('production') to switch back and forth.
          
$databases = array(
  'default' => array(
    'default' => array(
      'database' => 'dev_db_name',
      'username' => 'dev_db_username',
      'password' => 'dev_db_password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
  'production' => array(
    'default' => array(
      'database' => 'prod_db_name',
      'username' => 'prod_db_username',
      'password' => 'prod_db_password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);
          
          

2. Drush Command

MODULE_NAME.drush.inc
Add this to one of your modules by putting it in a file called MODULE_NAME.drush.inc. It will be auto discovered by Drush.
          
function MODULE_NAME_drush_command() {

  $items = array();

  $items['example-migration'] = array(
    'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_LOGIN,
  );

  return $items;
}

function drush_MODULE_NAME_example_migration() {

  $tables = array();

  // node
  $tables[] = 'node';
  $tables[] = 'node_access';
  $tables[] = 'node_comment_statistics';
  $tables[] = 'node_counter';
  $tables[] = 'node_revision';

  db_set_active('production'); // use the key from your $databases array in settings.php

  $schema = drupal_get_schema(NULL,TRUE);
  foreach ($schema as $table_name => $data) {
    if ((strpos($table_name, 'ield_data_') !== false) || (strpos($table_name, 'ield_revision_') !== false)) {
      $tables[] = $table_name;
    }
  }

  db_set_active('default');

  // taxonomy
  $tables[] = 'taxonomy_index';
  $tables[] = 'taxonomy_term_data';
  $tables[] = 'taxonomy_term_hierarchy';
  $tables[] = 'taxonomy_menu';

  // webforms
  $tables[] = 'webform';
  $tables[] = 'webform_submissions';
  $tables[] = 'webform_submitted_data';

  // other tables
  $tables[] = 'url_alias';
  $tables[] = 'field_collection_item';
  $tables[] = 'field_collection_revision';
  $tables[] = 'file_managed';
  $tables[] = 'file_usage';

  $batch = array(
    'operations' => array(),
    'title' => t('Processing Batch'),
    'init_message' => t('Process is starting.'),
    'progress_message' => t('Processed @current out of @total.'),
    'error_message' => t('Process has encountered an error.'),
    'file' => drupal_get_path('theme', 'MODULE_NAME') . '/MODULE_NAME.drush.inc',
    'finished' => 'MODULE_NAME_example_migration_batch_finished'
  );

  $start_time = time();

  $batch['progressive'] = FALSE;

  $batch['operations'][] = array('MODULE_NAME_example_migration_content_batch', array($tables, $start_time));

  drush_log(t('Batch process is starting !time', array('!time' => format_date($start_time, 'long'))), 'success');

  batch_set($batch);
  drush_backend_batch_process();

}

function MODULE_NAME_example_migration_content_batch($tables, $start_time, &$context) {

  set_time_limit(9999999);

  foreach ($tables as $key => $table_name) {
    if (db_table_exists($table_name)) {
      $results = db_query("TRUNCATE development_database_name." . $table_name . ";");
      $results = db_query("INSERT INTO development_database_name." . $table_name . " SELECT * FROM production_database_name." . $table_name . ";");
    }
    else {
      drush_log(t('Dev database is missing table !table_name but it exists in production.', array('!table_name' => $table_name)), 'warning');
    }
  }

  $context['results']['start_time'] = $start_time;

}

function MODULE_NAME_example_migration_batch_finished($success, $results, $operations) {
  if ($success) {

    $start_time = $results['start_time'];

    $seconds = time() - $start_time;
    $hours = floor($seconds / 3600);
    $mins = floor(($seconds - ($hours*3600)) / 60);
    $secs = floor($seconds % 60);

    drush_log(t('Job completed in: !hours hours !mins minutes !secs seconds', array('!hours' => $hours, '!mins' => $mins, '!secs' => $secs)), 'success');
  }
  else {
    $error_operation = reset($operations);
    $message = t('An error occurred while processing %error_operation with arguments: @arguments', array('%error_operation' => $error_operation[0], '@arguments' => print_r($error_operation[1], TRUE)));
    drush_log($message, 'error');
  }
}
          
          

Comments

Placeholder: I'll extend node comments with ajax and other functionality here.