Optimize your Drupal database dumps

In order to backup a Drupal site you need to take care of both code and database. The first problem is usually solved by placing the Drupal directory trees under Revision Control. The second requires a more careful analysis, and this post describes optimal stategies for MySQL.

Before you start: common caveats

Use a lock file

While the backup of files is usually more predictable, the database dumps can take more time than you expect: il will depend on site traffic and activity too; the usual good practice of using a lock file is a must in this case.

Keep your database clean

Drupal has known problems in clearing some cache tables, that are left unaffected by the normal cache clearing in Drupal or Drush. If you extensively use AJAX in forms, your cache tables can very quickly grow to hundreds of MBytes. Solve this by clearing expired cache elements manually; for example, with Drush, just run

drush sql-query "DELETE FROM cache_form WHERE expire < UNIX_TIMESTAMP(NOW())"

in your site directory tree to clean the DB.

What do you need the database dump for?

The best database backup stategy depends on how you actually expect to use the dump.

Use case 1: Verbatim copy of the database

This is not what you want ordinarily. Even if you do a good job of keeping your cache clear and deal manually with the tables that Drupal doesn't clear, the database will still contain a lot of information that is useful only for forensic-like examinations, like session information and watchdog messages.

If this is what you want, meet mysqlhotcopy, the most efficient way to perform a verbatim dump.

Examples (assuming that everything happens on localhost, that your database username is drupal, password is xyz, database name is dbname):

mysqlhotcopy --user=drupal --password=xyz dbname

will (re)create a database named dbname_copy and populate it with an exact copy of the dbname database. To restore the site database exactly like it was before the dump you merely need to edit settings.php and replace dbname with dbname_copy.

mysqlhotcopy --user=drupal --password=xyz dbname directory

copies the full database contents into the specified directory. This way you can store, by changing directory name, an arbitrary number of backups. Backups are restored by copying the files back to the MySQL database directory with cp -r or rsync -a (recommended, especially for remote use, since it will compress data and it won't replace a file with an identical copy, thus giving the same result in much less time).

Main problems with mysqlhotcopy:

• You need suitable privileges on the filesystem: not applicable on shared hosting most of the times.
• The dump is not human-readable: you won't be able to easily compare two dumps.

Use case 2: Copying the meaningful tables from the database

If your backup is more for safety than for archival, then you will probably want to be more careful in selecting what to dump and how to dump it. In most cases the best solution is a selective SQL dump.

Investigate at least the following:

• Drush (namely, drush sql-dump) can be enough for you. You can tell to only dump meaningful tables with the --structure-tables-key=structure-tables option (to use it, rename the file example.drushrc.php to drushrc.php), and you can enable slower but more usefully formatted dumps (one line per insert, to make the comparison of different dumps much easier) with --ordered-dump. A typical dump command would thus look like

drush sql-dump --structure-tables-key=structure-tables --ordered-dump --result-file=dumpfile.sql

• Backup and migrate is an ordinary Drupal module, with very flexible backup options and scheduling. All of this is configured and used from within Drupal, so no command line hacking needed. Depending on your infrastructure, Backup and migrate will take advantage of existing server capabilities like FTP transfers and backup encryption.

Main problems with this approach:

• You still need command-line access for Drush and enough flexibility for Backup and Migrate; though, you will be able to use Backup and Migrate on most shared hosting solutions.
• The dump is much more computationally expensive than the "raw" dump of use case 1: you will pay for the tuning possibilities and the readable SQL output with significantly longer execution times.

Use case 3: Sharing a database with other sites or developers

Once upon a time, database dumps were the only way to transfer a site, complete with full configuration, to other developers or to develop a feature on a staging site a migrating it to production.

Fortunately, this has changed in recent times: the Features module allows to export most configuration in code and enable it on a new site as easily as enabling an ordinary module. Take the time to investigate Features, or simpler dedicated modules like the built-in export functionalities in CCK for content type definitions, or Drush Views to export and import views from database to code and vice versa; don't rely on database dumps if at all possible.

If you do want to share a database for this purpose, take a look at dbscripts; configuration is a bit tedious, but it will allow you to define what you dump in different scenarios and how to merge the dumped database with the "production" one.

This article is released under the Creative Commons Attribution Noncommercial No Derivative Works 3.0 license.

Tags: AdministrationDrupal Planet