1、以下实验环境
操作系统版本:centos73
mysql版本:5.7.19-log MySQL Community Server (GPL)
2、MySQL中interactive_timeout和wait_timeout的区别
根据上述定义,两者的区别显而易见
(1)interactive_timeout针对交互式连接,wait_timeout针对非交互式连接。所谓的交互式连接,即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点,通过mysql客户端连接数据库是交互式连接,通过jdbc连接数据库是非交互式连接。
(2)在连接启动的时候,根据连接的类型,来确认会话变量wait_timeout的值是继承于全局变量wait_timeout,还是interactive_timeout。
(3)两者最大值与最小值:默认是28800s(即8个小时),最小值为1,最大值可设置为31536000s(即365天)
3、如何修改这两个参数
永久修改:
修改my.cnf,重启数据库
wait_timeout=31536000 interactive_timeout=31536000
临时修改(重启数据库会失效):
set wait_timeout=31536000; set global wait_timeout=31536000; set interactive_timeout=31536000; set global interactive_timeout=31536000;
4、针对不同值修改效果
(1)只修改wait_timeout,而不修改interactive_timeout情况
比如:wait_timeout=1800,则在mysql客户端下,查看wait_timeout的值,当前会话wait_timeout的值没有发现改变,全局会话发现改变。
当前会话值
(root@localhost) [(none)]> show variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> show variables like 'interactive_timeout'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | interactive_timeout | 28800 | +---------------------+-------+ 1 row in set (0.01 sec)
全局会话值:
(root@localhost) [(none)]> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 1800 | +---------------+-------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> show global variables like 'interactive_timeout'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | interactive_timeout | 28800 | +---------------------+-------+ 1 row in set (0.00 sec)
(2)只修改interactive_timeout,而不修改wait_timeout
比如:interactive_timeout=1800,则在mysql客户端下,发现当前会话值两者值都被修改成1800,而全局会话值,只有interactive_timeout被修改
当前会话值:
(root@localhost) [(none)]> show variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 1800 | | lock_wait_timeout | 1800 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 1800 | +-----------------------------+----------+ 13 rows in set (0.00 sec)
全局会话值:
(root@localhost) [(none)]> show global variables like 'wait_timeout%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> show global variables like 'interactive_timeout%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | interactive_timeout | 1800 | +---------------------+-------+ 1 row in set (0.00 sec)
评论前必须登录!