Skip to Content

Making sql-sync Safer

Drush aliases can be a huge timesaver when you’re working back-and-forth between your local dev environment and a remote server. You can clear caches remotely as easily as adding @remote to your usual drush cc all command. Or use the sql-cli command to log into the remote server’s MySQL instance without leaving the comfort of your local command prompt. My favorite is updating my local database to what’s on the remote server: drush sql-sync @remote @local (followed by a coffee break).

We've all accidentally swapped parameters on the command line, usually after too much work and too little sleep. Not a big deal when you swap source and destination for ln –s, it politely warns you that it can’t overwrite an existing file. But do that with sql-sync and you’ve just replaced your live server’s database with whatever test content you’ve been tooling around with! It’s good practice to test your daily backups from time to time, but not while your live server is showing nothing but Bacon Ipsum. Let’s save us the heart attack and data loss by preventing this situation in the first place.

One option is to never specify the destination using a site alias: use drush sql-sync @remote example.com to replace the database pointed to by sites/example.com/settings.php. Removing the @ in the destination prevents you from ever blowing away any database other than your local one. You’ll need to run this from within the Drupal install of the site you’re updating and you’ll need to get everyone working on the project to do it. But it’s a good habit to get into.

Option two solves the problem is a more systemic way. By implementing drush_hook_COMMAND_validate, we can add extra validation code to any Drush command. Open ~/.drush/policy.drush.inc and add the following code:

<?php
/**
 * Implements drush_hook_COMMAND_validate.
 *
 * Prevent a catastrophic brain-fart -- only allow users to overwrite their
 * local database.
 */
function drush_policy_sql_sync_validate($source = NULL, $dest = NULL) {
  if ($dest == '@local' || FALSE === strpos($dest, '@')) {
    return TRUE;
  }
  else {
    return drush_set_error(dt('You may only overwrite your local database -- check your source/destination order. It should be similar to "drush sql-sync @remote @local".'));
  }
}

This is a slightly more restrictive version of what ships in the Drush example policy file. This one disallows any site alias in the destination parameter except @local under the assumption that any you would never want to overwrite a remote database. If you use sql-sync to move databases from staging to production, you may need to add an override that will allow this.

Safe now, we can happily sql-sync databases late into the night.

Thanks for sharing

Great tip. I've turned it around; I only warn when the destination is production, as we sometimes want to copy local to the test environment. <?php /** * Implements drush_hook_COMMAND_validate. * * Prevent a catastrophic brain-fart -- only allow users to overwrite their * local database. */ function drush_policy_sql_sync_validate($source = NULL, $dest = NULL) { if (strpos($dest, '.prod')) { return drush_set_error(dt('You cannot overwrite the production database -- check your source/destination order. It should be similar to "drush sql-sync @remote @local".')); } }

Post new comment

The content of this field is kept private and will not be shown publicly.