Configuring MySQL

To get MySQL to run smoothly in the way you want, you'll need to configure it in certain ways, such as to set the default table type to InnoDB or to display error messages in a certain language. You can set most options in three ways: from the command line, from a configuration file, or from a preset environment variable. Setting options from the command line is useful for testing, but it's not useful if you want to keep those options over a long period of time. Environment variables are almost never used. The most common, and the most useful method, is through a configuration file.

On Unix, the startup configuration file is usually called my.cnf and can be placed in the following locations. MySQL reads this from top to bottom, so the lower positions have a higher precedence (see Table 10.2).

Table 10.2: Precedence of the Configuration Files on Unix

File

Description

/etc/my.cnf

Global options that apply to all servers and users. If you're unsure where to put a configuration file, place it here.

DATA_ DIRECTORY/my.cnf

Options specific to the server that stores its data in the specified DATA_DIRECTORY. This is usually /usr/local/mysql/data for binary or /usr/local/var for source installations. Be warned that this is not necessarily the same as the --datadir option specified for mysqld. Rather, it's the one specified when the system was set up.

defaults-extra-file

Options specific to server or client utilities started with the --defaults-extra-file=filename command-line option.

~/.my.cnf

Options specific to the user.

On Windows, the startup configuration file is usually called my.ini or my.cnf depending on its location (see Table 10.3).

Table 10.3: Precedence of the Configuration Files in Windows (continued)

File

Description

C:WINDOWS_ SYSTEM_ FOLDER\my.ini

Global options that apply to all servers and users. The Windows system folder is usually C:WINNT\System32, C:\WINNT, or C:\WINDOWS. If you're unsure which configuration file to use, I suggest this one.

C:\my.cnf

Global options that apply to all servers and users (could just use the previous my.ini file instead).

C:\DATA_ DIRECTORY\ my.cnf

Options specific to the server that stores its data in the specified DATA_DIRECTORY (which is usually C:\mysql\data).

defaults-extra-file=filename

Options specific to server or client utilities started with the --defaults-extra-file=filename command-line option.

In Windows, if the C drive is not the boot drive, or you use the winmysqladmin utility, you have to use a my.ini configuration file (in the Windows system folder).

Note 

Windows has no configuration file for options specific to the user.

A sample configuration file follows:

# The following options will be passed to all MySQL clients [client] #password       = your_password port            = 3306 socket          = /tmp/mysql.sock # 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 #set-variable   = ft_min_word_length=3 log-bin server-id       = 1 [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 [myisamchk] set-variable    = key_buffer=20M set-variable    = sort_buffer=20M set-variable    = read_buffer=2M set-variable    = write_buffer=2M [mysqlhotcopy] interactive-timeout 

The hash (#) denotes a comment, and the square brackets ([]) are section markers. Terms inside square brackets denote which program the settings that follow will affect. In this example, the setting interactive-timeout will apply when running the program mysqlhotcopy only. The options set beneath a section marker setting apply for the previously set section, until the next section marker.

In the previous example, the first port applies to clients, and the second port applies to the MySQL server. They're usually the same, but they don't have to be (such as when you run multiple MySQL servers on the same machine).

Options come in three types:

  • option=value (such as port=3306).

  • option (such as log-bin). These are Boolean options that are not set if the option is absent (the default is used) and are set if the option is present.

  • set-variable = variable=value (such as set-variable = write_buffer=2M). This allows you to set the MySQL server variables.

Warning 

The sample configurations come with a password option for clients that's commented out. It may be convenient to connect this way, but I don't suggest doing it in most cases for security reasons. Anyone who could possibly read this file could then access MySQL.

The following programs support option files: myisamchk, myisampack, mysql, mysql.server, mysqladmin, mysqlcheck, mysqld, mysqld_safe, mysqldump, mysqlimport, and mysqlshow.

Basically, almost any option that can be used with a MySQL program from the command line can be set in a configuration file as well.

A large part of mastering MySQL is in getting the configuration just right for your situation. Later in this chapter, you'll see what the various server options mean and how you can configure them to get the most performance from MySQL. Most MySQL distributions come with four sample configurations:

my-huge.cnf For systems with more than 1GB memory mostly dedicated to MySQL.

my-large.cnf For systems with at least 512MB memory mostly dedicated to MySQL.

my-medium.cnf For systems with at least 32MB memory dedicated entirely to MySQL, or at least 128MB on a machine that serves multiple purposes (such as a dual web/database server.

my-small.cnf For systems with less than 64MB memory where MySQL cannot take up too much of the resources.

Some distributions come with just the one sample: my-example.cnf.

View the files to check the latest documentation, though; 512MB is not going to remain a "large" system forever. I suggest copying the one that comes closest to your needs to the directory you're storing it in and then making any further modifications.

Tip 

Keep a backup of your configuration file as well. If your system fails, you may lose quite a lot of time reconfiguring the server.



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net