1.2 Configuration Files

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.

1.2.1 File Locations

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

On Windows, the order:

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.

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

1.2.3 Sample Files

The support-files directory of the MySQL distribution^[2] contains four sample configuration files:

^[2] These files aren't included in the Windows distribution of older MySQL releases.

• my-small.cnf

• my-medium.cnf

• my-large.cnf

• my-huge.cnf

The names of the files are meant to signify the size of the machine on which the MySQL server will run. Each contains comments describing where the size comes from. For example, my-medium.cnf says:

# This is for a system with little memory (32M - 64M) where MySQL plays

# a important part and systems up to 128M very MySQL is used together with

# other programs (like a web server)

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.

Let's look at the sample my-medium.cnf file from a newly installed system. Some of the information may not make sense right away (depending on how much experience you have), but the more examples you see, the more you'll begin to understand them.

The file starts with some helpful comments about the type of system this configuration is appropriate for and information needed to install it:

# Example mysql config file for medium systems.

#

# This is for a system with little memory (32M - 64M) where MySQL plays

# a important part and systems up to 128M very MySQL is used together with

# other programs (like a web server)

#

# You can copy this file to

# /etc/mf.cnf to set global options,

# mysql-data-dir/my.cnf to set server-specific options (in this

# installation this directory is /usr/local/mysq/var) or

# ~/.my.cnf to set user-specific options.

#

# One can in this file use all long options that the program supports.

# If you want to know which options a program support, run the program

# with --help option.

Next are the options that apply to all the client tools you might run on this host:

# The following options will be passed to all MySQL clients

[client]

#password       = your_password

port            = 3306

socket          = /tmp/mysql.sock

What follows next are the parameters specific to the server. The port and socket options, of course, should agree with what the clients were just told. The remaining settings allow MySQL to allocate more RAM for various caches and buffers as well as enable some basic replication options:

# Here follows entries for some specific programs



# The MySQL server

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

set-variable    = key_buffer=16M

set-variable    = max_allowed_packet=1M

set-variable    = table_cache=64

set-variable    = sort_buffer=512K

set-variable    = net_buffer_length=8K

set-variable    = myisam_sort_buffer_size=8M

log-bin

server-id       = 1

Next are a few options you probably don't need to change if you have sufficient disk space:

# Point the following paths to different dedicated disks

#tmpdir         = /tmp/         

#log-update     = /path-to-dedicated-directory/hostname

The BDB options refer to the BDB storage engine, which provide MySQL's first transaction-safe storage. You'll learn more about storage engines in Chapter 2.

# Uncomment the following if you are using BDB tables

#set-variable   = bdb_cache_size=4M

#set-variable   = bdb_max_lock=10000

InnoDB, another of MySQL's storage engines, has numerous options that must be configured before you can use them. Because it provides transaction-safe tables with its own memory management and storage system, you need to specify where the data files will live, as well as how much RAM should be used. (InnoDB was briefly known as Innobase, so you may see that name in configuration files.)

# Uncomment the following if you are using Innobase tables

#innodb_data_file_path = ibdata1:400M

#innodb_data_home_dir = /usr/local/mysql/var/

#innodb_log_group_home_dir = /usr/local/mysql/var/

#innodb_log_arch_dir = /usr/local/mysql/var/

#set-variable = innodb_mirrored_log_groups=1

#set-variable = innodb_log_files_in_group=3

#set-variable = innodb_log_file_size=5M

#set-variable = innodb_log_buffer_size=8M

#innodb_flush_log_at_trx_commit=1

#innodb_log_archive=0

#set-variable = innodb_buffer_pool_size=16M

#set-variable = innodb_additional_mem_pool_size=2M

#set-variable = innodb_file_io_threads=4

#set-variable = innodb_lock_wait_timeout=50

The final option groups are for specific MySQL command-line utilities, including the mysql shell:

[mysqldump]

quick

set-variable    = max_allowed_packet=16M



[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates



[isamchk]

set-variable    = key_buffer=20M

set-variable    = sort_buffer=20M

set-variable    = read_buffer=2M

set-variable    = write_buffer=2M



[myisamchk]

set-variable    = key_buffer=20M

set-variable    = sort_buffer=20M

set-variable    = read_buffer=2M

set-variable    = write_buffer=2M



[mysqlhotcopy]

interactive-timeout

That file would be considerably larger and certainly more confusing if all the possible settings were listed. For 90% (or more) of MySQL users, there is simply never a need to adjust more than a few of the settings listed in the sample files.

1.2.4 Reconfiguration

When an administrator adjusts the server parameters, it's common to go through an iterative process that involves making changes, restarting the server, performing some tests, and repeating the process. In fact, we'll look at doing just that in Chapter 3. In the meantime, it's worth mentioning that you should strongly consider putting your MySQL configuration files into some sort of revision control system (RCS, CVS, Subversion, etc.). Doing so gives you an easy way to track changes and back out of a bad configuration change.

As of MySQL 4.0, it's possible to change server variables on the fly at runtime. For example, if you wanted to increase the size of the key buffer from what it was set to at startup, you might do the following:

mysql> SET GLOBAL key_buffer=50M;

This sets the global value for key_buffer to 50 MB.

Some variables, such as sort_buffer_size, can be set globally so that they affect all new threads on the server, or they can be defined so that they apply only to the current MySQL client session. For example, if you wish to make a series of queries that might better use a large sort buffer, you can type:

mysql> SET SESSION sort_buffer_size=50M;

Variables set using the SESSION syntax are thread-specific and don't alter the values other threads use.

It's important to note that any change you make here, using either GLOBAL or SESSION syntax, will not survive a restart of the MySQL server; it's completely transient in that regard. Runtime changes like this are excellent for testing scenarios such as, "If I increase my key_buffer value, will it improve my query performance?" Once you've found a value that works for you, though, remember to go back to your /etc/my.cnf file and put that value into your configuration file, or you may find yourself wondering weeks or months later why performance was so horrible after that reboot, completely forgetting the variable change you made on the fly months prior.

It's also possible to use arguments on the mysqld_safe command line to override values defined in the configuration files. For example, you might do something like the following:

$ mysqld_safe -O key_buffer=50M

Like the earlier set-variable syntax, the -O syntax is deprecated as of Version 4.0. Here is a better way to issue that command:

$ mysqld_safe --key_buffer=50M

Command-line argument changes made in the mysql.server startup script will, obviously, survive from server restart to server restart, as long as that startup script is used to disable and reenable the server. It's important to point out, though, that it's usually better to have all your configuration declarations in a single place, so that maintenance doesn't become a game of hide-and-seek with the configuration options, trying to remember where you set which values.