Detritus

Archive for June, 2009

I guess I’ll have to admit it. I’m a little bit lazy. I really hate doing backups… almost as much as I hate not having them when I need it.

Looking for the easiest way to backup a number of mysql servers that required absolutely no intervention, I decided to leverage the ample email storage I have available to me and the power of cron. A simple bash script makes the magic happen.

First I needed to get the data out of mySQL. Might as well get the date and time for file naming while were at it.

#!/bin/sh
NOW=`date +%d%m%y.%H%M%S`
mysqldump --all-databases --opt >/tmp/backup.sql

Then, I wanted to compress it down to a manageable size. Since it’s all text in this case, the compression ratio is pretty good.

tar -zcvf Server156_Backup-$NOW.tgz /tmp/backup.sql

So, now there’s the issue of having all this data sitting unprotected. So, I added gpg encryption. Of course, I want to be able to restore this data from any PC and not have to worry about public and private keys so I opted for symmetric encryption.

I opted to have the passphrase reside on the server. This relies heavily on your belief that the server is secure. Other options would include using a pattern that would allow you to decrypt the file at a later date.

gpg  --passphrase-file /root/.passphrase --batch  --symmetric /tmp/Server156_Backup-$NOW.tgz

Then there was the issue of actually mailing it.The mailing is handled by a simple perl script with the file name passed as an argument.

perl /root/mail_backup.pl /tmp/Server156_Backup-$NOW.tgz.gpg

and the perl script

#!/usr/bin/perl -w
use MIME::Lite;
my $FN = $ARGV[0];
$message = MIME::Lite->new(
  From    => 'me@myemail.com',
  To      => 'me@myemail.com',
  Subject => "MySQL backup -Server156",
  Type    => "text/plain",
  Data    => "Backup Job Attached.");
$message->attach(Type=>'application/x-tar',
             Path =>"/tmp/".$FN,
             Filename =>$FN);
$message->send;

and cleaning up the directory.

rm /tmp/*.tgz
rm /tmp/*.sql

In this instance, I am using a local mailserver so that the email never leaves the network. The servers are hardened and are protected by thier own firewall and our internet facing firewall. All non-essential services are disabled. If this were not the case, I would certainly opt for a creative manner for establishing the gpg passphrase.

Overall this is an easy way to make sure you get backups of your databases regularly. Depending on the rate of data change I will schedule the script to run every 2-4 hours.

The complete shell script:

#!/bin/sh
NOW=`date +%d%m%y.%H%M%S`
mysqldump --all-databases --opt >/tmp/backup.sql
tar -zcvf Server156_Backup-$NOW.tgz /tmp/backup.sql
gpg  --passphrase-file /root/.passphrase --batch  --symmetric /tmp/Server156_Backup-$NOW.tgz
perl /root/mail_backup.pl /tmp/Server156_Backup-$NOW.tgz.gpg
rm /tmp/*.tgz
rm /tmp/*.sql

Sometimes you get data in a list format and you need it in comma separated format. Sure, you could open up excel and then use the transpose function but you could also paste it into a simple script.

#!C;\perl\bin
use Win32::Clipboard;
my $clipboard = Win32::Clipboard();
my $out;
while (){
	my $tmp = $_;
	last if ($_ =~ /^\s*$/);
	chomp($tmp);
	$out = $out . $tmp . ", ";
}
$return = substr($out, 0,-2);
$clipboard->Set($return);
print $return."\nCopied to Clipboard";

Simply paste your list into the command prompt and the values are copied to your clipboard in CSV format. You could easily expand this simple script to enclose values in qoutes or whatever you may need.