How-To: Limit MySQL usage for users

smtalk

smtalk

Administrator
Staff member
Joined
Aug 22, 2006
Messages
10,161
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:
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)
 
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
Click to expand...

Sorry if my question sounds stupid...
 
These are links in DirectAdmin where you can insert your own code, if required.

Jeff
 
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.
 
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.
 
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:
Hi smtalk,

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

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

Thanks
 
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




smtalk said:
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;
Click to expand...
 
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
 
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.
 
@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?
 
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.
 
You must log in or register to reply here.
Back
Top