Home arrow MySQL arrow Page 4 - Back to Basics

Configuration Files - MySQL

This chapter from High Performance MySQL by Jeremy Zawodny and Derek J. Balling. (O'Reilly Media, ISBN: 0-596-00306-4, April 2004) talks about binary distributions, the sections in a configuration file, and some SHOW commands that provide a window into whatís going on inside MySQL. This book is for the MySQL administrator who has the basics down but realizes the need to go further.

  1. Back to Basics
  2. Binary Versus Compiled From Source Installations
  3. MySQL.com Binary Versus Distribution Binary
  4. Configuration Files
  5. File Format
  6. Sample Files
  7. Reconfiguration
  8. The SHOW Commands
By: O'Reilly Media
Rating: starstarstarstarstar / 19
June 08, 2004

print this article



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:


Tells MySQL not to read any configuration files.


Tells MySQL to read this file only, and any other files explicitly declared with --defaults-extra-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.

Buy the book!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.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: