max connect mysql_mysql设置max_user_connections及其使用MySQL GUI Tools要注意的点

0. 背景介绍

随着项目的演进，数据库的操作也越来越频繁，近期mysql数据库做了权限控制，防止开发、运营、维护等环节中因为误操作而导致的数据库风险。相信不少人都做过这样的工作，这里如何设置mysql权限不是重点，而是权限设置中的一个可选参数max_user_connections是今天讨论的主题。

1.问题描述

在GRANT官方手册中，可选参数的最后有这么几个选项：

GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH {GRANT OPTION | resource_option} ...]

其中这个resource_option有四个选项：

resource_option: {

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

}

分别对应：

The number of queries an account can issue per hour

一个账号每小时可以发起查询操作的个数

The number of updates an account can issue per hour

一个账号每小时可以发起更新操作的个数

The number of times an account can connect to the server per hour

一个账号每小时可以连接服务器的次数

The number of simultaneous connections to the server by an account

一个账号同时可以连接服务器的个数,

说明：如果上述4个参数配置为0，则表示不限制。

在你操作的同时，为了防止他人盗用你的账户同时进行操作，而导致无法判定是谁进行了操作，因此感觉通过MAX_USER_CONNECTIONS限制可以同时连接的个数是极好的，所以毫不犹豫的选择配置这个参数

2. 配置方式

方式1

GRANT USAGE ON *.* TO db_user@localhost MAX_USER_CONNECTIONS 1;

方式2

UPDATE mysql.user SET max_user_connections = 1 WHERE user='db_user' AND host='localhost'; FLUSH PRIVILEGES;

方式3

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

max_connections = 400

max_user_connections=1

前两个是针对指定用户设置的，而方式3是针对全局的，并且方式3需要重启服务。使用方式1的grant执行后记得执行FLUSH PRIVILEGES;使之生效。

3. 问题来了

设置完成后打开终端执行mysql -uxxx -p -h xxx.xxx.xxx.xxx -P xxxx，输入密码，正常进入。

开启第二个终端执行mysql -uxxx -p -h xxx.xxx.xxx.xxx -P xxxx，输入密码，提示如下信息：

ERROR 1226 (42000): User 'xxx' has exceeded the 'max_user_connections' resource (current value: 1)。

看来设置完美生效。

就在认为完美结束权限设置的时候，一些喜欢MySQL GUI Tools的，如Navicat、Workbench、DataGrip的同事反馈无法连接成功。报的同样是上述的ERROR 1226 (42000): User 'xxx' has exceeded the 'max_user_connections' resource (current value: 1)。奇怪了，明明配置成功了，为什么会报超过connection的限制呢。

经过研究发现，MySQL GUI Tools的连接机制和终端这种方式不太一样。通过执行show databases;，返回：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| xxx_db |

+--------------------+

2 rows in set (0.05 sec)

发现，这里用户能访问2个database，而Navicat、DataGrip这类的MySQL GUI Tools在配置完成后，会将这两个schema都列出来连接，这个时候就相当于预启动两个终端分别连接use xxx_db 和 use information_schema.

为了验证这个猜想，这里通过上面的调整语句，将max_user_connections设置为2，然后通过DataGrip连接尝试，发现联通。通过create database test;再建立一个数据库。先断开Navicat这类的MySQL GUI Tools连接，再次连接，此时又会提醒ERROR 1226 (42000): User 'xxx' has exceeded the 'max_user_connections' resource (current value: 2)。

4.总结

1、MySQL GUI Tools会一次性展示所有的database，会为每个该用户有权限可操作的database预分配一个connection，所以设置max_user_connections用到该类工具的时候，一定要考虑到有权限可操作的database的个数。

2、建议max_user_connections配合还是使用终端这种方式，这样可以降低账号盗用的风险(此处只限于经常使用的情景，错峰使用无法保障)。

5.引用