One means of limiting use of MySQL server resources is to set the
system variable to a nonzero value. This limits the number of
simultaneous connections that can be made by any given account,
but places no limits on what a client can do once connected. In
max_user_connections does not
enable 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 use of 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
The number of simultaneous connections to the server by an account
Any statement that a client can issue counts against the query limit (unless its results are served from the query cache). Only statements that modify databases or tables count against the update limit.
An “account” in this context corresponds to a row in
mysql.user table. That is, a connection is
assessed against the
Host values in the
table row that applies to the connection. For example, an account
'usera'@'%.example.com' corresponds to a row in
user table that has
Host values of
%.example.com, to permit
usera to connect from any host in the
example.com domain. In this case, the server
applies resource limits in this row collectively to all
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
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.
To set resource limits for an account, use the
GRANT statement (see
GRANT Syntax). Provide a
that names each resource to be limited. The default value for each
limit is zero (no limit). For example, to create a new account
that can access the
customer database, but only
in a limited fashion, issue these statements:
CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';mysql>
GRANT ALL ON customer.* TO 'francis'@'localhost'->
WITH MAX_QUERIES_PER_HOUR 20->
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. For
MAX_USER_CONNECTIONS, the limit is an integer
representing the maximum number of simultaneous connections by the
account. If this limit is set to zero, the global
variable value determines the number of simultaneous connections.
max_user_connections is also
zero, there is no limit for the account.
To modify existing limits for an account, use a
statement at the global level (
ON *.*). The
following statement changes the query limit for
francis to 100:
GRANT USAGE ON *.* TO 'francis'@'localhost'->
WITH MAX_QUERIES_PER_HOUR 100;
The statement modifies only the limit value specified and leaves the account otherwise unchanged.
To remove a limit, set its value to zero. For example, to remove
the limit on how many times per hour
can connect, use this statement:
GRANT USAGE ON *.* TO 'francis'@'localhost'->
WITH MAX_CONNECTIONS_PER_HOUR 0;
As mentioned previously, the simultaneous-connection limit for an
account is determined from the
MAX_USER_CONNECTIONS limit and the
variable. Suppose that the global
max_user_connections value is 10
and three accounts have resource limits specified with
GRANT ... TO 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0; GRANT ... TO 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5; GRANT ... TO 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;
user1 has a connection limit of 10 (the global
because it has a zero
connection limits of 5 and 20, respectively, because they have
The server stores resource limits for an account in the
user table row corresponding to the account.
max_connections columns store the per-hour
limits, and the
MAX_USER_CONNECTIONS limit. (See
Section 4.2, “Privilege System Grant Tables”.) If your
user table does not have these columns, it must
be upgraded; see mysql_upgrade — Check and Upgrade MySQL Tables.
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.
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
USER_RESOURCES statement. The counts also can be
reset by reloading the grant tables (for example, with a
PRIVILEGES statement or a mysqladmin
The counts for an individual account can be set to zero by
re-granting it any of its limits. To do this, use
as described earlier and specify a limit value equal to the
value that the account currently has.
Counter resets do not affect the
All counts begin at zero when the server starts; counts are not carried over through a restart.
MAX_USER_CONNECTIONS limit, an edge
case can occur if the account currently has open the maximum
number of connections permitted to it: A disconnect followed
quickly by a connect can result in an error
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 permitted.