Cool, cheap and easy - Inline BASH

[Chris]

I always end up using inline BASH scripts when I'm troubleshooting a server. Here are some simple but useful examples for ya.

1. Check MySQL Replication and Innodb History Status
shell> while true; do mysql -uusername -ppassword -e "show slave status\\\\G" |egrep "Read_Master_Log_Pos|Exec_Master_Log_Pos|Seconds_Behind_Master"; mysql -uadmin -padmin -e "show engine innodb status\\\\G" |grep "History list length"; sleep 2; done

2. Very fast show processlist searching for 1 user
shell> while true; do mysql -uusername -ppassword -e "show processlist" |grep cactiuser; sleep .2; done

3. Build a host list and do something for all hosts
shell> for x in $(seq -w 1 10); do host=hostname${x}.domain.com; echo "HOST: ${host}"; mysql -uusername -ppassword --host=${host} -e "show variables like '%timeout%'"; done

4. Just a different way to loop through #3
shell> for x in {1..30}; do y=`printf blah%03d $x`; echo $y; done

Well, anyway, I think you might be getting the point! What a great pet to have.

[Chris]

Original reply from Dan Smythe:

for id in `mysql -NB -e "show processlist" | grep USERNAME | awk '{print $1}'` ; do
mysql -e "KILL $id"
done

Gets rid of a nasty user.

[Chris]

Hey Dan,
Nice to see this on the Cool, cheap and easy post. The one thing I might suggest to readers would be the background on your post. I am just assuming that you use this script when dealing with a "nasty" user in your shared hosting environment... That said, just note that there are several thousand users with their own unique user names access one instance on MySQL. Thanks again for the post and please correct me if I'm wrong.

[Seattlegaucho]

Depending on the server, you may want to avoid leaving credentials in the ".bash_history". Do the following:

1. Create a file for each host (host_cred for the current examples) with the lines:
user=username
password=passwd

2. chmod the file to 0x700 to secure it

3. Add these 2 lines to your .bashrc file:
export MYUSR=`grep user host_cred | cut -d "=" -f 2`
export MYPWD=`grep password host_cred | cut -d "=" -f 2`

4. Replace user and password in all the previous scripts by $MYUSR and $MYPWD respectively

My $.02
G

[Chris]

Seattlegaucho,
Great reply to this post!!!