Nearly every job advertisement for MySQL DBA positions asks for ‘shell scripting’, so I decided to investigate what it is. I remembered some performance training, where I was told how forking is bad, and one should attempt to use shell features as much as possible (like, avoid paths to something, what can be used by builtin (e.g. don’t use /usr/bin/[, just pure [ instead )
I tried to automate one MySQL DBA task (reinitializing slave after relay log corruption or after copying in cloned dataset from other server) using just bash – and it kind of worked. From now on I can put ‘Shell scripting’ proudly on my resume :-)
Next step – learn JCL (some people think this is funny :)
What was your most complicated task solved with shell scripts? :)
#!/bin/bash
HOST=$1
RPASS=$(<passwordfile)
IFS="
"
mysql -h $HOST -e "STOP SLAVE"
for line in $(mysql -e "SHOW SLAVE STATUS" -E -h $HOST)
do
key=${line%:*} # Split off what goes before :
key=${key// } # And trim spaces
data=${line#*:} # Split off what goes after :
data=${data// } # And trim spaces yet again!!!
case $key in
Exec_master_log_pos) LOGPOS=$data;;
Relay_Master_Log_File) LOGFILE=$data;;
Master_Host) RHOST=$data;;
Master_User) RUSER=$data;;
esac
done
if [ -z $LOGPOS -o -z $LOGFILE ]; then
echo "OMG FAIL NO POSITIONS KNOWN"; exit;
fi
mysql -f -h $HOST -e "
RESET SLAVE;
CHANGE MASTER TO
MASTER_HOST='$RHOST',
MASTER_USER='$RUSER',
MASTER_PASSWORD='$RPASS',
MASTER_LOG_FILE='$LOGFILE',
MASTER_LOG_POS=$LOGPOS ;
START SLAVE
"
Update: rewrote the last ‘mysql’ command to avoid multiple unnecessary forks! (thanks, Aidai :)
domas mituzas 
tuning-primer.sh — just under 1500 lines of bash. Maybe I should rename it to mini-mem.
http://forge.mysql.com/projects/project.php?id=44
Before Shane found the reproduction case that allowed MySQL devel to fix the intermittent relay log corruption with flaky network bug in MySQL, we had a very valuable ‘fix replication’ script used to restart slaves.
Hmmm – shell scripts that don’t check return codes connecting to live dbs?
Long time since I have seen that, long time
Mark, I had you in mind! :-) And yes, that bug is epic, and Shane’s work is even more so.
dc2447, an error in database connectivity would yield no positions, and hereby would die at “OMG FAIL” part! :-)
port?
3306
Are you sure OMG FAIL NO POSITIONS KNOWN will get triggered? I think the if line will be a syntax error if the strings are really empty (unset). I would double quote them.
Baron, it will:
$ if [ -z -o -z ]; then echo xx; fi
xx
Huh, I don’t know how, but in the past I “learned” I always have to quote things in shell scripts, apparently I’m wrong :)
I definitely should not have shell on my resume.
Well, -z works, but using ‘=’ would fail:
test “” = $NOSUCH
bash: test: : unary operator expected
which I guess is why I tend to always quote as well :p
since the above is posted under ‘sarcasm’ I’m not going to complain
much about $1 vs “$1” and using $() vs “. Nowadays bashisms are probably OK but in my time if you worked with Solaris you’d better stick to the original Bourne sh and keep in mind the awk/nawk/gawk thing. But the way I understand some rumors it’s not the Bourne shell one needs to learn but how to tie the tie and the subtle shades of blue. JCL, too. As for shell scripting I’d say a C compiler would be a worthy challenge. The code doesn’t have to be fast,just basic executable generation. I’ve always said I could do one but noone called me so far. Commands outside of builtins are allowed up to about gawk level, but no perl, please. Pretty much anyone could do a C compiler with perl 5.6 and above.