How to Set or modify system variables in MySQL

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

 

1) method 1 for setting global variables (not recommended): Modify the parameter file and restart mysqld.

# Vi/etc/My. CNF

[Mysqld]

Wait_timeout = 10

# Service mysqld restart

However, this method is too blunt, so we should try to avoid the restart of online services.

 

2) method 2 (recommended): set the global variables in the command line, and then modify the parameter file.

To modify a global variable, you must specify "Global" or "@ Global." And have the super permission.

Mysql> set global wait_timeout = 10;

Or

Mysql> set @ Global. wait_timeout = 10;

 

Then, check whether 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 show variables is used for query, the setting does not take effect unless you log on again and view it again. this is because show variables is equivalent to show session variables. Session variables are queried, and global variables are only queried using show global variables. if you only want to modify the session variable, you can use a syntax like set wait_timeout = 10; or set session wait_timeout = 10.

Currently, only the running MySQL instance parameters are modified, but the next restart of mysqld will return to the default value, so do not forget to modify the parameter file:

# Vi/etc/My. CNF

[Mysqld]

Wait_timeout = 10

 

3) method for setting session variables: Use set in the command line to set

To modify the session variable value, you can specify "session" or "@ session. "or" @ "or" local "or" @ local. ", or do not use anything.

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 whether 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) method for converting session variables and global variables: set it in 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 default value (wait_timeout = 28800) during MySQL Compilation ):

Mysql> set wait_timeout = default;

Note that not all system variables can be set to default. If these variables are set to default, an error is returned.