MySQL is a complex system that requires many tools to repair, diagnose, and optimize it. Fortunately for admins, MySQL has attracted a vibrant community of developers who are putting out high-quality open source tools to help with the complexity, performance, and health of MySQL systems, most of which are available for free.
The following 10 open source tools are valuable resources for anyone using MySQL, from a stand-alone instance to a multiple-node environment. The list has been compiled with variety in mind. You will find tools to help back up MySQL data, increase performance, guard against data drift, and log pertinent troubleshooting data when problems arise.
[ Discover InfoWorld's 7 performance tips for faster SQL queries. | Keep up to date on the key business tech news and insights with the InfoWorld Daily newsletter. Subscribe today! ]
There are several reasons why you should consider these tools instead of creating your own in-house tools. First, thanks to their wide use, they're mature and field-tested. Second, because they are free and open source, they benefit from the knowledge and experience of the continually expanding MySQL community. Finally, these tools are actively developed, and many are professionally supported (either for free or commercially), so they continue to improve and adapt with the evolving MySQL industry.
Keep in mind that there are many more tools worthy of your attention. I have chosen to emphasize free and open source, and to err on the side of usefulness and usability. Also note that all are Unix command-line programs but one, in large part because MySQL is more widely deployed and developed on Unix systems. If I missed a favorite, feel free to highlight it in the comments below (add a comment).
Now, let's meet the first of the 10 essential MySQL tools.
Essential MySQL tool No. 1: mk-query-digest
Nothing frustrates like slow MySQL performance. All too often, faster hardware is thrown at the problem, a solution that works only if hardware is in fact to blame. More often than not, poor performance can be attributed to slowly executing queries that are blocking other queries, creating a ripple effect of slow response times. Since it's a lot cheaper to optimize queries than to upgrade hardware, the logical first step in MySQL optimization is query log analysis.
Database administrators should analyze query logs frequently, depending on the volatility of the environment. And if you've never performed query log analysis, it's time to start, even if you are relying on third-party software, which is often assumed to be optimized when, in fact, it is not.
Today's best query log analyzer is mk-query-digest. Co-written by Baron Schwartz and myself, it is actively developed, fully documented, and thoroughly tested. MySQL distributions include the query log analyzer mysqldumpslow, but the tool is outdated, poorly documented, and untested. Other query log analyzers, like mysqlsla, which I wrote several years ago, suffer the same problems as mysqldumpslow.
mk-query-digest analyzes query logs and generates reports with aggregated, statistical information about execution times and other metrics. Since query logs usually contain thousands, if not millions, of queries, query log analysis requires a tool.
