【mysql】global & session variables

时间:2015-08-30 02:08:11      作者:beebol      标签: global mysql session variables      分类: mysql

续上篇“【mysql】read_only全局系统变量”这里保留了一个问题，都认为全局变量只影响变量设置值之后的新连接，已经存在的连接是不受影响的。为什么read_only会有影响到其它已经存在的连接呢？

官方是这么说的：

 If you change a session system variable, the value remains in effect until your session ends or
 until you change the variable to a different value. The change is not visible to other clients.

If you change a global system variable, the value is remembered and used for new connections 
until the server restarts. (To make a global system variable setting permanent, 
you should set it in an option file.) The change is visible to any client that accesses that global
 variable. However, the change affects the corresponding session variable only for clients that 
connect after the change. The global variable change does not affect the session variable for anyclient that is currently connected (not even that of the client that issues the SET GLOBAL statement).
 client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

session变量的值的影响时间是直到这个session结束或者这个session变量值被改成其它值，对其它客户端是不可见的。

如果你更改了一个全局变量，该值将被记住并用于新的连接直到重新启动服务器。（使全局系统变量设置永久性，你应该把它放在一个配置文件中）对任何访问该全局的客户来说，变化是可见的。然而，变化只会影响客户的相应的会话变量连接后的变化。全局变量变化不影响会话变量当前连接的客户端。

标红的这句话是没有问题的，但正是这句话让很多人忽略了一个问题，如果一个变量仅是全局变量呢？根本就没有会话变量，如max_connections,read_only等等，大家在第一时间，都认为都有session级别的了。所以需要注意系统变量是的范围。主要可以参考下“5.1.5.2 Dynamic System Variables”，看下是global,还是global/session。

那么为什么设置全局变量后，不影响当前连接，而影响新创建的连接呢？

结果可以看如下这断话：

 The server maintains two kinds of system variables. Global variables affect the overall operation of
 the server. Session variables affect its operation for individual client connections. A given system 
variable can have both a global and a session value. Global and session system variables are related
 as follows:
    When the server starts, it initializes all global variables to their default values. These defaults
 can be changed by options specified on the command line or in an option file. (See Section 4.2.3,
 “Specifying Program Options”.)
    The server also maintains a set of session variables for each client that connects. The client's
 session variables are initialized at connect time using the current values of the corresponding
 global variables. For example, the client's SQL mode is controlled by the session sql_mode value,
 which is initialized when the client connects to the value of the global sql_mode value.