MySQL連線超時相關的兩個引數interactive_timeout和wait_timeout的區別和解釋

先看看官方文件對於這兩個引數的定義

interactive_timeout
預設是28800，單位秒，即8個小時
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.


wait_timeout
預設同樣是28800s
The number of seconds the server waits for activity on a noninteractive connection before closing it.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.


interactive_timeout針對互動式連線，wait_timeout針對非互動式連線。所謂的互動式連線，即在mysql_real_connect()函式中使用了CLIENT_INTERACTIVE選項。
說得直白一點，透過mysql客戶端連線資料庫是互動式連線，透過jdbc連線資料庫是非互動式連線。 


interactive_timeout：互動式連線超時時間(mysql工具、mysqldump等)
wait_timeout：非互動式連線超時時間，預設的連線mysql api程式,jdbc連線資料庫等
 在連線啟動的時候，根據連線的型別，來確認會話變數wait_timeout的值是繼承於全域性變數wait_timeout，還是interactive_timeout。


如何設定和檢視:
mysql> set global interactive_timeout=1800;
Query OK, 0 rows affected (0.00 sec)


mysql> set global wait_timeout=1800;
Query OK, 0 rows affected (0.00 sec)


mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 1800  |
+---------------------+-------+
1 row in set (0.00 sec)


mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 1800  |
+---------------+-------+
1 row in set (0.00 sec)