2025年2月25日 星期二 甲辰(龙)年 腊月廿五 设为首页 加入收藏
rss
您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

MySQL 的连接时长控制--interactive_timeout和wait_timeout

时间:09-28来源:作者:点击数:23
城东书院 www.cdsy.xyz

在用MySQL客户端对数据库进行操作时,如果一段时间没有操作,再次操作时,常常会报如下错误:

  • ERROR 2013 (HY000): Lost connection to MySQL server during query
  • ERROR 2006 (HY000): MySQL server has gone away
  • No connection. Trying to reconnect...

这个报错信息就意味着当前的连接已经断开,需要重新建立连接。

那么,连接建立后,连接的时长是如何确定的呢?

在MySQL中,这个与两个参数interactive_timeoutwait_timeout的设置有关。

注:以下说明基于MySQL 5.7.

1.interactive_timeout和wait_timeout的定义

首先,看看官方文档对于这两个参数的定义。

interactive_timeout

  • 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.

interactive_timeout参数,定义了对于交互式连接,服务端等待数据的最大时间。如果超过这个时间,服务端仍然没有收到数据,则会关闭连接。

所谓交互式client,是指调用mysql_real_connect()函数建立连接时,设置了CLIENT_INTERACTIVE选项。比较常用的就是命令行终端。

查看interactive_timeout的值:

  • mysql> show global variables like 'interactive_timeout%';
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • +---------------------+-------+
  • 1 row in set (0.01 sec)

默认是28800,单位秒,即8个小时

wait_timeout

  • 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.

wait_timeout参数,定义对于非交互式连接,服务端等待数据的最长时间。如果超过这个时间,服务端仍然没有收到数据,则会关闭连接。

在连接线程启动的时候,根据连接的类型,决定会话级的wait_timeout的值是初始化为全局的wait_timeout,还是全局的interactive_timeout。即如果是交互式连接,会话变量wait_timeout初始化为全局的interactive_timeout,否则,初始化为全局的wait_timeout。

查看wait_timeout的值:

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

默认同样是28800s,即8小时。

根据上述定义,两者的区别显而易见:interactive_timeout针对交互式连接,wait_timeout针对非交互式连接。所谓的交互式连接,即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。

  • 说得直白一点,通过mysql命令行终端连接数据库是交互式连接,通过jdbc等连接数据库是非交互式连接。

下面来测试一下,确认如下问题:

  • 控制连接最大空闲时长的是哪个参数。
  • 会话变量wait_timeout的继承问题

2.控制连接最大空闲时长的是哪个参数

先给出答案:wait_timeout

接下来进行验证。

2.1 只修改会话的wait_timeout参数

查看当前会话的wait_timeout和interactive_timeout。

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

设置当前会话的wait_timeout为10s

  • mysql> set session wait_timeout=10;
  • Query OK, 0 rows affected (0.00 sec)

再次查看

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 10 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

等待10s,再次查看

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • ERROR 2006 (HY000): MySQL server has gone away
  • No connection. Trying to reconnect...
  • Connection id: 8
  • Current database: *** NONE ***
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.01 sec)

可以看到,等待10s后再执行操作,原来的连接已经断开,并重新建立连接。

2.2 只修改会话的interactive_timeout参数

首先查看当前会话的interactive_timeout和wait_timeout.

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

接着,设置当前会话的interactive_timeout为10s

  • mysql> set session interactive_timeout=10;
  • Query OK, 0 rows affected (0.00 sec)

再次查看

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 10 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.01 sec)

等待10s,再次查看

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 10 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

可以看到,即使等待10后,连接是正常的。所以,设置interactive_timeout,对连接的时长没有影响。

3.会话变量wait_timeout的继承问题

上面已经提到,如果是交互式连接,则继承自全局变量interactive_timeout的值,如果是非交互式连接,则继承自全局变量wait_timeout的值。

下面进行验证。

3.1 只修改全局变量interactive_timeout的值

首先查看全局的interactive_timeout和wait_timeout。

  • mysql> show global variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

接着,设置全局的interactive_timeout为10s。

  • mysql> set global INTERACTIVE_TIMEOUT=10;
  • Query OK, 0 rows affected (0.00 sec)

再次查看

  • mysql> show global variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 10 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

开启另外一个MySQL客户端,查看会话变量的值:

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 10 |
  • | wait_timeout | 10 |
  • +---------------------+-------+
  • 2 rows in set (0.01 sec)

发现,WAIT_TIMEOUT的值已经变为10了。

等待10s后,再次查看,会发现原来的连接已经断开,连接的时长设置已经生效。

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • ERROR 2006 (HY000): MySQL server has gone away
  • No connection. Trying to reconnect...
  • Connection id: 70
  • Current database: *** NONE ***
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 10 |
  • | wait_timeout | 10 |
  • +---------------------+-------+
  • 2 rows in set (0.01 sec)

但通过非终端测试,wait_timeout的值依旧是28800:

  • package main
  • import (
  • "database/sql"
  • "log"
  • _ "github.com/go-sql-driver/mysql"
  • )
  • var DB *sql.DB
  • var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"
  • func mysqlInit() {
  • var err error
  • DB, err = sql.Open("mysql", dataBase)
  • if err != nil {
  • log.Fatalln("open db fail:", err)
  • }
  • DB.SetMaxOpenConns(1)
  • err = DB.Ping()
  • if err != nil {
  • log.Fatalln("ping db fail:", err)
  • }
  • }
  • func main() {
  • mysqlInit()
  • execSql()
  • }
  • func execSql() {
  • var variableName string
  • var value int
  • sql := "show session variables where Variable_name in ('interactive_timeout', 'wait_timeout')"
  • rows, err := DB.Query(sql)
  • if err != nil {
  • log.Println("query failed:", err)
  • return
  • }
  • defer rows.Close()
  • for rows.Next() {
  • err = rows.Scan(&variableName, &value)
  • if err != nil {
  • log.Println("rows.Scan failed:", err)
  • return
  • }
  • log.Println("variable_name:", variableName, ", value:", value)
  • }
  • }

output:

  • 2019/10/13 17:11:22 variable_name: interactive_timeout , value: 10
  • 2019/10/13 17:11:22 variable_name: wait_timeout , value: 28800

结果输出如下

  • INTERACTIVE_TIMEOUT: 10
  • WAIT_TIMEOUT: 28800

3.2 只修改全局变量wait_timeout的值

首先查看全局的interactive_timeout和wait_timeout。

  • mysql> show global variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

接着,将全局的WAIT_TIMEOUT设置为20s。

  • mysql> set global WAIT_TIMEOUT=20;
  • Query OK, 0 rows affected (0.07 sec)

再次查看

  • mysql> show global variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 20 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

开启另外一个mysql客户端,查看会话变量的值

  • mysql> show session variables where Variable_name in ('interactive_timeout', 'wait_timeout');
  • +---------------------+-------+
  • | Variable_name | Value |
  • +---------------------+-------+
  • | interactive_timeout | 28800 |
  • | wait_timeout | 28800 |
  • +---------------------+-------+
  • 2 rows in set (0.00 sec)

WAIT_TIMEOUT的值依旧是28800.

查看非终端的代码执行的结果:

  • func execSql() {
  • var variableName string
  • var value int
  • sql := "show session variables where Variable_name in ('interactive_timeout', 'wait_timeout')"
  • rows, err := DB.Query(sql)
  • if err != nil {
  • log.Println("query failed:", err)
  • return
  • }
  • defer rows.Close()
  • for rows.Next() {
  • err = rows.Scan(&variableName, &value)
  • if err != nil {
  • log.Println("rows.Scan failed:", err)
  • return
  • }
  • log.Println("variable_name:", variableName, ", value:", value)
  • }
  • }

output:

  • 2019/10/13 17:23:10 variable_name: interactive_timeout , value: 28800
  • 2019/10/13 17:23:10 variable_name: wait_timeout , value: 20

修改程序,执行sql语句后,等待25s后,再次执行sql语句,查看执行情况。

  • func main() {
  • mysqlInit()
  • for {
  • execSql()
  • time.Sleep(25*time.Second)
  • }
  • }
  • func execSql() {
  • var variableName string
  • var value int
  • sql := "show session variables where Variable_name in ('interactive_timeout', 'wait_timeout')"
  • rows, err := DB.Query(sql)
  • if err != nil {
  • log.Println("query failed:", err)
  • return
  • }
  • defer rows.Close()
  • for rows.Next() {
  • err = rows.Scan(&variableName, &value)
  • if err != nil {
  • log.Println("rows.Scan failed:", err)
  • return
  • }
  • log.Println("variable_name:", variableName, ", value:", value)
  • }
  • }

output:

  • 2019/10/13 17:26:46 variable_name: interactive_timeout , value: 28800
  • 2019/10/13 17:26:46 variable_name: wait_timeout , value: 20
  • [mysql] 2019/10/13 17:27:11 packets.go:36: unexpected EOF
  • [mysql] 2019/10/13 17:27:11 packets.go:141: write tcp 127.0.0.1:53878->127.0.0.1:3306: write: broken pipe
  • 2019/10/13 17:27:11 variable_name: interactive_timeout , value: 28800
  • 2019/10/13 17:27:11 variable_name: wait_timeout , value: 20

可以看到,等待25s后,再次执行sql,此时连接已经断开。

底层又重新建立连接。

4.总结

  • 控制连接最大空闲时长的wait_timeout参数。
  • 关于wait_timeout的继承
    • 对于非交互式连接,类似于jdbc连接,wait_timeout的值继承自全局变量wait_timeout。
    • 对于交互式连接,类似于mysql命令行终端,wait_timeout的值继承全局变量interactive_timeout。
  • 判断一个连接的空闲时间,可通过show processlist输出中Sleep状态的时间
  • mysql> show processlist;
  • +----+------+----------------------+------+---------+------+-------+------------------+
  • | Id | User | Host | db | Command | Time | State | Info |
  • +----+------+----------------------+------+---------+------+-------+------------------+
  • | 2 | root | localhost | NULL | Query | 0 | init | show processlist |
  • | 6 | repl | 192.132.2.66:56001 | NULL | Sleep | 1201 | | NULL |
  • +----+------+----------------------+------+---------+------+-------+------------------+
  • 2 rows in set (0.03 sec)
城东书院 www.cdsy.xyz
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐