Posts Tagged ‘Bash’

Go Go Ghetto MySQL Parallel Dump and Import (or Hitting Things With Hammers Is Fun)!

Tuesday, October 18th, 2011

I am not a sophisticated man and my idea of elegance is hitting things with hammers. Bear that in mind when you look over the below code, which is here more for posterity than for any public edification.

One of the backup strategies we have is a complete table level dump and subsequent encryption of those files for storage in S3. Sure, it’s not a scalable solution as we discovered when the first iteration of the script did a complete dump of the db rather than by tables. This version runs them in parallel for the dump and works well enough for me not to sweat the timing at this point (~20 minutes from start to finish).

#!/bin/bash

# set variables
DAYNOW=$(date +%j)
TIMENOW=$(date +%H%M)
RPTIME=$(date +%x-%H:%M)
ME=$(hostname)

# stop the slave
mysql -utears -plamentations -e 'slave stop;'

# create directories
mkdir -p /mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW

function r {
for J in $(cat $1) ; do
mysqldump -utears -plamentations sorrowdb --tables $J | gzip > "/mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW/$J.sql.gz"
done
exit
}

mysql -u tears -plamentations -e 'show tables from sorrowdb' -s --skip-column-names | sort -R | split -d -l 5

for I in $(find x*) ; do
(r $I &)
done

sleep 15s

while [ "$(pgrep -c -f mysqldump)" -gt "0" ]
do
sleep 1s
done

#start slave
mysql -utears -plamentations -e 'slave start;'

cd /mnt/tmp/backup
gpg-zip --encrypt -r yourmom --output $ME-$DAYNOW-$TIMENOW.gpg $ME-$DAYNOW-$TIMENOW

# copy to s3
BACKUP=$(s3cmd --acl-private put /mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW.gpg s3://sorrowdb-db-backups/ | sed \
-e 's/File //g' \
-e 's/\/mnt\/tmp\/backup\///g' \
-e 's/s3:\/\/sorrowdb-db-backups\/'"$HOSTNAME"'-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9].tgz.gpg//g' \
-e 's/\[1 of 1\]//g' \
-e 's/(//g' \
-e 's/)//g' | awk '{ print $1 " " $2 " " $3 " " $5/1048576 "MB" }')

echo $BACKUP | mutt yourmom@sorrowdb.com -s "$ME - $DAYNOW $TIMENOW"

# clean up
rm -r /mnt/tmp/backup/

Now just like the World’s Most Interesting Man, I often only test things after it’s shipped. In this case I’ve tested that I can decrypt the package and see that all the files are there but I never took the time to actually do an import. Today is that day.

Version 0.01-ALPHA-WTFISTHISSHIT-b of the script just handles parallel imports of the tables from the dump file. As a bonus shoddiness, you need to run it in the directory where the files are sitting.

#!/bin/bash
function r {
for J in $(cat $1) ; do
gunzip < $J | mysql -utears -plamentations sorrowdb
done
exit
}

ls $1 | sort -R | split -d -l 18

for I in $(find x*) ; do
(r $I &)
done

Ugly but it works. Feel free to laugh, I always do when I look at my work.

Tags: , ,
Posted in Sysadmin | 2 Comments »

Of Monit and Mongrels, Quick Thoughts

Wednesday, March 5th, 2008

Monit has been serving us pretty well for the last 7 months or so in terms of keeping an eye on our mongrels and kicking them back in line if they act up. At the moment, we are running them in clusters of 3 with only 6 clusters in our current production set up and the monit restart all, for the most part, works fine when rolling them after a deploy. It is a completely different situation with 10 clusters which we are experimenting in a situation where Apache+mod_proxy sits on a separate server from the mongrels–it truly is wonderful to see Apache perform under load when it has all the resources it needs.

The problem seems to be with how resource hungry and ponderous Rails can be when firing up a mongrel, sucking up 25%+ user CPU and making the system gobble up another 10%+. This is enough so that when monit is trying to bring up or down 30 mongrels some of the pack gets left out, failing to either shutdown or start up. Now there is a monkey patch out there that addresses this very issue but I am a little wary in patching mongrel_cluster in our production environment as it might cause me headaches later with upgrades. So what is my solution?

Pressed for time it is a little kludgey and demonstrates some truly sloppy bash scripting but…it works.

#!/bin/bash
monit stop all -g pack_01
echo "Stopping 8100-02"
sleep 12s
monit stop all -g pack_02
echo "Stopping 8103-05"
sleep 12s
monit stop all -g pack_03
echo "Stopping 8106-08"
sleep 12s
monit stop all -g pack_04
echo "Stopping 8109-11"
sleep 12s
monit stop all -g pack_05
echo "Stopping 8112-14"
sleep 12s
monit stop all -g pack_06
echo "Stopping 8115-17"
sleep 12s
monit stop all -g pack_07
echo "Stopping 8118-20"
sleep 12s
monit stop all -g pack_08
echo "Stopping 8121-23"
sleep 12s
monit stop all -g pack_09
echo "Stopping 8124-26"
sleep 12s
monit stop all -g pack_10
echo "Stopping 8127-29"

So what I’ve done in the monitrc file is defined each mongrel as belonging to a group that reflects its cluster. Then I issue a stop to each group with a 12 second delay between each so that Rails and monit can navigate around each other with out either flipping out and forgetting to do their respective jobs. Starting is the same as above.

Does it work? Well, I have tried it under load several times (siege with a concurrency of 30 and the mongrels bloated up to our obese resting state) and it seems to work like a charm. I added the echos when our dev team expressed concern cap would time out while the script took its time to run all the way through.

File under: Ugly, Functional.

Tags: , , ,
Posted in Sysadmin | Comments Off