In MySQL 5.7, is there a way to limit specific type of queries up to a certain limit? select queries for instance to be max 100 query at a time.

And is it doable to limit queries per host or user?


Yes you can. You can create users with resource limits, e.g.:

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'some_password'
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

See here for more details: https://dev.mysql.com/doc/refman/5.7/en/user-resources.html

Since every connection can run at most one query at a time, for the specific case you are describing, you probably want to set MAX_USER_CONNECTIONS to 100.

  • But I can't specify the limitation for select queries only, correct? – Eng7 Apr 29 '20 at 11:40
  • 1
    No, it is not that finely grained. You could, however, give them two separate accounts, one with INSERT/UPDATE/DELETE privileges and one with SELECT privileges, and have different restrictions on those two accounts. That would also allow you to more easily implement read replica scaling by giving them write-only access on the master and read-only access on the replicas. – Gordan Bobic Apr 29 '20 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.