What is MySQL Configuration File?

Configuring a MySQL server is often just a matter of editing the configuration file to make any changes you need and then restarting the server. While that sounds rather simple, adjusting the server’s configuration is something you’re not likely to do on a daily basis. More likely, you’ve installed MySQL, configured it minimally or with the defaults, and then let it run. Most users never go back and adjust the server configuration until a problem arises. As a result, it’s easy to forget how to configure MySQL.

Another possibility is that you didn’t even know there was a configuration file for MySQL. For the majority of projects, MySQL’s default configuration is more than sufficient on modern hardware. It may not be as fast as it can be (because you haven’t optimized it), but it will certainly meet your basic needs.

File Locations

When MySQL starts, it reads its configuration files in a particular order, unless told otherwise. On Unix, the order is:

1. /etc/my.cnf
2. datadir/my.cnf
3. ~/.my.cnf

On Windows, the order:

1. %SystemRoot%/my.ini
2. C:\my.cnf

Three command-line arguments affect how MySQL reads its configuration files:

–no-defaults

Tells MySQL not to read any configuration files.

–defaults-file=/path/to/file

Tells MySQL to read this file only, and any other files explicitly declared with –defaults-extra-file.

–defaults-extra-file=/path/to/file

Tells MySQL to read this file after reading the /etc/my.cnf global configuration file .

Files read later in the process override those set in previously read files. If both /etc/my.cnf and datadir/my.cnf specify a value for the TCP port that MySQL should listen to, the latter takes precedence.

This behavior can be quite helpful when you need to run multiple servers either on the same host or on several different hosts. You can give all servers an identical copy of /etc/my.cnf that specifies all the values that aren’t specific to a single host. With that out of the way, the few host-specific settings can be maintained in a small supplemental file such as datadir/my.cnf.

A similar strategy works if you’d like to run multiple servers on a single host. By putting all the common settings in /etc/my.cnf and the server-specific settings in each datadir/my.cnf, it’s easy to keep several servers running with a minimum of effort.

For example, perhaps you want to run a couple different instances of the MySQL server, one for each character set you plan to use (to make your life easier). You might put all your “common” settings in /etc/my.cnf and the following in /etc/my.english.cnf:

default-character-set=latin1

port=3306

socket=/var/lib/mysql/english.sock

Your /etc/my.german.cnf file has:

default-character-set=latin1_de

port=3307

socket=/var/lib/mysql/german.sock

You might even have /etc/my.korean.cnf with:

default-character-set=euc_kr

port=3308

socket=/var/lib/mysql/korean.sock

Now, when you start up the three servers, you want each to load all the settings from the shared /etc/my.cnf file, and then get settings from one of each of the previous language-based configuration files. You can use a command like the following:

$ mysqld_safe –defaults-extra-file=/etc/my.german.cnf

$ mysqld_safe –defaults-extra-file=/etc/my.english.cnf

$ mysqld_safe –defaults-extra-file=/etc/my.korean.cnf

This command yields three different mysqld instances, running on ports 3306 through 3308, each using the language-specific configuration options mentioned in the file indicated by the defaults-extra-file switch.

MySQL is usually installed as a service on Windows. As a result, Windows users must call c:\mysql\bin\mysqld directly to pass command-line arguments.

File Format

The configuration file format consists of one or more sections, each of which may contain one or more lines. Sections begin with a name in square brackets, such as [mysqld]; this identifies the program to which the options should be applied. Each line contains a comment, a key/value pair, a set-variable directive, or a Boolean directive. Blank lines are ignored.

Two special section names can occur in each configuration file: [server] and [client]. Items listed in the [server] block apply to the MySQL server process. Those in the [client] section apply to all client programs that use the MySQL C client library, including mysql, mysqlhotcopy, and mysqldump.

Comments begin with # or ; and continue to the end of the line:

# this is a comment

; so is this

There is no multiline comment format. You can’t place a comment at the end of an otherwise non-empty line:

key_buffer=128M # a comment can't go here

The key/value pairs are settings such as:

user = mysql

port = 3306

The set-variable statements look like key/value pairs in which the value is a key/value pair itself:

set-variable = key_buffer=384M

set-variable = tmp_table_size=32M

Spaces aren’t important in set-variable lines. You can also write the two previous lines as follows:

set-variable = key_buffer = 384M

set-variable=tmp_table_size=32M

Either way, MySQL will understand you. However, consider using some space to enhance readability.

As of Version 4.1, the set-variable= portion of the variable definition is no longer needed and is deprecated. In current versions:

set-variable = key_buffer=384M

and:

key_buffer=384M

are both interpreted in an identical manner by the server at startup time. If you are running a version that supports leaving out the set-variable clause, it probably is best to do so because it won’t be supported forever. We’ve chosen to use the older format here because it’s what you’re likely to have already, and the sample configuration files in the standard MySQL distribution continue to use it.

The few boolean directives are just stated plainly:

skip-bdb

Individual lines in the configuration file are limited to 2 KB in length. While it’s rare that you’ll ever need to use a line that long, it can occasionally be a problem.

Sample Files

my-small.cnf- MySQL config file for small systems. This is for a system with little memory (<= 64M) where MySQL is only used from time to time and it’s important that the mysqld daemon doesn’t use much resources.

my-medium.cnf- MySQL config file for medium systems. This is for a system with little memory (32M – 64M) where MySQL plays an important part, or systems up to 128M where MySQL is used together with other programs (such as a web server).

my-large.cnf- MySQL config file for large systems. This is for a large system with memory = 512M where the system runs mainly MySQL.

my-huge.cnf- MySQL config file for very large systems. This is for a large system with memory of 1G-2G where the system runs mainly MySQL.

my-innodb-heavy-4G.cnf- MySQL config file for very large systems for innodb only. This is a MySQL example config file for systems with 4GB of memory running mostly MySQL using InnoDB only tables and performing complex queries with few connections.

To use a sample file, simply copy it to /etc/my.cnf (or systemdir\win.ini on Windows) and making changes as necessary. While none is likely to be ideal for any particular setup, each file is a good starting point for setting up a new system. Failure to make adjustments to the sample configuration can lead to worse performance in some cases.