mysql调优工具

• 2019 年 10 月 5 日
• 笔记

下载地址:

https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

直接使用

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

下载即可。

chmod +x mysqltuner.pl

./mysqltuner.pl 然后输入用户名和密码即可。

下图是我一台线上服务器的检测结果。

浏览输出的结果，特别是末尾的 Recommendations ，里面一般会提到您需要在 my.cnf 修改的内容。修改 my.cnf 后记得重启 MySQL 。重启后再运行 MySQLTuner 检查。

另外需要注意的是 MySQL 需要启动 24 小时候再运行 MySQLTuner ，不然有些内容会不准。

另外，./mysqltuner.pl –help 能显示支持哪些参数。【从下面的参数可以发现这个工具还支持远程主机的体检】

   Connection and Authentication

      –host <hostname>    Connect to a remote host to perform tests (default: localhost)

      –socket <socket>    Use a different socket for a local connection

      –port <port>        Port to use for connection (default: 3306)

      –user <username>    Username to use for authentication

      –pass <password>    Password to use for authentication

      –defaults-file <path>  Path to a custom .my.cnf

      –mysqladmin <path>  Path to a custom mysqladmin executable

      –mysqlcmd <path>    Path to a custom mysql executable

      –noask              Don't ask password if needed

   Performance and Reporting Options

      –skipsize           Don't enumerate tables and their types/sizes (default: on)

                           (Recommended for servers with many tables)

      –skippassword       Don't perform checks on user passwords(default: off)

      –checkversion       Check for updates to MySQLTuner (default: don't check)

      –updateversion      Check for updates to MySQLTuner and update when newer version is available (default: don't check)

      –forcemem <size>    Amount of RAM installed in megabytes

      –forceswap <size>   Amount of swap memory configured in megabytes

      –passwordfile <path>Path to a password file list(one password by line)

   Output Options:

      –silent             Don't output anything on screen

      –nogood             Remove OK responses

      –nobad              Remove negative/suggestion responses

      –noinfo             Remove informational responses

      –debug              Print debug information

      –dbstat             Print database information

      –idxstat            Print index information

      –sysstat            Print system information

      –pfstat             Print Performance schema information

      –bannedports        Ports banned separated by comma(,)

      –maxportallowed     Number of ports opened allowed on this hosts

      –cvefile            CVE File for vulnerability checks

      –nocolor            Don't print output in color

      –json               Print result as JSON string

      –prettyjson         Print result as human readable JSON

      –buffers            Print global and per-thread buffer values

      –outputfile <path>  Path to a output txt file

      –reportfile <path>  Path to a report txt file

      –template   <path>  Path to a template file

      –verbose            Prints out all options (default: no verbose)

例如我有1台服务器，上面跑的是多实例的mysql上面，体检的话，需要使用

./mysqltuner.pl –socket /data/multi_instance/3306/mysql.sock