Essential MySQL tool No. 6: stalk and collect
Problems have a way of waiting until you're not looking or at home sleeping to occur, and diagnosing them after the fact is sometimes impossible without data about the state of MySQL and the server at the time of the problem. The natural inclination is to write your own script to wait for or detect a problem and then start logging extra data because, after all, no one knows your system better than you. The problem is, you know your system when it's working, and if you knew the kinds of problems the system would have, you would simply fix them rather than try to capture and analyze them.
Thankfully, those who specialize in knowing when MySQL is not working, and in fixing the problems, have written a duo of tools called stalk and collect. The first tool waits for certain conditions to become true before running an instance of the second tool. That seems trivial, but these tools are made efficient by certain details addressed.
Firstly, stalk runs collect in configurable intervals, keeping you from logging too much redundant data, which can obfuscate postproblem analysis. Secondly, collect gathers not only the standard information that MySQL can report about itself but a lot more data that you might not have thought to include: lsof, strace, tcpdump, and so on. Thus, if you end up having to consult with a professional who specializes in fixing MySQL problems, you will have all the data that they need.
stalk and collect are configurable, so they can be used for almost any problem. The one requirement is a definable condition to establish a trigger for stalk. If multiple conditions signal the problem, then you may also need to consult with a professional for a more extensive review of your MySQL environment because problems can appear in MySQL even though the underlying cause is elsewhere.
stalk and collect can be used proactively, too. For example, if you know that there should never be more than 50 active MySQL connections at a time, then you could proactively monitor this stalk, making these tools helpful both for problems that you know and problems that you have not yet seen.
Download: http://aspersa.googlecode.com/svn/trunk/stalk | http://aspersa.googlecode.com/svn/trunk/collect
Maintainer: Baron Schwartz
More info: http://aspersa.googlecode.com/svn/html/index.html | http://code.google.com/p/aspersa/
Essential MySQL tool No. 7: mycheckpoint
You don't always want to wait for something to go wrong before addressing a problem, and dashboards provide an essential way for you to monitor your MySQL environment for potential problems before they arise.
There are many free and commercial monitoring applications for MySQL, some MySQL-specific and others generic with MySQL plug-ins or templates. mycheckpoint is notable because it is free, open source, MySQL-specific, and full-featured.