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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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 Generate Unique IDs For MySQL Cluster Backups
  2. What are Reserved User Accounts in MySQL
  3. How to Restore a Cluster Slave Using its Own Backups
  4. How to find location of MySQL configuration file(s)
  5. “expect” script to provide password to mysql_config_editor
  6. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  7. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  8. MySQL Cluster Point-In-Time Recovery (PITR)
  9. MySQL Error “Too many connections” and how to resolve it
  10. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP

You May Also Like

Primary Sidebar

Recent Posts

  • raw: command not found
  • raw Command Examples in Linux
  • rankmirrors Command Examples in Linux
  • radeontop: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright