Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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/
share|improve this question
    
Did you checked if there is any my.cnf in /etc/? –  adrian7 Jun 1 '12 at 10:18
    
Ye, no my.cnf can be found there :S - i tried adding it there after also. still nothing –  mr12086 Jun 1 '12 at 10:23
1  
Can you verify that your mysql daemon is using /etc/mysql/my.cnf by running ps? –  dkaragasidis Jun 1 '12 at 10:30
2  
Run 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. –  dkaragasidis Jun 1 '12 at 10:39
1  
Give 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. –  dkaragasidis Jun 1 '12 at 11:15

3 Answers 3

up vote 1 down vote accepted

Anything interesting in /etc/mysql/conf.d/ ? The version of Mysql 5.1 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
share|improve this answer
    
I have a file named mysqld_safe_syslog.cnf containing 2 lines :[mysqld_safe] syslog –  mr12086 Jun 1 '12 at 15:43
    
I added the file, no change to my buffer pool :S –  mr12086 Jun 1 '12 at 15:51
    
After doing a service mysql stop.. mysql was still running with no problems.. that seems to be an issue to me? what would cause that –  mr12086 Jun 1 '12 at 16:23
    
Could 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. –  kashani Jun 1 '12 at 16:34

Looks like your mysql instance is not running

You have mysql.server instance running which successfully runs, but it uses its own my.cnf (meaning, default my.cnf)

I am from centos background but it is same in all env i guess.

mysql.server start

Hopefully you will see the following message:

Starting MySQL. SUCCESS!

This is generally, with locked pid, dead/lock subsys..

(dont know if this really provides you any clue or help)

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.