mysql my.cnf ignored
Solution 1
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 2
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 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
On Unix platforms, MySQL ignores configuration files that are world-writable.
This is intentional as a security measure.
Solution 4
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).
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
I also ensured that my configs were not world writable, like Franco suggested
Related videos on Youtube
L.Dutch
Updated on September 18, 2022Comments
-
L.Dutch over 1 year
I wrote a macro to check the value being entered in some cells.
If the input is higher than 8 the excess is written to another cell and the input is changed to 8. If the input is lower than 8 the missing amount is written to a third cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) TA = Target.Address: R = Target.Row: C = Target.Column If C = 2 Or C = 7 Then If (R < 19 And R > 11) Or (R < 33 And R > 25) Then Hours = Cells(R, C).Value If Hours <> 0 Then If Hours > 8 Then Cells(R, C) = 8 Cells(R, C + 1) = Hours - 8 End If If Hours < 8 Then Cells(R, C + 2) = 8 - Hours End If End If End If End If End Sub
The problem is the macro is not executed when I enter the input, only when I select the cell again.
-
aash almost 12 yearsDid you checked if there is any my.cnf in /etc/?
-
mr12086 almost 12 yearsYe, no my.cnf can be found there :S - i tried adding it there after also. still nothing
-
dkaragasidis almost 12 yearsCan you verify that your mysql daemon is using
/etc/mysql/my.cnf
by runningps
? -
mr12086 almost 12 yearsSorry could you explain that further, mysql --help results in >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
-
dkaragasidis almost 12 yearsRun
ps aux | grep mysql
. Does your mysqld uses any of the--defaults-extra-file
,--defaults-extra-file
or--no-defaults
options, which point to a different configuration file? If so, the configuration directives in that file might override the changes that you made in my.cnf. -
mr12086 almost 12 yearsNope, none of the phrases you listed are present on anything returned.
-
mr12086 almost 12 yearsmysql 27769 6366 1.4 402136 59568 ? Sl Mar15 24326837:56 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --skip-grant-tables --pid-file=/var/lib/mysql/[url].pid --socket=/var/run/mysqld/mysqld.sock --port=[port] This is the only line not also present on the dev machine.
-
dkaragasidis almost 12 yearsGive it a try starting it manually by explicitly specifying your my.cnf:
sudo service mysql stop; sudo /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --skip-grant-tables --pid-file=/var/lib/mysql/[url].pid --socket=/var/run/mysqld/mysqld.sock --port=[port] --defaults-file=/etc/mysql/my.cnf
. -
mr12086 almost 12 yearsOk I have now run this.. and still no joy, the changes in my.cnf are still not being updated :S After running the starting command I got a resonable pause before I was able to type again, but no message saying server started etc..?
-
-
mr12086 almost 12 yearsI have a file named mysqld_safe_syslog.cnf containing 2 lines :[mysqld_safe] syslog
-
mr12086 almost 12 yearsI added the file, no change to my buffer pool :S
-
mr12086 almost 12 yearsAfter doing a service mysql stop.. mysql was still running with no problems.. that seems to be an issue to me? what would cause that
-
kashani almost 12 yearsCould be a number of things. Missing pid file, Mysql was started without using the service cmd, etc. I would do a quick mysqldump so you have a recent backup and then kill the mysql process. Don't kill -9 or you might lose data. Once it's down try to restart it normally. It may take a 30-60secs to shut down.
-
KillABug almost 9 years@kashani I tried adding the innodb.cnf file and also added my
thread_stack=256k
there and removed it from my.cnf,performed a restart but still did not work. Any ideas,ow to move ahead? -
KillABug almost 9 yearsThe new version does not allow this. It forces to use mysql satrt and stop options
-
KillABug almost 9 yearskashani your comments helped to resolve it. Thank you