MySQL性能诊断工具实战：4款脚本优化数据库参数与安全

对于运行中的MySQL数据库，其性能表现、参数配置合理性以及账号安全设置，都需要定期检查和优化。本文将介绍四款实用的脚本工具，帮助您全面掌握数据库运行状态，提升整体性能。

mysqltuner.pl

mysqltuner.pl是一款常用的MySQL数据库性能诊断工具，能够检查参数设置的合理性，包括日志文件、存储引擎、安全建议及性能分析。该工具会针对潜在问题提供改进建议，是MySQL优化的重要辅助工具。

在最新版本中，MySQLTuner支持MySQL/MariaDB/Percona Server的约300个指标。

项目地址：https://github.com/major/MySQLTuner-perl

下载

[root@localhost ~]#wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

使用

[root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock
>> MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.7.23
[OK] Operating on 64-bit architecture

报告分析

1. 重点关注标有[!!]的项，例如[!!] Maximum possible memory usage: 4.8G (244.13% of installed RAM)，表示内存使用严重超标。
2. 仔细阅读最后的"Recommendations"建议部分。

tuning-primer.sh

tuning-primer.sh是另一款MySQL优化工具，能够对数据库进行全面体检，并提供优化建议。

项目地址：https://github.com/BMDan/tuning-primer.sh

目前支持检测和优化的内容涵盖：

下载

[root@localhost ~]#wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

使用

[root@localhost ~]# ./tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

报告分析

重点查看红色告警选项，根据建议结合系统实际情况进行调整。

pt-variable-advisor

pt-variable-advisor可以分析MySQL变量并就可能出现的问题提出建议。

安装

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/

[root@localhost ~]#wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
[root@localhost ~]#yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

使用

[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock

报告分析

重点关注带有WARN信息的条目。

pt-query-digest

pt-query-digest主要用于从日志、进程列表和tcpdump分析MySQL查询。

安装

参考前述pt-variable-advisor的安装步骤。

使用

[root@localhost ~]# pt-query-digest /var/lib/mysql/slowtest-slow.log

常见用法

1. 直接分析慢查询文件：

   pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log

2. 分析最近12小时内的查询：

   pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log

3. 分析指定时间范围内的查询：

   pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00' > slow_report3.log

4. 分析包含select语句的慢查询：

   pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log > slow_report4.log

5. 针对特定用户的慢查询：

   pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log > slow_report5.log

6. 查询全表扫描或full join的慢查询：

   pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log > slow_report6.log

报告分析

第一部分：总体统计结果

• Overall：总查询数量
• Time range：查询执行时间范围
• unique：唯一查询数量
• total：总计
• min：最小值
• max：最大值
• avg：平均值
• 95%：95百分位数
• median：中位数

第二部分：查询分组统计结果

• Rank：语句排名
• Query ID：语句ID
• Response：总响应时间
• time：时间占比
• calls：执行次数
• R/Call：平均响应时间
• V/M：响应时间方差均值比
• Item：查询对象

第三部分：详细统计结果

• ID：查询ID
• Databases：数据库名
• Users：用户执行分布
• Query_time distribution：查询时间分布
• Tables：涉及表
• Explain：SQL语句