MySQL reads its settings from one or more option files, commonly called my.cnf. On Ubuntu and Debian, the configuration is split across several files and directories, which confuses many people the first time they try to change a setting. Here’s where the files are, how they’re read, and which settings are worth changing.

Where are the files?

Ask MySQL itself which files it reads, in order:

mysqld --verbose --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

Later files override earlier ones. On Ubuntu/Debian, /etc/mysql/my.cnf mostly just includes directories:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
Distribution Main server settings
Ubuntu / Debian (MySQL) /etc/mysql/mysql.conf.d/mysqld.cnf
Ubuntu / Debian (MariaDB) /etc/mysql/mariadb.conf.d/50-server.cnf
RHEL / CentOS / AlmaLinux /etc/my.cnf and /etc/my.cnf.d/
Docker official image /etc/mysql/conf.d/ (mount your own .cnf)

Best practice: don’t edit the package’s files. Add your own file, such as /etc/mysql/mysql.conf.d/zz-custom.cnf (files in a directory are read in alphabetical order, so zz- loads last and wins). Package upgrades then never overwrite your changes. Only files ending in .cnf are read.

Option groups

Settings belong to groups in square brackets:

[mysqld]          # the server
innodb_buffer_pool_size = 2G

[client]          # all client programs (mysql, mysqldump, …)
default-character-set = utf8mb4

[mysql]           # only the mysql command-line client
prompt = "\\u@\\h [\\d]> "

[mysqldump]
single-transaction

MariaDB also reads [mariadb] and [mariadbd] groups.

Apply and verify changes

sudo systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

If MySQL fails to start after an edit, check the error log (sudo journalctl -u mysql -n 50, or /var/log/mysql/error.log). A typo in a variable name stops the server. You can also check what the server would read:

my_print_defaults mysqld

Changing settings without editing files

Many variables are dynamic, and can be changed at runtime:

SET GLOBAL max_connections = 300;          -- lost at restart
SET PERSIST max_connections = 300;         -- MySQL 8+: saved to mysqld-auto.cnf, survives restarts
RESET PERSIST max_connections;             -- remove the persisted value

SET PERSIST writes to mysqld-auto.cnf in the data directory, which is read after all other option files. That’s convenient, but it can surprise you when a value in my.cnf seems to be ignored. Check performance_schema.persisted_variables.

Client credentials file

Store credentials for scripts and backups in ~/.my.cnf, readable only by its owner:

[client]
user = backup
password = "StrongPassword!"
chmod 600 ~/.my.cnf
mysqldump --all-databases > all.sql     # no password on the command line

MySQL’s mysql_config_editor can store obfuscated login paths (--login-path=backup) as an alternative.

Settings that matter most

Setting Purpose Typical starting value
innodb_buffer_pool_size Cache for data and indexes 50–70% of RAM on a dedicated DB server
innodb_redo_log_capacity (8.0.30+) Redo log size; write performance 1–4G for busy servers
max_connections Max client connections 151 default; raise with care
bind-address Network interface 127.0.0.1 unless remote access needed
slow_query_log, long_query_time Find slow queries ON, 1
character-set-server Default charset utf8mb4 (default in 8.0)
binlog_expire_logs_seconds Binary log retention e.g. 7–14 days
tmp_table_size, max_heap_table_size In-memory temp tables 64M–256M

Tune with real measurements, rather than copying huge values from the internet: oversized per-connection buffers can exhaust RAM. See MySQL performance tuning, the InnoDB buffer pool, the slow query log, and MySQL too many connections.

A sample custom file

# /etc/mysql/mysql.conf.d/zz-custom.cnf (VPS with 4 GB RAM, app and DB on the same server)
[mysqld]
bind-address              = 127.0.0.1
innodb_buffer_pool_size   = 1G
innodb_redo_log_capacity  = 512M
max_connections           = 200
slow_query_log            = ON
long_query_time           = 1
binlog_expire_logs_seconds = 604800

Quick reference

Task Command
Which files are read mysqld --verbose --help | grep -A1 'Default options'
Effective option values my_print_defaults mysqld
Current runtime value SHOW VARIABLES LIKE 'name';
Persist at runtime SET PERSIST name = value;
Custom file (Ubuntu) /etc/mysql/mysql.conf.d/zz-custom.cnf
Credentials for scripts ~/.my.cnf, mode 600

Related: installing MySQL on Ubuntu and changing the MySQL data directory.

  • MySQL
  • Database
  • Configuration