Classification and setting of database system variables

Title index

Variable classification

Variable setting

Variable classification

Variables are mainly divided into global variables and session variables. global variables are the values of some global variables that are automatically integrated when the client program establishes a session with the server, while session variables are only valid for the current variable. in addition, some global variables support online modification, some are read-only variables, and read-only variables are only allowed to load values in the configuration file.

Variable setting

1. Global variable setting

Global variables are saved in the GLOBAL_ variables table of database information_schema, so you can modify them when you load the configuration file, and some variables can also be modified online. There are usually two command methods for online modification, as follows:

Mysql > SET GLOBAL system_var_name=value

Mysql > SET @ @ global.system_var_name=value

Examples are as follows:

MariaDB [(none)] > show global variables like 'transaction_prealloc_size' +-- +-+ | Variable_name | Value | +-+-+ | transaction_prealloc_size | 4096 | +- +-+ 1 row in set (0.00 sec) MariaDB [(none)] > set global transaction_prealloc_size=8192 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show global variables like 'transaction_prealloc_size' +-- +-+ | Variable_name | Value | +-+-+ | transaction_prealloc_size | 8192 | +- +-+ 1 row in set (0.00 sec) MariaDB [(none)] > set @ @ global.transaction_prealloc_size=4096 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show global variables like 'transaction_prealloc_size' +-- +-+ | Variable_name | Value | +-+-+ | transaction_prealloc_size | 4096 | +- +-+ 1 row in set (0.00 sec)

two。 Session variable setting

Session variables are saved in the SESSION_ variables table of the database information_schema, so you can modify them when you load the configuration file, and some of the variables can also be modified online. There are usually two commands for online modification, as follows:

Mysql > SET SESSION system_var_name=value

Mysql > SET @ @ session.system_var_name=value

MariaDB [(none)] > show session variables like 'wait_timeout';+-+-+ | Variable_name | Value | +-+ | wait_timeout | 28800 | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > set session wait_timeout=30000 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show session variables like 'wait_timeout' +-+-+ | Variable_name | Value | +-+-+ | wait_timeout | 30000 | +-+-+ 1 row in set (sec) MariaDB [(none)] > set @ @ session.wait_timeout=28800 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show session variables like 'wait_timeout';+-+-+ | Variable_name | Value | +-+ | wait_timeout | 28800 | +-+-+ 1 row in set (0.00 sec)

3. Session variable to global variable

When a session variable is adjusted to a global variable, the following methods are usually used:

Mysql > set @ @ session.wait_timeout=@@global.wait_timeout