Is it possible to set a maximum number of open connections that a user (NOT all users combined, just one specific user) may have at a given time in mysql?


If I have a user "user1234", and want to only allow him to have a max 10 connections open at any given time.



You can set this per-user on a per-hour basis.

Under the WITH option for the MySQL GRANT command yu gave the following:


You can set

  • queries per hour
  • updates per hour
  • inserts per hour
  • connection per hour

This can give you finer per-hour granularity not only in terms of the number of connections made, but what can be executed.

The columns for these user grants are locate in mysql.user:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select column_name from information_schema.columns
    -> where table_schema='mysql'
    -> and table_name='user'
    -> and column_name like 'max%'
    -> order by ordinal_position;
| column_name          |
| max_questions        |
| max_updates          |
| max_connections      |
| max_user_connections |
4 rows in set (0.01 sec)


I wrote about these things before

Your Original Question

As shown above, you can set user per-hour user limits. However, I came to notice that you want just DB Connections open for any given user.

You may require some special customized authentication that counts how many connections a user has. In MySQL 5.1+ you could possibly script this:

SELECT COUNT(1) ConnectionCount,user
FROM information_schema.processlist
WHERE user <> 'system user'
GROUP BY user;

This would allow you to know the counts for any connected user.

You may want to look into connection pooling and persistent connections to regulate the number of users connected.

| improve this answer | |
grant usage on *.* to user1234@'%' with max_user_connections 10;

Please run the above command.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.