a faster alternative to sql-sync

Where I work I probably load environments about 50 times a day. Testing bug fixes, data migrations, reproducing errors, failure analysis, and so on.

Even if I can save 30 seconds with an automated database reload process, it will add up.

There’s been work on improving drush sql-sync, including https://drupal.org/project/drush_sql_sync_pipe

The bottleneck is that drush sql-sync works with temporary files – meaning it has to:

Connect to the remote machine
Perform a sql-dump to a file on the remote machine and compress it
Transfer that file to your machine
Restores the dump to database

The problem with this is that each step is executed consecutively. It would be better if all these steps were performed concurrently. Drush defaults to this method because it is compatible with most systems. If you’re a power user though, you may want a find a faster solution.

What we’d like to do is

Connect to the remote machine
Perform these steps at the same time
1. Read the file remotely
2. Compress on the fly
3. Stream it to your local machine
4. Uncompress on the fly
5. Pipe sql to database

I wrote this little script that accomplishes just that and a little extra for dumping locally. The key is piping data instead of saving it temporarily. Note that this only works on Linux/Mac.

#!/bin/bash -x
drush -y sql-drop # this doesnt have an alias for a reason. only work locally
drush $1 sql-dump --gzip | gzip -cd | drush sqlc
drush -y updb
# Set last update date to now to prevent checking for updates for a bit
drush -y vset update_last_check `date +%s`
# Setting file paths (use default)
drush -y vdel file_directory_path
drush -y vdel file_public_path
# Clear cache
drush cc drush

#!/bin/bash -x

drush -y sql-drop # this doesnt have an alias for a reason. only work locally

drush $1 sql-dump --gzip | gzip -cd | drush sqlc

drush -y updb

# Set last update date to now to prevent checking for updates for a bit

drush -y vset update_last_check `date +%s`

# Setting file paths (use default)

drush -y vdel file_directory_path

drush -y vdel file_public_path

# Clear cache

drush cc drush

Put this script somewhere (maybe ~/bin) and chmod a+x it.

From within your site directory, run fastdump @someAlias

This will

Delete all the local tables (to ensure tables that don’t exist in your source are gone)
Restore the database from an alias
Run updates

But quickly! The next step for this would be making it into a Drush command instead of a shell script.