wait_timeout 、interactive_timeout、slave_net_timeout、master_heartbeat_period

 

 

1、连接、网络类超时

  • 共有如下几个:
  • connect_timeout:默认为10S
  • wait_timeout:默认是8小时,即28800秒
  • interactive_timeout:默认是8小时,即28800秒
  • net_read_timeout:默认是30S
  • net_write_timeout:默认是60S

 

igoodful@XXX((none)) > show global variables like '%time%';
+----------------------------+-------------------+
| Variable_name              | Value             |
+----------------------------+-------------------+
| connect_timeout            | 10                |
| datetime_format            | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout     | 300               |
| flush_time                 | 0                 |
| innodb_lock_wait_timeout   | 50                |
| innodb_old_blocks_time     | 0                 |
| innodb_rollback_on_timeout | OFF               |
| interactive_timeout        | 28800             |
| lc_time_names              | en_US             |
| lock_wait_timeout          | 31536000          |
| long_query_time            | 0.500000          |
| net_read_timeout           | 30                |
| net_write_timeout          | 60                |
| slave_net_timeout          | 3600              |
| slow_launch_time           | 2                 |
| system_time_zone           | CST               |
| time_format                | %H:%i:%s          |
| time_zone                  | +08:00            |
| timed_mutexes              | OFF               |
| wait_timeout               | 28800             |
+----------------------------+-------------------+

 

 

connect_timeout

该参数没有session级别,是一个global级别变量
## 使用mysql客户端打开一个会话,并设置全局 connect_timeout=5
MySQL [(none)]> set global connect_timeout=5;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 

## 由于mysql客户端不是很好模拟连接阶段(authenticate)的超时,所以使用telnet来发包给mysql,因为telnet的包并不遵循mysql的通讯协议
[root@localhost ~]# time telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
N
5.6.30-logwA{k)'&)S9#A`?Z&O9pJ`mysql_native_passwordConnection closed by foreign host.

real    0m5.022s  #这里可以看到5S之后连接断开
user    0m0.000s
sys    0m0.010s

## 回到mysql客户端:修改全局 connect_timeout为10S
MySQL [(none)]> set global connect_timeout=10;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 

## 使用telnet再试一次
[root@localhost ~]# time telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
N
5.6.30-loggZoA3{6:S\D}iu3;n:uafmysql_native_passwordConnection closed by foreign host.

real    0m10.012s
user    0m0.000s
sys    0m0.002s
从上面的结果中可以看到,MySQL客户端与服务端的连接阶段(authenticate)的超时由参数connect_timeout控制。

 

mysql的报错日志记录:

2021-01-28T11:36:10.920834+08:00 681121 [Note] Got timeout reading communication packets
2021-01-28T11:36:10.920881+08:00 681121 [Note] Aborted connection 681121 to db: 'unconnected' user: 'unauthenticated' host: '10.10.10.10' (Got timeout reading communication packets)
2021-01-28T11:36:47.726267+08:00 681134 [Note] Got timeout reading communication packets
2021-01-28T11:36:47.726309+08:00 681134 [Note] Aborted connection 681134 to db: 'unconnected' user: 'unauthenticated' host: '10.136.10.10' (Got timeout reading communication packets)

 

 

 

 

interactive_tineout和wait_timeout参数

 

1.3.1. interactive_timeout:(MySQL命令行客户端)
1.3.1.1. session级别修改interactive_timeout
## 打开第一个会话,设置session级别的interactive_timeout=2
MySQL [(none)]> set session interactive_timeout=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 2        |  #session级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

## 打开第二个会话,执行show语句
MySQL [(none)]> show session variables like '%timeout%';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     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)
从上面的结果可以看到,设置session级别的interactive_timeout对wait_timeout的session和global级别都没有影响
1.3.1.2. global级别修改interactive_timeout
### 回到第一个会话中,设置global interactive_timeout=20
MySQL [(none)]> set global interactive_timeout=20;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 2        |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 20       |  #global级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

# 第二个会话断开之后重连,再执行show语句
MySQL [(none)]> show session variables like '%timeout%';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     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 20       |  #session级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 20       |  #session级别的wait_timeout改变了
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 20       |  #global级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)
从上面的结果中可以看到:如果改变了global级别的interactive_timeout值,对当前连接不生效,对后续新连接的wait_timeout的session级别生效,global级别不生效,interactive_timeout的global级别和session级别都生效
1.3.2. wait_timeout:(MySQL命令行客户端)
1.3.2.1. session级别修改wait_timeout
这里为了验证后续的值不产生混乱,先把interactive_timeout的值恢复为172800并重连连接(connect_timeout默认是10,此时已经是这个值了,不用再修改),然后再修改wait_timeout
MySQL [(none)]> set global interactive_timeout=172800;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> Ctrl-C -- exit!
Aborted
[root@localhost ~]# mysql -uqogir_env -p'letsg0' -S /home/mysql/data/mysqldata1/sock/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show session variables like '%timeout%';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     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |
+------------------------------+----------+
13 rows in set (0.00 sec)

现在,开始1.3.2.小节的验证

# 打开第一个会话,修改session级别wait_timeout=2
MySQL [(none)]> set session wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    22
Current database: *** NONE ***  #从这里可以看到,当前连接被断开并重连了

+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #重连之后的session级别参数, interactive_timeout 没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #重连之后的session级别参数,wait_timeout恢复了172800
+------------------------------+----------+
13 rows in set (0.01 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #重连之后的global级别参数, interactive_timeout 没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   | #重连之后的global级别参数,wait_timeout恢复了172800,即新的连接不受影响
+------------------------------+----------+
13 rows in set (0.00 sec)

# 打开第二个会话,第二个会话注意要重连
MySQL [(none)]> show session variables like '%timeout%';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     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   | #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

# 对于超时断开的连接,错误日志中会报如下错误:
2016-11-07 19:08:24 3391 [Warning] Aborted connection 21 to db: 'unconnected' user: 'qogir_env' host: 'localhost' (Got timeout reading communication packets)

从上面的结果中可以看到:
session级别的wait_timeout变量在连接初始化时,继承global的interactive_timeout参数值
session级别的wait_timeout对当前交互连接生效(即当前连接的超时使用的是session wait_timeout,session interactive_timeout不生效)
有一点要注意,如果是新的连接(即断开重连的或者新的连接),session级别的wait_timeout会使用global级别的interactive_timeout值覆盖,因为interactive_timeout值是对后续新连接生效(参考1.2.2小节验证过程)
1.3.2.2. global级别修改wait_timeout

# 打开第一个会话,修改global wait_timeout=2
MySQL [(none)]> set global wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 2        |  #global级别的wait_timeout改变了
+------------------------------+----------+
13 rows in set (0.00 sec)

# 打开第二个会话,注意需要断开重连,再执行show语句
MySQL [(none)]> show session variables like '%timeout%';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     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响,因为前面说过,这里新连接的session的wait_timeout会继承global interactive_timeout的值
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 2        |  #global级别的wait_timeout改变了
+------------------------------+----------+
13 rows in set (0.00 sec)

从上面的结果中可以看到:global级别的wait_timeout变量在初始化时,继承global的wait_timeout参数值,默认8小时
1.3.3. interactive_timeout和wait_timeout参数(python MySQL driver)
本小节演示的python代码如下:

#cat test_timeout.py 
#!/bin/env python
# coding=utf8

import MySQLdb
import sys
import time

# 设置wait_timeout的值
wait_timeout=5

# 设置interactive_timeout的侄
interactive_timeout=10

# MySQL帐号
mysql_user='qbench'

# MySQL密码
mysql_password='qbench'

# MySQL ip地址
mysql_ip='10.10.30.68'




rest_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
rest_cur = rest_conn.cursor()
rest_cur.execute("show variables like '%timeout%';")
datas = rest_cur.fetchall()
datas = dict(datas)
    
rest_wait_timeout = datas['wait_timeout']
rest_interactive_timeout = datas['interactive_timeout']

rest_cur.close()
rest_conn.close()


def new_connect(info,timeout):
    new_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
    new_cur = new_conn.cursor()
    print '%s \n%s' % ('-' * 50,str(info))
    #sql = "select sleep(%s);" % int(timeout+1)
    #print "执行sleep sql语句:%s" % str(sql)
    new_cur.execute("show variables like '%timeout%';")
    new_datas = new_cur.fetchall()
    new_datas = dict(new_datas)
    
    print 'wait_timeout=%s' % new_datas['wait_timeout']
    print 'interactive_timeout=%s' % new_datas['interactive_timeout']

    print "sleep %s 秒之后再次执行sql---" % int(timeout)
    time.sleep(int(timeout))
    #new_cur.execute("%s" % str(sql))
    new_cur.execute("show variables like '%timeout%';")
    new_datas = new_cur.fetchall()
    new_datas = dict(new_datas)
    
    print 'wait_timeout=%s' % new_datas['wait_timeout']
    print 'interactive_timeout=%s' % new_datas['interactive_timeout']

    new_cur.close()
    new_conn.close()

def current_connect():
    curr_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
    curr_cur = curr_conn.cursor()
    print "在第一个连接中修改global wait_timeout为:%s" % wait_timeout
    curr_cur.execute("set global wait_timeout=%s;" % wait_timeout)
    curr_cur.execute("show variables like '%timeout%';")
    curr_datas1 = curr_cur.fetchall()
    curr_datas1 = dict(curr_datas1)
    
    print "%s\n第一个连接保持不断开的session级别的超时信息:" % ('-' * 100) 
    print 'wait_timeout=%s' % curr_datas1['wait_timeout']
    print 'interactive_timeout=%s' % curr_datas1['interactive_timeout']

    new_connect(info='第一个连接修改global wait_timeout为:%s之后,登录新的连接的session级别的超时信息如下:' % wait_timeout,timeout=wait_timeout)

    restore()

    curr_cur.close()
    curr_cur = curr_conn.cursor()
    print "在第一个连接中修改global interactive_timeout为:%s" % interactive_timeout
    curr_cur.execute("set global interactive_timeout=%s;" % interactive_timeout)
    curr_cur.execute("show variables like '%timeout%';")
    curr_datas2 = curr_cur.fetchall()
    curr_datas2 = dict(curr_datas2)
    
    print "%s\n第一个连接保持不断开的session级别的超时信息:" % ('-' * 100) 
    print 'wait_timeout=%s' % curr_datas2['wait_timeout']
    print 'interactive_timeout=%s' % curr_datas2['interactive_timeout']

    new_connect(info='第一个连接修改global interactive_timeout为:%s之后,登录新的连接的session级别的超时信息如下:' % interactive_timeout,timeout=interactive_timeout)

    curr_cur.close()
    curr_conn.close()


def restore():
    print "开启新的连接执行恢复参数初始设置----------------------"
    rest_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
    rest_cur = rest_conn.cursor()
    rest_cur.execute("set global wait_timeout=%s,interactive_timeout=%s;" % (rest_wait_timeout,rest_interactive_timeout))

    rest_cur.close()
    rest_conn.close()
   

print '=' * 100
try:
    current_connect()
except Exception,e:
    print e
else:
    restore()

print '=' * 100

跑一下这个脚本,打印结果如下:

#python test_timeout.py 
====================================================================================================
在第一个连接中修改global wait_timeout为:5
----------------------------------------------------------------------------------------------------
第一个连接保持不断开的session级别的超时信息:
wait_timeout=5
interactive_timeout=172800
-------------------------------------------------- 
第一个连接修改global wait_timeout为:5之后,登录新的连接的session级别的超时信息如下:
wait_timeout=5
interactive_timeout=172800
sleep 5 秒之后再次执行sql---
(2013, 'Lost connection to MySQL server during query')
====================================================================================================

从上面的结果中可以看到,第一个会话中修改global wait_timeout=5之后,新的连接上来,超过5秒没有发送新的数据包,连接就被断开。

综合1.3小节演示结果来看

MySQL命令行客户端下:global级别的interactive_timeout修改对当前连接不生效,但能影响新的连接的globa interactive_timeout、session interactive_timeout、session wait_timeout数值

MySQL命令行客户端下:session级别的interactive_timeout的修改除了能使session interactive_timeout数值改变之外没有什么作用

MySQL命令行客户端下:global级别的wait_timeout的修改除了能使global wait_timeout数值改变之外没有什么作用

MySQL命令行客户端下:session级别的wait_timeout能改变session wait_timeout数值其对当前连接生效。

python MySQL driver:修改global wait_timeout对当前连接不生效,但能影响新的连接的global wait_timeout、session wait_timeout

python MySQL driver:修改session wait_timeout只对当前连接生效

python MySQL driver:修改global interactive_timeout对当前连接不生效,能影响新的连接的global interactive_timeout、session interactive_timeout

python MySQL driver:修改session interactive_timeout除了能使session interactive_timeout数值改变之外没有什么作用

PS:思考?

为什么MySQL命令行客户端中新的连接的session wait_timeout不是使用的global wait_timeout的值,而是使用的interactive_timeout的值?但是,为什么python MySQL driver中,
新的连接的session wait_timeout就是按照正常的逻辑使用的是global wait_timeout的值?这里先卖个关子,问题的答案得去源码中找,参考链接:http:
//dev.mysql.com/doc/refman/5.6/en/mysql-real-connect.html

 

 

 

 

 

 

net_write_timeout

mysql服务端向客户端写(发送)数据时,服务端等待客户端响应的超时时间,当服务端正在写数据到客户端时,net_write_timeout控制何时超时
对于这个参数,session和global级别并没有什么特别,session级别只对当前连接生效,global级别只对新的连接生效。默认值是60S
下面使用tc命令模拟网络延迟来进行演示

## 使用sysbench在MySQL server上造数一张500W行数据的表

## tc命令对MySQL客户端的网卡加延迟
tc qdisc add dev eth0 root netem delay 1s

## MySQL 客户端登录server,修改net_write_timeout参数为1S
mysql -uqbench -pqbench -h 10.10.30.68
mysql > set global net_write_timeout=1;
Query OK, 0 rows affected (0.00 sec)

## 在MySQL客户端使用mysqldump备份
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data] # time mysqldump -uqbench -pqbench -h 10.10.30.68 --single-transaction --master-data=2  sbtest  sbtest2 > sbtest2.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `sbtest2` at row: 85  #从这里可以看到,不到一分钟时间,连接就被断开了

real    0m54.049s
user    0m0.009s
sys    0m0.011s

## MySQL客户端登录server,修改net_write_timeout参数为默认的60S
mysql -uqbench -pqbench -h 10.10.30.68
mysql > set global net_write_timeout=60;
Query OK, 0 rows affected (0.00 sec)

## 在MySQL客户端使用mysqldump重试备份
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# time mysqldump -uqbench -pqbench -h 10.10.30.68 --single-transaction --master-data=2  sbtest  sbtest2 > sbtest2.sql
Warning: Using a password on the command line interface can be insecure.

real    14m41.744s
user    0m18.662s
sys    0m7.886s

[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# ls -lh
total 963M
drwxr-xr-x 12 mysql mysql  137 Dec 30 15:04 mysqldata1
drwxr-xr-x  2 mysql mysql    6 Dec 30 15:04 recovery
-rw-r--r--  1 root  root  963M Dec 30 15:30 sbtest2.sql  #这里可以看到,消耗15分钟之后,备份成功,备份文件大小接近1G
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# 

 

 

 

 

 

net_read_timeout

mysql服务端从客户端读取(接收)数据时,服务端等待客户端响应的超时时间,当服务端正在从客户端读取数据时,net_read_timeout控制何时超时
对于这个参数,session和global级别并没有什么特别,session级别只对当前连接生效,global级别只对新的连接生效。默认值是30S
下面接着1.4小节进行演示,使用1.4小节中的备份结果导入数据库

## MySQL客户端登录server,先查看一下net_read_timeout参数的侄
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15453
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%net_read_timeout%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 30    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> 

## 现在,把1.4小节备份出来的sbtest2.sql文件导入server中的sbtest库
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# time mysql -uqbench -pqbench -h 10.10.30.68 sbtest < sbtest2.sql 
Warning: Using a password on the command line interface can be insecure.

real    37m17.831s  #导入成功,耗时38分钟左右
user    0m22.797s
sys    0m3.436s

## 现在,使用MySQL客户端登录server,修改net_read_timeout参数
[root@555f12f7-850d-4f42-867c-2d12890beb40 data]# mysql -uqbench -pqbench -h 10.10.30.68
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17040
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> 

## 修改tc模拟规则,模拟丢包10%,损坏包20%,延迟2秒,包乱序20%
tc qdisc del dev eth0 root
tc qdisc add dev eth0 root netem corrupt 20% loss 10% delay 2s reorder 20%

## 使用备份文件再次尝试导入
time mysql -uqbench -pqbench -h 10.10.30.68 sbtest < sbtest2.sql 

## 很囧的一个事情发生了。此时反复查看server端的processlist,只发现客户端连接上来了,但是一直是sleep状态
mysql> show processlist;
+-------+--------+-------------------+--------+---------+------+-------+------------------+
| Id    | User   | Host              | db     | Command | Time | State | Info             |
+-------+--------+-------------------+--------+---------+------+-------+------------------+
| 17129 | qbench | 10.10.30.78:16167 | sbtest | Sleep   |  207 |       | NULL             |
| 17159 | qbench | 10.10.30.68:47148 | NULL   | Query   |    0 | init  | show processlist |
+-------+--------+-------------------+--------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql> kill 17129;  ## 尝试kill掉这个连接
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+-------+--------+-------------------+------+---------+------+-------+------------------+
| Id    | User   | Host              | db   | Command | Time | State | Info             |
+-------+--------+-------------------+------+---------+------+-------+------------------+
| 17159 | qbench | 10.10.30.68:47148 | NULL | Query   |    0 | init  | show processlist |
+-------+--------+-------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> use sbtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from sbtest2;  ## 然后再查询一下sbtest2表的数据,发现是空的
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

## 此时,查看客户端的导入数据的连接
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# time mysql -uqbench -pqbench -h 10.10.30.68 sbtest < sbtest2.sql 
ERROR 2006 (HY000) at line 47: MySQL server has gone away  ## 发现断开了,囧。。

real    5m42.419s
user    0m0.031s
sys    0m0.017s

从上面的结果中可以看到:修改net_read_timeout=1,并在客户端导入数据到server的时候,并没有如预期的超时断开客户端连接。猜测可能是客户端导入数据到server端的时候,

server端接收包超时之后没有发起kill掉客户端的操作,所以不手动执行一把kill的话,客户端一直在那里不动,而server端的连接线程也一直处于sleep状态 PS:
1.4和1.5小节演示用数据库帐号权限:SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, LOCK TABLES, PROCESS 与net_read_timeout和net_write_timeout相关的还有一个参数,net_retry_count,官方描述如下: If a read or write on a communication port is interrupted, retry this many times before giving up.

 

 

 

 

 

 

innodb_lock_wait_timeout

       innodb的锁等待超时退出时间,单位为秒,一般默认设置为60s,

       对于高度交互式的应用程序或 OLTP 系统,您可能会降低该值,以便快速显示用户反馈,或者将更新放入队列中以便稍后处理。

       对于长时间运行的后端操作,例如等待其他大型插入或更新操作完成的数据仓库中的转换步骤,您可能会增加此值。

      InnoDB _ lock _ wait _ timeout仅适用于 InnoDB 行锁。MySQL 表锁不会发生在 InnoDB 内部,并且此超时不适用于等待表锁。

      锁等待超时值不适用于死锁,因为 InnoDB 会立即检测到它们,并回滚其中一个死锁事务。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

 

innodb_rollback_on_timeout

官方描述:
In MySQL 5.6, InnoDB rolls back only the last statement on a transaction timeout by default. If 

--innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction

默认情况下innodb_lock_wait_timeout 超时后只是超时的sql执行失败,整个事务并不回滚,也不做提交,如需要事务在超时的时候回滚,则需要设置innodb_rollback_on_timeout=ON,该参数默认为OFF ## 先测试一下innodb_rollback_on_timeout为默认值时的情况,打开第一个会话,显式开启一个事务,插入几行测试数据,不提交: MySQL [test]> show variables like '%rollback%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | +----------------------------+-------+ 2 rows in set (0.00 sec) MySQL [test]> use test Database changed MySQL [test]> show tables; Empty set (0.00 sec) MySQL [test]> create table test(id int); Query OK, 0 rows affected (0.05 sec) MySQL [test]> begin; Query OK, 0 rows affected (0.00 sec) MySQL [test]>insert into test(id) values(1),(2),(3),(4); Query OK, 1 row affected (0.00 sec) MySQL [test]> select * from test; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) ## 现在,打开第二个会话,显式开启一个事务,并插入数据5,不提交 MySQL [(none)]> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A MySQL [(none)]> begin; Query OK, 0 rows affected (0.00 sec) MySQL [test]> insert into test values(5); Query OK, 1 row affected (0.00 sec) MySQL [test]> select * from test; +------+ | id | +------+ | 5 | +------+ 2 rows in set (0.00 sec) ## 再回到第一个会话中,更新id为5的数据行为6 MySQL [test]> update test set id=6 where id=5; #因为第二个会话插入第=5这行数据时,对5及其以后的范围加了锁,也没有提交,所以这个这里的操作需要进行锁等待 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [test]> select * from test ; #这里可以看到,超时之后,第一个会话最开始在显式事务中插入的几行数据并没有回滚 +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) ##此时,你需要自行决定会话1中插入的数据是要提交,还是需要回滚,当然,如果断开连接,事务会自动回滚,为了方便后续的测试,先在两个会话中都做rollback操作 从上面的结果中可以看到,默认情况下innodb_rollback_on_timeout为OFF,此时超时终止的会话中的事务DML修改的数据不会自动回滚。 现在,把innodb_rollback_on_timeout参数在my.cnf中加入并改为ON,重启mysql,再次插入相同数据试试看 ## 第一个会话中显示开启一个事务,插入几行数据,不提交 MySQL [test]> show variables like '%rollback%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | ON | | innodb_rollback_segments | 128 | +----------------------------+-------+ 2 rows in set (0.00 sec) MySQL [test]> use test Database changed MySQL [test]> begin; Query OK, 0 rows affected (0.00 sec) MySQL [test]>insert into test(id) values(1),(2),(3),(4); Query OK, 1 row affected (0.00 sec) MySQL [test]> select * from test; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) ## 现在,打开第二个会话,显式开启一个事务,并插入数据5,不提交 MySQL [(none)]> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A MySQL [(none)]> begin; Query OK, 0 rows affected (0.00 sec) MySQL [test]> insert into test values(5); Query OK, 1 row affected (0.00 sec) MySQL [test]> select * from test; +------+ | id | +------+ | 5 | +------+ 2 rows in set (0.00 sec) ## 再回到第一个会话中,更新id为5的数据行为6 MySQL [test]> update test set id=6 where id=5; #因为第二个会话插入第=5这行数据时,对5及其以后的范围加了锁,也没有提交,所以这个这里的操作需要进行锁等待 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [test]> select * from test ; #这里可以看到,超时之后,第一个会话最开始在显式事务中插入的几行数据已经回滚 Empty set (0.00 sec) 从上面的结果中可以看到,把参数innodb_rollback_on_timeout设置为ON之后(注意,这个变量是只读变量,需要添加到my.cnf中并重启mysql),如果一个事务发生锁等待超时,那么这个事务没有提交的数据都会被回滚掉。

 

 

 

lock_wait_timeout

官方描述:
This variable specifies the timeout in seconds for attempts to acquire metadata locks.
这里不得不提一下2.1小节的innodb_lock_wait_timeout超时参数,相信有不少人是没有搞太清楚这两者的区别,从字面上来看,前者是innodb的dml操作的行级锁的等待时间 后面是获取MDL锁的等待时间,默认值是31536000秒=1年。那么,下面来演示一把吧

## 打开第一个会话,显示开启一个会话,执行select...for update语句,不提交事务
MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from test for update;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

## 现在,打开第二个会话,修改session lock_wait_timeout=5,并执行DDL语句
MySQL [test]> set lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> use test
Database changed

MySQL [test]> alter table test add column test varchar(100);  #DDL语句执行被阻塞,5秒之后超时终止
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [test]> 

从上面的结果中可以看到,DDL语句的超时时间是受lock_wait_timeout参数控制的
PS:注意,凡是需要获取MDL锁的操作都受到这个超时参数的影响,不单单是DDL语句,包含在表上的DML、DDL操作,以及视图、存储过程、存储函数、lock table,flush table with read lock语句等。但不适用于隐式访问系统表的语句,如:grant和revoke等

 

 

 

 

 

 

 

 

 

1)slave_net_timeout,默认值是60s,是从库端主动发起重新连接请求,避免主库重启后而从库却傻傻地原地等待这种情况。表示slave在slave_net_timeout时间之内没有收到master的任何数据(包括binlog和heartbeat),slave认为连接断开,会进行重连。
   超时后,先断开io线程,再立刻通过开启io线程发起重连请求。后续重连的时间间隔由chnage master to命令的master_connect_retry参数指定。slave_net_timeout如果设置太大,那么主库重启后,从库将会长时间无法同步主库数据,这时需要重启io线程。
   因此,该参数很重要,不宜设置太大,同样也不要设置太小,太小的话,就会出现从库频繁向主库发起连接请求。一般将该值设置为:30s到300s之间。即默认值即可。
2)master_connect_retry默认值为60s。表示重连的时间间隔。重连次数上限由master_retry_count定义,默认值3600s,即1小时。 当重新建立主从连接时,如果连接建立失败,间隔多久后重试。
3)master_retry_count默认值86400次。表示重连的最大次数。 # 作为一个从库,一旦和某个主库建立了主从关系,并且开始正常的复制后,如果与主库失联时间超过slave_net_timeout时间,则触发从库向主库发起重连请求: 如果重试的过程中,连上了主库,那么它认为当前主库是好的,又会开始 slave-net-timeout 秒的等待。 如果从库向主库发起的重连请求依旧没有收到主库的binlog数据和心跳数据,则从库再等待master_connect_retry时间,如果master_connect_retry时间到了后,还是没有收到主库的binlog数据和心跳数据,则继续重复上述等待master_connect_retry时间、发起请求的过程 这个过程最长能持续多久呢?那就是从库每次等待master_connect_retry时间,次数是master_retry_count,当达到了master_connect_retry*master_retry_count时间后,还是没有收到主库的binlog数据和心跳数据,那就是彻底断开主从关系。 这个时间是多久呢?假设master_connect_retry=60s,master_retry_count=86400,那么这就是60天的时间,足够大了。 4)master_heartbeat_period,默认值为slave_net_timeout参数值得一半。表示主库心跳时间。主库在没有数据更新的时候,每过master_heartbeat_period时间就发送一个心跳包给所有从库,这样从库就能知道主库还健在。
在 mysql 的复制协议里,由 slave 发送一个 com_binlog_dump 命令后,就完全由 master 来推送数据,master、slave 之间不再需要交互。如果 master 没有更新,也就不会有数据流,slave 就不会收到任何数据包。
但是如果由于某种原因造成 master 无法把数据发送到 slave ,比如发生过网络故障或其他原因导致 master 上的 tcp 连接丢失,由于 tcp 协议的特性,slave 没有机会得到通知,所以也没法知道收不到数据是因为 master 本来就没有更新呢还是由于出了故障。
mysql5.5提供的新的配置master_heartbeat_period。即复制心跳,能够在复制停止工作和出现网络中断的时候帮助我们迅速发现问题。
#########################################################

5)设置参数

stop slave;

change master to master_heartbeat_period = 10;

start slave;

 

针对网络类超时参数,先简单梳理一下在MySQL建立连接、发送数据包的整个过程中,每一个阶段都用到了哪些超时参数

a)、connect_timeout:在获取连接阶段(authenticate)起作用

  • 获取MySQL连接是多次握手的结果,除了用户名和密码的匹配校验外,还有IP->HOST->DNS->IP验证,任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上,超过connect_timeout的连接请求将会被拒绝。
  • 官方描述:connect_timeout(The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds)

b)、interactive_timeout和wait_timeout:在连接空闲阶段(sleep)起作用

  • 即使没有网络问题,也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout(或interactive_timeout,取决于client_interactive标志)的客户端,MySQL会主动断开连接。

  • 官方描述:

  • 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_timeoutvalue, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).

  • 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()

c)、net_read_timeout和net_write_timeout:则是在连接繁忙阶段(query)起作用。

  • 即使连接没有处于sleep状态,即客户端忙于计算或者存储数据,MySQL也选择了有条件的等待。在数据包的分发过程中,客户端可能来不及响应(发送、接收、或者处理数据包太慢)。
  • 为了保证连接不被浪费在无尽的等待中,MySQL也会选择有条件(net_read_timeout和net_write_timeout)地主动断开连接。
  • 这个参数只对TCP/IP链接有效,只针对在Activity状态下的线程有效
  • 官方描述:
  • net_read_timeout:The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client,net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort
  • net_write_timeout:The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

d)、 handshake流程

  • 在TCP三次握手的基础之上,简历MySQL通讯协议的连接,这个连接建立过程受connect_timeout参数控制
    --------------------TCP established--------------------
    MySQL Server(10.10.20.96)------->Client(10.10.20.51)
    Client(10.10.20.51)------->MySQL Server(10.10.20.96)
    MySQL Server(10.10.20.96)------->Client(10.10.20.51)

--------------------established--------------------

    • 在MySQL通讯协议建立连接之后,此时客户端连接的超时受wait_timeout和interactive_timeout参数控制
      建立连接后无交互:MySQL server ---wait_timeout--- Client
      建立连接交互后:MySQL server ---interactive_timeout--- Client

    • 在如果客户端有数据包传输,那么这个数据包的传输超时由net_read_timeout和net_write_timeout参数控制
      -------------------client与server端有数据传输时-------------------
      client ----->MySQL Server(net_read_timeout)
      client <-----MySQL Server(net_write_timeout)

 

posted @ 2020-03-12 16:16  igoodful  阅读(495)  评论(4编辑  收藏  举报