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
- 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:
- clear cache
- sanitize user logins/emails
- disable modules specific to test server environment
- 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 -y
Adding 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');