MySQL用户登录相关连接超时设置

MySQL是一款流行的关系型数据库，它允许多个用户通过不同的身份验证方式连接到同一数据库。连接超时是保证系统资源可用性和安全性的重要因素之一，因此我们需要根据不同用户的登录方式设置不同的连接超时时间，以优化数据库的性能和安全性。

阅读更多：MySQL 教程

MySQL的连接超时类型

MySQL提供了两种类型的连接超时：wait_timeout和interactive_timeout。其中，wait_timeout对于非交互用户（例如后台应用程序、自动化或其他脚本）是适用的，而interactive_timeout对于交互用户（例如MySQL shell、phpMyAdmin或其他GUI工具）是适用的。

wait_timeout定义了服务器在关闭空闲连接之前等待的时间，而interactive_timeout定义了服务器在关闭空闲交互式连接之前等待的时间。因此，在设置连接超时时间时，需要根据用户的身份验证方式来选择合适的选项。

以下是设置连接超时的命令以及它们的默认值：

SHOW VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE 'interactive_timeout';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| interactive_timeout  | 28800 |
+----------------------+-------+
1 row in set (0.00 sec)

通过用户来设置连接超时

我们可以通过设置用户的默认连接超时来控制它们的连接超时行为。可以通过以下命令更改用户的默认连接超时：

SET GLOBAL wait_timeout = 30;
SET GLOBAL interactive_timeout = 30;

以上命令将所有用户的连接超时时间设置为30秒。现在，如果查询wait_timeout或interactive_timeout，会看到它们的值已更改为30：

SHOW VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 30    |
+---------------+-------+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE 'interactive_timeout';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| interactive_timeout  | 30    |
+----------------------+-------+
1 row in set (0.00 sec)

为不同的用户设置不同的连接超时

如果您希望为每个用户单独设置连接超时时间，则需要创建单独的用户并设置其连接超时时间。假设我们有两个用户：user1和user2。user1是一个自动化脚本，只需要在系统空闲时连接，而user2是一个交互式MySQL shell用户。

1. 为user1设置等待超时

为了为user1设置连接超时时间，我们需要先创建它。我们可以使用以下命令创建一个新用户：

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1';

接下来，我们需要将用户授予指定的数据库和表的访问权限：

GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';

现在，我们可以使用以下命令为user1设置连接超时时间：

SET SESSION wait_timeout = 30;

以上命令将用户的等待超时值设置为30秒。为了确认连接超时时间是否已更新，请使用以下命令：

SHOW SESSION VARIABLES LIKE 'wait_timeout';

2. 为user2设置交互式连接超时

对于user2，我们可以使用以下命令设置其连接超时时间：

SET SESSION interactive_timeout = 300;

以上命令将user2的交互式连接超时设置为300秒。为了确认连接超时时间是否已更新，请使用以下命令：

SHOW SESSION VARIABLES LIKE 'interactive_timeout';

使用MySQL Proxy进行连接超时管理

我们也可以使用MySQL代理来更好地管理连接超时时间。MySQL Proxy是一种用于MySQL服务器的中间件，它可以将流量转发到不同的MySQL服务器，并对流量进行修改。通过使用MySQL Proxy，我们可以在运行时调整连接超时时间并根据需要更改它们。

以下是使用MySQL Proxy进行连接超时管理的步骤：

1. 安装MySQL Proxy

首先，需要将MySQL Proxy安装在服务器上。可以使用以下命令在Ubuntu上安装MySQL Proxy：

sudo apt-get install mysql-proxy

2. 编写Lua脚本

MySQL Proxy使用Lua脚本来处理和修改网络流量。以下是一个简单的Lua脚本，通过使用MySQL Proxy更改连接超时时间：

function read_query(packet)
  -- Set wait_timeout to 30 seconds for non-interactive users
  if packet:byte() == proxy.COM_QUERY then
    if string.match(packet:sub(2), "^SET ") then
      return
    end

    local client_flags = proxy.connection.client_capabilities
    if bit.band(client_flags, proxy_constants.CLIENT_INTERACTIVE) == 0 then  
      proxy.queries:append(1, string.char(proxy.COM_QUERY)..
        "SET @@SESSION.wait_timeout=30")
    end
  end
end

此脚本会将带有空闲非交互式用户的连接超时时间设置为30秒。可以根据需要更改此超时时间。

3. 启动MySQL Proxy

现在，可以使用以下命令启动MySQL Proxy：

mysql-proxy --proxy-lua-script=/path/to/lua/script.lua \
      --proxy-backend-addresses=127.0.0.1:3306 \
      --daemon

在此命令中，–proxy-lua-script选项引用我们刚刚编写的Lua脚本。–proxy-backend-addresses指定MySQL后端服务器的地址和端口号。–daemon选项使MySQL Proxy以守护进程模式运行。

总结

在MySQL中设置连接超时对于保护系统资源和确保安全性非常重要。通过使用MySQL的内置命令和MySQL Proxy，可以根据用户的身份验证方式为其设置不同的连接超时时间。这些技术和工具是管理大型MySQL部署的必备知识之一。