Rechercher dans le manuel MySQL
6.2.20 Setting Account Resource Limits
One means of restricting client use of MySQL server resources is
to set the global
max_user_connections
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
addition, setting
max_user_connections
does not
enable management of individual accounts. Both types of control
are of interest to MySQL administrators.
To address such concerns, MySQL permits limits for individual accounts on use of these server resources:
The number of queries an account can issue per hour
The number of updates 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. Only statements that modify databases or tables count against the update limit.
An “account” in this context corresponds to a row in
the mysql.user
system table. That is, a
connection is assessed against the User
and
Host
values in the user
table row that applies to the connection. For example, an account
'usera'@'%.example.com'
corresponds to a row in
the user
table that has User
and Host
values of usera
and
%.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
connections by usera
from any host in the
example.com
domain because all such connections
use the same account.
Before MySQL 5.0, an “account” was assessed against
the actual host from which a user connects. This older method of
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.
To establish resource limits for an account at account-creation
time, use the CREATE USER
statement. To modify the limits for an existing account, use
ALTER USER
. Provide a
WITH
clause 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:
- -> 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. 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
max_user_connections
system
variable value determines the number of simultaneous connections.
If max_user_connections
is also
zero, there is no limit for the account.
To modify limits for an existing account, use an
ALTER USER
statement. The following
statement changes the query limit for francis
to 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 francis
can connect, use this statement:
As mentioned previously, the simultaneous-connection limit for an
account is determined from the
MAX_USER_CONNECTIONS
limit and the
max_user_connections
system
variable. Suppose that the global
max_user_connections
value is 10
and three accounts have individual resource limits specified as
follows:
user1
has a connection limit of 10 (the global
max_user_connections
value)
because it has a MAX_USER_CONNECTIONS
limit of
zero. user2
and user3
have
connection limits of 5 and 20, respectively, because they have
nonzero MAX_USER_CONNECTIONS
limits.
The server stores resource limits for an account in the
user
table row corresponding to the account.
The max_questions
,
max_updates
, and
max_connections
columns store the per-hour
limits, and the max_user_connections
column
stores the MAX_USER_CONNECTIONS
limit. (See
Section 6.2.3, “Grant 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, the server rejects further connections for the account until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, the server rejects further queries or updates until the hour is up. In all such cases, the server issues appropriate error messages.
Resource counting occurs 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 aFLUSH PRIVILEGES
statement or a mysqladmin reload command).The counts for an individual account can be reset to zero by setting any of its limits again. Specify a limit value equal to the value currently assigned to the account.
Per-hour counter resets do not affect the
MAX_USER_CONNECTIONS
limit.
All counts begin at zero when the server starts. Counts do not carry over through server restarts.
For the 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_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 permitted.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-user_resources.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.