Bashing MySQL Dumps

JR

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

[shell]

#!/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

[/shell]

mysqlbackup.sh:

[shell]

#!/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

[/shell]

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:

[shell]

#!/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;

[/shell]