Drush command for copying and importing source database to target database. Transferring via pipes whenever possible. Optional progress indication support via PipeViewer.

screenshot

Installation:

Drush 7 (use the 7.x-2.x branch/releases), 8 (necessary for Drupal 8):
drush dl drush_sql_sync_pipe --destination=$HOME/.drush && drush cc drush

Drush 4, 5, 6 (use the 7.x-1.x branch/releases):
drush dl drush_sql_sync_pipe-7.x-1.4 --destination=$HOME/.drush && drush cc drush

Automatically Check for Updates

As of 7.x-1.1, Drush SQL Sync Pipe will automatically check for an update when this command is used, limited to once per day. If an update is found, it will ask to download the latest version, install it and re-dispatch the same command with the options specified. Note: be cautious with using the --yes or -y options as this also applies to this prompt.

Documentation

drush help sql-sync-pipe

History

This Drush command is not a duplicate of the Drush core sql-sync command. It will not, nor should it, be incorporated into Drush core. Both Drush core sql-sync and this command have distinct use cases. This was already proposed, discussed and rejected in #1125560: Fileless sql-sync via pipes, not rsync.

Standard sql-sync requires that you save the database dump on the server, transfer it via rsync and then import it into your destination's database. This normally would probably be fine if you have a small site. But lets say that you have a database that is huge, 1GB+ huge. This is a very inefficient method of transportation.

Not only do I have to wait for the source to dump and transfer just the tiny bit that was changed, but I then have to wait again for the database to then import on the destination. Also, it dumps all the cache tables too, we normally don't need those. If you do, you can easily pass the --include-cache-tables option.

This command also allows alias files to specify the option of db-allows-remote so it connects directly to the database server instead of first trying to log into the source server via SSH. Finally something that will work with Pantheon sites with out having to install back-end commands in the repository :)

Benchmarks

Below are examples of syncing the same 1.05Gib database using the two different
methods. Keep in mind that these "benchmarks" are relative to the connection
speed of both the source (server) and destination (local machine).

drush sql-sync

The standard sql-sync command shipped with Drush.
NOTE: This benchmark simply measures the time elapsed from the start of the command to the end. There is no real way to measure the individual dump, transfer and import steps as they are done silently. In theory you could watch the debug -d output, if you wanted to... I didn't.

Command:            drush sql-sync @alias.dev @alias.sandbox --no-cache
Transfer size:      88.1MiB (compressed using rsync)
Import size:        1.05GiB
Total time elapsed: 46 minutes 47 seconds

drush sql-sync-pipe

This command pipes the entire syncing process. mysqldump is passed the following options to make the quickest and smallest dump as possible: -CceKq --single-transaction. It then pipes the dump via gzip, passes through the SSH tunnel, gunzip the pipe and then imports directly into the destination's database.

Command:                 drush sql-sync-pipe @alias.dev @alias.sandbox --progress
Transfer size:           88.1MiB (sent compressed using gzip)
Import size:             1.05GiB
Import & transfer time:  27 minutes 05 seconds
Total time elapsed:      30 minutes 35 seconds

drush sql-sync-pipe --dump

If, for whatever reason, you need or want to save the dump to the destination's HDD, specify the --dump option. It is similar to using sql-sync, however there are a few distinct differences. It will still pass the same options to mysqldump as above. Where it differs, is that it will gzip the dump and then pass it via the ssh tunnel so it saves to the destination's HDD (local machine) instead of the source's (server). After the dump has been saved, it will decompress the dump using gunzip and then pipe it to MySQL for import.

NOTE: If the destination alias has a specified dump file, that will be used and remain on the destination's HDD. If the destination does not have a dump file specified or the --temp option is used, a temporary file will be created and then removed afterwards.

Command:            drush sql-sync-pipe @alias.dev @alias.sandbox --progress --dump --temp
Transfer Size:      88.1MiB (compressed using gzip)
Transfer Time:      5 minutes 14 seconds (includes time taken to gunzip)
Import Size:        1.05GiB
Import Time:        29 minutes 23 seconds
Total Time Elapsed: 37 minutes 36 seconds

drush sql-sync-pipe --progress

If you have Pipe Viewer installed, you can pass the --progress option to show syncing progress between the source and destination. Due to how the default functionality of this command pipes the entire syncing process, an ETA will not be known and only generic information will be shown (such as data process and time elapsed). If you pass the --dump option, the initial dump transfer ETA will not be known, but the import process ETA will be shown.

Below are some common commands if you wish to install the Pipe Viewer command (see link for
other options):

  • For UNIX: yum install pv or apt-get install pv
  • For Mac: port install pv or brew install pv
Supporting organizations: 
Fixed sanitization feature, module maintenance

Project information

Downloads