Bashing MySQL Dumps

A quick set of batch scripts I wrote up (two of the three scripts, anyway) for dumping all of my (specified) MySQL databases into an archive for backup. The log mailing (emailsql.pl)requires Perl and the MIME:Lite module to correctly function. I’ve also utilized a wrapper script so the log outputs to a separate file [to be mailed]. There’s also a ‘dummy’ log file I use in my crontab file, though this isn’t really necessary:

mysqlbackupwrapper.sh

#!/bin/bash
#Wrapper script used to call the primary backup script and output to a specified file
sh /home/administrator/scripts/mysqlbackups > /home/administrator/scripts/sql.backup.log 2>&1

mysqlbackup.sh:

#!/bin/sh
#Timestamp for your logs:
date

#Dump the databases - Make sure to specify your root or user password following the -p switch:
mysqldump -uroot -p --opt intraforum > /home/administrator/scripts/sqldata/intra_apdforum.sql
mysqldump -uroot -p --opt joomla_intranet > /home/administrator/scripts/sqldata/intra_intranet.sql
mysqldump -uroot -p --opt mysql > /home/administrator/scripts/sqldata/intra_mysql.sql
tar -zcvf /home/administrator/scripts/sqldata.tgz /home/administrator/scripts/sqldata/*.sql
echo
echo "Backup completed successfully for: "
echo
echo "MySQL - PHPbb3 Forum"
echo "MySQL - Joomla 1.0.X Intranet"
echo "MySQL - Intranet MySQL Tables"
echo
echo "Copying to SERVER.yourdomain.local . . . ."
echo

#Use SCP to transfer to file so you can verify successful backups -- Make sure to use identity/keys for SCP instead of a password:
scp -v -i /home/administrator/identity /home/administrator/scripts/sqldata.tgz administrator@this.host:/backup/sqldata_backup.tgz

perl /home/administrator/scripts/emailsql.pl

emailsql.pl (I did not write this one):

Instead of just sending the text of the log file, this script attaches the file and sends the message:

#!/usr/bin/perl -w
use MIME::Lite;

$msg = MIME::Lite->new(
From    => 'Backup Log',
To      => 'liveaverage@yourdomain.org',
Subject => 'MySQL Data Backup - Intranets',
Type    => 'text/plain',
Data    => "See the attached log for details on the most recent MySQL Database Dumps.");

$msg->attach(
Type       =>'text/plain',
Path       =>'/home/administrator/scripts/sql.backup.log',
Filename   =>'sql.backup.log',
Disposition        =>'attachment');

$msg->send;