共计 7885 个字符，预计需要花费 20 分钟才能阅读完成。
自动写代码机器人，免费开通
丸趣 TV 小编给大家分享一下 mysql timeout 变量的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
1、timeout 变量知多少
打开 mysql，用 show variables like %timeout% 命令一看，不看不知道，一看吓一跳，结果如下面所示，这么多 timeout 相关变量，一下就吓尿了。。原来对 mysql 的了解原来是如此的不够，好了，这么些 timeout 究竟各自是什么意思，花了一下午去学习，做了几个小实验，总算明白了一二，如有错误，请不吝赐教啊。
mysql show variables like %timeout%
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| 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 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+2. 分析
下面从 timeout 里面找些比较常用的出来逐个分析下。
2.1 connect_timeout
connect_timeout 指的是连接过程中握手的超时时间，在 5.0.52 以后默认为 10 秒，之前版本默认是 5 秒。官方文档是这样说的：
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 as of MySQL 5.0.52 and 5 seconds before thatmysql 的基本原理应该是有个监听线程循环接收请求，当有请求来时，创建线程（或者从线程池中取）来处理这个请求。由于 mysql 连接采用 TCP 协议，那么之前势必是需要进行 TCP 三次握手的。TCP 三次握手成功之后，客户端会进入阻塞，等待服务端的消息。服务端这个时候会创建一个线程 (或者从线程池中取一个线程) 来处理请求，主要验证部分包括 host 和用户名密码验证。host 验证我们比较熟悉，因为在用 grant 命令授权用户的时候是有指定 host 的。用户名密码认证则是服务端先生成一个随机数发送给客户端，客户端用该随机数和密码进行多次 sha1 加密后发送给服务端验证。如果通过，整个连接握手过程完成。（具体握手过程后续找到资料再分析）
由此可见，整个连接握手可能会有各种可能出错。所以这个 connect_timeout 值就是指这个超时时间了。可以简单测试下，运行下面的 telnet 命令会发现客户端会在 10 秒后超时返回。
telnet localhost 3306在超时之前 mysql 中该连接状态如下：
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL2.2 interactive_timeout wait_timeout
还是先看官方文档，从文档上来看 wait_timeout 和 interactive_timeout 都是指不活跃的连接超时时间，连接线程启动的时候 wait_timeout 会根据是交互模式还是非交互模式被设置为这两个值中的一个。如果我们运行 mysql -uroot - p 命令登陆到 mysql，wait_timeout 就会被设置为 interactive_timeout 的值。如果我们在 wait_timeout 时间内没有进行任何操作，那么再次操作的时候就会提示超时，这是 mysql client 会重新连接。
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_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).测试如下：
mysql set global interactive_timeout=3; ## 设置交互超时为 3 秒重新进入 mysql，这时候可以看到：
mysql show variables like %timeout% ##wait_timeout 已经被设置为 3 秒
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 3 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 3 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 3 |
+-----------------------------+----------+可以看到 wait_timeout 被设置为了 interactive_timeout 的值，这样，我们 3 秒后再执行其他命令，会提示如下：
mysql show variables like %timeout%
ERROR 2006 (HY000): MySQL server has gone away ## 超时重连
No connection. Trying to reconnect...
Connection id: 50
Current database: *** NONE ***
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 3 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 3 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 3 |
+-----------------------------+----------+2.3 innodb_lock_wait_timeout innodb_rollback_on_timeout
还是先祭出官方文档，从文档中看，这个值是针对 innodb 引擎的，是 innodb 中行锁的等待超时时间，默认为 50 秒。如果超时，则当前语句会回滚。如果设置了 innodb_rollback_on_timeout，则会回滚整个事务，否则，只回滚事务等待行锁的这个语句。
The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction同样来测试下(先创建一个 innodb 引擎的表 test，只有一列，列名为 a)：
mysql CREATE TABLE `test` ( `a` int primary key) engine=innodb;首先插入三条测试数据
mysql select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |当前 innodb_rollback_on_timeout=OFF，设置 innodb_lock_wait_timeout=1，我们开启两个事务
## 事务 1 加行锁
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql select * from test where a=2 for update;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.01 sec)## 事务 2，请求行锁
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql delete from test where a=1;
Query OK, 1 row affected (0.00 sec)
mysql delete from test where a=2; ## 请求行锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql select * from test;
+---+
| a |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)
mysql begin; ## 这里我们直接开启另外的事务(或者直接 commit 当前事务)，则原来的事务只会回滚第二条语句，最终结果就是 test 表中只剩下 2 和 3. 如果这里我们显示的 rollback，则会回滚整个事务，保持 1，2，3 不变。那么如果 innodb_rollback_on_timeout=ON, 同样事务 2 会超时，但是这个时候如果我们 begin 开启新的事务，那么会回滚请求锁超时的整个事务，而不是像前面那样只回滚了超时的那条语句。
2.4 lock_wait_timeout
文档中描述如下，简单说来 lock_wait_timeout 是元数据锁等待超时，任意锁元数据的语句都会用到这个超时参数，默认为一年。元数据锁可以参加 mysql metadata lock，为了保证事务可串行化，不管是 myisam 还是 innodb 引擎的表，只要是先在一个 session 里面开启一个事务，就会获取操作表的元数据锁，这时候如果另一个 session 要对表的元数据进行修改，则会阻塞直到超时。
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.
This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements测试例子：
我们用一个 myisam 引擎的表 myisam_test 来测试。其中有一条记录(1,1)，现在我们先开启一个 session，然后执行一个 select 语句。另外打开一个 session，然后执行表的元数据操作，如删除表，会发现操作阻塞直到 lock_wait_timeout 秒后提示超时。
## 第一个 session，获取 metadata lock
mysql show create table myisam_test;
-----------------------------------------------------------+
| Table | Create Table |
+-----------------------------------------------------------
| myisam_test | CREATE TABLE `myisam_test` ( `i` int(11) NOT NULL,
`j` int(11) DEFAULT NULL,
PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql select * from myisam_test;
+---+------+
| i | j |
+---+------+
| 2 | 1 |
+---+------+
1 row in set (0.00 sec)
## 另一个 session，删除表提示超时
mysql drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction其中更改表结构的元数据操作指令有如下这些：
DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE；当然，多说一句，对于 myisam 表的加锁以及并发插入等，这篇博客 myisam 表锁非常详细，有兴趣的可以看看。
2.5 net_read_timeout net_write_timeout
文档中描述如下，就是说这两个参数在网络条件不好的情况下起作用。比如我在客户端用 load data infile 的方式导入很大的一个文件到数据库中，然后中途用 iptables 禁用掉 mysql 的 3306 端口，这个时候服务器端该连接状态是 reading from net，在等待 net_read_timeout 后关闭该连接。同理，在程序里面查询一个很大的表时，在查询过程中同样禁用掉端口，制造网络不通的情况，这样该连接状态是 writing to net，然后在 net_write_timeout 后关闭该连接。slave_net_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测试：
我创建一个 120M 的数据文件 data.txt。然后登陆到 mysql。
mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1导入过程设置 iptables 禁用 3306 端口。
iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP可以看到连接状态为 reading from net，然后经过 net_read_timeout 秒后关闭。
经过几个实验可以发现，connect_timeout 在握手认证阶段（authenticate）起作用，interactive_timeout 和 wait_timeout 在连接空闲阶段（sleep）起作用，而 net_read_timeout 和 net_write_timeout 则是在连接繁忙阶段（query）或者网络出现问题时起作用。
以上是“mysql timeout 变量的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
向 AI 问一下细节
