Friday, 16 May 2014

MySQL Configuration Files

MySQL supports three different configuration files, one for global settings, one for server specific settings, and an optional one for user-customised settings.

MySQL Global Settings

The /etc/my.cnf configuration file is used for global settings applied to both clients and servers. The /etc/my.cnf file provides information such as the data directory (/var/lib/mysql) and the log file (/var/log/mysql.log) locations, as well as the server base directory (/var/lib).

Options are specified according to different groups, usually the names of server tools, and are arranged in group segments. The group name is specified within square brackets, followed by the options.

For example:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The above example specifies the options for the daemon mysqld, server-options mysql.server and the MySQL startup script safe_mysqld. Database files will be placed in /var/lib/mysql. MySQL will run as the mysql user. Server tools and daemons are located in the basedir directory, /var/lib.

To see what options are currently set, you can run mysqld with the --help option
/usr/libexec/mysqld --help

MySQL Server Settings

The /var/lib/mysql/my.cnf file is used for server settings only.

MySQL User Customised Settings

The .my.cnf file allows users to customise their access to MySQL. It is located in a user's home directory.

This file contains user configuration settings such as the password used to access the database and the connection timeouts.

[client]
password=mypassword

[mysql]
no-auto-rehash
set-variable = connect_timeout=2

[mysql-hotcopy]
interactive-timeout