ScriptBits, Little Bits Of Code You Cannot Live Without

I won't go thru how to set up LVM, or even really how to do run a snapshot. If you're interested in scripting your snapshots for backup purporses, it is assumed you already have LVM set up correctly, and can take a snapshot. So...let's get right to the ScriptBit and automate this thing....

The following script requires 20G free for a snapshot. If you don't need/have that much, simply change the @20G to whatever fits your needs. It also assumes you are running mysql. If you aren't, comment that stuff out to. If you are, please note that the tables are LOCKED. My 150G db takes about 30 seconds to snap after the lock is required. If you cannot live with that, please don't run it. So far I've yet to see a snapshot without locking the tables be of any use. Lastly, it appends the slave status to a file so you may use it to restore a slave. Here it is:

Name this /root/crons/mysnap_lvm

#!/usr/bin/perl

use DBIx::DWIW;

my $db_user = 'dbuser';
my $db_pass = 'dbpassword';
my $db_host = 'localhost';
my $db_name = 'mysql';
my $conn =  DBIx::DWIW->Connect(DB=>$db_name, User=>$db_user, Pass=>$db_pass, Host=>$db_host);
my $lvmbinpath = '/usr/sbin';
my $masterstatus = '';

printf "%-12s %s \n", "removing", scalar localtime time;
`$lvmbinpath/lvremove -f /dev/VolGroup00/dbbackup.1`;

printf "%-12s %s \n", "stopping slave", scalar localtime time;
system ("mysql -uroot -p$db_pass -e 'slave stop'");
sleep 10;

printf "%-12s %s \n", "flushing", scalar localtime time;
$conn->Execute("FLUSH TABLES");
$conn->Execute("FLUSH TABLES WITH READ LOCK");
$conn->Execute("FLUSH LOGS");

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);

printf "%-12s %s \n", "snapping", scalar localtime time;

#   2. From another shell, create snapshot.
`$lvmbinpath/lvcreate -L20G -s -n dbbackup.1 /dev/VolGroup00/LogVol00`;

system ("mysql -uroot -p$db_pass -e 'show slave status\\G' >> master.status.latest");
system ("mysql -uroot -p$db_pass -e 'show master status\\G' >> master.status.latest");

printf "%-12s %s \n", "Master Status", scalar localtime time;
system ("tail -n 34 master.status.latest");

printf "%-12s %s \n", "unlocking", scalar localtime time;

#   3. From the first client, execute UNLOCK TABLES.
$conn->Execute("UNLOCK TABLES");
system ("mysql -uroot -p$db_pass -e 'slave start'");

#   4. Show current snaphots

printf "%-12s %s \n", "LVM Status", scalar localtime time;
system ("$lvmbinpath/lvscan");

printf "%-12s %s \n", "done", scalar localtime time;
$conn->Disconnect;

Now, lets be really cool and use a little wrapper to cron this job, email us the results, and log it.

Call this guy /root/crons/mycronwrapper.sh

#!/bin/bash

# wrapper to launch mysql backup job and report status.
to="someguy@somewhere.com"
dir="/root/crons"
cmd="/root/crons/mysnap_lvm"
status="FAILURE"
subject="`hostname -s` Hour:`date +%H` MySQL Snapshot"
log="$dir/mysqlsnapshot-`date +%Y%m%d%H`.log"
cd $dir

# do command
echo $cmd > $log
$cmd >> $log
cmdrtn=$?

# check log
grep -vq inactive $log
logrtn=$?

if [ "$cmdrtn" -eq "0" ] && [ "$logrtn" -eq "0" ]; then
  status="SUCCESS"
fi

cat $log |mail -s "$subject $status" $to

Cron it and you're all set:

cd /etc/cron.daily
ln -s /root/crons/mycronwrapper.sh mycronwrapper

You're done!

Tags: mysql backup, mysql disaster recovery, mysql lvm snapshot, mysql snapshot

This entry was posted on Thursday, July 9th, 2009 at 08:29 and is filed under Linux, MySQL, Shell Scripts. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.