mysql 配置文件

1 mysqld的配置文件my.cnf

mysqld --verbose --help | less。显示mysqld的帮助文件

Usage: mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-5.5

2 mysql的配置文件

 

3 配置文件加载的一些事项

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.

If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.

On Unix platforms, MySQL ignores configuration files that are world-writable.This is intentional as a security measure.

4 配置文件格式

先来看看命令行输入的选项格式：

a Option names are case sensitive. -v and -V are both legal and have different meanings.

b For a long option that takes a value, separate the option name and the value by an “=” sign. For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent.However, to password, for the short form, if the password value is given, it must follow the option letter with no intervening space. 

c Within option names, dash (“-”) and underscore (“_”) may be used interchangeably. For example, --skip-grant-tables and --skip_grant_tables are equivalent. (However, the leading dashes cannot be given as underscores.)

d For options that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:
shell> mysqladmin --count=1K --sleep=10 ping

d Option values that contain spaces must be quoted when given on the command line.

e Multiple SQL statements may be passed in the option value on the command line, separated by semicolons:

再来看配置文件选项格式

a The syntax for specifying options in an option file is similar to command-line syntax. However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line.For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines in an option file.

b 其他需要注意的地方

 

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively. The [client] option group is read by all client programs (but not by mysqld).

Suppose that you want to specify a base directory of C:\Program Files\MySQL\MySQL Server 5.6 in an option file. This can be done several ways. Some examples:

c 包含其他文件

For example, to include the /home/mydir/myopt.cnf file, use the following directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive:

!includedir /home/mydir

Note：
Any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

5 实例

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M
[mysqldump]
quick

Here is a typical user option file:

[client]
# The following password will be sent to all standard MySQL clients
password="my_password"
[mysql]
no-auto-rehash
connect_timeout=2
[mysqlhotcopy]
interactive-timeout

The following group indicates that the sql_mode setting should be used only by MySQL servers with 5.6.x version numbers:

[mysqld-5.6]
sql_mode=TRADITIONAL