Disclaimer:
Please bear in mind that, 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 or system administrator that you trust. Always test your changes in staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.
It's also important to wait at least a day of uptime to get accurate results. In fact, running mysqltuner on a fresh restarted server is completely useless.
Introduction:
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.
MySQLTuner supports ~300 indicators for MySQL/MariaDB/Percona Server in this last version.
MySQLTuner is maintained and indicator collect is increasing week after week supporting a lot of configuration such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, ...
Installation:
Choose one of these methods:
1. Script direct download (the simplest and shortest method):
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
2. You can download the entire repository by using git clone or git clone --depth 1 -b master followed by the cloning URL above.
https://github.com/major/MySQLTuner-perl
Optional Sysschema installation for MySQL 5.6
Sysschema is installed by default under MySQL 5.7 and MySQL 8 from Oracle. By default, on MySQL 5.6/5.7/8, performance schema is enabled by default. For previous 5.6 version, you can follow this command to create a new database sys containing very useful view on Performance schema:
curl "https://codeload.github.com/mysql/mysql-sys/zip/master" > sysschema.zip
# check zip file
unzip -l sysschema.zip
unzip sysschema.zip
cd mysql-sys-master
mysql -uroot -p < sys_56.sql
Optional Performance schema and Sysschema installation for MariaDB 10.x
Sysschema is not installed by default under MariaDB 10.x. By default, on MariaDB, performance schema is disabled by default. consider activating performance schema across your my.cnf configuration file:
[mysqld]
performance_schema = on
You can follow this command to create a new database sys containing very useful view on Performance schema:
curl "https://codeload.github.com/FromDual/mariadb-sys/zip/master" > mariadb-sys.zip
# check zip file
unzip -l mariadb-sys.zip
unzip mariadb-sys.zip
cd mariadb-sys-master/
mysql -u root -p < ./sys_10.sql
Errors & solutions for performance schema installation
ERROR at line 21: Failed to open file './tables/sys_config_data_10.sql -- ported', error: 2 Have a look at #452 solution given by @ericx
Usage:
The most basic usage command would be to simply run the script using any existing Perl executable on your system:
perl mysqltuner.pl
Nonetheless, there are many other ways that different options can be specified when the script is run. Here are a few ways for reference:
Usage: Minimal usage locally
perl mysqltuner.pl --host 127.0.0.1
Of course, 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