MariaDB and MySQL in production: installation, files and my.cnf

Objective

Install a MariaDB or MySQL server the way it is done in production: the right version, the right repository, settings in your own files, a system prepared for a database, and the certainty of knowing which value the server really kept.

Key points

• A production database is a life cycle: you choose an LTS version (long-term support) and follow its minor bug-fix releases.
• The files to know: the datadir (/var/lib/mysql), the socket (/run/mysqld/mysqld.sock), the error log, the my.cnf files.
• my.cnf is read in a precise order, with inclusions; for a repeated option, the last value wins.
• You never modify the package's files: you add your own file (90-production.cnf) and check the effective value.
• The server is also tuned around the database: systemd limits, swap, a dedicated volume mounted with noatime.

1. The job: what the DBA adds to "apt install"

The Debian, Red Hat and Ubuntu courses showed you how to install MariaDB, create a database and an account. Here, you take the place of the person who is accountable for the database: it must last for years, absorb the load of the MasterLinux shop on the evening of the sales, be backed up, replicated, monitored and upgraded without loss.

Questions to ask yourself before installing:

2. Install and locate the files

apt install mariadb-server mariadb-backup        # Debian / Ubuntu
dnf install mariadb-server mariadb-backup        # Rocky / Alma (MySQL: dnf install mysql-server)

systemctl enable --now mariadb
mariadb -e "SELECT VERSION(), @@datadir, @@socket, @@log_error;"

The commands have been renamed: mariadb (client), mariadb-dump, mariadb-admin, mariadb-backup, mariadb-binlog. The old mysql* names still exist as links on many systems, but they are gradually disappearing.

3. How my.cnf is read

On Debian, /etc/mysql/mariadb.cnf looks like this:

[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Three rules to remember:

1. Sections (groups) say who reads the option: [mysqld], [server], [mariadb], [mariadb-11.8] (this version only) for the server; [client], [mysql], [mariadb-dump] for the tools.
2. !includedir reads the files of the directory in alphabetical order, and only those ending in .cnf.
3. For a repeated option, the last one read wins. A forgotten 99-vieux.cnf beats your 90-production.cnf.

To know what the server will read, without restarting it:

my_print_defaults --mysqld          # all the server options, in reading order
mariadbd --print-defaults           # same thing, as seen by the server binary
mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS go, @@max_connections;"   # CURRENT value

In the lab, the command cnf-effectif <option> gives the final value the server would have at startup (the equivalent of SELECT @@option).

Dynamic and static variables

Some settings can be changed on the fly:

SET GLOBAL max_connections = 300;            -- immediate, but lost at restart
SET GLOBAL innodb_buffer_pool_size = 10*1024*1024*1024;   -- online resizing (InnoDB)

Others require a restart (innodb_log_file_size on older versions, bind_address…). Operations rule: every change made on the fly is also written to the file, otherwise it disappears at the next restart. MySQL 8 offers SET PERSIST, which writes to mysqld-auto.cnf in the datadir; it is handy, but the whole team must know about this file, otherwise nobody understands where a value comes from.

4. The settings that matter for a dedicated server

# /etc/mysql/mariadb.conf.d/90-production.cnf — ml-db-01, 16 GB of RAM, dedicated
[mysqld]
bind_address                   = 10.10.20.11   # the private network address, never 0.0.0.0 without a firewall
skip_name_resolve              = ON            # no reverse DNS on each connection (accounts by IP)
max_connections                = 300

innodb_buffer_pool_size        = 10G           # 60 to 70% of the RAM of a DEDICATED server
innodb_log_file_size           = 2G            # redo log: large enough to smooth out writes
innodb_flush_log_at_trx_commit = 1             # 1 = no committed transaction lost in a crash
innodb_file_per_table          = ON            # one file per table (default)

• Buffer pool: the cache of InnoDB pages. Too small, everything goes to disk; too large, the server swaps or is killed by the OOM killer. Leave RAM for the system, for the connections (each one uses buffers) and for the file cache.
• innodb_flush_log_at_trx_commit: 1 is the only setting that guarantees durability. 2 or 0 are faster but can lose the last second of transactions in a power cut: keep them for a database you can rebuild.
• Character set: utf8mb4 everywhere (MySQL's old utf8 only stores 3 bytes and breaks emojis and some characters).

5. Prepare the system

# /etc/systemd/system/mariadb.service.d/limites.conf
[Service]
LimitNOFILE=65535        # a table = open files; the default 1,024 is not enough
TimeoutStopSec=900       # leave time to flush a large buffer pool at shutdown

systemctl daemon-reload && systemctl restart mariadb
cat /proc/$(pidof mariadbd)/limits | grep 'open files'

If systemd kills the server after 90 seconds during shutdown, the database restarts in crash recovery: longer, and worrying on a maintenance evening.

echo 'vm.swappiness = 1' > /etc/sysctl.d/60-mariadb.conf && sysctl --system   # swap as a last resort

The datadir on a dedicated volume (LVM): the database does not fill /, you can grow the volume without touching the system, and you can take a snapshot. noatime avoids a write on every file read.

/dev/vg_bdd/lv_mysql  /var/lib/mysql  xfs  defaults,noatime  0 2

By version

Common pitfalls

• Modifying 50-server.cnf: it is replaced, or triggers a question, at every package update. Your 90-… file remains yours.
• An old file that wins: a 99-…cnf or a zz-…cnf from a former contractor cancels your settings. Always check the effective value.
• Renaming to .bak the wrong way round: fichier.cnf.bak is no longer read (good), but fichier.bak.cnf still is.
• Buffer pool at 90% of the RAM on a server that also runs PHP or Java: the kernel kills mariadbd in the middle of the day.

Real case

One Friday evening, the MasterLinux shop becomes very slow. Yet the server has 16 GB of RAM, and the team "set the buffer pool to 10 GB" in 90-production.cnf six months earlier. SELECT @@innodb_buffer_pool_size answers… 256 MB. A 99-optimisation-2023.cnf file, copied from an old server, was read last. The whole dataset was going through the disk. The fix took two minutes; the lesson remains: check the effective value after every change.

Lab

On a Debian 13 machine that mimics the MariaDB 11.8 file tree, you will:

1. write your production settings file;
2. find out why the server would not keep them, and fix it without deleting anything;
3. add the systemd override;
4. tune the swap;
5. declare the dedicated data volume in /etc/fstab.

The commands my_print_defaults --mysqld and cnf-effectif <option> show you what the server would read.

Quiz

1. In a directory included by !includedir, in which order are the files read, and which ones?
2. Two files define max_connections: which one wins?
3. Why innodb_flush_log_at_trx_commit = 1 in production?
4. What share of the RAM should you give the buffer pool on a dedicated server, and why not more?
5. What happens if systemd kills MariaDB during a shutdown that takes too long?