I have for the first time seen a client implement MySQL Resource Limits. I got the following error tying to connect to the database.
$ mysql -udba -p ERROR 1226 (42000): User 'dba' has exceeded the 'max_user_connections' resource (current value: 10)
I see from the documentation the ability to see the limits in the mysql.user table. I see this is included in the SHOW GRANTS output.
SHOW GRANTS for 'dba'@'%'; +--------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for dba@% | +--------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*CAABA4CFB7E71E51477E0658FC2D2BBA1267E669' WITH MAX_USER_CONNECTIONS 10 | +--------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
The documentation includes details that you can flush the resource limits, however I have found no way to monitor the current usage.
I welcome any feedback from the MySQL Community.
The only ways I can think of are:
select count(*) count_dba_connections From information_schema.processlist where user=’dba’ and host=’%';
or in 5.0, “show processlist” and then outside of mysql filter out dba users which aren’t localhost.
Hi,
Agree to Ryna’s solution. However, there are even stricter rules which cannot be easily traced:
MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR
Unless you are *very lucky* to catch all connections/updates/queries while busy monitoring SHOW PROCESSLIST, I don’t recognize any way to know at any point in time how much of these resources a user has already consumed.