Why isn't MySQL using the my.cnf settings I've specified?
By Vince Carbone on Nov 04, 2008
You are just getting started with MySQL on OpenSolaris. You've installed the OpenSolaris Community Edition and CoolStack MySQL. To explore this new environment you decide to run some tests using the sysbench benchmark. After running a number of tests you realize that for some reason the options you are setting in /etc/my.cnf are not getting used.
What is going on? Not to worry, you've just hit a problem common to new users of MySQL on OpenSolaris.
Run this command:
# /opt/coolstack/mysql/bin/mysqladmin | more
page down until you see:
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf ~/.my.cnf
This shows the order that my.cnf option files are read. Now do a copy and paste and 'ls -l' of these files:
$ ls -l /etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf ~/.my.cnf /opt/coolstack/mysql/etc/my.cnf: No such file or directory /opt/coolstack/mysql/.my.cnf: No such file or directory -rw-r--r-- 1 mysql mysql 676 Oct 23 15:21 /etc/my.cnf lrwxrwxrwx 1 root root 10 Nov 3 09:08 /etc/mysql/my.cnf -> 5.0/my.cnf
You expect to see /etc/my.cnf, after all you put it there. But how did /etc/mysql/my.cnf get there? It's there because by default MySQL is installed in /etc/mysql when you install OpenSolaris. Due to the order that my.cnf files are read any parameter specified in /etc/mysql/my.cnf will override the same setting in /etc/my.cnf.
So what to do? Well you could just delete the /etc/mysql/my.cnf symbolic link, but the best practice we've established in the MySQL Performance and Scalability project is to explicitly specify the my.cnf file as part of MySQL startup using the --defaults-file parameter:
# mysqld_safe --defaults-file=/etc/my.cnf &
guarantees that only the configuration parameters you expect are
used by the MySQL instance you started. When using the --defaults-file
there is no restriction on the name and location of the configruation
file. This allows better sharing
of a system environment where multiple instances of MySQL are being run
with different configuration parameters.