RESET SLAVE, bash edition

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 :)

12 thoughts on “RESET SLAVE, bash edition”

  1. 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.

  2. Hmmm – shell scripts that don’t check return codes connecting to live dbs?

    Long time since I have seen that, long time

  3. 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.

  4. 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.

  5. 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

  6. 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.

Comments are closed.