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 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,
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
'mysqldump <dbname> | gzip -'
| gunzip -
Here, we take that compressed stream from the one-off command and pipe it to
<( ... )
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.