Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the meaning of Mysql system variable and state variable". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what Mysql system variables and state variables mean.

System variable what is a system variable

System variables are some of the parameters that Mysql runs for its own program. For example, through the system variable, we can specify settings such as the number of clients allowed to connect at the same time, how the client and server communicate, the default storage engine of the table, the size of the query cache, and so on.

Classification of system variables

GLOBAL: a global variable that affects the overall operation of the server.

SESSION: session variable that affects the operation of a client connection. (note: there are individuals named LOCAL in SESSION)

Note:

When the server starts, each global variable is initialized to its default values (which can be changed through the options specified on the command line or in the options file). The server then maintains a set of session variables for each connected client, which is initialized with the current value of the corresponding global variable when connecting.

Not all system variables have the scope of GLOBAL and SESSION. Some system variables have only the scope of GLOBAL, such as max_connections, which indicates the maximum number of client programs that the server program supports to connect at the same time. There are some system variables that have only the scope of SESSION, such as insert_id, which represents the initial value of a table containing an AUTO_INCREMENT column when the column is inserted. There are some system variables whose values have both GLOBAL scope and SESSION scope, such as our default_storage_engine (storage engine), and most system variables are like this.

How to view system variabl

Command: SHOW [GLOBAL | SESSION] VARIABLES [pattern matched by LIKE] (do not write GLOBAL or SESSION equals SESSION)

How to set system variables

Set the startup option, such as the command: mysqld-- default-storage-engine=MyISAM-- max-connections=10, to configure the default storage engine is MyISAM, and the maximum number of connections is 10.

Note: in Unix-like systems, startup scripts include mysqld, mysqld_safe and mysql.server, where mysqld means to start the mysql server program directly, and mysqld_safe will start a monitoring process on this basis, which will redirect the error information and other diagnostic information of the server program to a file to generate error logs. Mysql.server can also start Mysql and use the command mysql.server start, which has the same effect as mysqld_safe. Mysqld_multi is a script for starting and stopping multiple mysql server processes on a single machine.

Each MySQL program has many different options. For example, using mysql-- help, you can see the startup options supported by the mysql program, and mysqld_safe-- help can see the startup options supported by the mysqld_safe program. To see the startup options supported by mysqld is somewhat special, you need to use mysqld-- verbose-- help.

Add startup options to the my.cnf configuration file

The configuration file looks like:

[server] (specific startup options...) [mysqld] (specific startup options.) [mysqld_safe] (specific startup options.) [client] (specific startup options) [mysql] (specific startup options.) [mysqladmin] (specific startup options.)

For example, there are many groups defined in this configuration file, and the group names are server, mysqld, mysqld_safe, client, mysql, and mysqladmin. Several startup options can be defined under each group.

As configured under the server group:

[server] default-storage-engine=MyISAMmax-connections=10

Indicates that the default storage engine is MyISAM and the maximum number of connections is 10.

Set during the server program is running

Command: SET [GLOBAL | SESSION] system variable name = value or SET [@ @ (GLOBAL | SESSION).] System variable name = value (do not write GLOBAL or SESSION equals SESSION)

For example:

Statement 1: SET GLOBAL default_storage_engine = MyISAM

Statement 2: SET @ @ GLOBAL.default_storage_engine = MyISAM

Note: if a client changes the value of a system variable in the scope of GLOBAL, it will not affect the value of the system variable in the scope of SESSION of the currently connected client, but will only affect the value of SESSION of the client that is connected later.

State variable what is a state variable

The MySQL server program maintains a lot of variables about the running state of the program, which are called state variables. because the state variables are used to display the running status of the server program, their values can only be set by the server program itself, which we programmers cannot set. Similar to system variables, state variables also have the scope of GLOBAL and SESSION. For example, Threads_connected indicates how many clients are currently connected to the server, and Handler_update indicates how many rows of records have been updated.

View status variable command

SHOW [GLOBAL | SESSION] STATUS [LIKE matching pattern] (do not write GLOBAL or SESSION is equivalent to SESSION

At this point, I believe you have a deeper understanding of what "Mysql system variables and state variables mean". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.