Drush command for copying and importing source database to target database. Transferring via pipes whenever possible. Optional progress indication support via PipeViewer.
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
-y options as this also applies to this prompt.
drush help sql-sync-pipe
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 .
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
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 :)
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).
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
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
- For UNIX:
yum install pvor
apt-get install pv
- For Mac:
port install pvor
brew install pv
- Maintenance status: Actively maintained
- Development status: Under active development
- Reported installs: 1 site currently reports using this module. View usage statistics.
- Downloads: 9,991
- Last modified: January 27, 2017
- Stable releases for this project are covered by the security advisory policy.
Look for the shield icon below.