Automating MySQL Backups

This script has been heavily overhauled in the revised version (5/15/2017). Left here for history.

I recently began having a lot of trouble with my hosting company — site going down, database server not running for days on end, shell access disappearing, unanswered support emails, the works. So I switched to a new hosting company. One thing I used a lot was shell scripting and crons.

One new wrinkle was that the new host had every MySQL database on their own virtual server. I had been using phpMyBackupPro for backups, but it can't handle multiple servers. I decided to roll my own solution. Turns out, it took about an hour.

My requirements:

• Runs with a shell script in any/most Linux distros and Mac
• Extracts databases from multiple servers or users
• Compresses the text to conserve disk space
• Emails me the backups for safe-keeping
• Runs from cron so it won't miss

The database dump using mysqldump works fine. For compression without creating intermediate files, use a named pipe:

# Create a pipe named 'pipe'
mkfifo pipe

# Set the input/output
gzip -9 < pipe > ~/db_backups/database.sql.gz &

# Run the actual dump to the pipe
mysqldump -h hostname -u username -ppassword -q \
  --add-drop-table --extended-insert database > pipe

# Get the return code of mysqldump
result=$?

# Wait until gzip completes
wait

# Remove the pipe
rm pipe

For multiple servers, repeat the gzip and mysqldump lines for each server.

To email the backups, use PHPMailer. Save as send_backups.php:

<?php
$to_address   = 'your_name@gmail.com';
$from_address = 'backups@your_domain.com';
$subject      = 'MySQL Backup';
$body         = 'Database backup files attached.';

require_once('class.phpmailer.php');

date_default_timezone_set('America/Denver');
define('DS', DIRECTORY_SEPARATOR);
$path = dirname(__FILE__);

$mail = new PHPMailer();
$mail->From     = $from_address;
$mail->FromName = 'Backup Agent';
$mail->Subject  = $subject . ' - ' . date('Y-m-d H:i:s');
$mail->Body     = $body;
$mail->AddAddress($to_address, 'Backup Recipient');

foreach (glob('*.gz') as $filename) {
    $mail->AddAttachment($path . DS . $filename);
}

$mail->Send();

The crontab entry — backup every two hours, email once a day:

50 */2 * * * ~/db_backups/database_backup.sh
00 23 * * * /usr/local/bin/php5 ~/db_backups/send_backups.php

Update 8/5/2013: This script ran flawlessly, unattended, since installation in 2008.