How-To: Limit MySQL usage for users

smtalk

Administrator
Staff member
Joined
Aug 22, 2006
Messages
8,759
Location
LT, EU
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:

Code:
#!/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:
Code:
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:
Code:
#!/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;
 
Last edited:

gadgetsguy

Verified User
Joined
Nov 4, 2005
Messages
116
Location
Montreal, Canada
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)
 

neorder

Verified User
Joined
Oct 1, 2003
Messages
392
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

NoBaloney Internet Svcs - In Memoriam †
Joined
Jun 16, 2003
Messages
26,119
Location
California
These are links in DirectAdmin where you can insert your own code, if required.

Jeff
 

Freeaqingme

Verified User
Joined
Sep 9, 2008
Messages
23
Location
Holland
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

Administrator
Staff member
Joined
Aug 22, 2006
Messages
8,759
Location
LT, EU
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

Verified User
Joined
Jun 26, 2008
Messages
133
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?
 
Last edited:

jocker

Verified User
Joined
Oct 1, 2007
Messages
56
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
 

laptopmarket

New member
Joined
Jun 27, 2015
Messages
2
Problem with my website max_queries_per_hour = 10000

Hi

I am new to this forum also not much in to programming and database how do i fix the issue max_queries_per_hour

its happen every 20 to 30 min

My website is www.laptopmarket.ie




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:

Code:
#!/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:
Code:
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:
Code:
#!/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;
 

laptopmarket

New member
Joined
Jun 27, 2015
Messages
2
Problem with my website max_queries_per_hour = 10000

Hi I am new and having problem with the max_queries_per_hour im keep gettings since change of hosting is that hosting issue or database my web guy said hosting issue
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
14,105
Location
GMT +7.00
Hello,

If you bought a hosting account from a shared hosting company and face the issue with max_queries_per_hour, then you should either change your hosting company (if they do not offer less strict limits on a higher package) or start using a VPS.
 

ditto

Verified User
Joined
Apr 27, 2009
Messages
2,535
@smtalk, why can't I just set this in my.cnf? For example I want to set:

Code:
max_connections = 1000
max_user_connections = 200
But I am not completely sure if max_user_connections set in my.cnf will work? It should be for each database user, not each directadmin user. But when seeing your script, I am not sure if it is enough to set it in my.cnf?
 

smtalk

Administrator
Staff member
Joined
Aug 22, 2006
Messages
8,759
Location
LT, EU
max_user_connections can be set in my.cnf, but the thread is not only about it :) The reason why it sets max_user_connections this way is because you could have different limit for different users, and it's not possible to do it with my.cnf. I hope that answers your question.
 
Top