如何根据MySQL中的用户登录设置连接超时 [英] How to setup a connection timeout depending of the user login in MySQL

问题描述

我目前有超过100个处于睡眠状态的连接.

I have currently more than 100 connections in Sleep state.

某些连接必须保持睡眠状态(并且不要关闭)，因为它是永久连接，而其他一些(使用不同的用户名)则来自某些php脚本，我希望它们非常快地超时.

Some connection must stay in Sleep state (and don't close) because it's permanent connection but some others (with a different user name) are from some php script and I want them to timeout very fast.

是否可以为每个用户设置一个wait_timeout?如果可以，怎么办?

Is it possible to setup a wait_timeout per user? and if yes, How?

推荐答案

没有每个用户的超时配置，但是您可以动态设置wait_timeout值.也就是说，在以给定用户身份建立连接之后，您可以发出一条语句以将超时值更改为该用户会话所需的超时值.

There's no per-user timeout configuration, but you can set the wait_timeout value dynamically. That is, after you make a connection as a given user, you can issue a statement to change the timeout value to what you want it to be for that user's session.

在mysql命令行客户端中尝试以下实验:

Try the following experiment in the mysql command-line client:

mysql> SHOW VARIABLES LIKE 'wait_timeout';

...显示28800(即8小时)，这是默认的wait_timout.

...shows 28800 (i.e. 8 hours), which is the default wait_timout.

mysql> SET SESSION wait_timeout = 60;
mysql> SHOW VARIABLES LIKE 'wait_timeout';

...显示60.

然后，您可以退出会话，重新连接，默认的wait_timeout再次为28800.因此，它仅限于当前会话的范围.

Then you can quit the session, reconnect, and again the default wait_timeout is 28800. So it's limited to the scope of the current session.

您还可以打开第二个窗口并启动一个单独的mysql客户端会话，以证明在一个会话中更改wait_timeout不会影响其他并发会话.

You can also open a second window and start a separate mysql client session, to prove that changing the wait_timeout in one session does not affect other concurrent sessions.

这篇关于如何根据MySQL中的用户登录设置连接超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！