5.8.4. Limiting Account Resources
One means of limiting use of MySQL server resources is to set
the max_user_connections system variable to a
non-zero value. However, this method is strictly global, and
does not allow for management of individual accounts. In
addition, it limits only the number of simultaneous connections
made using a single account, and not what a client can do once
connected. Both types of control are 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:
The number of queries that an account can issue per hour
The number of updates that an account can issue per hour
The number of times an account can connect to the server per
hour
Any statement that a client can issue counts against the query
limit. Only statements that modify databases or tables count
against the update limit.
It is also possible to limit the number of simultaneous
connections to the server on a per-account basis.
An account in this context is a single row in the
user table. Each account is uniquely
identified by its User and
Host column values.
As a prerequisite for using this feature, the
user table in the mysql
database must contain the resource-related columns. Resource
limits are stored in the max_questions,
max_updates,
max_connections, and
max_user_connections columns. If your
user table doesn't have these columns, it
must be upgraded; see Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
To set resource limits with a GRANT
statement, use a WITH clause that names each
resource to be limited and a per-hour count indicating the limit
value. For example, to create a new account that can access the
customer database, but only in a limited
fashion, issue this statement:
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
-> IDENTIFIED BY 'frank'
-> 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
indicating 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 set or change 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
non-zero 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.
The current per-hour resource-use counts can be reset globally
for all accounts, or individually for a given account:
To reset the current counts to zero for all accounts, issue
a FLUSH USER_RESOURCES statement. The
counts also can be reset by reloading the grant tables (for
example, with a FLUSH PRIVILEGES
statement or a mysqladmin reload
command).
The counts for an individual account can be set to zero by
re-granting it any of its limits. To do this, use
GRANT USAGE as described earlier and
specify a limit value equal to the value that the account
currently has.
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.
