Why isn't MySQL using the my.cnf settings I've specified?

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 &

This 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.

Comments:

I know in 5.1.32 (not sure how far back that goes) you can also use the --print-defaults switch which will show you what options you will be getting. That can clue you into the fact you are using the wrong config file.

I don't think that --print-defaults will tell you which config file you \*do\* get, but on systems with truss(1) that's as easy as

truss -t open mysqld_safe --print-defaults

(other linux-like systems have the similar strace(1) command)
--
Quentin

Posted by Quentin Neill on May 01, 2009 at 08:02 AM PDT #

I know in 5.1.32 (not sure how far back that goes) you can also use the --print-defaults switch which will show you what options you will be getting. That can clue you into the fact you are using the wrong config file.

I don't think that --print-defaults will tell you which config file you \*do\* get, but on systems with truss(1) that's as easy as

truss -t open mysqld --print-defaults

(other linux-like systems have the similar strace(1) command)
--
Quentin

Posted by Quentin Neill on May 01, 2009 at 08:05 AM PDT #

Oops, sorry about the double post there.
Please ignore (or delete) the first one.
Running truss(1) on the 'mysqld_safe' script doesn't work unless you follow children with -f (and that gets messy). Easier to just call the mysld daemon directly in this case.
(You can delete this one as well if you like).
--
Quentin

Posted by Quentin Neill on May 01, 2009 at 08:08 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Vince Carbone

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today