Qsh.pl: distributed query tool

I've written quite a few tools over time to connect to many mysql servers and run queries. Most of these have been pretty specific to a small set of tasks such as running an alter across many servers. Any sysadmin that is in charge of many servers is probably familiar with dsh, and as I was using recently I realized how all those specific tools I've written for mysql could be generalized into a dsh like tool. Thus, Qsh.pl was born! (download at launchpad)

Usage should be familiar to anyone who has used dsh before, it even will read group files made for dsh in /etc/dsh/group/or /usr/local/etc/group/.

Here's an example where this tool was quite useful. I was getting a query error for SHOW GLOBAL STATUS. This was a curious result since we're running mysql 5.0 everywhere. So what better way to find out which machines are complaining than just run it everywhere:

# qsh.pl -Mcg all_servers --user root --ask-pass --db=test -e 'SHOW GLOBAL STATUS'  2>error.log
{snip ... lots of output}
Done. Total time 2.919

My group file for all_servers includes 120 mysql servers, executing that query on all of them took a total of 2.9 seconds, not bad. I also redirected stderr to a file, so any query errors are easy to find:

cat error.log
myserver1: Query Error (1064) You have an error in your SQL syntax ...
myserver2: Query Error (1064) You have an error in your SQL syntax ...

Ok, we found all the servers that return an error, why do they complain?

# qsh.pl -Mcm myserver1,myserver2,myserver3 --user root --ask-pass --db=test -e 'SELECT VERSION()'
Password:
myserver1: +-----------------+
myserver1: |       VERSION() |
myserver1: +-----------------+
myserver1: | 4.1.21-standard |
myserver1: +-----------------+
myserver3: +----------------------------+
myserver3: |                  VERSION() |
myserver3: +----------------------------+
myserver3: | 5.0.66a-enterprise-gpl-log |
myserver3: +----------------------------+
myserver2: +-----------------+
myserver2: |       VERSION() |
myserver2: +-----------------+
myserver2: | 4.1.21-standard |
myserver2: +-----------------+
Done. Total time 0.063

Ooops! That's right, still a few old versions for legacy reasons.

That's just one example of how I used it. There are probably lots of use cases out there, but since it's new I'm still learning to rely on it. It certainly makes things faster when I can think about querying many servers at once, and is a more efficient way to work when dealing with many machines. It might be useful for:

+ comparing explain plan between many machines
+ altering large tables across many slaves, before promoting one to master.
+ grabbing status output from many machines to feed into awk or sed