Drush Tip: Use sql-sync to Quickly and Easily Move a Database

If you’re using Drupal best practices, you probably maintain different copies of your website (e.g. some combination of production, staging, development, and local development environments). But what happens when your local copy needs to be synchronized with the latest content and configurations stored in the live production database?

If you’re not familiar with command line tools, the process can be super tedious. Typically it goes like:

1. Visit your production environment/website and login.
2. Navigate to the backup and migrate module page.
3. Export the database.
4. Visit your local development environment/website.
5. Navigate to the backup and migrate module page.
6. Import.

Not difficult, but if you have to do this even once a day, that time adds up. Before I knew this simple drush tip, I would spend as much as an hour doing a single sync because of the long waiting periods in between these steps for large databases.

It’s time to get that hour back!

Enter Drush’ sql-sync

Once you’ve created your drush aliases (this tip coming soon), you can run the following command:

drush sql-sync @server-alias.prod @server-alias.local --create-db

In a nutshell here’s what happens. 1. Drush tells the production server to dump a copy of the database in a temp folder on the production server. 2. That database is then secured copied to the local server. 3. Because we used the --create-db option, the current database is cleared out (by default, the import merges with the existing database, and that can be bad!). 4. The new, local copy of that database file is imported to the local environment.

That’s it! Sure, you can’t avoid the time it takes for the database dump, transfer, and import. However, that is all happening automatically from that one command, so you can go back to doing other things if the database is large and/or the connection speed is slow.

Additional Possibilities

This example was for just one direction and for one point to point transfer. You can also push in the other direction (NOT RECOMMENDED) as well as pull from a staging or dev environment to get specific changes. You could also chain them together at the same time to pull a copy of the production server to the staging, dev, and local dev environments all at once.

Now THAT is powerful stuff!

Additional Information

To effectively use this tip, you need to have some other configurations in your drush alias file located in ~/.drush. Here is an example excerpt from ~/drush/sitename.aliases.drushrc.php

$aliases['dev'] = array(
  'parent' => '@parent',
  'site' => 'sitename.com',
  'env' => 'dev',
  'root' => '/var/www/sitename.com/public_html',
  'remote-host' => 'remotehost.com',
  'remote-user' => 'username',
);

More on drush aliases and alias files will be included in a future tip!

Youtube Version