Mysqld server system variables and status variables

Server System Variables

The server maintains many system variables that indicate their configurations. All variables have default values. You can set the options in the command line or option file when the server starts. Most of them can be used at runtime.SetStatement.

MysqldServerMaintain two types of variables. Global variables affect global operations on the server. Session variables affect operations related to client connection.

When the server is started, all global variables are initialized to the default value. You can change these default values in the options file or the options specified in the command line. After the server is started, connect to the server and runSet globalVar_nameStatement can change the dynamic global variable. To change global variables, you must haveSuperPermission.

The server also maintains session variables for each client connection. The client session variable is initialized using the current value of the corresponding global variable during connection. The customer canSet sessionVar_nameStatement to change the dynamic session variable. You do not need special permissions to set session variables, but you can only change your session variables without changing the session variables of other customers.

Any client that accesses global variables can see changes to global variables. However, it only affects the Client Connected to which the corresponding session variable is initialized from the global variable after the change. It does not affect the session variables of connected clients(Even executionSet globalStatement Client).

To explicitly specify whether to set global or session variables, useGlobalOrSessionOption:

Mysql> set global sort_buffer_size =10*1024*1024; MySQL> Set session sort_buffer_size =10*1024*1024;
 

If none of the two options exist, the statement sets the session variable.

 

You can useShow VariablesStatement to view system variables and their values.

Mysql>Show variables;+ ------------------------------- + Hour + | variable_name | value | + ----------------------------------- + hour + | auto_increment_increment |1| Auto_increment_offset |1|....
 

Otherwise, the buffer size, length, and stack size are measured in bytes.

 

More references http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#server-system-variables

 

Server Status variable

The server maintains many status variables that provide operation-related information. You can useShow statusStatement to view these variables and their values:

Mysql> Show status; + --------------------------------- + ------------ + | Variable_name | value | + ----------------------------------- + ------------ + | aborted_clients | 0 | Aborted_connects | 0 | Bytes_received | 155372598 | Bytes_sent | 1176560426 | ... | Connections | 30023 | Created_tmp_disk_tables | 0 | Created_tmp_files | 3 | Created_tmp_tables | 2 | ... | Threads_created | 217 | Threads_running | 88 | Uptime | 1389872 | + ----------------------------------- + ------------ +

UseFlush statusStatement can reset many state variables0.

More http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#server-status-variables