How to use MySQLTuner in Linux
Note: This article is based on a tool hosted at the following GitHub repository
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
Requirements
- Perl 5.6 or later (with perl-doc package)
- Unix/Linux based operating system (tested on Linux, BSD variants, and Solaris variants)
- Unrestricted read access to the MySQL server (OS root access recommended for MySQL < 5.1)
Installation
Script direct download (for this method you will need wget installed):
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
Running MySQLTuner
To use MySQLTuner just run the following command:
[root@db01 ~]mysqltuner
Note: The MySQL user and password will be requested, after entering the credentials an output will be displayed.
MySQLTuner database information:
- Rows number
- Total size
- Data size
- Percentage of data size
- Index size
- Percentage of index size
This is an example:
Warning
It is extremely important for you to fully understand each change you make to a MySQL database server. If you don't understand portions of the script's output, or if you don't understand the recommendations, you should consult a knowledgeable DBA.
MySQL Tuner Other Common Usage
Performance tips
Metadata statistic updates can impact strongly performance of database servers and MySQLTuner.
Be sure that innodb_stats_on_metadata is disabled.
set global innodb_stats_on_metadata = 0;
Specific usage
Usage: Minimal usage locally
perl mysqltuner.pl --host 127.0.0.1
Note: You can add the execute bit (chmod +x mysqltuner.pl
) so you can execute it without calling perl directly.
Usage: Minimal usage remotely
perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password
Usage: Enable maximum output information around MySQL/MariaDb without debugging
perl mysqltuner.pl --verbose
perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat
Usage: Enable CVE vulnerabilities check for your MariaDB or MySQL version
perl mysqltuner.pl --cvefile=vulnerabilities.csv
Usage: Write your result in a file with information displayed
perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt
Usage: Write your result in a file without outputting information
perl mysqltuner.pl --silent --outputfile /tmp/result_mysqltuner.txt
Usage: Using template model to customize your reporting file based on Text::Template syntax.
perl mysqltuner.pl --silent --reportfile /tmp/result_mysqltuner.txt --template=/tmp/mymodel.tmpl
Usage: Enable debugging information
perl mysqltuner.pl --debug
Usage: Update MySQLTuner and data files (password and cve) if needed
perl mysqltuner.pl --checkversion --updateversion
Use the Feedback tab to make any comments or ask questions. You can also [start a conversation with us](https://www.rackspace.com/contact).
Updated 10 months ago