Drush command for copying and importing source database to target database. Transfers via a gzipped pipe and the SSH tunnel.
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. There is actually no compression going on whatsoever. This normally would probably
be fine if you have a small site. But lets say that you have a database that's huge, 1GB+ huge. This is a very
inefficient method of transportation.
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). It
should also be mentioned that even while, yes, the time it takes to sync a database improves, the real
value of this command lies in the reduced bandwidth consumption (transfer size) between the source and destination
using gzip.
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: 1.05GiB (uncompressed)
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.
NOTE: The total time elapsed includes the intial time it takes for the mysqldump to initialize the database for
transfer.
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
If you need to save the dump or simply wish to give an ETA estimate when using the --progress option, specify the
--dump option. It is similiar 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 uncompress the dump using gunzip and then pipe it to mysql for import.
NOTE: If the destination alias has a specificed 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.
NOTE: The total time elapsed includes the intial time it takes for the mysqldump to initialize the database for
transfer.
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
If you have 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 command (see link for other options):
- For UNIX:
yum install pvorapt-get install pv - For Mac:
port install pvorbrew install pv
Copy and import source database to target database. Transfers as a gzipped pipe via SSH.
Arguments:
source Name of the site-alias to use as a source.
destination Name of the site-alias to use as the destination.
Options:
--dump Dump to a local file, this will help determine an ETA if the
using the progress option.
--progress If the Pipe Viewer command is installed, show the progress of
the sync. See: http://www.ivarch.com/programs/pv.shtml. Common
installs: UNIX - "yum install pv" or "apt-get install pv",
MacPorts - "port install pv", HomeBrew - "brew install pv". See
URL for a complete list of installation instructions.
--sanitize Obscure email addresses and reset passwords in the user table
post-sync. Optional.
--sanitize-password The password to assign to all accounts in the sanitization
operation, or "no" to keep passwords unchanged. Default is
"password".
--sanitize-email The pattern for test email addresses in the sanitization
operation, or "no" to keep email addresses unchanged. May
contain replacement patterns %uid, %mail or %name. Default is
"user+%uid@localhost".
--confirm-sanitizations Prompt yes/no after importing the database, but before running
the sanitizations
--ssh-options A string of extra options that will be passed to the ssh
command (e.g. "-p 100")
--temp Use a temporary file to hold dump files.
Aliases: ssp, pipe