Mac/Linux – Backing up MySQL Databases

For similar reasons as to why I created drupal_db_dump for dumping Drupal databases; I wrote a wrapper script for the mysqldump command. One difference however, is that you can pass in options to the mysqldump command, which allows for greater utility. You do this by appending — to the end of the script and then specifying any valid mysqldump flags.

Example:

./msdw -d database1,database2,database3 -p /path/to/store/dumps -a — -u mysql_user –password=itsasecret –single-transaction

The only mysqldump flags you shouldn’t specify are ones relating to which databases to dump (ex: –all-databases). The database(s) you wish to dump must be the argument to -d flag and if you specify multiple databases, they must be separated by commas with no spaces in between. As with drupal_db_dump, specifying -a will auto-purge all but the last dump for the previous month when a new month begins. But again, by design, it will do this only if the backups span a maximum of 2 months.

For anyone wondering, msdw is an abbreviation for mysqldump wrapper. The guy that usually names my scripts got fired for being too verbose. drupab_db_dump, really? Terrible.

Linux/Mac – Backing up Drupal Databases

I manage several servers that are hosts to a variety of Drupal 6 & 7 sites, and I needed a script to intelligently dump each site’s database. Enter drupal_db_dump.  A  script I wrote in Ruby that uses drush (command line tool to manage Drupal) to do exactly just that. This script is best run via a cron job and takes two or three parameters: a Drupal sites directory, a directory where you’d like the dumps to be stored, and an optional archive toggle. Here’s what it will do:

  1. Go into every site in the specified sites directory and dump each database to a dated folder inside the dump directory (organized by site).
  2. Create an md5 checksum of the dump and write it to a file inside the dated folder.
  3. Compress the dated folder, and remove the original.

I also included an optional archiving functionality. If specified (-a), it will purge all but the last backup for the previous month when a new month begins. The last backup is moved to a folder called monthly_archives. By default, this functionality is turned off.

Benefits of use:

  1. Eliminates the need to configure a separate backup for each site,  as it will traverse all sites in the given directory.
  2. Quicker to setup, as you do not need to configure a regular and MySQL user. Drush will use the information in the site’s settings.php file to dump the database.
  3. Checksums!

Caveats:

  1. If the backups for a site span more than two months, this script will not attempt to archive them. This is by design. You will need to manually move or remove the backups until they only span one or two months.