MySQL中如何浅谈interactive_timeout和wait_timeout

本篇文章给大家分享的是有关MySQL中如何浅析interactive_timeout和wait_timeout，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
 
一 前言
  这篇文章源于自己一个无知的提问，作为一个DBA 老鸟，实在汗颜 。如图，修改wait_timeout参数之后 并没有及时生效，于是乎去跑到技术支持群里问了。。ps 应该去查g.cn 才对。。
 MySQL中如何浅析interactive_timeout和wait_timeout
  本文通过测试我们要弄清楚两个问题
  a 继承关系 wait_timeout在session和global级别分别继承那个参数？
  b 生效参数 在会话中到底哪个参数决定了会话的存活时间？
 
二 参数介绍  
首先说明两个关键词 通过MySQL 客户端连接db的是交互会话，通过jdbc等程序连接db的是非交互会话。
interactive_timeout: MySQL服务器关闭交互式连接前等待的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。参数默认值：28800秒（8小时）
wait_timeout:          MySQL服务器关闭非交互连接之前等待的秒数。在会话启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型--由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义。参数默认值：28800秒（8小时）
2.1 继承关系
1) 单独设置global级别的interactive_timeout  
 
set global interactive_timeout = 300
session1 [RO] 09:34:20 >set global interactive_timeout=300;
Query OK, 0 rows affected (0.00 sec)
session1 [RO] 09:39:15 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)
session1 [RO] 09:39:21 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)
登陆另外一个会话
session2 [RO] 09:39:35 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)
session2 [RO] 09:39:51 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 300            |
+---------------------+----------------+
2 rows in set (0.00 sec)
分析
  在交互模式下，session和global级别的 interactive_timeout 继承了 interactive_timeout global的值。而 wait_timeout 的值，session级别继承了interactive_timeout。global级别的wait_timeout 则不受影响 。
2) 设置session级别的 interactive_timeout
 
session1 [RO] 09:44:07 >set session interactive_timeout=300;
Query OK, 0 rows affected (0.00 sec)
session1 [RO] 09:44:27 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)
session1 [RO] 09:44:31 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)
另外开启一个会话
session2 [RO] 09:44:41 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 28800 |
+---------------------+----------------+
 
3) 同时设置两者的值，且不同。
 
session1 [RO] 09:46:42 >
(none) [RO] 09:46:42 >set global interactive_timeout=300;
Query OK, 0 rows affected (0.00 sec)
session1 [RO] 09:46:55 >set global wait_timeout=360;
Query OK, 0 rows affected (0.00 sec)
另开启一个会话
session2 [RO] 09:47:20 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 300            |
+---------------------+----------------+
2 rows in set (0.00 sec)
session2 [RO] 09:47:22 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 300            |
| WAIT_TIMEOUT        | 360            |
+---------------------+----------------+
2 rows in set (0.00 sec)
分析
 从案例1 2中可以得出session级别的wait_timeout 继承global 级别的 interactive_timeout 的值 global级别的session则不受影响。在没有改变 interactive_timeout的值的情况下，去修改wait_timeout的值
 结果无效。就会出现前言中我遇到的情况。