Monitor NDB memory usage with Nagios

We are planning to put online a MySQL NDB Cluster soon (more on this in another post), and one thing you have to do before putting anything in production is to monitor it for problems. In the case of a NDB cluster, you should care about monitoring your limited resources – basically because is a in-memory database – and be alerted when your developers are filling up the dedicated tablespace.

You can do this in two ways: performing a SELECT on the ndbinfo database through the MySQL interface to NDB or parsing the ndb_mgm output. I prefer the latter because maybe I’m using another frontend to the data (native NDB API, memcached etc) and I don’t want to maintain a MySQL server frontend just to check how much space I still have free.

So, you can use this script on GitHub

to parse its output and know if your tablesapce is OK, warning or critical. Feel free to post any comment, fork it and send patches! :)


esx-halt: shutdown VMWare ESXi from ssh

Long time no post – again – I hope this is the last time and I can be a little more prolific :) Anyway today I want to share a little script that I hacked to shutdown an ESXi (with the free license) host remotely, shutting down in a safely form all the VMs inside it. This could be quite usefull (and in fact it’s why I wrote it), if you want to shutdown ESXi from an UPS daemon when lights go out, and you cannot afford a complete ESXi license, so you’re running the free edition.
The script can be found here at GitHub

and it’s written in bash (I use bash4 but it should run on lesser versions too). On the server side, it works with VMWare ESXi 4.x.

Any question, patch or bug report are warmly welcome :)

A handful of bash tips – part 1

During last weeks I’ve been doing lot of scripting (a user management system for our internal infrastructure which is formed by lots of services) and it ended being something like 3000 lines of bash code. Not too much but probably this is my greatest experience til now with bash :)

So, I’ve learnt a couple of things with this project and I’d like to share some tips and lessons learnt during the process. I’m not a bash guru, and if you find something could be improved, feel free to leave a comment.

Stand on the giant’s shoulders

You can find lot of docs on bash, like the Advanced Bash Scripting Guide, but these are better in my opinion

and the #bash IRC channel on Just lurking the conversations will teach you lot of things

Use the latest and greatest bash version

This means right now to use Bash4. I mean, if you are using a shell to script things, use the latest version and take advantage of all its features! Really, I don’t care about portability, I script for my systems where I have full control. I’ve upgraded a Debian box from Lenny to Squeeze just to get bash4, go figure :) Obviously if you cannot update easily or you need to be portable, don’t fall in the bashisms trap, try to be as POSIX as possible and discard my tip(s).

Quote everything. I mean, really everything

If you are used to the simple $VARIABLE form, drop it right now and use always “${VARIABLE}”. It’s cleaner, it’s safer, it supports white spaces in the variable content etc.

Only the first quote matters

I’ve seen very often (and I was using this too) escaped quote chars when creating queries or strings to be passed somewhere. For example

QUERY="SELECT * FROM foo WHERE name=\"${VAR}\";"

because you fear that ${VAR} won’t be expanded if single quoted. But what really matters in this case is the first double quote. bash will interpret every other single quote before the closing double quote as a normal char, and pass it to the next hop. So this is perfectly right:

QUERY="SELECT * FROM foo WHERE name='${VAR}';"

${VAR} will be expanded as expected. This is quite useful if calling another script with parameters via ssh, and leave a cleaner syntax (I hate escaping chars)

ssh user@host "/path/to/script 'foobar goes first' 'second parameter'"

Use shift when receiving parameters

Probably you are already doing this, anyway it’s a lesson learnt in these days. If you’re are passing parameters to functions/other scripts instead of

# etc

use the power of shift. If you are going to change your mind about those parameters, you won’t need to rename everything.

shift 1
shift 1
# etc

Use input redirection instead of pipes when possible

A classic example

cat /path/to/mylst|while read foo
# do somtething with foo

should be

while read foo
# do something with foo
done < /path/to/mylist

why? because input redirection it is meant to do that!

ssh host completion in bash with a predifined user

If you use a lot ssh from your workstation to connect to many servers using the same user (for example, err… root, or a standard user present in every of your servers) as I do, this is a must-have :)

First of all, ensure you have

HashKnownHosts no

in your ssh_config.

Then, edit your .bashrc and append these lines:

complete -r ssh
_cssh ()
COMPREPLY=($(compgen -W "$(cat ${HOME}/.ssh/known_hosts |awk -F ',' '{print $1}')" -- ${cur##root@}))
complete -P "root@" -F _cssh ssh

you can change the root@ with your user@.


Using tee to redirect output to multiple programs


You already know that if you want to pass the output of a program to the input of another program, you can use the pipe | character.

You now that if you want to write the ouput of a program to the disk and at the same time pass it as input to another program, you can use tee.

But maybe you don’t know that if you want to pass the ouput of a program to multiple programs as input, you can use tee again with a little of subshelling.

# source_program | tee (> program1) (> program2) (> programN)| programN+1

TIP: Installing untrusted packages without confirmation on Debian


Maybe you are interested on installing untrusted packages on your Debian box, but by default you are prompted with this prompt:

Do you want to continue? [Y/n/?] y
WARNING: untrusted versions of the following packages will be installed!

Untrusted packages could compromise your system’s security.
You should only proceed with the installation if you are certain that
this is what you want to do.


Do you want to ignore this warning and proceed anyway?
To continue, enter “Yes”; to abort, enter “No”:

This is fine, since it warns you about it, but it breaks non-interactive scripts, because needs user confirmation.

What then? the solution is easy, you only need to tell to aptitude that you want to use those packages without user confirmation.

From aptitude manual:
Option: Aptitude::CmdLine::Ignore-Trust-Violations
Default: false
Description: In command-line mode, causes aptitude to ignore the installation of untrusted packages. This is a synonym for Apt::Get::AllowUnauthenticated.

Just go!
# aptitude -o Aptitude::Cmdline::ignore-trust-violations=true -y install your_untrusted_package

WARNING: untrusted versions of the following packages will be installed!

Untrusted packages could compromise your system’s security.
You should only proceed with the installation if you are certain that
this is what you want to do.


*** WARNING *** Ignoring these trust violations because
aptitude::CmdLine::Ignore-Trust-Violations is ‘true’!

Writing extended state information… Done

It does not use an interactive prompt and of course your script will continue :)

See you!

Kill every MySQL SELECT older than X seconds

If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar.  Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…

So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.

It’s rather quick’n’dirty, but for it’s small use case, it works. It expects GNU userland

if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid