MySQL parameter related concepts and query change methods

Preface:

In some previous articles, we often see the introduction of the functions of certain parameters. Some friends may still not understand what MySQL parameters are. In this article, let’s talk about MySQL parameters and learn how to manage and maintain MySQL parameters.

1.MySQL parameter concept

The parameters we are talking about are called system variables in official documents. Different variables have different functions. The MySQL server maintains many system variables that represent its configuration, all of which have default values. They can usually be set on the startup command line or in a configuration file.

System variables are divided into global system variables (global) and session system variables (session). Some variables are both global and session variables, and some are only global variables. Global variables affect the global operation of the server, while session variables only affect specific client connection-related operations. If a session variable is not set individually, it is inherited from the corresponding global variable.

When the MySQL service is started, global variables are assigned values ​​according to the options specified in the configuration file or command line. If no options are specified, the default values ​​are used. After the service is started, you can dynamically change the values ​​of some global variables by connecting to the server and executing the SET GLOBAL var_name statement. To change global variables, you must have the SUPER privilege. MySQL also maintains session variables for each client connection, and initializes client session variables with the current values ​​of corresponding global variables when connecting. The client can dynamically change session variables through the SET SESSION var_name statement. Setting session variables does not require special permissions, but session variables only apply to the current connection.

2. Parameter query and change examples

It should also be noted here that not all parameters can be modified dynamically. Some parameters can only take effect by writing them into the configuration file and restarting the database. Next we will demonstrate the query and modification of MySQL parameters.

# Parameter query show global variables like 'var_name'; //View the value of global system variables. You can use the % wildcard show session variables like 'var_name'; //View the value of session system variables show variables like 'var_name'; //Prioritize returning session system variables. If the session system variables do not exist, return global system variables.

# You can also use select to query a specific parameter select @@global.var_name; //Global system variable select @@session.var_name; //Session system variable select @@var_name; //Priority session system variable # Query example mysql> show global variables like 'server_id';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| server_id | 1003306 |
+---------------+---------+
1 row in set (0.00 sec)

mysql> show global variables like 'log_bin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/logs/binlog |
| log_bin_index | /data/mysql/logs/binlog.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------------------------------+
5 rows in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|1003306|
+-------------+
1 row in set (0.00 sec)

# Dynamically modify parameters set global var_name = value;
set session var_name = value;
set var_name = value;

set @@global.var_name = value;
set @@session.var_name = value;
set @@var_name = value;

# Parameter modification example mysql> set global sort_buffer_size = 2097152;
Query OK, 0 rows affected (0.00 sec)

mysql> set session sort_buffer_size = 4194304;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.sort_buffer_size,@@session.sort_buffer_size;
+---------------------------+----------------------------+
| @@global.sort_buffer_size | @@session.sort_buffer_size |
+---------------------------+----------------------------+
| 2097152 | 4194304 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

After the parameters are dynamically modified, it is recommended to write them to the configuration file. Because dynamically modified parameters will become invalid after the MySQL service is restarted, they can only be effective after the restart if they are written into the configuration file. For some parameters that cannot be modified dynamically, we can only make them take effect by modifying the configuration file and then restarting. The configuration file mentioned here is the my.cnf file, which is usually located in the /etc directory on Linux systems and in the basedir directory on Windows systems. It can be named my.ini. Most parameters need to be configured under [mysqld]. An example of a simple configuration file is as follows:

vi /etc/my.cnf
# The simple template is as follows:
[mysqld]
user = mysql    
datadir = /data/mysql/data        
socket = /data/mysql/tmp/mysql.sock
pid-file = /data/mysql/tmp/mysqld.pid  
skip_name_resolve = 1
max_connections = 2000
lower_case_table_names = 1
log_timestamps=SYSTEM
max_allowed_packet = 32M
...

MySQL parameters are generally maintained by DBAs or operation and maintenance personnel, and some students may be unfamiliar with these parameters. It is recommended to set the corresponding database parameters according to the server specifications when initializing the database. For parameters whose functions are unclear, it is recommended to keep the default values. For more information about parameters, please refer to the official documentation:

https://dev.mysql.com/doc/refman/5.7/en/server-system-variable-reference.html

Summarize:

This article describes in detail the concepts related to MySQL parameters and query change methods. I hope that everyone can learn relevant knowledge.

The above is the detailed content of MySQL parameter-related concepts and query change methods. For more information about MySQL parameters, please pay attention to other related articles on 123WORDPRESS.COM!