How To Use The MySQLTuner Script?

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