MySQl Basics — Configuration Files

This is a continuation of the previous blog entry but now I am going to assume that you have your MySQL 5.5.8 software installed but not started – yet.

Why option files?

The MySQL server can be run with options provided on the command line and any DBA who has been around the job for a while will know –skip-grant-tables. Most instances run with too many options to input the desired options by hand over and over again. So most people use a option file to keep the configuration information.

What is the option file named?

Windows instances will have a my.ini file.

*NIX instances will have a my.cnf,

But that configuration file name can be changed on the command line.

Where are these files found?

Windows folks will should start to look in c:\Program Files\MySQL\MySQL Server 5.5. And *NIX folks should search in /etc but that may have been changed by the distribution, the installer, or who ever compiled the server.

Gee, that is kind of vague. Is there a better way?

You can use mysqld –help –verbose and you will see something similar to the following.

mysqld Ver 5.5.8 for Linux on i686 (MySQL Community Server (GPL)) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Starts the MySQL database server. Usage: mysqld [OPTIONS] Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf . . .

So this server will search /etc, /etc/mysql, /usr/etc for a my.cnf file and the home directory for .my.cnf option file. (the ~/.my.cnf file will be discussed later)

What if MySQL is already running but I can not find a my.cnf or a my.ini?

The mysqld program can take a –defaults-file= <file> option for the configuration file. You might try using ps and grep-ing for mysql to see if you can find the configuration file.

Do I really need a configuration file?

The MySQL server will use default settings and will work. But the performance not be optimal and you may not be able to use all of the functionality.

So what is in this configuration file?

The default parameters on the server are stored in the configuration file.

What are the my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf and my-innodb-heavy46.cnf files? Are these good examples of how to configure my option file?

Well, these files have not aged as well as they should have. A large system is defined as having ‘memory =512M where the system runs mainly MySQL’. Huge is a system with 1G-2G of memory! Small is <= 64M with infrequent MySQL use and medium system has between 32M and 64M shares with other programs. These days where you can buy 8GB servers off Ebay or Craigslist for a fraction of their original price, these example files are dated.

The my-innodb-heavy46 is a good place to start investigating configuration files. It was designed for a 4GB RAM, InnoDB only, transactions, few connections, and heavy queries.

For now, pick the example that is closest to your hardware and in the next installment we will delve into the basics of tuning your MySQL server.

For further reference: http://dev.mysql.com/doc/refman/5.5/en/option-files.html