1

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?

Improve this question

1 Answer 1

Reset to default
3

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

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'some_password'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          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.

Improve this answer
2
  • But I can't specify the limitation for select queries only, correct?
    – Eng7
    Apr 29, 2020 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 Bobić
    Apr 29, 2020 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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