How-To: Limit MySQL usage for users

[smtalk]

I've created a script for limiting MySQL resources for every new MySQL user (it limits the number of queries, updates, and logins a MySQL user can perform). You can set any limits you want. Just copy-paste the following content to /usr/local/directadmin/scripts/custom/database_user_create_post.sh and /usr/local/directadmin/scripts/custom/database_create_post.sh:



#!/bin/sh
#This script sets the number of queries, updates, and logins a new MySQL user can perform

#Limits (setting these options to 0 removes the limit)
MAX_QUERIES_PER_HOUR=30000
MAX_UPDATES_PER_HOUR=20000
MAX_CONNECTIONS_PER_HOUR=5000
MAX_USER_CONNECTIONS=100

#We get DirectAdmin MySQL root user and password here
DA_MYSQL=/usr/local/directadmin/conf/mysql.conf
MYSQLUSER=`grep "^user=" ${DA_MYSQL} | cut -d= -f2`
MYSQLPASSWORD=`grep "^passwd=" ${DA_MYSQL} | cut -d= -f2`

mysql -e "GRANT ALL ON ${database}.* TO ${user}@'localhost' IDENTIFIED BY '${passwd}' WITH MAX_QUERIES_PER_HOUR ${MAX_QUERIES_PER_HOUR} MAX_UPDATES_PER_HOUR ${MAX_UPDATES_PER_HOUR} MAX_CONNECTIONS_PER_HOUR ${MAX_CONNECTIONS_PER_HOUR} MAX_USER_CONNECTIONS ${MAX_USER_CONNECTIONS};" --user=${MYSQLUSER} --password=${MYSQLPASSWORD}
exit 0;
Give them chmod 755:


chmod 755 /usr/local/directadmin/scripts/custom/database_user_create_post.sh
chmod 755 /usr/local/directadmin/scripts/custom/database_create_post.sh
And you're done. Happy using! :)

P.S. if you want to set a limit for all current MySQL users, use the following script:


#!/bin/sh

#Limits (setting these options to 0 removes the limit)
MAX_QUERIES_PER_HOUR=30000
MAX_UPDATES_PER_HOUR=20000
MAX_CONNECTIONS_PER_HOUR=5000
MAX_USER_CONNECTIONS=100

#We get DirectAdmin MySQL root user and password here
DA_MYSQL=/usr/local/directadmin/conf/mysql.conf
MYSQLUSER=`grep "^user=" ${DA_MYSQL} | cut -d= -f2`
MYSQLPASSWORD=`grep "^passwd=" ${DA_MYSQL} | cut -d= -f2`

mysql -e "use mysql; UPDATE mysql.user SET max_questions=${MAX_QUERIES_PER_HOUR}, max_updates=${MAX_UPDATES_PER_HOUR}, max_connections=${MAX_CONNECTIONS_PER_HOUR}, max_user_connections=${MAX_USER_CONNECTIONS} WHERE user!='da_admin' AND user!='root' AND user!='da_roundcube' AND user!='da_atmail'; FLUSH PRIVILEGES;" --user=${MYSQLUSER} --password=${MYSQLPASSWORD}

echo "Limits have been set."
exit 0;

[gadgetsguy]

A very handy script indeed .... thanx Martynas!! :)

I could have used this 2 days ago - LOL!! ... one of my shared servers' mysqld took on a life of its own.

After me and 2 others wasted a day troubleshooting (and found 2 other minor problems in the process), it turned out to be an outdated Pligg install (pre version 1.0)

[sokolovas]

.. TO ${user}@'localhost' IDENTIFIED BY ...

Martynas, what about users connecting from another hosts? ;)

[smtalk]

Use ${user}@'any_other_host' then :) % means wildcard.

P.S. It should be included into the next release of da: http://directadmin.com/features.php?id=1049

[neorder]

this is great, but i don't understand why these two command will be automatically executed whenever a database is created...


/usr/local/directadmin/scripts/custom/database_user_create_post.sh
/usr/local/directadmin/scripts/custom/database_create_post.sh

Sorry if my question sounds stupid...

[nobaloney]

These are links in DirectAdmin where you can insert your own code, if required.

Jeff

[Freeaqingme]

I don't want to spoil your game here; but how effective do you expect your script to be?

I can do 100.00 queries of: "SELECT 1;" or one monster query that does a million joins on virtual tables. The latter being way more heavier than the first query a 100.000 times.

All in all it may protect you from idiots who require a 1000 queries per pageload, but furthermore it doesn't really protect you from anything.

[smtalk]

Freeaqingme, one of DA forum users asked me to write a script and I did it. It helped him to control his resource usage. However, I agree that there are more effective ways to control that.

[defomaz]

hi thanks for creating this script

i have try this script and after create user/db from da, when i check phpmyadmin user value of MAX_QUERIES_PER_HOUR and other variable still 0
but when i run script manually from command line, it's work perfectly

i also test script with print something and then i create db from da. script also executed when db/user created.

am i miss something?

[jocker]

Hi smtalk,


However, I agree that there are more effective ways to control that.

Can you talk more about how you manage several users on a mysql server please ?

Thanks