mysql my.cnf ignored

Solution 1:

If you want to know on a linux system if your mysqld is really reading this particular file I would recommend strace:

strace -e trace=open mysqld

This will show you all the files that get opened by the mysqld process during startup. In our case:

open("/etc/ld.so.cache", O_RDONLY)      = 3
open("/lib64/libpthread.so.0", O_RDONLY) = 3
open("/lib64/libaio.so.1", O_RDONLY)    = 3
open("/lib64/libm.so.6", O_RDONLY)      = 3
open("/lib64/librt.so.1", O_RDONLY)     = 3
open("/lib64/libcrypt.so.1", O_RDONLY)  = 3
open("/lib64/libdl.so.2", O_RDONLY)     = 3
open("/usr/lib64/libssl.so.10", O_RDONLY) = 3
open("/lib64/libcrypto.so.10", O_RDONLY) = 3
open("/lib64/libc.so.6", O_RDONLY)      = 3
open("/usr/lib64/libfreebl3.so", O_RDONLY) = 3
open("/lib64/libgssapi_krb5.so.2", O_RDONLY) = 3
open("/lib64/libkrb5.so.3", O_RDONLY)   = 3
open("/lib64/libcom_err.so.2", O_RDONLY) = 3
open("/lib64/libk5crypto.so.3", O_RDONLY) = 3
open("/lib64/libz.so.1", O_RDONLY)      = 3
open("/lib64/libkrb5support.so.0", O_RDONLY) = 3
open("/lib64/libkeyutils.so.1", O_RDONLY) = 3
open("/lib64/libresolv.so.2", O_RDONLY) = 3
open("/usr/lib64/libselinux.so.1", O_RDONLY) = 3
open("/proc/filesystems", O_RDONLY)     = 3
open("/sys/devices/system/cpu/online", O_RDONLY|O_CLOEXEC) = 3
open("/sys/devices/system/cpu", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 3
open("/etc/my.cnf", O_RDONLY)           = 3
open("/etc/localtime", O_RDONLY)        = 3
open("/dev/urandom", O_RDONLY|O_NOCTTY|O_NONBLOCK) = 3
open("/proc/sys/crypto/fips_enabled", O_RDONLY) = 3

In my case it turned out that even the property was defined (query_cache_size) on my my.cnf it was ignored. This happened after and upgrade to Percona-XtraDB-Cluster-server-55.x86_64 1:5.5.34-25.9.607.rhel6.

In the end I temporarily solved it by specifying it on the command line:

/etc/init.d/mysql start --query_cache_size=0

In case of Percona Cluster (based on Galera) you have to start the first node with bootstrap: /etc/init.d/mysql bootstrap-pxc --query_cache_size=0

Solution 2:

Anything interesting in /etc/mysql/conf.d/? The version of Mysql you're using should parse my.cnf then, anything in /etc/mysql/conf.d/ in order of the config file names. In previous versions the order could be somewhat non deterministic.

Whatever value is set last in the chain should win, which might explain why your changes in my.cnf aren't updating the server; If later files are overriding your settings.

If there is nothing in /etc/mysql/conf.d/ for the hell of it create a file called innodb.cnf (won't parse anything that doesn't end in .cnf) with only these two lines and see if your innodb setting updates after a restart.

[mysqld]
innodb_buffer_pool_size = 500M

Info From Docs:

username$ mysqld --verbose --help | grep '/my.cnf' -B 1

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

and details of this are in the MySQL Docs Look under Table 4.2

It is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files.....

...MySQL makes no guarantee about the order in which option files in the directory will be read...

Any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

---

Solution 3:

I got the same problem of my.cnf being ignored, in my case the file's permissions were wrong.

It was owned by root and mode set to 600.

sudo chmod 644 my.cnf

I changed it to 644 and the problem was solved.

Important note

From MySQL Docs:

On Unix platforms, MySQL ignores configuration files that are world-writable.

This is intentional as a security measure.