How to use MySQLTuner for MySQL or MariaDB
Running MySQLTuner against a busy MySQL or MariaDB server surfaces memory pressure, connection churn, temporary-table spill, and cache inefficiency before a blind variable change turns one bottleneck into another.
MySQLTuner reads global status counters, server variables, and selected metadata, then compares what it finds against heuristics for caches, concurrency, storage engines, logs, and security posture. The report is most useful after the server has seen representative workload and enough uptime for counters to describe real behavior instead of startup noise.
The report is advisory, not authoritative. Fresh test instances, idle replicas, disabled performance_schema data, and short uptimes can all skew the output, and some recommendations are server-family specific. Current MySQL uses innodb_redo_log_capacity, while current MariaDB still exposes innodb_log_file_size for redo sizing, so every suggested change needs a version-aware check before it is persisted.
Steps to use MySQLTuner for MySQL or MariaDB:
- Put the credentials in a private client option file so MySQLTuner can log in without exposing a password in the process list.
~/.my.cnf [client] user = mysqltuner password = STRONG_PASSWORD_HERE socket = /run/mysqld/mysqld.sock
Restrict the file to the current user, for example with chmod 600 ~/.my.cnf.
For a TCP connection, replace the socket line with host = 127.0.0.1 and port = 3306 or the remote endpoint.
- Use an account that can read global status, metadata, and replication state before trusting the report.
Current upstream privilege guidance differs slightly by server family: MySQL 8.0+ uses SELECT, PROCESS, SHOW DATABASES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, and SHOW VIEW, while MariaDB 10.5+ uses SELECT, PROCESS, SHOW DATABASES, EXECUTE, BINLOG MONITOR, SHOW VIEW, REPLICATION MASTER ADMIN, and SLAVE MONITOR.
- Run mysqltuner after a representative workload window so the counters reflect real pressure instead of startup activity.
$ mysqltuner --socket /run/mysqld/mysqld.sock --defaults-file ~/.my.cnf --forcemem 1024 --forceswap 0 >> MySQLTuner 2.8.38 >> Run with '--help' for additional options and output filtering ✔ Logged in using credentials from defaults file account. ℹ Assuming 1024 MB of physical memory ✘ Assuming 0 MB of swap space (use --forceswap to specify) -------- Performance Metrics ----------------------------------------------------------------------- ℹ Up for: 2m 5s (2K q [16.672 qps], 704 conn, TX: 621K, RX: 306K) ✔ Slow queries: 0% (0/2K) ✔ Aborted connections: 0.71% (5/704) ✔ Temporary tables created on disk: 16% (66 on disk / 412 total) ##### snipped #####
When MySQLTuner runs over TCP to another host, set --host and --port and pass the remote server's actual RAM and swap values with --forcemem and --forceswap because the tool cannot detect them reliably across the network.
Distro packages can lag upstream releases, so section names and output symbols can differ slightly from current upstream builds.
- Start the review with the sections that prove the report reflects reality: uptime, read/write mix, max possible memory, slow queries, aborted connections, thread cache behavior, and temporary tables written to disk.
A server that restarted recently or has barely handled traffic can still produce valid syntax and login results while producing low-signal tuning advice.
- Treat highlighted items as hypotheses to verify, not automatic settings to paste.
-------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL was started within the last 24 hours: recommendations may be inaccurate ##### snipped #####Warnings tied to short uptime, empty workloads, or unused storage engines often disappear after the server has processed normal traffic for longer.
- Check the current server variables before editing any configuration file so the suggested change is compared against the live value, not against memory.
$ mysql --defaults-file=~/.my.cnf --table -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','table_open_cache','thread_cache_size');" +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | | table_open_cache | 2000 | | thread_cache_size | 151 | +-------------------------+-----------+ - Translate redo-log advice to the correct variable for the server family before acting on it.
$ mysql --defaults-file=~/.my.cnf --table -e "SHOW VARIABLES LIKE 'innodb_log_file_size';" +----------------------+-----------+ | Variable_name | Value | +----------------------+-----------+ | innodb_log_file_size | 100663296 | +----------------------+-----------+ $ mysql --defaults-file=~/.my.cnf --table -e "SHOW VARIABLES LIKE 'innodb_redo_log_capacity';" +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_redo_log_capacity | 104857600 | +--------------------------+-----------+
Current MariaDB still reports redo size through innodb_log_file_size, while MySQL 8.0.30 and newer expose innodb_redo_log_capacity. Older MySQL releases also use innodb_log_file_size and innodb_log_files_in_group.
- Persist only the vetted changes under [mysqld] in the server option file.
[mysqld] innodb_buffer_pool_size = 1G table_open_cache = 4000 thread_cache_size = 64
Common paths include /etc/mysql/mysql.conf.d/mysqld.cnf for MySQL on Debian-style systems and /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB.
- Restart the service only when the chosen variable is not dynamic, then verify that the running server loaded the expected value.
$ sudo systemctl restart mariadb
Replace mariadb with mysql or mysqld when that is the unit name on the host.
Restarting the database service drops active sessions and interrupts in-flight work.
- Re-run mysqltuner after the same workload window and compare the same counters before accepting the change as a real improvement.
Keep both reports, then compare values such as Temporary tables created on disk, Aborted connections, Thread cache hit rate, and the server variables changed in the previous step.