MySQL学习笔记-04_Server_Config

Server Configuration

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:
o Specify the locations of important directories and files
o Control which log files the server writes
o 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)
o 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.


Option File Groups
Examples of groups include:
o [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.
o [mysql] and [mysqldump]: Used for specifying options that apply to mysql and mysqldump clients, respectively. Other client options can also be specified individually.
o [server]: Used for specifying options that apply to both the mysqld and mysqld_safe server programs
o [mysqld], [mysqld-5.6], and [mysqld_safe]: Used for specifying options to different server versions or startup methods


Write Option File
To create or modify an option file, the end user must have write permission for it. The server itself needs only read access; it reads option files but does not create or modify them. To write an option in an option file:
o Use the long option format, as used on the command line, but omit the leading dashes.
o 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 in the slide, note the following:
o [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.
o [mysql]: Options in this group apply only to the mysql client.
-show-warnings: Tells MySQL to show any current warnings after each statement
o The mysql client uses options from both the [client] and [mysql] groups, so it would use all three options shown.


Option File Locations
The standard option files are as follows:
o 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_HOME environment variable is set, it searches for the $MYSQL_HOME/my.cnf file.
o 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 <version number>, the server also searches this directory in Windows.
o 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 comman