MySQL [(none)]> show 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)a)、connect_timeout：在获取连接阶段（authenticate）起作用
b)、interactive_timeout和wait_timeout：在连接空闲阶段（sleep）起作用
c)、net_read_timeout和net_write_timeout：则是在连接繁忙阶段（query）起作用。
d)、 handshake流程
--------------------established--------------------
## 使用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## 打开第一个会话，设置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)### 回到第一个会话中，设置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)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) # 打开第一个会话，修改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) # 打开第一个会话，修改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) #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‘) ==================================================================================================== ## 使用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]# ## 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 ## 第一个会话，创建测试数据，并设置innodb_lock_wait_timeout=1 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 Database changed MySQL [test]> create table test(id int); Query OK, 0 rows affected (0.03 sec) MySQL [test]> insert into test values(1); Query OK, 1 row affected (0.01 sec) MySQL [test]> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) MySQL [test]> set innodb_lock_wait_timeout=1; Query OK, 0 rows affected (0.00 sec) MySQL [test]> ## 打开第二个会话，注意第二个会要重连，然后打开一个事务，使用select...for update不提交 MySQL [test]> use test Database changed MySQL [test]> begin; Query OK, 0 rows affected (0.00 sec) MySQL [test]> select * from test where id=1 for update; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) MySQL [test]> ## 此时 回到第一个会话中，执行相同的select..for update语句，等到1S之后会话超时终止 MySQL [test]> select * from test where id=1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MySQL [test]> ## 先测试一下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操作 ## 第一个会话中显示开启一个事务，插入几行数据，不提交 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) ## 打开第一个会话，显示开启一个会话，执行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]> This variable is available only if the master-side semisynchronous replication plugin is installed.
