If you're not using Drush: you should use Drush. But sometimes you find yourself on a server where, for whatever reason, you can't: not all sysadmins give developers the access they might ideally prefer; even if you could download it, PHP CLI might be misconfigured; and even if you could run it, the site might perform so badly that Drush essentially freezes...!
Besides, it's handy to know how certain Drush commands work, so you can run them outside of Drupal altogether: for example, dumping a MySQL database so that it can be very rapidly dropped into a different database on a different server. The drush sql-dump command does this very well indeed, so how does it work? What options does it pass to mysqldump?
The precise behaviour of Drush commands is something of a moving target. However, in Drush 5 at least (yes, I know I'm behind the times) a var_dump() call or two in commands/sql/sql.drush.inc yields the following command line equivalent:
mysqldump --no-autocommit --single-transaction --opt -Q $DB --host=$H --user=$U --password=$P > filename.sql
These options do the following:
no-autocommit- On restore, commit entire tables into the database as a single transaction each, for faster INSERTs.
single-transaction- Ensure transactional tables (using e.g. InnoDB) are dumped consistently without blocking other applications.
opt- Shorthand for (deep breath):
--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. A set of options tailored to making easily restorable database dumps. These days, enabled by default; but older MySQL might be configured differently. Q- Put backtick quotes around database, table and column names, for whitespace etc. safety and easy manual identification. Again, usually enabled by default.
host,user,password- As you'd expect: replace the
$variables with your own values.
All handy options, and some of them occasionally tricky to remember. In addition, you can simulate the behaviour of the Drush --gzip option by piping the output into gzip instead if into the filename:
... --password=$P | gzip -c > filename.sql.gz
The pipe means you never have a fully unzipped file lying around on your filesystem: useful if you've got large databases but very little user disk space. (I rather prefer this to drush sql-dump --gzip, as that option can create a temporary unzipped file.)
