Mysql – which cnf file to update

configurationenvironment-variablesMySQLmysql-8.0

I have mysql 8 running on a ubuntu 20.4 server. I want to apply some setting to optimize the database so that it uses less ram for a web application. typically switching of the performance_schema.

Which cnf file I should be adding my settings? When I run the following command I have get the following information:

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

/etc/my.cnf file doesn't exist

/etc/mysql/my.cnf contains the following:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

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

~/.my.cnf doesn't exist.

Then I have the following file /etc/mysql/mysql.conf.d/mysqld.cnf

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql
...

I've added the setting to /etc/mysql/mysql.conf.d/mysqld.cnf file but would be helpful if someone can advise which would be the correct file to add such settings?

Best Answer

By default I always do my changes at /etc/mysql/mysql.conf.d/mysqld.cnf to do not split my changes too much, although, both of those should work /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf, just don't forget restarting you mysql service.