Tuesday Nov 04, 2008

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.

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