如何根据 MySQL 中的用户登录设置连接超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/243644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to setup a connection timeout depending of the user login in MySQL
提问by acemtp
I have currently more than 100 connections in Sleep state.
我目前有 100 多个处于睡眠状态的连接。
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.
某些连接必须保持睡眠状态（并且不要关闭），因为它是永久连接，但其他一些连接（具有不同的用户名）来自某个 php 脚本，我希望它们能够非常快地超时。
Is it possible to setup a wait_timeout per user? and if yes, How?
是否可以为每个用户设置 wait_timeout？如果是，如何？
回答by Bill Karwin
There's no per-user timeout configuration, but you can set the wait_timeoutvalue 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.
没有每个用户的超时配置，但您可以wait_timeout动态设置该值。也就是说，在您以给定用户的身份建立连接后，您可以发出一条语句，将超时值更改为您希望该用户会话的超时值。
Try the following experiment in the mysql command-line client:
在mysql命令行客户端尝试以下实验：
mysql> SHOW VARIABLES LIKE 'wait_timeout';
...shows 28800 (i.e. 8 hours), which is the default wait_timout.
...显示 28800（即 8 小时），这是默认值wait_timout。
mysql> SET SESSION wait_timeout = 60;
mysql> SHOW VARIABLES LIKE 'wait_timeout';
...shows 60.
...显示 60。
Then you can quit the session, reconnect, and again the default wait_timeoutis 28800. So it's limited to the scope of the current session.
然后你可以退出会话，重新连接，再次默认wait_timeout是28800。所以它被限制在当前会话的范围内。
You can also open a second window and start a separate mysql client session, to prove that changing the wait_timeoutin one session does not affect other concurrent sessions.
您还可以打开第二个窗口并启动一个单独的 mysql 客户端会话，以证明更改wait_timeout一个会话中的 不会影响其他并发会话。
回答by Sych
You should set the following variables in your my.conf:
您应该在您的 中设置以下变量my.conf：
[mysqld]
interactive_timeout=180
wait_timeout=180
wait_timeoutis a timeout for automated connections(in my opinion more than 30 on a web server is too much).interactive_timeoutis a console interaction timeoutfor idle session.
wait_timeout是自动连接超时（在我看来，Web 服务器上超过 30 个太多了）。interactive_timeout是空闲会话的控制台交互超时。
回答by Bill Karwin
Another possibility: MySQL supports two different timeout variables, wait_timeoutfor non-interactive clients, and interactive_timeoutfor interactive clients.
另一种可能性：MySQL 支持两种不同的超时变量，wait_timeout用于非交互式客户端和interactive_timeout交互式客户端。
The difference between interactive and non-interactive clients seems to be simply whether you specified the CLIENT_INTERACTIVEoption when connecting.
交互式客户端和非交互式客户端之间的区别似乎只是您CLIENT_INTERACTIVE在连接时是否指定了选项。
I don't know if this helps you, because you need to somehow make mysql_real_connect()pass that option in its client_flagparameter. I'm not sure what language or interface you're using, so I don't know if it permits you to specify this connection flag.
我不知道这是否对您有帮助，因为您需要以某种方式mysql_real_connect()在其client_flag参数中传递该选项。我不确定你使用的是什么语言或界面，所以我不知道它是否允许你指定这个连接标志。
Anyway if you can pass that client flag, and you only need two different types of users, then you could configure wait_timeoutand interactive_timeoutdifferently in the MySQL server config, and then use the one with the shorter value when you want a given session to time out promptly.
无论如何，如果你可以通过该客户端的标志，而你只需要两种不同类型的用户，那么你可以配置wait_timeout并interactive_timeout在MySQL服务器配置不同，然后当你想要一个给定的会话超时及时使用带有较短值的一个.
回答by Toddius Zho
If you use Connector/J, you can use sessionVariablesin the client's JDBC URL like so: jdbc:mysql://hostname:3306/schema?sessionVariables=wait_timeout=600
如果使用Connector/J，则可以在客户端的 JDBC URL 中使用sessionVariables，如下所示：jdbc:mysql://hostname:3306/schema?sessionVariables=wait_timeout=600
Other connectors for other languages will probably allow the same.
其他语言的其他连接器可能会允许相同。
回答by Karthik Appigatla
init_connect will be executed whenever a user logs in, so we can write small case statement and set the value based on user. Please note that the init_connect won't be executed for super user.
init_connect 将在用户登录时执行，因此我们可以编写小 case 语句并根据用户设置值。请注意，不会为超级用户执行 init_connect。
mysql> SET GLOBAL init_connect="SET @@wait_timeout = CASE WHEN CURRENT_USER() LIKE 'app1@%' THEN '30' ELSE @@wait_timeout END";
mysql> SET GLOBAL init_connect="SET @@wait_timeout = CASE WHEN CURRENT_USER() LIKE 'app1@%' THEN '30' ELSE @@wait_timeout END";
回答by ferensick
http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html
http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html
It is possible to kill connections per user with pt-kill. You can schedule this or set up a background job to handle this.
可以使用 pt-kill 杀死每个用户的连接。您可以安排此操作或设置后台作业来处理此问题。
回答by Gary Richardson
I checked the mysql.usertable and it doesn't look like there is a setting there for it:
我检查了mysql.user表格，看起来那里没有设置：
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | | |
| x509_issuer | blob | NO | | | |
| x509_subject | blob | NO | | | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.00 sec)
Depending on whether you're using MySQLi or PDO, your PHP MySQL connections should either hang up when the request does, or be shared in a pool for the Apache process.
根据您使用的是 MySQLi 还是 PDO，您的 PHP MySQL 连接应该在请求执行时挂断，或者在 Apache 进程的池中共享。
For example, with PDO, to turn off persistent connections (I think this is the default), connect to your DB with:
例如，使用 PDO，要关闭持久连接（我认为这是默认设置），请使用以下命令连接到您的数据库：
$pdo = new PDO($dsn, $user, $pass, Array(PDO::ATTR_PERSISTENT => false));
$pdo = new PDO($dsn, $user, $pass, Array(PDO::ATTR_PERSISTENT => false));
If you want your scripts to use persistent connections, but you have too many connections open to your database in sleep mode, you should think about configuring your Apache's MaxServers, MaxSpareServers, MinSpareServersand StartServersso that not so many hang around when they aren't needed.
如果你希望你的脚本中使用永久连接，但你有太多的连接，打开在休眠模式下你的数据库，你应该想想你的配置Apache的MaxServers，MaxSpareServers，MinSpareServers并且StartServers使没有那么多流连在不需要时他们。
