One means of limiting use of MySQL server resources is to set the max_user_connections system variable to a nonzero value. However, this limits only the number of simultaneous connections made using a single account, and not what a client can do once connected. In addition, this method is strictly global, and does not allow for management of individual accounts. Both types of control are of interest to many MySQL administrators, particularly those working for Internet Service Providers.

In MySQL 5.1, you can limit the following server resources for individual accounts:

Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.

An “account” in this context corresponds to a single row in the user table. That is, connections are assessed against the User and Host values in the user table row that applies to the connection. Suppose that there is a row in the user table that has User and Host values of usera and %.example.com, to allow usera to connect from any host in the example.com domain. In this case, the server applies resource limits collectively to all connections by usera from any host in the example.com domain because all such connections use the same account.

Before MySQL 5.0.3, an “account” was assessed against the actual host from which a user connects. This older method accounting may be selected by starting the server with the --old-style-user-limits option. In this case, if usera connects simultaneously from host1.example.com and host2.example.com, the server applies the account resource limits separately to each connection. If usera connects again from host1.example.com, the server applies the limits for that connection together with the existing connection from that host.

The server limits account resources based on the resource-related columns of the user table in the mysql database: max_questions, max_updates, max_connections, and max_user_connections. If your user table does not have these columns, it must be upgraded; see Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.

To set resource limits, use the GRANT statement and provide a WITH clause that names each resource to be limited. For example, to create a new account that can access the customer database, but only in a limited fashion, issue these statements:

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. If the GRANT statement has no WITH clause, the limits are each set to the default value of zero (that is, no limit). For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections the account can make at any one time. If the limit is set to the default value of zero, the max_user_connections system variable determines the number of simultaneous connections for the account.

To modify limits for an existing account, use a GRANT USAGE statement at the global level (ON *.*). The following statement changes the query limit for francis to 100:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 100;

This statement leaves the account's existing privileges unchanged and modifies only the limit values specified.

To remove an existing limit, set its value to zero. For example, to remove the limit on how many times per hour francis can connect, use this statement:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    ->     WITH MAX_CONNECTIONS_PER_HOUR 0;

Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources.

As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.

Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.

Queries for which results are served from the query cache do not count against the MAX_QUERIES_PER_HOUR limit.

The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:

Counter resets do not affect the MAX_USER_CONNECTIONS limit.

All counts begin at zero when the server starts; counts are not carried over through a restart.

For the MAX_USER_CONNECTIONS limit, an edge case can occur if the account currently has open the maximum number of connections allowed to it: A disconnect followed quickly by a connect can result in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) if the server has not fully processed the disconnect by the time the connect occurs. When the server finishes disconnect processing, another connection will once more be allowed.