MySQL 各种超时参数的含义
# 打开第一个会话，修改session级别wait_timeout=2MySQL [(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> 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 Aborted connection 21 to db: 'unconnected' user: 'qogir_env' host: 'localhost' (Got timeout reading communication packets)
页:
[1]