Faster SQL Syncs with Command-line Magic

Sometimes, we've got to stop and examine why we do things the way we've always done them. One of the most mundane parts of working with Drupal every day is syncing Drupal databases with Drush. At this point, I think my fingers just write drush sql-sync on muscle memory. There is a faster way though:

$ drush sqlc < <(ssh -T [user@]<remotehost> 'mysqldump <dbname> | gzip -' | gunzip -)

The command above is probably the fastest way to sync two Drupal databases between two machines. Given a small enough database, Drush might even be your biggest bottleneck. It's certainly a lot faster than what we might typically be doing — like, drush sql-sync, or worse, ssh'ing into a server, dumping the database, compressing it, exiting, rsync/scp'ing the database down to our locals, gunzip'ing the compressed database, and finally importing that database with Drush again.

It's a complex Drush command though, so let's break it down.

ssh -T [user@]<remotehost>

We know what most of that is, we're just connecting to a remote server over SSH. The -T might be new — it disables starting a terminal session, meaning we're just going to run one command on the remote, then exit. Here is that one command (in single quotes):

'mysqldump <dbname> | gzip -'

We use mysqldump to dump the db in question. Then, we pipe that to gzip -, which compresses the data and spits it back out on stdout rather than writing it to a file. Note: You'll need a .my.cnf file on the remote to avoid passing a username and password in on the command line.

 | gunzip -

Here, we take that compressed stream from the one-off command and pipe it to gunzip -. This part is run locally since it falls outside the single quotes from the line above. This reverses the compression and spits this back out to stdout. We could leave the gzip/gunzip parts out, but since we're sending this over the network, it's probably faster to compress the data while it travels over the wire.

<( ... )

This is probably the most foreign thing in the command. It's called process substitution. Basically, you're creating a temporary file from the output of the command inside of the parentheses.

drush sqlc < ...

This one is more easily recognizable. We're connecting to the local MySQL database with Drush, then we're reading a file into MySQL with <.

Put all this together, and we've probably got the fastest way to stream one database into another. This works even better for syncing between two remotes. You'll never need to pull a copy down locally and push it back up to the other remote, which doubles the time you spend doing nothing but waiting for a download/upload to finish.

It's easy to forget to examine the little things that we do every day or even just ask why we do them. Often, it's just muscle memory or habit. I'd love to hear about some other tricks you've found or other slow commands that you've worked around. Better yet, I'd like to challenge you to beat my command above and make it even faster.