Drush aliases - synchronising databases with sql-sync

Intermediate drush tutorial - Part 2

synchronise databases image

One task that drush can complete, using just one command, is to replace the local database with your production database. This allows you, amongst other things, to test your local code updates with the production database.

A word of caution - remember with great power comes great responsibility. In a later tutorial (Part 3) I will demonstrate a file you can use to stop accidentally erasing your production database!

All we need to do is tell drush what to do with our database dump. As of drush 8, the dump file is stored temporary and is deleted after use.

Firstly appended the $options array variable to your local alias like so.


<?php
//local alias - details of you local server / machine
$aliases['local'] = array(
  'uri' => 'http://example.local',
  'root' => '/Users/YOUR_USERNAME/Sites/example.local',
  'path-aliases' => array(
    '%dump-dir' => '/tmp',
    '%files' => '/Users/YOUR_USERNAME/Sites/example.local/sites/default/files',
    '%drush-script' => 'PATH/TO/drush.php',  //example -> /Users/YOUR_USERNAME/.composer/vendor/bin/drush.php
  )
) + $options;
?>

Now you can add your options array at the top of the example.aliases.drushrc.php file. The finished file show look like this.


<?php

$options = array(
  'target-command-specific' => array(
    'sql-sync' => array(
      //Create a fresh db not a merge.
      'create-db' => TRUE,
      //Sanitizing is useful, especially if you automate email campaigns.
      //You probably don't want to email your site users from a local machine
      //when testing.
      'sanitize' => true,
      'confirm-sanitizations' => TRUE,
      //%uid is the users id
      'sanitize-email' => 'user%uid@local',
      'sanitize-password' => 'pass',
      //A comma-separated list of tables to include for structure, but not data.
      //Include 'watchdog' if you have dlog on production. 
      //* is a wildcard so cache_* removes all cache data
      'structure-tables-list' => 'history,sessions,cache,cache_*',
    ),
  ),
);

//local alias - details of you local server / machine
$aliases['local'] = array(
  'uri' => 'http://example.local',
  'root' => '/Users/YOUR_USERNAME/Sites/example.local',
  'path-aliases' => array(
    '%dump-dir' => '/tmp',
    '%files' => '/Users/YOUR_USERNAME/Sites/example.local/sites/default/files',
    '%drush-script' => 'PATH/TO/drush.php',  //example -> /Users/YOUR_USERNAME/.composer/vendor/bin/drush.php
  )
) +$options;
    
//production alias - details of you remote server 
$aliases['com'] = array(
  'remote-host' => 'SERVER_IP_ADDRESS',
  'remote-user' => 'USERNAME_ON_SERVER', // example -> www-data
  'root' => 'SITE ROOT', // example -> /var/www/html/example.com/public_html'
  'uri' => 'https://example.com',
  'os' => 'Linux',
  //If you use keys for ssh, add path to you ssh private key (on your local machine) 
  'ssh-options' => '-o PasswordAuthentication=no -i /home/YOUR_USERNAME/.ssh/id_rsa',
  'path-aliases' => array(
    '%dump-dir' => '/tmp',
    '%files' => '/var/www/html/example.com/public_html/sites/default/files',
    '%drush-script' => 'PATH/TO/drush.php', //example -> /home/YOUR_USERNAME/.config/composer/vendor/bin/drush.php
  )
  'php' => '/usr/bin/php', // PATH/TO/YOUR/PHP
  'php-options' => '-d error_reporting="E_ALL^E_DEPRECATED"',
);
?>

Now by using the command drush sql-sync @example.com @example.local you can replace your local database with your production database, while automatically sanitizing your user data and removing superfluous cache data.

The drush command order has to be drush sql-sync remote local. Drush does not stop you from replacing your production database with the one from you local machine.

The next tutorial will look at how to protect your production database with a policy.drush.inc file. This will automatically stop you replacing the production database by accident. Drush aliases Part 3 - safety first with policy.drush.inc file.