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

- 此内容更新于:2014-12-30
主题:

目前我有100多个连接在睡眠状态。 一些连接必须保持睡眠状态(不要关闭),因为它的永久连接,但其他一些(用不同的用户名)来自一些php脚本,我希望他们超时非常快。 每个用户可以设置一个wait_timeout吗?如果是的,怎么了?

原文:

I have currently more than 100 connections in Sleep state.

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.

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

解决方案:
原文:

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.

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

mysql> SHOW VARIABLES LIKE 'wait_timeout';

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

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

...shows 60.

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.

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.

解决方案:
原文:

Another possibility: MySQL supports two different timeout variables, wait_timeout for non-interactive clients, and interactive_timeout for interactive clients.

The difference between interactive and non-interactive clients seems to be simply whether you specified the CLIENT_INTERACTIVE option when connecting.

I don't know if this helps you, because you need to somehow make mysql_real_connect() pass that option in its client_flag parameter. 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.

Anyway if you can pass that client flag, and you only need two different types of users, then you could configure wait_timeout and interactive_timeout differently in the MySQL server config, and then use the one with the shorter value when you want a given session to time out promptly.

解决方案:
你在my.conf应该设置以下变量: wait_timeout是自动连接的超时(在我看来30多个web服务器太多)。 interactive_timeout是一个控制台交互为空闲会话超时。
原文:

You should set the following variables in your my.conf:

[mysqld]
interactive_timeout=180
wait_timeout=180

wait_timeout is a timeout for automated connections (in my opinion more than 30 on a web server is too much).
interactive_timeout is a console interaction timeout for idle session.

解决方案:
如果你使用连接器/ J,你可以在客户的JDBC URL使用sessionVariables一样:JDBC:mysql:/ /主机名:3306 /模式? sessionVariables = wait_timeout = 600 其他连接器为其他语言可能会允许相同的。
原文:

If you use Connector/J, you can use sessionVariables in the client's JDBC URL like so: jdbc:mysql://hostname:3306/schema?sessionVariables=wait_timeout=600

Other connectors for other languages will probably allow the same.

解决方案:
原文:

I checked the mysql.user table and it doesn't look like there is a setting there for it:

+-----------------------+-----------------------------------+------+-----+---------+-------+
| 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.

For example, with PDO, to turn off persistent connections (I think this is the default), connect to your DB with:

$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, MinSpareServers and StartServers so that not so many hang around when they aren't needed.

解决方案:
http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html 它可以杀死pt-kill每个用户连接。你可以安排这个处理这个问题或建立一个背景的工作。
原文:

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.