MySql server system variables and status variables-mysql tutorial

Source: Internet
Author: User
Tags 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.