MySQL连接相关的timeout参数解读

环境介绍：

操作系统：CentOS 6.5

MySQL数据库：MariaDB10.0.13

最近一同事提问，在连接外网生产数据库做查询时，总提示timeout类的提示；
开始解决：
1).排查跳板机的连接用户profile文件是否定制了TIMEOUT时间，答案是否定的，没有做限制;
2).secure CRT设置，请参考如下文章：http://blog.csdn.net/jacson_bai/article/details/41016815;
3).查看mysql的timeout参数
4).检查本地网络到远程服务器间的网络，原因就在这，不稳定！！！
相关timeout参数解读：
MariaDB [(none)]> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| deadlock_timeout_long       | 50000000 |
| deadlock_timeout_short      | 10000    |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
14 rows in set (0.00 sec)
和网络连接相关的timeout参数有connect_timeout、interactive_timeout、net_read_timeout、net_write_timeout、wait_timeout
参数参考链接：https://mariadb.com/kb/en/mariadb/full-list-of-mariadb-options-system-and-status-variables
https://mariadb.com/kb/en/mariadb/server-system-variables/
connect_timeout：
  Time in seconds that the server waits for a connect packet before returning a 'Bad handshake'. 
Increasing may help if clients regularly encounter 'Lost connection to MySQL server at 'X', 
system error: error_number' type-errors

interactive_timeout：
 Time in seconds that the server waits for an interactive connection (one that connects with the mysql_real_connect() CLIENT_INTERACTIVE option) 
to become active before closing it. See also wait_timeout.

net_read_timeout：
Time in seconds the server will wait for a client connection to send more data before aborting the read. 
See also net_write_timeout and slave_net_timeout
net_write_timeout:
Time in seconds the server will wait for a client connection to send more data before aborting the read. 
See also net_write_timeout and slave_net_timeout

slave_net_timeout:
 Time in seconds for the slave to wait for more data from the master before considering the connection broken, 
after which it will abort the read and attempt to reconnect. 
The retry interval is determined by the MASTER_CONNECT_RETRY open for the CHANGE MASTER statement, 
while the maximum number of reconnection attempts is set by the master-retry-count variable. 
The first reconnect attempt takes place immediately.
wait_timeout:
  Time in seconds that the server waits for a connection to become active before closing it. 
The session value is initialized when a thread starts up from either the global value, 
if the connection is non-interactive, or from the interactive_timeout value, if the connection is interactive.


总结：
connect_timeout：在获取连接阶段（authenticate）起作用
interactive_timeout和wait_timeout：在连接空闲阶段（sleep）起作用
net_read_timeout和net_write_timeout：则是在连接繁忙阶段（query）起作用。