Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Eg:

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

Thanks.

share|improve this question
2  
You can set it up for all users with max_user_connections or per account as described dev.mysql.com/doc/refman/5.5/en/user-resources.html –  a1ex07 Feb 21 '13 at 17:17
add comment

2 Answers 2

grant usage on *.* to user1234@'%' with max_user_connections 10;

Please run the above command.

share|improve this answer
add comment

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

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

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

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

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)

mysql>

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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