I finally cobbled together an incredibly ugly but functional script for recovering or setting up a slave. The pure hideousness stems from the brute force, lack of error checking, cram that data down the db’s throat method that I am leveraging. See, I know just enough to get the job done but not nearly enough to do it with any elegance, flair, or care and concern for stability. Running with scissors, at night, with a blindfold, through a roomful of children’s toys and cats is my style.

Anyways, here we go…

This script is executed on the slave instance and will fetch the most recent copy of the db from the master, stop the slave, drop the db, recreate the db, read in the backup, issue the change master command, start the slave, and then display the slave status after a minute.

#!/bin/bash
# Recover slave post crash

# run backup from master
# transfer it to the slave
echo "Getting backup, this may take a while."
ssh master "/scripts/slave_recovery.sh WHATSLAVE"

echo

# untar backup
echo "Expanding backup and getting ready to import."
cd /mnt/tmp/recovery
recover=$(ls | grep yourdb)
tar -xf $recover

# set variables
recodir=${recover:0:21}
mastfle=$(ls $recodir/ | grep master)
fullbin=$(cat $recodir/$mastfle | grep A.)
binlog=${fullbin:2}
fullpos=$(cat $recodir/$mastfle | grep B.)
positn=${fullpos:2}

echo "Here's what I have..."
echo $recodir
echo $recover
echo $mastfle
echo $binlog
echo $positn

# stop slave
echo "Stopping slave..."
mysql -e "slave stop;"

# drop database
echo "Dropping the database..."
mysql -e "drop database yourdb;"

# recreate database
echo "Recreating the database..."
mysql -e "create database yourdb;"

# source database from backup
echo "Importing the database..."
mysql yourdb < $recodir/$recodir.sql

# issue change master command
echo "Issuing the change master command..."
mysql -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='USERNAME', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='$binlog', MASTER_LOG_POS=$positn;"

# start slave
echo "I am starting the slave..."
mysql -e "slave start"

# clean up
rm -r *yourdb*

# check status
echo "I'm waiting one minute the checking the status of the slave..."
sleep 1m
mysql -e "show slave status \G;"

echo
echo "I am all done."

Now, you might have noticed that on the seventh line I call another script on the master and you might have noticed a variable trailing it. WHATSLAVE is whatever you called your slaves in the host file on the master in my unimaginative case it is slavea and slaveb but you could have Tom, Dick, and Harry, or the names of your favorite Hostess snackcake characters.

#! /bin/bash
# This script runs on the master and is built off the backup script

# set date variables
DAYNOW=$(date +%j)
TIMENOW=$(date +%H%M)

# grab info about the binlog and position of the database

status1=$(mysql -e 'show master status \G' | grep mysql)
status2=$(mysql -e 'show master status \G' | grep Position)
sql=${status1:18}
posit=${status2:18}

mkdir /mnt/tmp/backup/slave-yourdb-$DAYNOW-$TIMENOW

echo A.$sql >> /mnt/tmp/backup/slave-yourdb-$DAYNOW-$TIMENOW/master-$DAYNOW-$TIMENOW.txt
echo B.$posit >> /mnt/tmp/backup/slave-yourdb-$DAYNOW-$TIMENOW/master-$DAYNOW-$TIMENOW.txt

# dump database
mysqldump yourdb > /mnt/tmp/backup/slave-yourdb-$DAYNOW-$TIMENOW/slave-yourdb-$DAYNOW-$TIMENOW.sql

# tar SQL dump
cd /mnt/tmp/backup

tar -chf - slave-yourdb-$DAYNOW-$TIMENOW | gzip - > slave-yourdb-$DAYNOW-$TIMENOW.tar.gz

rm -r /mnt/tmp/backup/slave-yourdb-$DAYNOW-$TIMENOW/

# copy tar to slaves
scp /mnt/tmp/backup/slave-yourdb-$DAYNOW-$TIMENOW.tar.gz root@$1:/mnt/tmp/recovery/slave-yourdb-$DAYNOW-$TIMENOW.tar.gz
#clean up
rm /mnt/tmp/backup/*.gz*
echo "I'm all done!"

This is just our basic backup script but rather than trying to pass all the variables through ssh I decided to be lazy and just execute the script remotely.

Some of the things I would like to add would be more flexibility in reading the backup name and error checking. Down the line I want to see if I can just backup the schema and import that into the slave db so that I don’t loose all that time reading the db back in (500MB+ can take awhile) and it would help with rapid recovery from data migrations. If you have any comments or suggestions, particularly if they trip your “WTF is wrong with this guy?” sensor I’m all ears.