Why MySQL doesn't use settings specified in my.cnf?

Solution 1:

I had a similar problem after I upgraded to MySQL 5.5.14, my query cache and innodb buffer pool settings would not take effect on the server, but everything I checked on the server showed that the config file was being loaded, just found the solution here:

MySQL ignoring my.cnf

Seems that grouping items in [square brackets] are actually commands and not comments, make sure to put your MySQL config options under the [mysqld] group, and don't make your own up like I did to group your config settings, use # at the beginning of the line to comment.

Use: mysql --print-defaults after restarting to see that your settings have taken effect.

Hope this solves your problem

Solution 2:

If you installed it through apt-get then, the last my.cnf read is in /etc/mysql/my.cnf. The one you showed us (/etc/my.cnf) is read first, but it's overwritten later by /etc/mysql/my.cnf . So, if you put some directive causing troubles, will be found first, but if you write some other, maybe overwritten later.

You can also use --print-defaults options to get the default options that were loaded. Check this page too: http://dev.mysql.com/doc/refman/5.1/en/option-files.html

EDIT:

Sorry, I think confused things a little bit. My English is not good. Just try to do what you wanted to do in /etc/mysql/my.cnf

Solution 3:

I was stuck with this problem for about a day in my case, on Ubuntu 18.04, MySQL 5.6 was not following symlinks. (Why I don't really know).

So ensure that your my.cnf file is not a symlink. To check this, you can use ls -al, here is some sample output

lrwxrwxrwx   1 root root    24 Oct  3  2017 my.cnf -> /etc/alternatives/my.cnf

It should look like:

-rw-r--r--   1 root root  1028 Feb 15 14:43 my.cnf

In my environment, I had /etc/mysql/my.cnf which was symlinked to, /etc/alternatives/my.cnf, which was then symlinked to /etc/mysql/my.cnf.fallback

So I ran the following commands in /etc/mysql/:

sudo mv my.cnf my.cnf.bak
sudo cp my.cnf.fallback my.cnf

Note: If you use this workaround, upgrading mysql via apt get may fail because it expects my.cnf.fallback file to exists. To fix this, I used a symlink to point my.cnf.fallback to point to the main config file. e.g:

sudo ln -s my.cnf my.cnf.fallback