Recently I had a website that was getting hammered with traffic. Every hit on the website requires multiple MySQL SELECT statements. While SELECTs are typically quite fast, the database in question has several million records. Upgrading hardware isn’t an option for this website right now, and disabling the account is far from the best solution. The situation demanded a way to set a hard limit on MySQL requests for an individual user account.

MySQL supports global connection limits with the max_user_connections variable, but this is a server-wide option and will affect all accounts on the server. Fortunately, MySQL has capabilities for limiting resources on individual user accounts on a server using the GRANT statement.

Limiting account resource limits with MySQL is fairly straightforward. The idea is to modify individual user permissions with the GRANT statement, and this method allows for limiting queries, connections and UPDATE statements per hour. By issuing the correct MySQL statement you can set a specific limit, or remove limits entirely.

The basic syntax for setting resource limits on an account looks like this:

1
2

GRANT USAGE ON *.* TO 'username'@'localhost'
WITH MAX_QUERIES_PER_HOUR 60;

This statement tells MySQL to modify rights for username on all databases, and sets a limit of 60 queries per hour using the MAX_QUERIES_PER_HOUR limit type. There are a few different limit types that can be used:

MAX_QUERIES_PER_HOUR

Limits the account to X queries per hour.

MAX_UPDATES_PER_HOUR

Limits the account to X UPDATE statements per hour.

MAX_CONNECTIONS_PER_HOUR

Limits the account to X total connections per hour.

MAX_USER_CONNECTIONS

Limits the account to X total simultaneous connections for the account.

You can combine all these limit types to limit resource usage quite specifically:

1
2
3
4
5

GRANT ALL ON *.* TO 'username'@'localhost'
WITH MAX_QUERIES_PER_HOUR 100
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 20
MAX_USER_CONNECTIONS 5;

You can also set limits for specific databases by modifying the GRANT statement slightly:

1

GRANT ALL ON mydatabase.* TO 'username'@'localhost' ...

Check out the full reference on resource limits to learn all the details straight from the source.

So far this has worked out as an excellent temporary solution to prevent server bog from individual accounts. In production implementations, you should of course take into consideration how this impacts your users, and handle refused DB requests appropriately on your website.