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)
igoodful@qq.com
