MySql server system variables and status variables-mysql tutorial

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 SET using the SET statement during running. The mysqld server maintains two variables. Global variables affect global operations on the server. Session variable shadow

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 SET using the SET statement during running. The mysqld server maintains two variables. Global variables affect global operations on the server. Session variable shadow

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 SET using the SET statement during running.

The mysqld server maintains two 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, you can change the dynamic GLOBAL variables by connecting to the server and executing the set global var_name statement. To change global variables, you must have the SUPER permission.

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. You can use the set session var_name statement 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 (or even the clients that execute the set global statement ).

To explicitly specify whether to set GLOBAL or SESSION variables, use GLOBAL or SESSION options:

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 use the show variables statement to view system VARIABLES and their values.

Mysql> show variables;
+ --------------------------------- + ------------------------------------------- +
| Variable_name | Value |
+ --------------------------------- + ------------------------------------------- +
| 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 use the show status statement 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 |
+ ----------------------------------- + ------------ +

The flush status statement can be used to reset many state variables to 0.

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