Drush aliases w/ PDO SSL support

At Classic, some developers are standardizing the way they sync their local development environments with remote testing servers. I recently invested time re-familiarizing myself with drush aliases. I am documenting the process in hopes of simplifying the steps for others.

Before

Here’s the typical workflow beforehand:

ssh into remote server
Backup test server database by some means
1. Ex: mysqldump, drush sql-dump, backup/migrate, locate a recent deploy-site backup
Copy and restore database backup in step #2 to local environment
Run a set of commands, a bash script, or some such to do one, or any of the following:
1. clear cache
2. sanitize user logins/emails
3. disable modules specific to test server environment
4. enable modules specific to, or aiding, your local development environment
Begin feature development

After

Using drush aliases and the the sync enable command, we can simplify the process to one step:

$ drush sql-sync @mysite.mysite-test @mysite.mysite-local -yAdding PDO SSL Support to Drush

Before creating the drush aliases, we need a drush patch to add SSL support to PDO when syncing with remote databases. This is a requirement for Classic developers, as drush does not yet support passing optional driver_options to a PDO constructor. See this related issue for more background.

Check out or install the latest drush. I've applied the patch from step #2 against drush-7.x-5.x (5.0-dev).
Get the drush PDO SSL patch:

cd <drush install directory> wget http://drupal.org/files/drush-pdo-ssl-2009292-2.patch
Apply drush PDO SSL patch:

git apply --index drush-pdo-ssl-2009292-2.patch
Copy SSL certificates from remote classic test servers, or use your own self-signed certificates. I’ve placed mine in a SSL directory in my home folder, similar to our test server’s location:

$ scp <user>@<mysite>:/etc/ssl/mysql/ca.crt /Users/<user>/SSL/mysql/<mysite>/mysql $ scp <user>@<mysite>:/etc/ssl/mysql/<mysite>.crt /Users/<user>/SSL/mysql/<mysite>/mysql $ scp <user>@<mysite>:/etc/ssl/mysql/<mysite>.key /Users/<user>/SSL/mysql/<mysite>/mysql
Next, add and install the sync_enable.drush.inc drush command, included with drush. This adds options to sql-sync to enable and disable modules after sql-sync commands complete. Copy sync_enable command to your drush install.

$ cp <drush install directory>/examples/sync_enable.drush.inc to ~/.drush/commands
Create file to store your drush aliases. I've chosen to use the alias group prefixes where aliases are stored together in a single file. This is well documented in <drush install directory>/examples/example.aliases.drushrc.php.

$ cd ~/.drush/ $ touch <mysite>.aliases.drushrc.php
Edit the following template for your own drush aliases to suit your local environment.

<?php # ***** # sample drush site alias # # usage examples (assumed filename: <mysite>.aliases.drushrc.php): #   drush sql-sync @mysite.mysite-test @mysite.mysite-local -y #   drush rsync @mysite.mysite-test:%files/ @mysite.mysite-local:%files -y # ***** $aliases['<mysite>-test'] = array(   'uri' => '<mysite>-test.example.com',   'root' => '/var/www',   'db-url' => 'mysql://<user>:<password>@mysql.example.com/<mysite>_test',   'remote-port' => '3306',   'remote-host' => '<mysite>-test.example.com',   'remote-user' => '<user>',   'pdo' => array (     PDO::MYSQL_ATTR_SSL_CA => '/Users/<user>/SSL/mysql/<mysite>-test.example.com/mysql/ca.crt',     PDO::MYSQL_ATTR_SSL_CERT => '/Users/<user>/SSL/mysql/<mysite>-test.example.com/mysql/<mysite>-test.example.com.crt',     PDO::MYSQL_ATTR_SSL_KEY => '/Users/<user>/SSL/mysql/<mysite>-test.example.com/mysql/<mysite>-test.example.com.key',   ),   'command-specific' => array (     'sql-sync' => array (       'no-cache' => TRUE,       'sanitize' => TRUE,     ),   ),   // prevent upstream syncs   'target-command-specific' => array (     'sql-sync' => array (       'simulate' => TRUE,     ),     'rsync' => array (       'simulate' => TRUE,     ),   ),   'path-aliases' => array(     '%dump-dir' => '/home/users/<user>/tmp',   ), ); $aliases['<mysite>-local'] = array(   'uri' => '<mysite>.localhost',   'root' => '/Users/<user>/Sites/<mysite>.localhost/httpdocs',   'path-aliases' => array (     '%files' => '/Users/<user>/Sites/<mysite>.localhost/private',     '%dump-dir' => '/tmp',   ),   'target-command-specific' => array (     'sql-sync' => array (       'no-cache' => TRUE,       'sanitize' => TRUE,       'confirm-sanitizations' => FALSE,       'enable' => array('environment_indicator,devel,devel_generate,coder_review,reroute_email,xhprof'),       'disable' => array('autologout,hide_php_fatal_error,ldap_servers,memcache_admin,paranoia,password_policy'),     ),   ), );

Usage

To perform a database sync from your remote database to local:

$ drush sql-sync @mysite.mysite-test @mysite.mysite-local -y
To sync files from your remote server to local, run the following command. Note: this does not sync everything in private files. If you know why, please let me know in the comments below.

$ drush rsync @mysite.mysite-test:%files/ @mysite.mysite-local:%files -y
Other interesting options could be added to your aliases. For more options, see example.aliases.drushrc.php. For example:

/ * 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_filter', 'cache_menu', 'cache_page', 'history', 'sessions', 'watchdog'); / * List of tables to be omitted entirely from SQL dumps made by the 'sql-dump' * and 'sql-sync' commands when the "--skip-tables-key=common" option is * provided on the command line.  This is useful if your database contains * non-Drupal tables used by some other application or during a migration for * example.  You may add new tables to the existing array or add a new element. */ # $options['skip-tables']['common'] = array('migration_data1', 'migration_data2');