• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL: How to Set Account Resource Limits

By admin

Limit the use of server resources by setting the global MAX_USER_CONNECTIONS variable to a non-zero value. This limits the number of simultaneous connections by any one account but does not limit what a client can do when connected. To set resource limits for an account, use the GRANT statement with a WITH clause that names each resource to be limited. The default value for each limit is zero, indicating no limit. For example, to set limits for user Francis to access the customer database, issue the following statement:

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;

Limit the following server resources for individual accounts:

  • MAX_QUERIES_PER_HOUR: The number of queries that an account can issue per hour
  • MAX_UPDATES_PER_HOUR: The number of updates that an account can issue per hour
  • MAX_CONNECTIONS_PER_HOUR: The number of times an account can connect to the server per hour
  • MAX_USER_CONNECTIONS: The number of simultaneous connections allowed.

Provide resource limits in the WITH clause in any order. Set the MAX_USER_CONNECTIONS limit to 0 to set it to the global default, indicating that the maximum number of simultaneous connections allowed for this account is the global value of the max_user_connections system variable.

To reset an existing limit for any of the per-hour resources to the default of “no limit,” specify a value of 0, as in this example:

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

Filed Under: mysql

Some more articles you might also be interested in …

  1. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size
  2. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  3. What are Reserved User Accounts in MySQL
  4. Configuring mysqld to log slow queries
  5. Understanding the REVOKE statement in MySQL
  6. Backup and Restore Of Group Replication Node ( MySQL 8.0 )
  7. How to Disable Client Access Control in MySQL
  8. Understanding mysqlcheck and myisamchk utilities
  9. Understanding MySQL Pluggable Authentication
  10. MySQL ‘show processlist’ statement

You May Also Like

Primary Sidebar

Recent Posts

  • What are Command Rules in oracle Database
  • Using Rule Sets in Oracle Database Vault
  • How Realms Work in Oracle Database Vault
  • How to use Privilege Analysis in Oracle Database
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary