MySQL工具:管理员必备的10款MySQL工具

MySQL是一个复杂的的系统，需要许多工具来修复，诊断和优化它。幸运的是，对于管理员，MySQL已经吸引了很多软件开发商推出高品质的开源工具来解决MySQL的系统的复杂性，性能和稳定性，其中大部分是免费提供的社区。 从单机到多个节点的环境，以下10个开源工具，对于使用MySQL的任何一个人都是比较管用的。该列表已经考虑到了各种编译。你会发现这些工具，可以帮助备份MySQL数据，提高性能，防止数据偏差，并在出现问题时记录相关的故障排除数据。   
MySQL 工具1: mk-query-digest
      没有什么比缓慢的MySQL性能更令人沮丧的。尽管常常认为是硬件的加速问题，但事实上并不一定。性能表现不佳有如下原因，可能是因为缓慢执行的查询阻塞其他查询，由此产生一个响应时间缓慢的的连锁反应。优化查询指令比升级硬件更能节约成本，所以，第一步应是MySQL优化查询日志分析。   
    数据库管理员应该经常分析查询日志，来把握环境的变化。如果你从来没有进行查询日志分析，请立即进行。假设进行优化的时候，而依靠第三方软件，但其实它并不可靠。   
    目前最好的查询日志分析工具是mk-query-digest。它是积极深入，充分记录，并进行彻底的测试。MySQL分发包括查询记录分析工具mysqldumpslow，但该工具已经过时，记录不良，并且经不起考验。其他查询日志分析，像mysqlsla，这是几年前发布的，也是和mysqldumpslow的问题一样。   
    mk-query-digest分析查询日志，并生成报告，关于执行时间和其他指标的汇总统计信息。由于查询日志通常包含成千上万，并且是以百万计的查询，所以mk-query-digest正是查询日志分析所需要的工具。   
    mk-query-digest的优点，就是它的查询比其他查询执行时间要短。优化这些慢速查询，将让MySQL运行速度更快，减少最大的延迟。查询指令的优化工作本身堪称艺术，其中包含诸多细致入微的技巧，但基本目标是相同的：找到慢查询，优化，提高查询响应时间。   
    这个工具使用比较容易使用；执行mk-query-digest的slow-query.log，将迟缓的查询指令输出到slow-query.log。该工具还包括“查询指令复核”，意在列出那些我们尚未加以核对或批准的查询指令，使得频繁的日志分析更快速，高效。   
下载地址：http://maatkit.org/get/mk-query-digest
  MySQL 工具2：mydumper   
    能够生成数据转储的快速备份和服务器克隆至关重要。不幸的是，MySQL发布的mysqldump组件是单线程的，这就使得它无法迅速解决数据密集型用户所面临的实际问题。。值得庆幸的是，现在更换的mydumper，使用的是多线程，使得它比mysqldump快10倍。   
    另一款工具是MySQL Data Dumper，这个工具无法管理备份集，差异化，或一个完整的备份计划的其他部分。和mysqldump比起来，它只是尽可能的快速从MySQL转储数据，使你在时间紧迫的时候完成备份，如通宵，雇员处于脱机状态，或执行备份更加频繁的时候。   
    从技术角度解释mydumper的话，它就是锁定表，所以它不是在工作时间执行备份的理想工具。话又说回来，专业的数据恢复费用达每小时数百美元，即使数据是不可恢复的的，你依然会收到一张账单。而mydumper是免费的，所以这是值得考虑的，包括基本的备份。   
    mydumper当克隆服务器时还是比较方便的。其他工具对硬盘内容重复执行整体复制，但是当你需要MySQL的数据的时候， mydumper是最快的方式。在云里面设置的服务器特别适合使用mydumper的克隆。从现有的服务器转储你的MySQL数据，并把它复制到新的实例中。   
    克隆创建从属服务器、基准和分析是有效的，在测试和开发无处不显示它的重要。对于动态的MySQL环境，在上线之前能够加速副本的快速测试是必不可少的。而mydumper，您可以快速创建一个服务器，和正在作业的服务器几乎是相同的，使您的测试结果，能更好地模仿作业结果。   
下载地址：https://launchpad.net/mydumper/+download
  MySQL 工具3: xtrabackup and xtrabackup-manager
      如果你的数据库每天全天候都在使用，在备份锁定表期间，没有“整晚”给你备份，xtrabackup是您最好的解决方案。xtrabackup又称Percona   
    XtraBackup，这个工具是唯一免费的执行非阻塞备份，开源的工具可以做到这一点，非常难得。相比之下，专有非阻塞的备份软件，每台服务器的成本超过5,000美金。   
    xtrabackup还提供了增量备份，让您只备份自上次完全备份以来已更改的数据。添加增量备份到你的备份过程是强大的，这些巨大的较小备份将减少性能的瞬时干扰。   
    此外，xtrabackup的另一款产品已经问世，使得管理一个完整的备份计划更容易，它就是xtrabackup-manager。虽然这是一个新的工具并仍处于开发阶段，它拥有很大的潜力，因为它提供比较先进的功能，如同组的旋转备份和备份集终止。xtrabackup和xtrabackup-manager是一个强大的且免费的备份解决方案。   
下载地址：http://www.percona.com/software/percona-xtrabackup/downloads/
  MySQL 工具4：tcprstat
      tcprstat可能是10个工具中最难懂的。该工具监视TCP请求，和打印统计低级别的响应时间。当你熟悉响应时间对性能的思维方式，tcprstat的作用是很大的。   
    阐述原理在Cary Millsap 和 Jeff Holt撰写的“Oracle性能优化”一书，它同样适用于MySQL。其基本思路是，在MySQL这个案例中，这是一种服务，接受一个请求（查询），满足这一请求（执行时间），和响应的结果（结果集）。该服务的响应时间是接收请求和发送响应之间的时间跨度。响应时间越短，更多的请求，可以在相同的时间内送达。   
    并行处理和其他低级别的因素发挥的重要组成部分，但简化的结果是每八小时工作日的实际运行时间按28800秒计算，因此如果能将每条请求的响应时间在原有基础上缩短400毫秒(从原有的0.5秒缩短至0.1秒)，那么就意味着我们每天可以多处理230400条请求。tcprstat将帮助你实现这一目标。   
    由于篇幅所限，本文中只能在功能性方面略加描述(即讲解MySQL响应时间优化工作的第一步)以激起诸位你的好奇心。完成这个工具的介绍：读“Oracle性能优化”， 然后开始使用tcprstat。   
下载地址：https://launchpad.net/tcprstat
  二进制：http://www.percona.com/docs/wiki/tcprstat:start
  MySQL 工具5: mk-table-checksum
      “数据偏差”是广泛存在于动态MySQL环境之中的一项重大问题。其实际含义为：从属数据未能与主体数据正确同步，发生的原因主要是从属数据端出现写入操作或者主体数据端执行了具备不确定性的查询指令。更糟糕的是，数据偏差情况很可能会被管理人员所忽视，直到爆发严重后果。Mk-table-checksum该登场了，这款工具的作用是在执行复杂、敏感的计算时，并行验证两个或多个列表中相关数据内容的一致性。
      mk-table-checksum 能够分别为独立服务器及同步架构中的服务器提供帮助，这也是该工具最大的亮点所在。主体服务器与从属服务器之间的数据一致性在同步时必须得到充分的重视。由于主体数据变更在向从属数据同步的过程中存在一定程度的滞后(即延迟)，因此直接读取服务器数据的方式无法严格保证信息的一致性，因为数据在同步完全结束之前，一直处于不断变化且并不完整的状态下。锁定列表、等等所有数据同步结束之后再进行验证当然行之有效，但这种方案意味着我们不得不同时中止服务器服务的正常响应。mk-table-checksum允许大家在不锁定列表的前提下，对主体及从属数据间的差异性进行验证(至于该技术的具体实现方法，请单击此处参阅工具文档)。http://www.maatkit.org/doc/mk-table-checksum.html
      除了同步过程中的一致性，数据验证在其它一些方面也颇具意义，例如列表尺寸问题。MySQL的CHECKSUM TABLE指令对于小型列表来说完全够用，但规模庞大的列表往往需要“分块”处理，以避免在校验及计算的过程中CPU或内存发生长期锁死或超载的状况。
      分块处理能够应付的第二个大问题是对数据一致性定期检查的要求。虽然数据偏差可能只是一次偶然的意外，但事实上遇到脸丑手黑的管理员，这类问题也许会反复发作。mk-table-checksum的设计初衷正是对列表进行定期检查，且整个验证过程分步分块、循序渐进，直到整套大规模列表处理完毕。这种持续性处理方式有助于管理员对数据偏差进行经常性校对。
  下载地址: http://maatkit.org/get/mk-table-checksum
  MySQL 工具6: stalk 及collect
      有时候，问题会在我们疏于监控或回家睡觉的时间段内发生，大家都知道在问题发生之后才对MySQL及服务器运行状态进行诊断往往很难甚至不可能得出正确结论。这时大家普遍的做法往往是亲自编写一套脚本然后静待检测结果，或者是对额外数据进行记录，毕竟没人比自己更了解自己所使用的系统。但问题是，系统正常工作时大家当然对其分外熟悉，如果系统当前的工作状态可能存在各类隐患，我们也往往会试图简单地将其解决掉而非进行深入的探索及分析。
      值得庆幸的是，有人对MySQL崩溃状态下的状况非常了解，并针对那些常见多发的问题编写了两款分别名为stalk及 collect的故障排查工具。前一款工具的作用是在第二款真正运行实例之前等待设备状态符合故障发生时的情形。尽管粗看起来这一点似乎无关紧要，但事实上该工具确实简单高效地收集了各类可能引发问题的细节变化。
      首先，stalk根据配置内容的要求每隔一段时间运行一次collect，该步骤能够消除记录中那些繁杂无用的冗余数据，使对此前故障的分析更有条理。接下来，collect会将MySQL对自身运行情况的报告及其它各类我们可能想都没想过的数据进行汇总，其中包括：曾经打开的文件夹、应用程序接受及调用的系统信息、网络通信量以及其它种种。如此一来，如果最终大家不得不求助于解决MySQL故障的专业咨询团队，那么他们在询问中所要涉及到的各类信息我们就都已经掌握了。
      stalk 与collect能够根据需要进行配置，因此它们能够应付几乎所有故障情况。惟一的要求是为stalk的触发建立一项可定义的条件。如果有多项条件都是引发故障的嫌疑对象，那么大家可能需要与自己的MySQL运行环境专家进行磋商，以部署更广泛的审查工作。事实上，导致MySQL崩溃的根本原因也可能潜伏于该系统之外。
      stalk 与 collect也可以用于主动防御。举例来说，如果大家了解到相同时间段内不应该同时存在50个以上的活跃MySQL连接，那么stalk可以主动监控这一问题。换句话说，这两款工具能够帮你解决许多初显端倪以及尚不明朗的麻烦。
  下载地址：http://aspersa.googlecode.com/svn/trunk/stalkhttp://aspersa.googlecode.com/svn/trunk/collect
  MySQL 工具7: mycheckpoint
      没人希望问题确切发生之后才忙着想办法补救，因此通过可视化仪表对MySQL运行环境进行实时监控是防患于未燃的一项重要途径。
      MySQL相关的免费或商业化监控应用程序很多，有些是专门服务于MySQL的、有些则是具备MySQL插件或模板的通用型工具。Mycheckpoint值得关注的原因是：它不仅免费开源，而且只针对MySQL，同时各类功能一应俱全。
      跟当下大多数监控解决方案一样， mycheckpoint基于见面运行。以下图为例：
  

      mycheckpoint可以经由设置对MySQL及服务器各项指示同时进行监控，例如InnoDB缓冲池刷新、临时列表创建、操作系统负载、内存使用情况等等。如果大家不喜欢阅读图表，mycheckpoint还能够生成文字报告。
      正如 stalk的功能，警报条件可以定义为电子邮件通知，但不必运行collect这类收集额外故障排查数据的工具。Mycheckpoint的另一项实用功能是通过监控MySQL中的变量揪出可能导致问题的隐患，或者是阻止那些本不该存在的对MySQL的修改。
      监控MySQL不仅仅对数据中心或者庞大的设备部署生效。即使大家只拥有一台MySQL服务器，监控措施仍然是不可或缺的;经由此类媒介，我们能够确切了解自己系统的相关运行情况，进而有效地预见或规避可能发生的故障。
  下载地址: http://code.google.com/p/mycheckpoint/downloads/list
  MySQL 工具8: shard-query
      还在为针对诸多分区或是数据碎片集合的查询速率低下而烦恼?其实只需使用shard-query，整个处理速度会大大加快。那些基于下列架构的查询指令能够从shard-query工具中得到最大的提升：
      ●通过FROM串联自子句的子查询
      ●UNION 及 UNION ALL
      ●IN
      ●BETWEEN
      复合函数 SUM, COUNT, MIN, and MAX 等也能够使用上述架构。举例来说，下面这条查询指令即可由shard-query并行执行:
  

      根据基准测试的结果显示，通过并行处理的方式，该查询指令的响应时间缩短了85%左右，从原先的21秒降低至3秒。
      Shard-query并不是一款能够独立运行的工具;它需要诸如Gearman之类的其它程序提供支持，而且设置过程也相对比较复杂。但如果大家的数据分区及查询指令符合上面列出的构造，那么付出一些努力也是值得的，毕竟优化效果非常明显。
  下载地址: http://code.google.com/p/shard-query/source/checkout
  MySQL 工具9: mk-archiver
      随着列表体积的日益增大，查询指令生效时间也每况愈“长”。响应时间不理想的干扰因素当然很多，但如果我们已经对各个角度实施了优化，那么最后仍然制约性能表现的瓶颈所在就是列表的规模了。将庞大列表中的各行内容进行归档操作能够有效缩短查询指令的响应时间。
      除非列表内容并不重要，否则大家千万不能贸然删除其中的内容行。归档也需要技巧，因为首先数据不能缺失、列表也不能过分锁定以免影响访问，还要注意归档操作不能导致MySQL及服务器的超载。我们的目标是让整个归档过程稳定可靠，除了缩短查询响应时间外不产生任何负面效果。mk-archiver 能够帮我们达到愿望。
      mk-archiver有两条基本工作要求，第一是归档对象必须能够被识别。举例来说，如果列表中存在日期列，而且一般来说只有几年之内的数据有实际价值，那么在这几年之前的数据行可以进行归档。另外，必须具备一套惟一的索引系统以帮助mk-archiver 工具进行定位，而不必扫描整个列表中的内容行。扫描一套巨型列表在时间及经济方面的成本都相当高昂，因此关键指数及特定的SELECT语句在避免整体扫描方面至关重要。
      在实际应用当中，mk-archiver 会自动处理各类技术细节。大家需要做的只是告知该工具哪个列表需要归档、如何识别可归档的内容行以及将这些行归至何处。如果需要的话，也可以将这些行剪切至另一个新列表中，或者是以书面的形式生成一个转储文件，方便日后需要的时候另行导入。一旦熟悉了这款工具的用法，其中的大量细微调节选项能够帮我们实现各种特殊的归档要求。此外，mk-archiver 具备嵌入式端口，因此它可以在未经代码修正的情况下解决诸多复杂的归档需求。
  下载地址: http://maatkit.org/get/mk-archiver
  MySQL 工具10: oak-security-audit
      大家最后一次全面审核自己MySQL服务器安全性是在什么时候?如果答案是“从来没有”，其实也不必担心，因为从不搞安全检查的群体相当庞大。许多企业提供安全审核服务，但除非在审计之后不存在任何大规模变更，否则我们MySQL环境的安全性应该得到定期的检查。
      外部威胁是执行MySQL安全审核的一大重要原因，但内部威胁，尤其是来自现任或前任雇员的因素往往更加危险，因为他们目前(或曾经)具备信任和权限。安全性在隐私性信息的保障(例如医疗及健康保险方面)方面同样不容忽视，必须尽力阻止意外访问(例如登录至生产服务器而非开发服务器)或者第三方程序与系统之间的交互。
      对于那些希望增进安全性的用户来说，oak-security-audit大有可为，它是一款免费的开源工具，能够应对基本的MySQL安全审核。它不需要进行任何设置，将其运行于自己的MySQL服务器之上，它就会打印出一份关于账户、账户权限、密码、一般改进方案以及潜在风险的建议报告，例如推荐暂时禁用网络访问。
      oak-security-audit的工作重点在于MySQL的安全性方面，因此它并不能代替一套完整的、由技术人员提出的安全审核方案，但它作为第一道防线能够起到相当了不起的防护作用，而且操作简单。大家可以将其固化进cron指令，每周按时运行，并将生成的报告通过电子邮件发送给自己并加以审阅。
下载地址: http://openarkkit.googlecode.com/svn/trunk/openarkkit/src/oak/oak-security-audit.py