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 and KPIs (including Weighted Health Score) for MySQL/MariaDB/Percona Server in this latest version.
MySQLTuner is actively maintained supporting many configurations such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, …
You can find more details on these indicators here: Indicators description.
MySQLTuner needs contributors for documentation, code and feedback:
Test result are available here for LTS only:
Thanks to endoflife.date
Windows Support is partial
UNSUPPORTED ENVIRONMENTS - NEED HELP WITH THAT
Advanced Intelligence & Ecosystem
XtraDB is not maintained anymore
CVE vulnerabilities detection support from https://cve.mitre.org
MINIMAL REQUIREMENTS
To run MySQLTuner with all features, the following privileges are required:
MySQL 8.0+:
sql
GRANT SELECT, PROCESS, SHOW DATABASES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'mysqltuner'@'localhost';
MariaDB 10.5+:
sql
GRANT SELECT, PROCESS, SHOW DATABASES, EXECUTE, BINLOG MONITOR, SHOW VIEW, REPLICATION MASTER ADMIN, SLAVE MONITOR ON *.* TO 'mysqltuner'@'localhost';
Legacy versions:
sql
GRANT SELECT, PROCESS, EXECUTE, REPLICATION CLIENT, SHOW DATABASES, SHOW VIEW ON *.* TO 'mysqltuner'@'localhost';
OS root access recommended for MySQL < 5.1
It is 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 on staging environments, and always keep in mind that improvements in one area can adversely affect MySQL in other areas.
It’s also important to wait at least 24 hours of uptime to get accurate results. In fact, running mysqltuner on a fresh restarted server is completely useless.
Also review the FAQ section below.
Hi directadmin user!
We detected that you run mysqltuner with da_admin’s credentials taken from /usr/local/directadmin/conf/my.cnf, which might bring to a password discovery!
Read link for more details Issue #289.
All checks done by MySQLTuner are documented in MySQLTuner Internals documentation.
Choose one of these methods:
1) Script direct download (the simplest and shortest method):
bash
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/jmrenouard/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/jmrenouard/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
1) You can download the entire repository by using git clone or git clone --depth 1 -b master followed by the cloning URL above.
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. For previous MySQL 5.6 version, you can follow this command to create a new database sys containing very useful view on Performance schema:
```bash curl “https://codeload.github.com/mysql/mysql-sys/zip/master” > sysschema.zip
unzip -l sysschema.zip unzip sysschema.zip cd mysql-sys-master mysql -uroot -p < sys_56.sql ```
```bash curl “https://github.com/FromDual/mariadb-sys/archive/refs/heads/master.zip” > sysschema.zip
unzip -l sysschema.zip unzip sysschema.zip cd mariadb-sys-master mysql -u root -p < ./sys_10.sql ```
By default, performance_schema is enabled and sysschema is installed on latest version.
By default, on MariaDB, performance schema is disabled (MariaDB<10.6).
Consider activating performance schema across your my.cnf configuration file:
ini
[mysqld]
performance_schema = on
performance-schema-consumer-events-statements-history-long = ON
performance-schema-consumer-events-statements-history = ON
performance-schema-consumer-events-statements-current = ON
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON
performance-schema-consumer-events-transactions-current=ON
performance-schema-consumer-events-transactions-history=ON
performance-schema-consumer-events-transactions-history-long=ON
performance-schema-consumer-events-waits-current=ON
performance-schema-consumer-events-waits-history=ON
performance-schema-consumer-events-waits-history-long=ON
performance-schema-instrument='%=ON'
max-digest-length=2048
performance-schema-max-digest-length=2018
Sysschema is not installed by default under MariaDB prior to 10.6 MariaDB sys
You can follow this command to create a new database sys containing a useful view on Performance schema:
```bash curl “https://codeload.github.com/FromDual/mariadb-sys/zip/master” > mariadb-sys.zip
unzip -l mariadb-sys.zip unzip mariadb-sys.zip cd mariadb-sys-master/ mysql -u root -p < ./sys_10.sql ```
This error can be safely ignored Consider using a recent MySQL/MariaDB version to avoid this kind of issue during sysschema installation
In recent versions, sysschema is installed and integrated by default as sys schema (SHOW DATABASES)
ERROR at line 21: Failed to open file ‘./tables/sys_config_data_10.sql – ported’, error: 2
Fixing sysctl configuration (/etc/sysctl.conf)
– It is a system wide setting and not a database setting: Linux FS Kernel settings
You can check its values via:
bash
$ cat /proc/sys/fs/aio-*
65536
2305
For example, to set the aio-max-nr value, add the following line to the /etc/sysctl.conf file:
bash
fs.aio-max-nr = 1048576
To activate the new setting:
bash
sysctl -p /etc/sysctl.conf
Usage: Minimal usage locally
bash
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
In previous version, –forcemem shoud be set manually, in order to be able to run an MySQLTuner analysis
Since 2.1.10, memory and swap are defined to 1Gb by default.
If you want a more accurate value according to your remote server, feel free to setup –forcemem and –forceswap to real RAM value
bash
perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password
Usage: Enable maximum output information around MySQL/MariaDb without debugging
bash
perl mysqltuner.pl --verbose
perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat
Usage: Enable CVE vulnerabilities check for your MariaDB or MySQL version
bash
perl mysqltuner.pl --cvefile=vulnerabilities.csv
Usage: Write your result in a file with information displayed
bash
perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt
Usage: Write your result in a file without outputting information
bash
perl mysqltuner.pl --silent --outputfile /tmp/result_mysqltuner.txt
Usage: Using template model to customize your reporting file based on Text::Template syntax.
bash
perl mysqltuner.pl --silent --reportfile /tmp/result_mysqltuner.txt --template=/tmp/mymodel.tmpl
Important: Text::Template module is mandatory for --reportfile and/or --template options, because this module is needed to generate appropriate output based on a text template.
Usage: Dumping all information_schema and sysschema views as csv file into results subdirectory
bash
perl mysqltuner.pl --verbose --dumpdir=./result
Usage: Enable debugging information
bash
perl mysqltuner.pl --debug
Usage: Update MySQLTuner and data files (password and cve) if needed
bash
perl mysqltuner.pl --checkversion --updateversion
Usage: Integrating Sysbench performance results
bash
perl mysqltuner.pl --sysbench-file=/path/to/sysbench_output.txt
Usage: Historical Trend Analysis (compare with previous run)
```bash perl mysqltuner.pl –json –outputfile=run1.json
perl mysqltuner.pl –compare-file=run1.json ```
MySQLTuner now has experimental support for cloud-based MySQL services.
--cloud: Enable cloud mode. This is a generic flag for any cloud provider.--azure: Enable Azure-specific support.--ssh-host <hostname>: The SSH host for cloud connections.--ssh-user <username>: The SSH user for cloud connections.--ssh-password <password>: The SSH password for cloud connections.--ssh-identity-file <path>: The path to the SSH identity file for cloud connections.HTML generation is based on Python/Jinja2
HTML generation Procedure
Jinja2 Templates are located under templates sub directory
A basic example is called basic.html.j2
Installation Python j2
bash
python -mvenv j2
source ./j2/bin/activate
(j2) pip install j2
Using Html report generation
bash
perl mysqltuner.pl --verbose --json > reports.json
cat reports.json j2 -f json MySQLTuner-perl/templates/basic.html.j2 > variables.html
or
bash
perl mysqltuner.pl --verbose --json | j2 -f json MySQLTuner-perl/templates/basic.html.j2 > variables.html
HTML generation is based on AHA
HTML generation Procedure
Installation Aha
Follow instructions from Github repo
Using AHA Html report generation
perl mysqltuner.pl –verbose –color > reports.txt aha –black –title “MySQLTuner” -f “reports.txt” > “reports.html”
or
perl mysqltuner.pl –verbose –color | aha –black –title “MySQLTuner” > reports.html
Question: What are the prerequisites for running MySQL tuner ?
Before running MySQL tuner, you should have the following:
Question: Can MySQL tuner make changes to my configuration automatically ?
No., MySQL tuner only provides recommendations. It does not make any changes to your configuration files automatically. It is up to the user to review the suggestions and implement them as needed.
Question: How often should I run MySQL tuner ?
It is recommended to run MySQL tuner periodically, especially after significant changes to your MySQL server or its workload.
For optimal results, run the script after your server has been running for at least 24 hours to gather sufficient performance data.
Question: How do I interpret the results from MySQL tuner ?
MySQL tuner provides output in the form of suggestions and warnings.
Review each recommendation and consider implementing the changes in your MySQL configuration file (usually ‘my.cnf’ or ‘my.ini’).
Be cautious when making changes and always backup your configuration file before making any modifications.
Question: Can MySQL tuner cause harm to my database or server ?
While MySQL tuner itself will not make any changes to your server, blindly implementing its recommendations without understanding the impact can cause issues.
Always ensure you understand the implications of each suggestion before applying it to your server.
Question: Does MySQL tuner support MariaDB and Percona Server ?
Yes, MySQL tuner supports MariaDB and Percona Server since they are derivatives of MySQL and share a similar architecture. The script can analyze and provide recommendations for these systems as well.
Question: What should I do if I need help with MySQL tuner or have questions about the recommendations ?
If you need help with MySQL tuner or have questions about the recommendations provided by the script, you can consult the MySQL tuner documentation, seek advice from online forums, or consult a MySQL expert.
Be cautious when implementing changes to ensure the stability and performance of your server.
Question: Will MySQLTuner fix my slow MySQL server ?
No. MySQLTuner is a read only script. It won’t write to any configuration files, change the status of any daemons. It will give you an overview of your server’s performance and make some basic recommendations for improvements that you can make after it completes.
Question: Can I fire my DBA now?
MySQLTuner will not replace your DBA in any form or fashion.
If your DBA constantly takes your parking spot and steals your lunch from the fridge, then you may want to consider it - but that’s your call.
Once you create it, make sure it’s owned by your user and the mode on the file is 0600. This should prevent the prying eyes from getting your database login credentials under normal conditions.
Question: I get “ERROR 1524 (HY000): Plugin ‘unix_socket’ is not loaded” even with unix_socket=OFF. How to fix?
This occurs because the MariaDB client attempts to use the unix_socket plugin by default when no user/password is provided.
~/.my.cnf file as described above to provide explicit credentials.perl mysqltuner.pl --user root --pass your_password.Question: How to securely re-enable unix_socket authentication?
If you decide to use unix_socket (which allows the OS root user to log in to MariaDB root without a password), follow these steps:
/etc/my.cnf: unix_socket=ON (or remove OFF).In MariaDB, set the authentication plugin for the root user:
sql
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;
Verify that the auth_socket or unix_socket plugin is ACTIVE in SHOW PLUGINS.
Question: Is there another way to secure credentials on latest MySQL and MariaDB distributions ?
You could use mysql_config_editor utilities.
bash
$ mysql_config_editor set –login-path=client –user=someusername –password –host=localhost
Enter password: ********
After which, ~/.mylogin.cnf will be created with the appropriate access.
To get information about stored credentials, use the following command:
bash
$mysql_config_editor print
[client]
user = someusername
password = *****
host = localhost
Question: It’s not working on my OS! What gives?!
These kinds of things are bound to happen. Here are the details I need from you to investigate the issue:
Question: How to perform CVE vulnerability checks?
Question: How to use mysqltuner from a remote host? Thanks to @rolandomysqldba
Connection and Authentication
–host
Since you are using a remote host, use parameters to supply values from the OS
–forcemem
If the database has too many tables, or very large table, use this:
–skipsize Don’t enumerate tables and their types/sizes (default: on) (Recommended for servers with many tables)
Question: Can I install this project using homebrew on Apple Macintosh?
Yes! brew install mysqltuner can be used to install this application using homebrew on Apple Macintosh.
MySQLTuner contains following Vagrant configurations:
Vagrant File is stored in Vagrant subdirectory.
MySQLTuner contains a Vagrant configurations for test purpose and development
$ sh build/createTestEnvs.sh
$ source build/bashrc
$ mysql_percona80 sakila
sakila> ...
$ docker images
mariadb 10.1 fc612450e1f1 12 days ago 352MB
mariadb 10.2 027b7c57b8c6 12 days ago 340MB
mariadb 10.3 47dff68107c4 12 days ago 343MB
mariadb 10.4 92495405fc36 12 days ago 356MB
mysql 5.6 95e0fc47b096 2 weeks ago 257MB
mysql 8.0 b8fd9553f1f0 2 weeks ago 445MB
percona/percona-server 5.7 ddd245ed3496 5 weeks ago 585MB
percona/percona-server 5.6 ed0a36e0cf1b 6 weeks ago 421MB
percona/percona-server 8.0 390ae97d57c6 6 weeks ago 697MB
mariadb 5.5 c7bf316a4325 4 months ago 352MB
mariadb 10.0 d1bde56970c6 4 months ago 353MB
mysql 5.5 d404d78aa797 4 months ago 205MB
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
da2be9b050c9 mariadb:5.5 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:5311->3306/tcp mariadb55
5deca25d5ac8 mariadb:10.0 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:5310->3306/tcp mariadb100
73aaeb37e2c2 mariadb:10.1 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:5309->3306/tcp mariadb101
72ffa77e01ec mariadb:10.2 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:5308->3306/tcp mariadb102
f5996f2041df mariadb:10.3 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:5307->3306/tcp mariadb103
4890c52372bb mariadb:10.4 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:5306->3306/tcp mariadb104
6b9dc078e921 percona/percona-server:5.6 "/docker-entrypoint.…" 7 hours ago Up 7 hours 0.0.0.0:4308->3306/tcp percona56
3a4c7c826d4c percona/percona-server:5.7 "/docker-entrypoint.…" 7 hours ago Up 7 hours 0.0.0.0:4307->3306/tcp percona57
3dda408c91b0 percona/percona-server:8.0 "/docker-entrypoint.…" 7 hours ago Up 7 hours 33060/tcp, 0.0.0.0:4306->3306/tcp percona80
600a4e7e9dcd mysql:5.5 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:3309->3306/tcp mysql55
4bbe54342e5d mysql:5.6 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:3308->3306/tcp mysql56
d985820667c2 mysql:8.0 "docker-entrypoint.s…" 7 hours ago Up 7 hours 0.0.0.0:3306->3306/tcp, 33060/tcp mysql 8 0
How to contribute using Pull Request ? Follow this guide : Pull request creation