Home » Random Goodies » Limit MySQL usage for a specific user

Limit MySQL usage for a specific user

Feel the need to limit the usage of a specific user consuming too many resources without modifying global max_user_connections or max_updates etc? Luckily MySQL is smart enough to handle your needs 🙂

The following will limit max connections, updates and questions to 1 after which additional queries will be denied:
mysql -e "UPDATE mysql.user SET max_questions='1', max_updates='1', max_connections='1',max_user_connections='1' where user='resourcehog'"

If you’re on a cPanel/WHM server, the following would apply:
mysql -e "UPDATE mysql.user SET max_questions='1', max_updates='1', max_connections='1', max_user_connections='1' where user LIKE 'resourcehog_%'"

And of course, if you have the need to use a variable in a loop for example, you can use the following:
user=resourcehog;mysql -e "UPDATE mysql.user SET max_questions='1', max_updates='1', max_connections='1', max_user_connections='1' where user LIKE '$user\\\_%'"

To remove the limits, you can execute the same queries replacing 1 with 0. If you’re not looking to suspend their usage, then don’t set the max_questions or max_updates. Of course we already know these changes are permanent 😉