mysql socket timeout_MySQL中 timeout相关参数解析

前言：

MySQL中有两个关于连接超时的配置项。他们之间在某些条件下会互相继承，那究竟这两个参数会在什么情况下起作用呢？

本文将会通过一些测试实例来证明总结两者的相互关系。

参数介绍：

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 theCLIENT_INTERACTIVEoption tomysql_real_connect(). See alsowait_timeout.

The number of seconds the server waits for activity on a noninteractive connection before closing it. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVEconnect option to mysql_real_connect()). See also interactive_timeout.

CLIENT_INTERACTIVE

Permitinteractive_timeoutseconds (instead ofwait_timeoutseconds) of inactivity before closing the connection. The client's sessionwait_timeoutvariable is set to the value of the sessioninteractive_timeoutvariable.

简单的说 interactive就是交互式的终端，例如在shell里面直接执行mysql，出现 mysql> 后就是交互式的连接。而mysql -e 'select 1' 这样的直接返回结果的方式就是非交互式的连接。

第二部分  测试

2.1 继承关系

Q：通过Socket连接 timeout会从哪个global timeout继承

A：由下例可见，通过socket登录，timeout 继承于global.interactive_timeout;

mysql> set global interactive_timeout = 11111;

Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout = 22222;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%timeout%';

+----------------------------+----------+

| Variable_name | Value |

+----------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 11111 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| slave_net_timeout | 3600 |

| wait_timeout | 22222 |

+----------------------------+----------+

10 rows in set (0.00 sec)

mysql -uroot -ppassword -S /usr/local/mysql3310/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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 session variables like '%timeout%';

+----------------------------+----------+

| Variable_name | Value |