How to choose what tables to skip for drush sql-sync @dev @staging?

Question

Following my previous question How to set Drush sql-sync not to clone users between @dev and @staging? I have manually preselected over 80 tables that should not be automatically synced (content, roles and permissions, users, temporary, cache). I am not fully satisfied with this solution. Why? Because my filter decisions is based on experience, intuition and guess when I would like to use the best practice method.

How to properly choose what tables to skip for drush sql-sync @dev @tst and drush sql-sync @production @staging?

My setup:

git.drupal.org ==> local drupal git repository ==>  @dev -> @tst -> @staging -> @production (configuration, static content, roles and permissions)


@staging <- @production (content, production scenarios to be tested)

The Definitive Guide to Drupal 7 by Benjamin Melançon and others says:

Here is the list of tables that appears in example.drushrc.php:

$options['structure-tables'] = array(  'common' => array('cache',
  'cache_filter', 'cache_menu', 'cache_page', 'history', 'sessions',
  'watchdog'),
);

You may need to add some more tables to this list. A good place to start would be to consider the list of tables that contain cache in their name via drush sql-query 'show tables;' | grep cache

Remove from this list tables such as imagecache_action and so on, and add the remainder to your structure tables list.

After you do an sql-sync with skipped tables, you will need to clear the cache on the target site to make sure that things work right.

What are the other tables to consider? Why?

Answer 1

Drush sql-sync does not support shared databases that use prefixes. You might have some luck if you wrote a wrapper script that used drush sqlq 'show tables;' to get a list of available tables, filter them manually by prefix, and then pass them back to sql-sync via the --tables-list option.

Answer 2

In Drupal 7 these tables you should consider:

'cache', 'cache_field', 'cache_form', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'sessions', 'watchdog'

In Drupal 8, these:

'cache', 'cache_bootstrap', 'cache_config', 'cache_container', 'cache_data', 'cache_default', 'cache_discovery', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'search_index', 'sessions', 'watchdog'

This doesn't include specific tables which are related to Migrate module or any other custom tables which may store some temporary data.

Example drush command to dump and skip these tables (create just a structure without data):

drush sql-dump --structure-tables-list=cache,cache_filter,cache_menu,cache_page,history,sessions,watchdog --result-file=dump.sql

^{Hint: Add --ordered-dump for nicer human output, but a bit slower on import.}

Normally adding structure-table option should be enough for local operations ($options['structure-tables']), but for sql-sync, I think this should be added for specific alias in command-specific section, e.g.

$aliases['foo'] = array(
  // These options will only be set if the alias is used with the specified command.
  'command-specific' => array(
    'sql-sync' => array(
      'structure-tables-key' => 'common',
      'skip-tables-key' => 'common',
      'structure-tables' => array(
        // You can add more tables which contain data to be ignored by the database dump
        'common' => array('cache', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'search_index', 'sessions', 'watchdog'),
      ),
      'skip-tables' => array(
        'common' => array('field_deleted_revision_63'),
      ),
    ),
    'sql-dump' => array(
      'ordered-dump' => TRUE,
      'structure-tables-key' => 'common',
      'skip-tables-key' => 'common',
    ),
  ), // end: command-specific
  // Applied only if the alias is used as the source.
  'source-command-specific' => array(
  ),
  // Applied only if the alias is used as the target.
  'target-command-specific' => array(
  ),
);