Mysql Server system variable and state variable introduction _mysql

Server System Variables

The server maintains a number of system variables that represent its configuration. All variables have default values. You can set them on the command line or option file Setup options when the server starts. Most can be set at run time by using the SET statement.

The MYSQLD server maintains two variables. Global variables affect the global operation of the server. Session variables affect specific client connection-related operations.

When the server starts, initializes all global variables to their default values. You can change these defaults by specifying options in the options file or on the command line. After the server is started, you can change the dynamic global variable by connecting to the server and executing the SET global var_name statement. To change a global variable, you must have super permissions.

The server also maintains session variables for each client connection. Initializes a client session variable with the current value of the corresponding global variable when connected. The client can change the dynamic session variable through the SET VAR_NAME statement. Setting session variables does not require special permissions, but customers can change their own session variables without changing the session variables of other clients.

Any client accessing a global variable can see changes to the global variable. However, it affects only the clients that are connected after the change to initialize the corresponding session variable from the global variable. It does not affect the session variables of the clients that are already connected (even the clients that execute the SET global statement).

To explicitly specify whether to set global or session variables, use the Global or Sessions option:

Copy Code code as follows:

mysql> SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;
Mysql> SET Session sort_buffer_size = 10 * 1024 * 1024;

Two options are not available, the statement sets the session variable.

You can view system variables and their values through the show variables statement.

Copy Code code as follows:

Mysql> show VARIABLES;
+---------------------------------+-------------------------------------------+
| variable_name | Value |
+---------------------------------+-------------------------------------------+
| auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
....

The buffer size, length, and stack size are bytes if not otherwise specified.

More reference http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html

Server state variables

The server maintains a number of state variables that provide information about the operation. You can view these variables and their values through the show status statement:

Copy Code code as follows:

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 |
+-----------------------------------+------------+

You can reset many state variables to 0 with the flush status statement.

More reference http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html