Configuration Files - MySQL

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 configu ration 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 lis ten 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 sup plemental file such as datadir / my.cnf .

A similar strategy works if you’d like to run multiple servers on a single host. By put ting 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=latin 1
port=330 6
socket=/var/lib/mysql/english.soc k

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

default-character-set=latin1_d e
port=330 7
socket=/var/lib/mysql/german.soc k

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

default-character-set=euc_k r
port=330 8
socket=/var/lib/mysql/korean.soc k

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.cn f
$ mysqld_safe --defaults-extra-file=/etc/my.english.cn f
$ mysqld_safe --defaults-extra-file=/etc/my.korean.cn f

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:mysqlbinmysqld directly to pass command-line arguments.

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.