You are here: Home / Technologies / Databases / Be sure to know your my.cnf [sections]

Be sure to know your my.cnf [sections]

by

The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.

[mysqld]
...
log-bin=mysql-bin
server-id=1
query_cache_size = 100M
query_cache_type = 1

...

[mysqld_safe]
...
key_buffer_size=600M
skip-innodb
...

In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.

mysql> show global variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+

Be sure to add the right options to the [mysqld] section.

What I didn’t know until yesterday was that some programs read from multiple groups. From the 5.1.2. Server Command Options MySQL reference manual page. In helping the describe the problem for the benefit of readers I actually learned something new myself.


mysqld reads options from the [mysqld] and [server] groups. mysqld_safe reads options from the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups. mysql.server reads options from the [mysqld] and [mysql.server] groups.

I have for example always put log-error in both the [mysqld_safe] and [mysql]d sections because both of these write different errors. Seems that is unnecessary.

Comments

  1. says

    I made it a habit to

    KK:bin kris$ ./mysql –help | egrep ‘(groups|cnf)’
    /etc/my.cnf /etc/mysql/my.cnf /opt/local/etc/mysql5/my.cnf ~/.my.cnf
    The following groups are read: mysql client

    and

    KK:bin kris$ /opt/local/libexec/mysqld –verbose –help | egrep ‘(groups|cnf)’
    /etc/my.cnf /etc/mysql/my.cnf /opt/local/etc/mysql5/my.cnf ~/.my.cnf
    The following groups are read: mysql_cluster mysqld server mysqld-5.1

    on unusual platforms (such as Windows, MacOS or Debian :) ) or self compiled systems in order to make sure that there are no bad surprises lurking anywhere.

    Also –defaults-file is your friend. Unfortunately, there is no –defaults-group. Otherwise it would be easy to disable this magic searches and point the mysqld to just one set of config, no surprises allowed.

  2. Kathy Mazur Worden says

    Thanks for the tip on different error logs being written to. I never knew those errors could be separated out.

Trackbacks