MySQL设置wait_timeout注意事项
Contents
wait_timeout 是服务器等待客户端多久没有活动时，就关闭连接的时间，单位是秒.
查看
-- global level
mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 28800 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
-- session level
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 120 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql>
有global时，是查看全局，没有global时，是查看session级别.
设置全局wait_timeout
mysql> set global wait_timeout = 280000;
Query OK, 0 rows affected (0.00 sec)
mysql>
开启新会话，再查看:
-- session level
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 120 |
+-----------------------------+----------+
12 rows in set (0.01 sec)
-- global level
mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 280000 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql>
这里奇怪的是，为什么新会话的wait_timieout会话级别的值，不是之前设置的全局变量的值。
原因
查看了一下官方文档，如下:
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.
大意是说: wait_timeout的会话初始值，根据mysql客户端的类型不同，而选择使用全局 wait_timeout的值，或者使用全局 interactive_timeout 的值.
global wait_timeout： 是指那些非交互式的客户端，比如用JDBC连接的。
global interactive_timeout： 是指那些交互式的客户端，比如使用了命令行的mysql.
这就可以理解这里为什么我们的会话级别的wait_timeout的初始值，不是我们之前设置的280000，而是120了。因为全局的interactive_timeout=120，而我们又是使用交互式客户端。
设置
如果想让MySQL无论从哪种客户端都能让wait_timeout一致，那就要同时设置global wait_timeout和global interactive_timeout了。例如:
mysql> set global wait_timeout = 280000;
Query OK, 0 rows affected (0.00 sec)
mysql> set global interactive_timeout = 280000;
Query OK, 0 rows affected (0.00 sec)
mysql>
这样了，新打开的会话的wait_timeout就会是280000了.如：
-- session level
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 30 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 280000 |
| lock_wait_timeout | 600 |
| net_read_timeout | 3 |
| net_write_timeout | 6 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 30 |
| wait_timeout | 280000 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql>