Setting Up Drush Aliases And Using sql-sync

Drush sql-sync is a very easy way of grabbing a copy of a Drupal database from one site and installing it on another site. The normal application of this task is to copy the database from the production site to your local environment for facilitate local development. There are one or two steps to sort out before you can use the command

This post assumes you have Drush 8 installed on your machine as Drush 9 uses a different syntax for sotring Drush aliases. This also assumes that you have setup SSH key access to all servers you need to get access to.

Setting Up Aliases

The first thing you need to do is set up your Drush aliases, this allows you to connect to the remote Drupal instance from your local machine. Drush aliases are kept in your ~/.drush directory. To see what aliases you have on your system use the command.

drush sa

Different aliases are created in different ways, as such this section discusses how to set up aliases for each situation.

Drupal VM Drush Aliases

Drupal VM will automatically add aliases to your computer for each of your local environments.

Acquia Aliases

Acquia aliases can be updated automatically via a Drush command, or manually by downloading the aliases directly.

Download the Acquia Drush commands by going to https://cloud.acquia.com/proxy/api/account/drush-aliases/download

Next, extract the files to your home directory by using the command. You may need to clear the Drush caches to get them to be recognised.

tar -C $HOME -xf $HOME/Downloads/acquia-cloud.drush-aliases.tar.gz

With that in place you can now create all of the Drush aliases using the command.

drush acquia-update

More information on how to use Acquia Drush aliases can be found in their help documentation https://docs.acquia.com/acquia-cloud/drush/aliases.

Other Hosting Aliases

Custom hosting must be hand crafted, but creating them isn't too difficult. It's best to create another alias file within your ~/.drush directory so that it's not overwritten. Create a file ending in aliases.drushrc.php and Drush will automatically pick it up when looking for aliases.

The file itself just needs to contain the SSH access details of the server. These are defined as PHP arrays that look a little like this.

$aliases['example.com'] = array(
  'uri' => 'example.com',
  'root' => '/var/www/vhosts/example.com/current/docroot',
  'remote-host' => 'example.com',
  'remote-user' => 'username',
  'ssh-options' => '-o PasswordAuthentication=no -i "~/.ssh/myaccesskey"',
);

Note that if your destination site has Drush 9 installed then you might want to target that executable directly instead of relying on the systemwide Drush version being used. This is done by adding the path-aliases option.

$aliases['example.com'] = array(
  'uri' => 'example.com',
  'root' => '/var/www/vhosts/example.com/current/docroot',
  'remote-host' => 'example.com',
  'remote-user' => 'username',
  'ssh-options' => '-o PasswordAuthentication=no -i "~/.ssh/myaccesskey"',
  'path-aliases' => array(
    '%drush-script' => '/var/www/vhosts/example.com/current/vendor/bin/drush',
  ),
);

One of the many good things about Drush is that it is possible to get a Drush 8 instance to communicate with a Drush 9 instance quite happily. There are numerous other options available for these PHP arrays, and this covers just the bare minimum. See this page for more information : https://github.com/drush-ops/drush/blob/8.x/examples/example.drushrc.php

Once in place, clear the Drush cache to get Drush to pick up the new alias. The above example will create a Drush alias called @example.com.

On a side note, it is possible to generate a site alias by running the command.

drush site-alias @self --full --with-optional

However, this doesn't take into account any access details or remote users so it has limited uses. It's a good starting point though so might come in handy.

Inspecting Aliases

To see what aliases are available on your system you can use the drush site-alias (sa) command. This will print out a list of all the aliases that your system knows about. My system tends to have quite a few different aliases present, so to see what an alias contains you can use append an alias name to the site-alias command.

Here is an example of the command in action, which prints out a typical alias created by Drupal VM for a site called example.local.

drush sa @example.local
$aliases["example.local"] = array (
  'uri' => 'example.local',
  'root' => '/var/www/example/docroot',
  'remote-host' => 'example.local',
  'remote-user' => 'vagrant',
  'ssh-options' => '-o "SendEnv PHP_IDE_CONFIG PHP_OPTIONS XDEBUG_CONFIG" -o PasswordAuthentication=no -i "/Users/username/.vagrant.d/insecure_private_key"',
  'path-aliases' =>
  array (
    '%drush-script' => 'drush',
  ),
);

Using Aliases

With your new alias in place you can use it to interact with your remote Drupal site directly. The only change you need to make is to include the alias that you want to communicate with in your command. For example, to get the current status of a site you would use the following.

drush @example.com status

To log into the site use the uli Drush command as you normally would. The neat thing about this is that once you have generated the URL your browser will open automatically and take you to the link.

drush @example.com uli

To clear the caches on a remote site use the following. This assumes that the site is running Drupal 8.

drush @example.com cr

The sql-sync Command

Perhaps the most useful and convenient Drush command is sql-sync. This will copy a database from one Drupal site and install it in another. The syntax for this is pretty easy.

drush @copy_db_from_here @copy_db_to_here sql-sync

To copy the database from the production site at @example.com to a local development site at @example.local use the following command.

drush @example.com @example.local sql-sync

There are also couple of options that will help when running sql-sync.

--sanitize - This allows you to clear out all personal data from the database, but only once it has been fully imported locally.

--structure-tables-key - Allows you to skip the data from some tables being imported. Drush comes with a default of 'common' as a key that will skip cache, search, sessions and watchdog data exports.

An additional step to run before you sync is to clear out the database. This can be done by using the Drush command sql-drop on your local database. After that you can run the fill sync and not have to worry about old data or tables being present.

drush @example.local sql-drop

A full sql-sync command to sanitize data and skip common tables might look like this.

drush @example.com @example.local sql-sync --sanitize --structure-tables-key=common

It's important to note that you should always check that these two aliases are the right way around. It is perfectly possible to delete the production database using this command so you should run this command with the utmost care.