Drush 8: Exclude cache tables from drush sql dump backup files

This is a short tutorial on how to create drupal database dumps using drush, in such a way that the cache tables are not included in the database backup file.

Update 2019-03-26 Note that the drushrc.php file structure described below applies for Drush 8. For Drush 9, a the yml format is used for the equivalent drush.yml file, more details here: https://raw.githubusercontent.com/drush-ops/drush/master/examples/example.drush.yml

Prepare a drushrc.php file

With drush, you can create a drushrc.php file, this helps you define default behaviour for drush (drushrc stands for drush runtime configuration).

You can see examples of all the various options for drushrc here. This file is worth reading in its entirety.

https://raw.githubusercontent.com/drush-ops/drush/8.x/examples/example.drushrc.php

Using the knowledge gained from the example.drushrc.php file, I created my own drushrc.php file, and placed in the location ~/.drush/drushrc.php

Important: please remember to put <?php in the first line of the file.

~/.drush/drushrc.php

<?php
/**
 * @file
 * Examples of valid statements for a Drush runtime config (drushrc) file.
 *
 * Use this file to cut down on typing out lengthy and repetitive command line
 * options in the Drush commands you use and to avoid mistakes.
 *
 * Rename this file to drushrc.php and optionally copy it to one of the places
 * listed below in order of precedence:
 *
 * 1.  Drupal site folder (e.g. sites/{default|example.com}/drushrc.php).
 * 2.  Drupal /drush and sites/all/drush folders, or the /drush folder
 *       in the directory above the Drupal root.
 * 3.  In any location, as specified by the --config (-c) option.
 * 4.  User's .drush folder (i.e. ~/.drush/drushrc.php).
 * 5.  System wide configuration folder (e.g. /etc/drush/drushrc.php).
 * 6.  Drush installation folder.
 */

/**
 * Specify the filename and path where 'sql-dump' should store backups of
 * database dumps.  The default is to dump to STDOUT, however if this option is
 * set in a drushrc.php file, the default behaviour can be achieved by
 * specifying a value of FALSE ("--result-file=0" on the command line).  Two
 * substitution tokens are available: @DATABASE is replaced with the name of the
 * database being dumped, and @DATE is replaced with the current time and date
 * of the dump of the form: YYYYMMDD_HHMMSS.  A value of TRUE ("--result-file=1"
 * on the command line) will cause 'sql-dump' to use the same temporary backup
 * location as 'pm-updatecode'.
 */
$options['result-file'] = TRUE;
$options['result-file'] = '/home/yourusername/backup/@DATABASE_@DATE.sql';

/**
 * List of tables whose *data* is skipped by the 'sql-dump' and 'sql-sync'
 * commands when the "--structure-tables-key=common" option is provided.
 * You may add specific tables to the existing array or add a new element.
 */
$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'sessions', 'watchdog');

Copy the text above (replacing /home/yourusername/backup/ with a directory that exists on your file system), and place it in ~/.drush/drushrc.php.

Once you have done this, cd to your drupal docroot directory, and run drush sql-dump --gzip --structure-tables-key=common.

Let's take a look at what the ~/.drush/drushrc.php file is doing.

$options['result-file'] = TRUE;
$options['result-file'] = '/home/yourusername/backup/@DATABASE_@DATE.sql';

These lines ensure that drush sql-dump will output, by default, to the file path and file name we have specified.

You may wish to add other tables to this list, for example any 'migrate_*' tables from a historical data migration.

$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'sessions', 'watchdog');

This line is actually far more important - it ensures that, during the creation of the database dump, the specified tables are left empty.

So, now we can call:

drush sql-dump --gzip --structure-tables-key=common

... and the resulting file will be a lot smaller than it would be if all the cache tables were included.

Update 2018-09-28

I changed the list of tables to be omitted from the backup: I have decided to include search_api_item and search_api_task in my backup now, after reading this:

https://www.drupal.org/project/search_api/issues/2935646

Specifically, this comment from Thomas Seidl (drunken monkey)

Or, in the backend, just keep the data of search_api_item. That table just contains IDs plus a few numeric columns, so it's not that large.

The main reason I want to keep the search_api_item table in backup is so that when, after restoring the backup, I run drush sapi-s, I still have the entries in that table relating to the entries on the apachesolr index.

So, I changed this line:

$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'search_*', 'sessions', 'watchdog');

... to this:

$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'sessions', 'watchdog');