mysql my.cnf ignored
The Issue
I'm trying to modify a my.cnf value on my production server but the changes aren't taking effect after a sudo service mysql restart, using an exact copy of the my.cnf (downloaded and replaced original) on my development server the changes made are visible from show variables in mysql commandline.
my.cnf is located at /etc/mysql/my.cnf
sudo find / -name my.cnf
/etc/mysql/my.cnf
So only one file exists on the entire system..
Production is ubuntu 10.04 LTS 64bit
Development is ubuntu 11.10 32bit
Mysql versions are 5.1.61 & 5.1.62 respectively.
Update 2 :
After running mysql stop and mysql status return mysql stop/waiting, if i run top -b | grep mysql
27652 root 20 0 4096 424 420 S 0 0.0 0:00.01 mysqld_safe
27769 mysql 20 0 392m 57m 7236 S 0 1.5 119116,08 mysqld
This looks like its still running and the time doesnt look good to me, but I'm now worried if i kill these/this process I wont be able to get mysql running again, and being production this is bad :S.
I realise it's probably not something that can be answered but killing these processes and then running service mysql start, will this have mysql running again? - Also, do the proccesses above have normal numbers for them?
Update:
Doesn't this imply its getting the settings from my.cnf... but not using it? So very confused right now.
At the end its got the innodb_buffer.. settings.
mysqld --print-defaults
mysqld would have been started with the following arguments:
--user=mysql --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --skip-external-locking --bind-address=127.0.0.1 --key_buffer=16M --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --myisam-recover=BACKUP --query_cache_limit=1M --query_cache_size=16M --log_error=/var/log/mysql/error.log --expire_logs_days=9 --max_binlog_size=100M --innodb_file_per_table=1 --innodb_buffer_pool_size=500M --innodb_buffer_pool_size=500M --user=mysql --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --skip-external-locking --bind-address=127.0.0.1 --key_buffer=16M --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --myisam-recover=BACKUP --query_cache_limit=1M --query_cache_size=16M --log_error=/var/log/mysql/error.log --expire_logs_days=9 --max_binlog_size=100M --innodb_file_per_table=1 --innodb_buffer_pool_size=500M --innodb_buffer_pool_size=500M
my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_file_per_table = 1
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
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
!includedirectives in option files to include other option files and!includedirto 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.inior.cnfextension.
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
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.
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