How to locate and modify MySQL and MariaDB configuration files

Correct database configuration keeps MySQL and MariaDB predictable under load, prevents accidental network exposure, and reduces the “works in dev, explodes in prod” class of surprises.

Both servers read option files (commonly my.cnf on Linux and my.ini on Windows) in a defined order and apply settings by section, such as [mysqld] for the server daemon and [client] for client tools. Package installs often add include directories so custom overrides can live in separate files while leaving vendor-managed defaults intact.

Locations and filenames vary by operating system, distribution packaging, and version, so the safest workflow confirms the actual option-file search order before editing. Invalid directives can prevent the database service from starting, and some settings are workload-specific enough to deserve a maintenance window and a rollback plan.

1. Display the option files read by the server binary.

   $ mysqld --verbose --help 2>/dev/null | sed -n '/Default options are read from the following files in the given order:/,/^$/p'
   Default options are read from the following files in the given order:
   /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
   ##### snipped #####

   If the output mentions include directories, changes placed in a later include file override earlier defaults.

2. Identify common configuration file locations by platform.

   Common locations for option files vary by packaging and version.

   Operating system	MySQL option file (common)	MariaDB option file (common)
   Linux (Debian/Ubuntu packages)	/etc/mysql/my.cnf (includes /etc/mysql/mysql.conf.d)	/etc/mysql/mariadb.conf.d/50-server.cnf (included by /etc/mysql/mariadb.cnf)
   Windows	%PROGRAMDATA%\MySQL\MySQL Server X.Y\my.ini	%PROGRAMDATA%\MariaDB\MariaDB Server X.Y\my.ini
   macOS (Homebrew)	/usr/local/etc/my.cnf or /opt/homebrew/etc/my.cnf	/usr/local/etc/my.cnf.d/mariadb.cnf or /opt/homebrew/etc/my.cnf.d/mariadb.cnf

3. Locate the include directories referenced by the installed base config.

   $ sudo grep -nE '^(\\!includedir|\\!include)' /etc/my.cnf /etc/mysql/my.cnf /etc/mysql/mariadb.cnf 2>/dev/null
   /etc/my.cnf:36:!includedir /etc/mysql/conf.d/

   On many Linux packages, the highest-numbered file in the include directory wins when the same variable is set more than once.

4. Create a dedicated override file in the server include directory.

   $ sudoedit /etc/mysql/mysql.conf.d/99-custom.cnf

   On MariaDB packages that use /etc/mysql/mariadb.conf.d, create /etc/mysql/mariadb.conf.d/99-custom.cnf instead.

5. Add the desired settings under the correct option group.

   [mysqld]
   max_connections = 300
   bind-address = 127.0.0.1
   slow_query_log = ON
   long_query_time = 2

   Server variables belong under [mysqld], while client defaults like [client] affect tools such as mysql and mysqldump.

6. Save the override file.

   Edits that change networking (bind-address) or authentication can cut off remote access if applied over SSH without console fallback.

7. Restart the mysql service to apply configuration changes on MySQL systems.

   $ sudo docker restart sg-mysql
   sg-mysql

   Restart mariadb on MariaDB systems.

8. Confirm the service is running after the restart.

   $ docker ps --filter name=sg-mysql --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
   NAMES     IMAGE       STATUS
   sg-mysql  mysql:8.0   Up 56 minutes

   If the state is failed, check recent logs.

   $ docker logs --tail 50 sg-mysql
   ##### snipped #####

9. Verify a changed value from the running server.

   $ mysql --table --user=root --password --execute "SHOW VARIABLES LIKE 'max_connections';"
   +-----------------+-------+
   | Variable_name   | Value |
   +-----------------+-------+
   | max_connections | 151   |
   +-----------------+-------+

   Equivalent verification via mysqladmin is also common.

   $ mysqladmin --user=root --password variables | grep max_connections
   ##### snipped #####

Memory tuning controls how aggressively the database caches data and how much memory each connection can consume. A small number of global caches can improve performance dramatically, while overly large per-connection buffers can trigger sudden out-of-memory events during traffic spikes.

[mysqld]
innodb_buffer_pool_size = 2G
tmp_table_size = 64M
max_heap_table_size = 64M

Networking settings determine which interfaces accept connections and how many concurrent sessions can be sustained. A safe baseline binds locally and grows outward only when a firewall and authentication plan exist.

[mysqld]
max_connections = 300
bind-address = 127.0.0.1
skip-name-resolve = ON
wait_timeout = 300

Logging supports troubleshooting and performance tuning, but it can also create load and store sensitive information. Log paths and permissions should align with system logging and backup policies.

[mysqld]
log_error = /var/log/mysql/error.log
general_log = OFF
slow_query_log = ON
long_query_time = 2

1. Size global caches based on workload and available RAM before raising concurrency limits.
2. Avoid oversized per-connection buffers when increasing max_connections.
3. Use the slow_query_log to identify queries worth indexing or rewriting.
4. Review temporary table behavior when complex sorts and joins spill to disk.