Optimizing Server Settings - High Performance MySQL - page 333

there are files in several places, perhaps because a previous system administrator was
confused as well. If you don't know which files your server reads, you can ask it:
$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf
This applies to typical installations, where there's a single server on a host. You can
design more complicated configurations, but there's no standard way to do this. The
MySQL server distribution used to include a now-deprecated program called mysql-
manager , which can run multiple instances from a single configuration with separate
sections. (This was a replacement for the even older mysqld_multi script.) However,
many operating system distributions don't include or use this program in their startup
scripts. In fact, many don't use the MySQL-provided startup script at all.
The configuration file is divided into sections, each of which begins with a line that
contains the section name in square brackets. A MySQL program will generally read
the section that has the same name as that program, and many client programs also
read the client section, which gives you a place to put common settings. The server
usually reads the mysqld section. Be sure you place your settings in the correct section
in the file, or they will have no effect.
Syntax, Scope, and Dynamism
Configuration settings are written in all lowercase, with words separated by under-
scores or dashes. The following are equivalent, and you might see both forms in com-
mand lines and configuration files:
/usr/sbin/mysqld --auto-increment-offset=5
/usr/sbin/mysqld --auto_increment_offset=5
We suggest that you pick a style and use it consistently. This makes it easier to search
for settings in your files.
Configuration settings can have several scopes. Some settings are server-wide (global
scope); others are different for each connection (session scope); and others are per-
object. Many session-scoped variables have global equivalents, which you can think of
as defaults. If you change the session-scoped variable, it affects only the connection
from which you changed it, and the changes are lost when the connection closes. Here
are some examples of the variety of behaviors of which you should be aware:
• The query_cache_size variable is globally scoped.
• The sort_buffer_size variable has a global default, but you can set it per-session
as well.