Dùng Drush để Exporting and reloading Drupal databases

The Drush system is a tremendous productivity booster for those who learn to use it, it can be used for simplifying module updates, to upgrading from Drupal 6 to 7, to juggling Features. Furthermore, its potential is near limitless once a relatively simple set of hooks is learned. My focus today, however, is on a rather rudimentary problem - exporting & re-importing databases, a task that is typically used to make a copy of a production database in order to run it locally.

The Drush "sql-dump" command allows the current site to be exported to an SQL file and it'll automatically handles the database being used on a site, e.g. for MySQL it uses mysqldump to do the actual work. By default it will output all data from all tables, which isn't desirable as this can result in a HUGE database dump with lots of redundant data that can be regenerated as needed, especially the cache tables.

The solution to this problem is to tell Drush to not export data in tables that can be ignored, e.g. the cache tables, search index tables, sessions table and watchdog table. This is done by adding lines to the sites/all/drushrc.php file like so:

<?php
// Ignore the data in these tables, but keep the table structures. Make sure to
// update the list when new cache tables are added.
$options['structure-tables']['common'] = array(
  'cache',
  'cache_admin_menu',
  'cache_apachesolr',
  'cache_block',
  'cache_bootstrap',
  'cache_commerce_shipping_rates',
  'cache_field',
  'cache_filter',
  'cache_form',
  'cache_image',
  'cache_libraries',
  'cache_media_xml',
  'cache_menu',
  'cache_page',
  'cache_path',
  'cache_rules',
  'cache_token',
  'cache_update',
  'cache_views',
  'cache_views_data',
  'history',
  'search_dataset',
  'search_index',
  'search_node_links',
  'search_total',
  'sessions',
  'watchdog',
);
// Use the list of cache tables above.
$options['structure-tables-key'] = 'common';
?>

The first variable defines a list of tables that will be exported without the data, the second variable tells Drush which list to use - multiple lists can be defined and there is no default.

Personally I've seen small-ish sites generate multi-gigabyte database dumps because of the tables above, so excluding them will greatly reduce the amount of time taken to download & import the database, thus reducing wasted time & headaches.

When "drush sql-dump" is ran it will automatically output to the terminal, which is hardly desirable. Instead it's possible to export to a file using the "--result-file" argument, e.g.:

drush sql-dump --result-file=omega_20131119.sql

It's worth noting that this will be exported to the site's root directory, so the file will need to be moved afterwards.

Multiple hosts

I set up sites so that each site instance has its own hostname configuration directory & file, e.g. sites/dev.example.com/settings.php controls the settings for the develop site. The problem, though, is that Drush only works with the settings file from sites/default/settings.php, which means that when running Drush on the develop site it'll fail to connect to the database. The solution is to tell Drush to use a specific hostname config using the "uri" option in the drushrc.php file:

<?php
$options['uri'] = 'dev.example.com';
?>

Now any drush commands will automatically load the "sites/dev.example.com/settings.php" file instead of "sites/default/settings.php" file.

There's only one drushrc.php file!

There's one last difficulty with this. Because there can only be one drushrc.php file, some additional tweaking is necessary to streamline this so that on all site instances it's no longer necessary to tell Drush what hostname to use.

What I recommend is adding a drushrc.php to each per-hostname directory, e.g. sites/dev.example.com/drushrc.php for the dev.example.com site, and add them to the repository. Then, add a file called e.g. "symlink_drushrc_file_here.txt" within the sites/all/drush directory. Next up, add the new text file to the repository (git add sites/all/drush/symlink_drushrc_file_here.txt), update the .gitignore file to list "sites/all/drush/drushrc.php", and commit these changes. Then, on each separate site instance, i.e. on each server that the site runs on, create a symlink from that hostname's drushrc.php file to the sites/all/drush directory, e.g.:

ln -s sites/dev.example.com/drushrc.php sites/all/drush/drushrc.php

The result of all of these steps is that on each instance of the site the drush commands will be available without having to specify the hostname, i.e. "drush sql-dump" instead of "drush sql-dump --uri=dev.example.com", and each Drush settings file

Bonus round!

For bonus points reduce the amount of duplication in the drushrc.php files by putting some of them in another file that is loaded in by each hostname config. For example, put the 'structure-tables' part in sites/all/drush/drushrc_shared.php and then add the following to each drushrc.php file:

<?php
// Drush settings used by all hostnames.
include_once('./sites/all/drush/drushrc_shared.php');
?>

Et voila.

Loading the database

The result of the above steps is that there'll be an easy way to export the site to an SQL file that can then be reloaded on another computer. Drush provides the "sql-query" command that can be used to load the database:

drush sql-query < example_20131119.sql

That said, by reloading the database using Drush it could, under certain circumstances, run into conflicts, i.e. it could be trying to replace a table that is locked because Drush is currently executing. One alternative is to use a graphical database tool, but I've found most of these have problems with large SQL files, never mind the file permissions problems that phpMyAdmin throw around.

To avoid potential problems I tend to just use the MySQL commands directly. To do this just use the database settings taken from the settings.php file, i.e.:

mysql -uUSERNAME -pPASSWORD DATABASENAME < DATABASEDUMPFILENAME.sql

In practical terms this would become something like the following:

mysql -uexample -pexample exampledotcom < example_20131119.sql

This will result in a copy of that site running locally with a fresh reload of the (production?) database. Now, back to work!

Other options

As with everything Drupal-related there's more than one way to export & reload databases. Drush includes a command called "sql-sync" specially for copying databases between servers that many people may prefer to use. Personally I just use sql-dump for a few reasons:

• Having an export of the database makes it redistributable to other team members so that everyone can be sure to be working off the same edition of the site.
• It makes it possible to keep a backup of the database on a specific date & time.
• Most of the times I'm logged into the server to do other things at the time, e.g. deploy code changes, and I like to make a before & after backup.
• If a database import fails I don't have to wait for Drush to do the export portion again, I can just rerun the import.

In the end, whatever options are selected, Drush has it covered.