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

The method of setting session,global variable by mysql

-- set or modify the validity period of the system log

SET GLOBAL expire_logs_days=8

SHOW VARIABLES LIKE'% expire_logs_days%'

-- set or modify the maximum number of connections to the system

SET GLOBAL max_connections = 2648

SHOW VARIABLES LIKE'% max_connections%'

-- modify MYSQL automatic numbering step size

SHOW VARIABLES LIKE'% auto_increment%'

SET GLOBAL auto_increment_offset = 1

SET GLOBAL auto_increment_increment = 1

For example, set the MySQL instance parameter wait_timeout to 10 seconds.

1) set global variable method 1 (not recommended): modify the parameter file, and then restart mysqld

# vi / etc/my.cnf

[mysqld]

Wait_timeout=10

# service mysqld restart

However, this method is too blunt, online service restart should be avoided as much as possible.

2) set global variables method 2 (recommended): set it through SET on the command line, and then modify the parameter file

If you want to modify a global variable, you must display the specified "GLOBAL" or "@ @ global." and you must have SUPER permission.

Mysql > set global wait_timeout=10

Or

Mysql > set @ @ global.wait_timeout=10

Then check to see if the settings are successful:

Mysql > select @ @ global.wait_timeout=10

Or

Mysql > show global variables like 'wait_timeout'

+-+ +

| | Variable_name | Value |

+-+ +

| | wait_timeout | 10 | |

+-+ +

If you use show variables when querying, you will find that the settings do not take effect unless you log in again. This is because using show variables is equivalent to using show session variables, querying session variables, and only using show global variables to query global variables. If you just want to modify session variables, you can use syntax like set wait_timeout=10; or set session wait_timeout=10;.

Currently, only the running MySQL instance parameters have been modified, but the next time you restart mysqld, you will return to the default value, so don't forget to modify the parameter file:

# vi / etc/my.cnf

[mysqld]

Wait_timeout=10

3) set the session variable method: set it through SET on the command line

If you want to modify the session variable value, you can specify "SESSION" or "@ @ session." Either "@ @" or "LOCAL" or "@ @ local." or nothing.

Mysql > set wait_timeout=10

Or

Mysql > set session wait_timeout=10

Or

Mysql > set local wait_timeout=10

Or

Mysql > set @ @ wait_timeout=10

Or

Mysql > set @ @ session.wait_timeout=10

Or

Mysql > set @ @ local.wait_timeout=10

Then check to see if the settings are successful:

Mysql > select @ @ wait_timeout

Or

Mysql > select @ @ session.wait_timeout

Or

Mysql > select @ @ local.wait_timeout

Or

Mysql > show variables like 'wait_timeout'

Or

Mysql > show local variables like 'wait_timeout'

Or

Mysql > show session variables like 'wait_timeout'

+-+ +

| | Variable_name | Value |

+-+ +

| | wait_timeout | 10 | |

+-+ +

4) session variable and global variable conversion method: set through SET on the command line

Set the session variable value to the corresponding global variable value:

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

Set the session variable value to the MySQL compile-time default (wait_timeout=28800):

Mysql > set wait_timeout=DEFAULT

Note here that not all system variables can be set to DEFAULT. Setting these variables to DEFAULT will return an error.

-- end-

References:

[1] scope and setting method of Dynamic and Not Dynamic type variables within the range of Global, Session and Both (Global & Session) in MySQL, http://blog.csdn.net/zyz511919766/article/details/13294479

[2] several methods of setting or modifying system variables in MySQL

Http://wenku.baidu.com/link?url=wvLTvtJv-B-YHLfaaCYANwq0ieBf5TDonJtXx-2cJgXI0jIhr3n_1yn4c60TnbiEBiE7F9ttv6VxpWRRwFbhY7xX3aoV75VWaw2htrCqh7m

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.