mysql数据库有三个wait_timeout的构造,暗许值为28800(即8钟头卡塔尔(قطر‎.

Linux下mysql修改连接超时

jdbc:mysql://localhost:3306/database
?connectTimeout=10000:数据库连接10秒超时
&socketTimeout=100000:执行sql超时100秒
&useAffectedRows=true:true为修正行数,false为影响行数 ,默感觉false
,假设改变前后数据风姿洒脱致,为false时 重临1

wait_timeout过大有坏处,其呈现正是MySQL里多量的SLEEP进度不也许顿时放出,拖累系统特性,可是也不可能把这些指设置的过小,不然你可能会遭蒙受“MySQL
has gone
away”之类的难点,平日来讲,小编觉着把wait_timeout设置为10是个不错的筛选,但一些情状下或然也会出标题,比如说有三个CRON脚本,此中五遍SQL查询的间距时间大于10秒的话,那么那一个装置就有标题了(当然,那亦非不可能一蹴即至的标题,你能够在程序里平日mysql_ping一下,以便服务器知道你还活着,重新总计wait_timeout时间):

mysql连接超时的参数设置

方今系统因为数据库连接超时的难点,现身了几遍故障。逐个检查了下my.cnf的配置难点,最后是代码设计有标题。刚巧就把持有的timeout参数都理二遍,首先数据Curry查一下看有哪些超时:

 

mysql> 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  | 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    |

+—————————–+———-+

12 rows in set (0.00 sec)

 

我们来深入分析下相继参数的意思:

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.1.23 and 5 seconds before that.

Increasing the connect_timeout value might help if clients frequently
encounter errors of the form Lost connection to MySQL server at ‘XXX’,
system error: errno.

 

表明:在赢得链接时,等待握手的过期时间,只在报届期有效,登陆成功那一个参数就不管事了。首假若为了幸免网络不好时应用重连招致连接数涨太快,常常默许就可以。

 

delayed_insert_timeout

 

手册描述:

How many seconds an INSERT DELAYED handler thread should wait for INSERT
statements before terminating.

解释:这是为MyISAM INSERT DELAY设计的过期参数,在INSERT
DELAY中止前等候INSERT语句的光阴。

 

innodb_lock_wait_timeout

 

手册描述:

The timeout in seconds an InnoDB transaction may wait 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 will
hang for at most this many seconds before issuing the following error:

 

1

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

When a lock wait timeout occurs, the current statement is not executed.
The current transaction is not rolled back. (To have the entire
transaction roll back, start the server with the
–innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See
also Section 13.6.12, “InnoDB Error Handling”.)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL
table lock does not happen inside InnoDB and this timeout does not apply
to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table
immediately and rolls back one transaction. The lock wait timeout value
does not apply to such a wait.

For the built-in InnoDB, this variable can be set only at server
startup. For InnoDB Plugin, it can be set at startup or changed at
runtime, and has both global and session values.

 

分解:描述十分短,同理可得,就是职业碰到锁等待时的Query超时时间。跟死锁不相符,InnoDB后生可畏旦检查评定到死锁立时就能够回滚代价小的至极事情,锁等待是从未死锁的意况下叁个工作有着另一个专门的学问必要的锁能源,被回滚的早晚是央求锁的十一分Query。

innodb_rollback_on_timeout

 

手册描述:

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction
timeout by default. If –innodb_rollback_on_timeout is specified, a
transaction timeout causes InnoDB to abort and roll back the entire
transaction (the same behavior as in MySQL 4.1). This variable was added
in MySQL 5.1.15.

 

演讲:那些参数关闭或荒诞不经的话蒙受超时只回滚事务倒数Query,展开的话事务蒙受超时就回滚整个业务。

 

interactive_timeout/wait_timeout

手册描述:

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 the CLIENT_INTERACTIVE option to
mysql_real_connect(). See also

表达:一个穿梭SLEEP状态的线程多长时间被关闭。线程每便被采纳都会被晋升为acrivity状态,实践完Query后成为interactive状态,重新最初计时。wait_timeout不一致在于只效劳于TCP/IP和Socket链接的线程,意义是相像的。日常安装是8小时,日常网址白天都有人访问,从晚上到早晨貌似都会超过8小时,所以再来访谈就能够以此主题材料。

 

net_read_timeout / net_write_timeout

手册描述:

The number of seconds to wait for more data from a connection before
aborting the read. 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. 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. See also
slave_net_timeout.

On Linux, the NO_ALARM build flag affects timeout behavior as indicated
in the description of the net_retry_count system variable.

表明:那个参数只对TCP/IP链接有效,分别是数据库等待选取顾客端发送网络包和发送网络包给顾客端的超时时间,那是在Activity状态下的线程才使得的参数

 

slave_net_timeout

手册描述:

The number of seconds to wait for more data from the master before the
slave considers the connection broken, aborts the read, and tries to
reconnect. The first retry occurs immediately after the timeout. The
interval between retries is controlled by the MASTER_CONNECT_RETRY
option for the CHANGE MASTER TO statement or –master-connect-retry
option, and the number of reconnection attempts is limited by the
–master-retry-count option. The default is 3600 seconds (one hour).

释疑:这是Slave决断主机是或不是挂掉的晚点设置,在设定时期内依然未有赢得到Master的回答就人为Master挂掉了

近日系统因为数据库连接超时的主题材料,现身了四次故障。各个考察了下my.cnf的布局难题,最终是代码设计有题目。恰好…

在默许配置不转移的景况下,要是老是8钟头内都未曾访谈数据库的操作,再次做客mysql数据库的时候,mysql数据库会回绝访谈。

 

另附上常用字段列表

# vi /etc/my.cnf

抽薪止沸方案:

1,首先步向mysql,查看
wait_timeout、interactive_timeout那几个值是还是不是为私下认可的8小时(即 28800) 

图片 1

[mysqld]
wait_timeout=10

 

[[email protected]
~]# mysql -u root -p 

QQ拼音截图20171023214629.png

# /etc/init.d/mysql restart

先是种门路使用命令行在mysql提醒符下>set  global
wait_timeout=1814400
这种办法是后生可畏种暂且措施,重启服务就能够重回私下认可值了。

Enter password: 

但是那些点子太刚强了,线上劳动重启无论如何都应有尽量避免,看看哪些在MySQL命令行里通过SET来安装:

图片 2
第两种路子改正my.ini配置文件
[mysqld]

输密码步入 

mysql> set global wait_timeout=10;

wait_timeout=31536000 
interactive_timeout=31536000 
在mysqld下边增添以上两行,后面包车型地铁数字是光阴
率先服务中找到mysql,然后右键属性,在可实施文件的门径中,使劲向后拖动鼠标就能够以看到见my.ini的公文了

实行如下命令 

mysql> show global variables like ‘wait_timeout’;
+—————————-+——-+
| Variable_name              | Value |
+—————————-+——-+
| wait_timeout               | 10    |
+—————————-+——-+

show variables like ‘%timeout%’; 

此地叁个轻巧把人搞蒙的地点是一旦查询时行使的是show
variables的话,会开采安装好像并从未生效,那是因为只有运用show
variables的话就同样使用的是show session
variables,查询的是会话变量,唯有利用show global
variables,查询的才是全局变量。

+—————————-+——-+ 

网络上许五人都对天长叹说她们set global之后采纳show
variables查询未有发觉改造,原因就在于混淆了对话变量和全局变量,假如只是想矫正会话变量的话,能够采纳相似set
wait_timeout=10;或者set session wait_timeout=10;这样的语法。

| Variable_name              | Value | 

另二个值得注意的是会话变量wait_timeout开始化的主题素材,那点在手册里已经分明提出了,笔者就径直拷贝了:

+—————————-+——-+ 

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()).

| connect_timeout            | 10    | 

图片 3

| delayed_insert_timeout     | 300   | 

| innodb_lock_wait_timeout   | 50    | 

| innodb_rollback_on_timeout | OFF   | 

| interactive_timeout        | 28800 | 

| net_read_timeout           | 30    | 

| net_write_timeout          | 60    | 

| slave_net_timeout          | 3600  | 

| table_lock_wait_timeout    | 50    | 

| wait_timeout               | 28800 | 

+—————————-+——-+ 

日常来讲表所示,表明连接超时时间使用的是暗中同意的8钟头 

修改之 

2.在mysql 5以上的版本供给更正my.cnf这么些文件,那个文件之处在 

/etc/my.cnf 

在[mysqld]末尾加上如下两句话 

wait_timeout=388000 

interactive_timeout=388000 

3.下边重启下mysql 

service mysqld restart 

呈现如下内容 

停止 MySQL:                                               [确定] 

启动 MySQL:                                               [确定] 

目前您能够在用1的主意,查看下那八个参数的值 

+—————————-+——–+ 

| Variable_name              | Value  | 

+—————————-+——–+ 

| connect_timeout            | 10     | 

| delayed_insert_timeout     | 300    | 

| innodb_lock_wait_timeout   | 50     | 

| innodb_rollback_on_timeout | OFF    | 

| interactive_timeout        | 388000 | 

| net_read_timeout           | 30     | 

| net_write_timeout          | 60     | 

| slave_net_timeout          | 3600   | 

| table_lock_wait_timeout    | 50     | 

| wait_timeout               | 388000 | 

+—————————-+——–+ 

OK,校订好了 

 

1,首先步向mysql,查看
wait_timeout、interactive_timeout那些值是不是为暗中认可的8钟头(即 28800)
[[email protected]
~]# mysql -u root…

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图