How to Set up MySQL server configuration files

MySQL Configuration Options

You can specify startup options on the command line when you invoke the server (or client), or in an option file. MySQL client programs look for option files at startup and use appropriate options.

By default, the server uses precompiled values for its configuration variables when it runs. However, if the default values are not suitable for your environment, add runtime options to tell the server to use different values to:

Specify the locations of important directories and files
Control which log files the server writes
Override the server’s built-in values for performance-related variables (that is, to control the maximum number of simultaneous connections and the sizes of buffers and caches)
Enable or disable precompiled storage engines at server startup

You can specify runtime options when the server is started (to change its configuration and behavior) by using command-line options or an option file, or by using a combination of both. Command-line options take precedence over any settings in an option file.

To find out what options your server supports, execute the following at a shell prompt:

# mysqld --verbose --help

Note: The preceding command provides information. It does not start the MySQL server.

Reasons to Use Option Files

When you invoke the server from the command line, you can specify any of the server options listed with the –help option. However, it is more useful to list them in an option file, for several reasons:

When you put options in a file, you do not need to specify them on the command line each time you start the server. This is more convenient and less error-prone for complex options, such as those used to configure the InnoDB tablespace.
If a single option file contains all server options, you can see how the server has been configured at a glance.

MySQL programs can access options from multiple option files. Programs look for each of the standard option files and read any that exist. No error occurs if a given file is not found. To use an option file, create it as a plain text file by using an editor. To create or modify an option file, you must have write permission for it. Client programs need only read access.

Option File Groups

Options in option files are organized into groups, with each group preceded by a [groupname] line that names the group. Typically, the group name is the category or name of the program to which the group of options applies. Examples of groups include:

[client]: Used for specifying options that apply to all client programs. A common use for the [client] group is to specify connection parameters, because typically connections are made to the same server no matter which client program is used.
[mysql]and [mysqldump]: Used for specifying options that apply to mysql and mysqldumpclients, respectively. Other client options can also be specified individually.
[server]: Used for specifying options that apply to both the mysqld and mysqld_safeserver programs
[mysqld], [mysqld-5.6], and [mysqld_safe]: Used for specifying options to different server versions or startup methods

Writing an Option File

Below is a brief example of groups in an option file:

[client]
host = myhost.example.com
compress

[mysql]
show-warnings

To create or modify an option file, the end user must have write permission for it. The server itself needs only readaccess; it reads option files but does not create or modify them.

To write an option in an option file:

Use the long option format, as used on the command line, but omit the leading dashes.
If an option takes a value, spaces are allowed around the equal ( = ) sign. This is not true for options specified on the command line.

In the example shown above, note the following: 1. [client]: Options in this group apply to all standard clients. - host: Specifies the server host name - compress: Directs the client/server protocol to use compression for traffic sent over the network.

2. [mysql]: Options in this group apply onlyto the mysql client. - show-warnings: Tells MySQL to show any current warnings after each statement

3. The mysql client uses options from boththe [client] and [mysql] groups, so it would use all three options shown.

Option File Locations

MySQL server looks for files in standard locations. Standard files are different for Linux and Windows: – In Linux, use the my.cnf file. – In Windows, use the my.ini file.

There is no single my.cnf or my.ini file. The server might read multiple my.cnf or my.ini files from different locations. You can view option file lookup locations, the order in which they are read, and groups with options:

shell> mysql –-help
 ... 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
 The following groups are read: mysql client ...

The standard option files are as follows: 1. Linux: The file /etc/my.cnf serves as a global option file used by all users. You can create a user-specific option file named .my.cnf in the user’s home directory. If the MYSQL_HOMEenvironment variable is set, it searches for the $MYSQL_HOME/my.cnf file.

2. Windows:Programs look for option files in the following order: my.ini and my.cnf in the Windows C:\ directory, then the C:\Windows(or C:\WinNT) directory. However, because the Windows installation wizard places the configuration file in the directory C:\Program Files\MySQL\MySQL Server , the server also searches this directory in Windows.

3. MySQL command-line programs search for option files in the MySQL installation directory. To view the locations of options file and the order in which they are read, use the mysql command-line client with the –help option. The following command filters the output of the command:

mysqld --help --verbose 2> /dev/null | grep -A1 "Default options"

Startup Options in an Option File

To specify server options in an option file, indicate the specific options under the [mysqld] or [server]groups.

1. Logging: You can enable logging for your server by turning on the types of logs required. The following options turn on the general query log, the binary log, and the slow query log:

general_log
log-bin
slow_query_log

2. Default Storage Engine: You can specify a default storage engine different from InnoDB by using the –default-storage-engineoption.

3. System Variables: You can customize your server by setting server system variable values. For example, to increase the maximum allowed number of client connections and to increase the number of the InnoDB buffer pools from their defaults, set the following variables:

max_connections=200
innodb_buffer_pool_instances=4

4. Shared Memory: Not enabled by default on Windows. You can turn on shared memory by using the shared-memory option.

5. Named Pipes: To turn on named-pipe support, use the enable-named-pipe option.

Sample Option Files

Linux: The MySQL installation provides a sample configuration file called my-default.cnf. The my-default.cnf sample option file is in /usr/share/mysql for RPM installations or the share directory under the MySQL installation directory for TAR file installations. The my-default.cnf file is not read by MySQL programs. The install process copies the my-default.cnf sample file to /etc/my.cnf. If /etc/my.cnf already exists, it copies it to /etc/mynew.cnf instead.

Windows: The my-default.ini sample option file and my.ini are located in the MySQL installation directory.

Before you change any of the default options, make sure that you fully understand the effects that the options have on server operation.

–defaults-file=:Use the option file at the specified location.
–defaults-extra-file=:Use an additional option file at the specified location.
–no-defaults:Ignore all option files.

For example, to use only the /etc/my-opts.cnffile and ignore the standard option files, invoke the program like this:

shell> mysql --defaults-file=/etc/my-opts.cnf

If an option is specified multiple times, either in the same option file or in multiple option files, the option value that occurs last takes precedence. For more information about using option files, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/option-files.html.

Displaying Options from Option Files

You can view which options are used by programs that read the specified option groups by executing the mysql client with the –print-defaultsoption or by using the my_print_defaultsutility. The output consists of options, one per line, in the form that they would be specified on the command line. This output varies according to your option file settings.

shell> my_print_defaults mysql client
--user=myusername
--password=secret
--host=localhost
--port=3306
--character-set-server=latin1

Or (for the same output):

mysql --print-defaults mysql client

my_print_defaults accepts the following options:

–help, -?: Display a help message and exit.
**–config-file=, –defaults-file=, -c **: Read only the given option file.
**–debug=, -# **: Write a debugging log.
**–defaults-extra-file=, –extra-file=, -e **: Read this option file after the global option file, but (on Linux) before the user option file.
**–defaults-group-suffix=, -g **: Read groups with this suffix.
–no-defaults, -n: Return an empty string.
–verbose, -v: Verbose mode. Print more information about what the program does.
–version, -V: Display version information and exit.