Chapter 4
Chapter 4 — MySQL Configuration
A freshly installed MySQL runs on conservative defaults designed to work on any hardware. On your actual server, with a known amount of RAM and a specific workload, those defaults leave significant performance on the table — and also omit important diagnostics like the slow query log. This chapter covers finding your config files, the key settings that matter for a shared web server, and how to apply changes safely.
What this chapter covers: How MySQL's configuration file hierarchy works and where to find the right file on Ubuntu/Debian. Three ways to change a setting (file,
SET GLOBAL, SET PERSIST) and when to use each. Validating config before restart. The single most important tuning setting: innodb_buffer_pool_size. Connection limits and timeout settings. The slow query log — the most valuable diagnostic tool in MySQL. Default character set. A complete annotated config file for a shared web+database server. Troubleshooting.
The Configuration File Hierarchy
MySQL reads multiple config files at startup. Options in later files override earlier ones. Knowing the order matters — editing the wrong file has no effect.
# See the exact files MySQL reads, in priority order
$ mysql --help 2>/dev/null | grep -A1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
# For mysqld specifically (the server process)
$ mysqld --help --verbose 2>/dev/null | grep -A1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /etc/mysql/mysql.conf.d/*.cnf /etc/mysql/conf.d/*.cnf
/etc/mysql/
├── my.cnf ← top-level file: just !include directives, don't edit directly
├── mysql.conf.d/
│ └── mysqld.cnf ← SERVER config — THIS is where you make changes on Ubuntu
└── conf.d/
└── mysql.cnf ← CLIENT config (mysql client charset, etc.)
On Ubuntu, edit
/etc/mysql/mysql.conf.d/mysqld.cnf. This is the primary MySQL server configuration file. The top-level /etc/mysql/my.cnf just contains !includedir lines that pull in the subdirectories — editing it directly achieves nothing for server settings. On Debian with the MySQL APT repo, the same file structure applies.
You can also add your own override files in a conf.d or mysql.conf.d drop-in directory rather than editing the main file — useful for keeping your customisations separate and easier to manage in version control.
Three Ways to Change a Setting
Edit mysqld.cnf
Add or change a line in /etc/mysql/mysql.conf.d/mysqld.cnf, then restart MySQL. Works for all settings — dynamic and static.
Permanent — survives restart
SET GLOBAL
Change a dynamic variable immediately without restart:
SET GLOBAL max_connections = 100;. Good for testing a value. Takes effect instantly for new connections.Temporary — lost on restart
SET PERSIST
MySQL 8.0+ only. Changes the variable now AND writes it to
/var/lib/mysql/mysqld-auto.cnf, so it survives a restart. Best of both worlds for dynamic variables.Permanent — survives restart
RESET PERSIST
Removes a persisted variable from
mysqld-auto.cnf: RESET PERSIST max_connections;. Allows the config file value (or default) to take over again.Removes the SET PERSIST override
# Check the current value of any variable
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
# Wildcard search — see all InnoDB variables
mysql> SHOW VARIABLES LIKE 'innodb%';
# Change a dynamic variable immediately (and persistently, MySQL 8.0+)
mysql> SET PERSIST max_connections = 75;
Query OK, 0 rows affected (0.00 sec)
# Confirm the change took effect right now
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Value | 75 |
+-----------------+-------+
# See all variables that have been SET PERSIST'd
mysql> SELECT variable_name, variable_value, set_time
-> FROM performance_schema.persisted_variables;
Validating config before restart
# MySQL 8.0+ — validate the config file without starting the server
$ sudo mysqld --validate-config
2026-06-15T10:30:01Z 0 [System] Config file is valid.
# If there's an error, it tells you exactly what and on which line
2026-06-15T10:30:01Z 0 [ERROR] unknown variable 'innodb_buffer_pool_siz=1G'
# Always validate BEFORE doing a systemctl restart on a production server
$ sudo mysqld --validate-config && sudo systemctl restart mysql
A restart disconnects everyone.
systemctl restart mysql closes all active database connections. If your website has a connection pool, connections will fail and reconnect after MySQL comes back up, typically within a few seconds. Static variables require a restart; dynamic ones changed via SET GLOBAL or SET PERSIST take effect immediately without disconnect.
The Most Important Setting: innodb_buffer_pool_size
InnoDB (MySQL's default storage engine) caches frequently-accessed table data and index pages in a memory pool called the buffer pool. If a query's data is in the buffer pool, it's served from RAM — microseconds. If it's not, MySQL reads from disk — milliseconds or worse. The buffer pool size is the single biggest lever for MySQL performance.
The default is 128 MB — enough to run on a tiny VM, far too small for any real workload. For a shared web server running Apache, PHP, and MySQL, allocate 20–30% of total system RAM to the buffer pool.
# Check how much RAM your server has
$ free -h
total used free shared buff/cache available
Mem: 7.7Gi 2.1Gi 1.2Gi 312Mi 4.4Gi 5.0Gi
Swap: 2.0Gi 0B 2.0Gi
# Example: 8 GB server. Apache + PHP use ~2 GB. Set buffer pool to 1-2 GB.
innodb_buffer_pool_size guide — shared web + database server
2 GB RAM
256M–512M
Leave headroom for OS + Apache + PHP
4 GB RAM
512M–1G
Comfortable for a personal site
8 GB RAM
1G–2G
Good balance for web+DB server
16 GB RAM
4G–6G
Enough to cache most databases in RAM
# Check the current buffer pool size
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
# 134217728 bytes = 128 MB default — almost certainly too small
# Check how well the current buffer pool is performing
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_requests | 14523891 |
| Innodb_buffer_pool_reads | 284 |
+---------------------------------------+----------+
# Hit ratio = 1 - (reads / read_requests) = 1 - (284/14523891) = 99.998% ← excellent
# If hit ratio is below 95%, the buffer pool is too small for your workload
Buffer pool size is a static variable on most MySQL builds — changing it requires a restart. In MySQL 8.0.14+ on systems with enough RAM, it can be changed online with
SET GLOBAL innodb_buffer_pool_size, but this is a slow operation that resizes chunk by chunk in the background.
Connection Settings
Each MySQL connection consumes RAM — stack space, buffers, and per-connection memory allocations. Setting max_connections too high wastes memory; too low and legitimate connections are refused with "Too many connections."
# See current connections and the historical maximum
mysql> SHOW STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 12 |
+----------------------+-------+
# If this peaked at 12, a max_connections of 151 is massively over-provisioned
# How many connection attempts were rejected as "too many connections"
mysql> SHOW STATUS LIKE 'Connection_errors_max_connections';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 0 |
+-----------------------------------+-------+
# 0 = no one has ever been turned away — limit is fine, can reduce it
| Variable | Default | Recommended (shared server) | Notes |
|---|---|---|---|
| max_connections | 151 | 50–100 | Each idle connection uses ~1 MB. Match to Max_used_connections + 20% headroom |
| wait_timeout | 28800 | 300 | Seconds before an idle non-interactive connection is closed. 28800 = 8 hours — far too long for web apps |
| interactive_timeout | 28800 | 300 | Same but for interactive clients (mysql CLI, phpMyAdmin). Set to same value as wait_timeout |
| max_allowed_packet | 64M (8.0) | 64M–256M | Max size of a single query/result packet. Increase if you store large blobs or get "packet too large" errors |
| connect_timeout | 10 | leave default | Seconds to wait for a client to authenticate. 10 seconds is fine |
Per-connection memory buffers compound with max_connections. Variables like
sort_buffer_size, join_buffer_size, and read_buffer_size are allocated per connection when needed. If you have 100 connections each using a 4 MB sort buffer simultaneously, that's 400 MB — just for sort operations. Leave these at their defaults unless you have a specific query pattern that needs them.
The Slow Query Log — Your Most Valuable Diagnostic Tool
MySQL can log every query that takes longer than a threshold you define. This log is the starting point for almost every performance investigation — it shows you exactly which queries are slow, how long they took, and how many rows they examined. Enable it from day one; the overhead on a small server is negligible.
# Check if the slow query log is currently enabled
mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
Enable it permanently by adding these lines to mysqld.cnf, then restart:
# Slow query log settings (add under [mysqld])
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # log queries taking > 2 seconds
log_queries_not_using_indexes = 1 # also log full table scans
log_throttle_queries_not_using_indexes = 10 # max 10 no-index logs per minute
Or enable it right now without restarting — the slow query log is a dynamic variable:
mysql> SET PERSIST slow_query_log = 1;
mysql> SET PERSIST long_query_time = 2;
mysql> SET PERSIST log_queries_not_using_indexes = 1;
Reading the slow log
Each entry in the slow log shows the query's cost: how long it took, how many rows MySQL looked at, and how many it returned.
# Query_time: 4.823156 Lock_time: 0.000042 Rows_sent: 1 Rows_examined: 128430
SELECT * FROM books WHERE title LIKE '%adventure%';
Summarising the slow log with mysqldumpslow
# Show the top 10 slowest queries (grouped by pattern)
$ sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
Reading mysql slow query log from /var/log/mysql/slow.log
Count: 42 Time=4.82s (202s) Lock=0.00s (0s) Rows=1.0 (42), bookshop_app[bookshop_app]@localhost
SELECT * FROM books WHERE title LIKE '%S%'
Count: 7 Time=1.94s (13s) Lock=0.00s (0s) Rows=523.0 (3661), bookshop_app[bookshop_app]@localhost
SELECT * FROM orders WHERE created_at > 'S'
# -s t = sort by total time | -s c = sort by count | -t 10 = top 10 results
# The first result ran 42 times, total 202 seconds — the one to fix first
Default Character Set and Collation
By default, MySQL 8.0 uses utf8mb4 and utf8mb4_0900_ai_ci — but earlier versions and some custom installs use the 3-byte utf8 which can't store emoji or certain CJK characters. Set these explicitly so every new database inherits the right encoding automatically.
# Check current defaults
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
# utf8mb4_0900_ai_ci = accent insensitive, case insensitive (good default)
# utf8mb4_unicode_ci = slightly slower but better multilingual sorting
Which collation to choose:
utf8mb4_0900_ai_ci is the MySQL 8.0 default — faster and uses the Unicode 9.0 algorithm. utf8mb4_unicode_ci is the MySQL 5.x default and may be more appropriate if you need consistent sorting across old and new MySQL versions, or if you're following documentation written for MySQL 5.x. Either is fine for a single-server setup.
A Tuned Config for a Shared Web + Database Server
Here is a complete, annotated mysqld.cnf for a server that runs Apache, PHP, and MySQL together. Adjust the memory values to match your server's RAM using the guide above.
[mysqld]
#
# === Networking ===================================================
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
# Only listen on localhost — remote access controlled separately (Chapter 6)
bind-address = 127.0.0.1
#
# === Logging ======================================================
log_error = /var/log/mysql/error.log
# Slow query log — enable immediately
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
#
# === Character set ================================================
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
#
# === Memory — ADJUST THESE for your server's RAM =================
# Formula for shared web+DB server:
# innodb_buffer_pool_size = total_RAM * 0.25
innodb_buffer_pool_size = 1G # example: 4 GB server
# For buffer pools > 8 GB, set instances to number of GB (max 64)
innodb_buffer_pool_instances = 1 # 1 is fine for pools <= 8 GB
# In-memory temp table size (per query — don't set too high)
tmp_table_size = 64M
max_heap_table_size = 64M # must match tmp_table_size
#
# === Connections ==================================================
max_connections = 75 # check Max_used_connections status first
wait_timeout = 300 # close idle connections after 5 minutes
interactive_timeout = 300 # same for interactive clients
max_allowed_packet = 64M
#
# === InnoDB reliability ==========================================
# 1 = safest (flush to disk on every commit) — always use this
# 2 = faster, but last ~1 second of transactions can be lost on crash
innodb_flush_log_at_trx_commit = 1
# Each table in its own file — makes storage management easier
innodb_file_per_table = ON # default in MySQL 8.0, just making it explicit
# Apply the changes
$ sudo mysqld --validate-config
Config file is valid.
$ sudo systemctl restart mysql
$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Active: active (running)
Binary Logging — Brief Overview
The binary log (binlog) records every data change on the server. It's required for replication and enables point-in-time recovery (PITR) — restoring a backup from Monday, then replaying binlog events up to the moment before a disaster on Wednesday afternoon.
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
# On Ubuntu 22.04+, binary logging is ON by default. Check binlog expiry:
mysql> SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
# 2592000 seconds = 30 days. Fine for most setups.
# Binary logs can accumulate — check disk usage:
$ sudo du -sh /var/lib/mysql/binlog.*
Binary logging is on by default in MySQL 8.0 on Ubuntu. If disk space is tight and you don't need PITR or replication, you can disable it by adding
disable_log_bin to mysqld.cnf — but think carefully before doing so. PITR is what lets you recover from "someone accidentally ran DELETE without WHERE" — a situation that happens sooner or later to everyone.
Verifying Your Config Changes
# Check the buffer pool is now the new size
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
# 1073741824 bytes = 1 GB ✓
# Confirm the slow log is enabled and writing to the right file
mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+-------------------------+
| Variable_name | Value |
+---------------------+-------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+-------------------------+
# Generate a slow query to test the log is working
mysql> SELECT SLEEP(3);
+----------+
| SLEEP(3) |
+----------+
| 0 |
+----------+
# Check the slow log captured it
$ sudo tail -20 /var/log/mysql/slow.log
# Time: 2026-06-15T12:00:03.001234Z
# Query_time: 3.001234 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SELECT SLEEP(3);
# ✓ Slow log is working
# Confirm timeouts and max_connections
mysql> SHOW VARIABLES WHERE variable_name IN
-> ('max_connections', 'wait_timeout', 'max_allowed_packet', 'character_set_server');
Troubleshooting
MySQL fails to start after editing mysqld.cnf
Run
sudo mysqld --validate-config first to see the exact error line — it will name the unknown or invalid variable. If MySQL is already not starting, check the error log: sudo tail -30 /var/log/mysql/error.log. Common causes: typo in variable name, wrong value format (wrote 1G where the variable expects bytes), editing the wrong config file (changes in /etc/mysql/my.cnf itself have no effect — only files included from it do). To recover, revert your last change and restart.
innodb_buffer_pool_size change has no effect after restart
You may have a
SET PERSIST override in /var/lib/mysql/mysqld-auto.cnf that takes precedence over mysqld.cnf. Check: SELECT variable_name, variable_value FROM performance_schema.persisted_variables;. If the old value appears there, clear it: RESET PERSIST innodb_buffer_pool_size; then restart. After resetting, the config file value will be used.
Slow query log file is not being created
MySQL can't write to the log file — usually a permissions issue. The
mysql OS user must own the log directory. Check: ls -la /var/log/mysql/ — should be mysql:adm or mysql:mysql. Fix: sudo chown mysql:mysql /var/log/mysql/slow.log or sudo touch /var/log/mysql/slow.log && sudo chown mysql:mysql /var/log/mysql/slow.log. Then SET GLOBAL slow_query_log = OFF; SET GLOBAL slow_query_log = ON; to make MySQL re-open the file.
ERROR 1040: Too many connections
max_connections has been reached. Immediate fix: SET GLOBAL max_connections = 200; (dynamic, takes effect immediately). Root cause: check SHOW PROCESSLIST; for Sleep connections piling up — if many show Sleep for a long time, reduce wait_timeout to close them sooner (e.g., SET PERSIST wait_timeout = 60;). The underlying problem is often a connection pool in your application not returning connections properly.
Out of memory / server becomes unresponsive after changing buffer pool size
Set
innodb_buffer_pool_size too large — MySQL is competing with Apache and the OS for RAM, causing swapping. A swapping server is slower than one with a small buffer pool. Roll back: set the value to 25% of RAM instead of a higher percentage. Remember that each max_connections connection also allocates buffers — total MySQL memory ≈ innodb_buffer_pool_size + (max_connections × per-connection buffers). On a 2 GB server, keep the buffer pool at 256–512 MB.
Quick Reference — Chapter 4
| Variable / Command | Purpose / Notes |
|---|---|
| /etc/mysql/mysql.conf.d/mysqld.cnf | Primary server config file on Ubuntu/Debian — this is where you make changes |
| sudo mysqld --validate-config | Check the config file for errors before restarting |
| SHOW VARIABLES LIKE 'pattern'; | See current value of any MySQL variable (supports % wildcard) |
| SET PERSIST variable = value; | Change a dynamic variable immediately AND persist it across restarts (MySQL 8.0+) |
| RESET PERSIST variable; | Remove a persisted override so the config file value takes effect again |
| innodb_buffer_pool_size | Most important setting — set to 20–30% of RAM on shared servers. Requires restart. |
| max_connections | Cap total connections. Match to Max_used_connections status + 20% headroom |
| wait_timeout / interactive_timeout | Seconds before idle connections are closed. Set both to 300 on web servers |
| slow_query_log = 1 | Enable the slow query log (dynamic — no restart needed) |
| long_query_time = 2 | Log queries taking longer than 2 seconds |
| log_queries_not_using_indexes = 1 | Also log full table scans — essential for finding missing indexes |
| sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log | Summarise slow log — top 10 worst queries by total execution time |
| SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; | Buffer pool hit ratio — if below 95%, pool is too small |
| SHOW STATUS LIKE 'Max_used_connections'; | Peak concurrent connections since last restart — use to size max_connections |