sites.php + drush: A cautionary tale.

The worst kind of error is one that doesn't look like an error.

You may recall that I manage several Drupal multi-site installations and use Drush to move them through development to production. I thought I'd document a drush sql-sync gotcha that could cause a lot of damage, and hopefully save others from doing the same.


The preferred method of setting up multi-sites in Drupal is to create a new folder under sites with the same name as the (sub)domain name of your new site. For example; if your site is the folder should be sites/ (or sites/

So for development, or staging servers, you would set up as, or example.stg, then rename the directory when it gets promoted to live, right..?


Well, not exactly.


Yes, you would point your apache site file (or other web-server) to the relevant .dev/.stg/.com URL, but the directory should be sites/ or whatever tld extension your site uses) on every server.

Enter the sites.php file...


Drupal 7 introduced a sites.php file (in the sites folder) which transposes the site URL to the appropriate Drupal site folder.


For the example, the sites.php file on your development server would contain an entry something like:

$sites[''] = '';

This means that the site folder can be migrated unchanged between stages – which allows you to use Drush, or Git, or some other tool to do the migration.


But beware! If using drush sql-sync to migrate the database, make sure the sites.php file is present and correct, otherwise you might not be copying the database you think...


In my case, a small typo in my sites.php file, resulted in the target site's database being replaced by that of the default site. Unfortunately, because drush did not produce an error message, everything appeared to go OK.

Obviously, as soon as I browsed to the site I could see that something was wrong. Nevertheless, there was quite a bit of head-scratching before the penny dropped... Fortunately, I had a recent backup.


You can confirm that the sites.php file is in order (for the current target) by using the command:

drush -s sql-sync -v @example.stg

The -s (or --simulate) flag follows the drush command, while the -v (--verbose) follows the sql-sync command – otherwise the verbose output will be mostly about drush site-aliases. We want the info about the sql-sync part of the process.


Here is an example of the output from this command:


drush sql-sync output

You can see that there is a system call to mysqldump (1) – two calls, actually – which writes to a temporary file (2). Then there is a call to mysql (3), which reads the temporary file produced by the first part of the process into the specified database (4). Make sure this is the correct database! If the entry in sites.php is incorrect or missing, then you might end up overwriting another site's database.


If everything here looks OK, then you're good to go. Just run the command again without the -s flag.


Anyway, this post has run a bit longer than I planned, but I hope it is useful to somebody.




Edit - 2 more things:

  1. Make sure the URI in your alias file matches the 'name' part of the URI in the 'sites' array.
  2. You can also easily check drush is using the correct database with the 'status' command. e.g.
    drush status