View and settings for MySQL variables (parameters)

Similar to Oracle's parameter file, MySQL's option file (such as MY.CNF) is used to configure the MySQL server, but unlike Oracle, in MySQL, the official call variable (varialbes), but actually called the parameter is also OK, Just understand that these two are the same thing.

The MySQL variable is divided into the following two types:

1 System Variables: Configure the operating environment of the MySQL server, you can use show variables view

2 state variable: monitor the running state of the MySQL server, you can view it with show status

System variables

The system variables can be divided into the following two types depending on their scope:

1 is divided into global level: effective for the entire MySQL server

2 sessions (session or local) level: affects only the current session

While some variables have at least two levels, MySQL initializes session-level variables with global-level variables when the connection is established, but once the connection is established, the changes to the global-level variables do not affect the conversation-level variables.

To view the value of a system variable

You can view the value of a system variable through the show Vairables statement:

Mysql> Show variables like ' log% ';

Mysql> Show variables where variable_name like ' log% ' and value= ' on ';

Note: Show variables first displays the value of the session-level variable, and if that value does not exist, the value of the global-level variable is displayed, but you can also add the global or Session keyword distinction:

Show global variables;

Show session/local variables;

When you write some stored procedures, you may need to refer to the values of the system variables, and you can use the following methods:

@ @GLOBAL. var_name  
@ @SESSION. var_name or  
@ @LOCAL. var_name

If there is no level qualifier before the variable name, the session-level value is displayed first.

The last method to view the value of a variable is obtained from the Global_variables and Session_variables tables in the INFORMATION_SCHEMA database.

Set and modify values for system variables

When the MySQL server starts, there are two ways to set the value of the system variable:

1 command line parameters, such as: Mysqld--max_connections=200

2) option file (MY.CNF)

After the MySQL server is started, if you need to modify the value of the system variable, you can use the SET statement:

SET GLOBAL var_name = value;  
SET @ @GLOBAL. var_name = value;  
SET session var_name = value;  
SET @ @SESSION. var_name = value;

If there is no level qualifier before the variable name, the session-level variable is modified.

Note: Unlike startup, variables set at run time do not allow the suffix letter ' K ', ' M ', etc., but can be used to achieve the same effect, such as:

SET GLOBAL read_buffer_size = 2*1024*1024

State variables

State variables allow us to keep abreast of the health of the MySQL server and can be viewed using the show status statement.

The state variable is similar to the same variable, is also divided into global level and session level, show status also supports like matching query, the larger difference is that state variables can only be set up and modified by the MySQL server itself, for the user is read-only, can not set and modify them through the SET statement.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/