Understanding the MariaDB configuration and data files

In this lesson you will learn how MariaDB handles configuration and data files. You'll explore the configuration hierarchy on Ubuntu, understand which INI-style sections to use, and try a safe example of changing a server setting.

You'll also verify that MariaDB's socket location matches PHP's defaults, and see where the data directory lives on disk for backups, migrations, and disaster recovery.

MariaDB configuration

The main MariaDB configuration file lives in /etc/mysql/my.cnf which on modern Ubuntu and Debian systems is a symlink to /etc/mysql/mariadb.cnf. This file includes all the important configuration files from the /etc/mysql/mariadb.conf.d directory.

MySQL and MariaDB use INI-style configuration, where the sections correspond to a specific context, program, or even version. For example:

• [server] and [mysqld] apply to all server variants
• [mariadb] applies specifically to MariaDB (ignored by MySQL)
• [mariadb-10.11] is read by version 10.11 of MariaDB
• [client] applies to all MySQL and MariaDB clients
• [mariadb-client] for MariaDB clients only
• [mysqldump], [mysqlcheck], [mysqlimport] sections apply only when running these specific utilities

This is not a full list of sections, but it's good to know these variants exist, especially when working through MariaDB upgrades in the future. For most common configuration I recommend keeping things simple and sticking to only [mysqld] and [client] sections, which give you some compatibility and portability.

Unlike PHP and Nginx, most MariaDB configuration settings will be global, and apply to all databases on your server. This is perfectly fine, even for a large number of WordPress sites on the same server. However, if you're looking for per-site configuration, you'll need to run separate instances, something we'll look at closer in our advanced modules.

Changing server settings

Let's create a new configuration file in our config repository for MariaDB and call it /config/mysql/global.cnf. Here's the full contents of this file:

[mysqld]
max_statement_time = 30

[client]

We have a mysqld section with a max_statement_time set to 30 seconds, and an empty client section for future use. By default, MariaDB and MySQL do not impose any time limits on queries, which means your query can basically run forever, until it's explicitly killed.

While this may seem like a good and flexible idea, it's not great for performance. I like to limit all my queries at 30 seconds. If something is taking longer than that, I want it to fail and free up system resources for other queries to succeed faster.

If you are expecting to run longer queries, for big and complex reports for example, feel free to use a higher number, but I don't recommend using the default 0 or infinity.