Archivio Domande

This article provides a guide demonstrating how to install and run MySQLTuner on Linux server.

MySQLTuner is a popular Perl-based diagnostic script that analyzes a running MySQL or MariaDB server and provides recommendations for improving performance, stability, and security. It is read-only and does not make any changes to your database server, making it safe to use in production environments.

This guide demonstrates how to install, run, and interpret MySQLTuner on Ubuntu, Debian, AlmaLinux, Rocky Linux, and CentOS systems.

What is MySQLTuner?

MySQLTuner examines:

• Memory usage
• InnoDB configuration
• MyISAM configuration
• Query cache (legacy versions)
• Temporary table usage
• Slow queries
• Connection statistics
• Table cache
• Thread cache
• Binary logging
• Replication
• Security settings
• SSL configuration
• Performance Schema
• Index usage
• Fragmented tables

The recommendations are generated based upon the server's actual workload.

Requirements

• Root or sudo privileges
• Perl installed
• Running MySQL or MariaDB server
• Database administrator credentials

Supported versions include:

• MySQL 5.7
• MySQL 8.x
• MariaDB 10.x
• MariaDB 11.x
• Percona Server

How to Install and Run MySQLTuner on Linux

To install and run MySQLTuner on Linux, follow the steps outlined below:

1. Verify MySQL is Running

   systemctl status mysql
   

   or

   systemctl status mariadb
   

   Example:

   Active: active (running)
   

2. Install Perl (if necessary)

   Ubuntu/Debian

   sudo apt update
   sudo apt install perl wget curl -y
   

   AlmaLinux/Rocky/CentOS

   sudo dnf install perl wget curl -y
   

   Older CentOS:

   sudo yum install perl wget curl -y
   

3. Download MySQLTuner

   Download the latest version directly from the official repository:

   cd /usr/local/bin
   
   sudo wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
   

   or

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

   Make it executable:

   sudo chmod +x mysqltuner.pl
   

4. Verify Installation

   /usr/local/bin/mysqltuner.pl --version
   

   Example:

   MySQLTuner 2.x.x
   

5. Create a Read-Only Monitoring User (Recommended)

   Rather than using the MySQL root account, create a dedicated user.

   Login:

   mysql -u root -p
   

   Create the account:

   CREATE USER 'mysqltuner'@'localhost'
   IDENTIFIED BY 'StrongPasswordHere';
   

   Grant the minimum required privileges:

   GRANT PROCESS,
   REPLICATION CLIENT,
   SELECT
   ON *.*
   TO 'mysqltuner'@'localhost';
   
   FLUSH PRIVILEGES;
   

   Exit:

   EXIT;
   

6. Run MySQLTuner

   Basic usage:

   mysqltuner.pl
   

   or

   /usr/local/bin/mysqltuner.pl
   

   If prompted:

   User:
   Password:
   

   Enter your database credentials.

7. Run Non-Interactively

   mysqltuner.pl \
   --user mysqltuner \
   --pass StrongPasswordHere
   

8. Save the Report

   mysqltuner.pl \
   --user mysqltuner \
   --pass password \
   > mysqltuner-report.txt
   

9. Run with a Socket

   Sometimes TCP connections are disabled.

   Locate the socket:

   mysqladmin variables | grep socket
   

   Example:

   socket = /var/lib/mysql/mysql.sock
   

   Run:

   mysqltuner.pl \
   --socket /var/lib/mysql/mysql.sock
   

10. Run Against a Remote Server

    mysqltuner.pl \
    --host db.example.com \
    --port 3306 \
    --user mysqltuner
    

11. Analyze Only After Sufficient Uptime

    For the most accurate recommendations, let the server accumulate workload before running MySQLTuner:

    • At least 24 hours of normal activity
    • Ideally several days
    • After a representative production workload

    Running it immediately after a reboot may produce misleading recommendations because many counters will still be near zero.

    Check uptime:

    SHOW GLOBAL STATUS LIKE 'Uptime';
    

Understanding the Output

A typical report includes sections like:

General Statistics

Storage Engines

Security Recommendations

Performance Metrics

Memory Usage

InnoDB Metrics

Recommendations

The final Recommendations section is the most useful place to start.

Example Recommendation

Increase table_open_cache

Current:

400

Recommended:

2000

For MySQL 8:

[mysqld]

table_open_cache=2000

Restart:

systemctl restart mysql

Common Recommendations

Increase InnoDB Buffer Pool

Current:

128M

Suggested:

4G

Example:

innodb_buffer_pool_size=4G

Increase Thread Cache

thread_cache_size=100

Increase Table Cache

table_open_cache=4096

Increase Temporary Table Size

tmp_table_size=256M
max_heap_table_size=256M

Enable Slow Query Log

slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2

Increase Open Files Limit

open_files_limit=65535

Binary Logging

log_bin=mysql-bin

Useful for:

• Replication
• Point-in-time recovery
• Incremental backups

Remove Anonymous Users

Check:

SELECT User, Host
FROM mysql.user;

Delete:

DROP USER ''@'localhost';

Remove Test Database

DROP DATABASE test;

Secure Root Accounts

Avoid remote root logins.

SELECT User, Host
FROM mysql.user
WHERE User='root';

Configuration Files

Ubuntu:

/etc/mysql/mysql.conf.d/mysqld.cnf

Debian:

/etc/mysql/mariadb.conf.d/50-server.cnf

AlmaLinux:

/etc/my.cnf

MariaDB:

/etc/my.cnf.d/server.cnf

Restart the Database

MySQL:

sudo systemctl restart mysql

MariaDB:

sudo systemctl restart mariadb

Verify Changes

Run MySQLTuner again:

mysqltuner.pl

Compare:

• Memory usage
• Efficiency scores
• Cache hit rates
• Recommendations

Repeat this process after significant configuration changes or workload shifts.

Automating MySQLTuner

Create a weekly cron job:

sudo crontab -e

Add:

0 2 * * 0 /usr/local/bin/mysqltuner.pl \
--user mysqltuner \
--pass StrongPasswordHere \
> /var/log/mysqltuner-$(date +\%F).log

Or create a wrapper script to avoid exposing credentials on the command line:

#!/bin/bash

/usr/local/bin/mysqltuner.pl \
  --defaults-file /root/.my.cnf \
  > /var/log/mysqltuner-$(date +%F).log

Then make it executable:

chmod 700 /usr/local/bin/run-mysqltuner.sh

Security Best Practices

• Create a dedicated read-only MySQLTuner user instead of using root.
• Avoid placing passwords directly on the command line, as they may be visible in process listings. Prefer a protected MySQL option file such as /root/.my.cnf with restrictive permissions (chmod 600).
• Run MySQLTuner only after the server has been under a representative workload.
• Review recommendations critically—some are heuristic suggestions and may not fit every environment.
• Make one configuration change at a time and measure its impact before applying additional tuning.
• Back up your MySQL configuration before making changes and document any adjustments for future reference.

Conclusion

You now know how to install and run MySQLTuner on Linux server.

MySQLTuner is an excellent first step in optimizing a MySQL or MariaDB server. It quickly highlights common configuration issues, inefficient resource allocation, and potential security concerns without modifying your system. By running it periodically and validating each recommendation against your workload, you can improve database performance, reduce resource consumption, and maintain a healthier production environment.