Dedicated Server How to Limit MySQL Usage for Users

Dedicated Server How to Limit MySQL Usage for Users

MySQL permits limits on dedicated server for individual database user accounts for the following:

A database user account in this sense is one with a unique database username and a unique database hostname. So, fred on remote.mysqlbox.tld is separate account from fred on localhost.

All of the limits listed above can be configured globally or per database user account.

Dedicated Server Global Limits

Simply edit the /etc/my.cnf with the desired limits and restart MySQL.

Let’s say you wanted to limit all users to 200 max connnections. You would add the following in your /etc/mysql.cnf and restart mysql (service mysqld restart):

max_user_connections=200

• da_admin
• root
• da_roundcube
• da_atmail

You’ll need to set these to a sufficiently high, non-zero value to bypass the global limit.

Per-User Limits

Create a new user or edit an existing user with the limits using SQL statements. You can use Create statements for new users and Alter statements for existing users. Since DirectAdmin will likely be creating your users for you, you will need only the Alter syntax.

Note that you may have to log out of the mysql> CLI session and back in to see per-User limit changes applied.

Syntax:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf
ALTER USER 'fred'@'localhost' WITH MAX_QUERIES_PER_HOUR 200;
quit

Using Both Global and Per-User Limits

If a user has a non-zero limit, that limit is used. Otherwise, the global limit is used.

If the user limit is 0, and the global limit is non-zero, then the global limit is used. If both the user limit and the global limit are 0, then the limit is unlimited.

A user limit could exceed the global limit, thus allowing the useful functionality of setting a maximum value globally, and only allowing certain users to bypass this.

Automating per-User MySQL Limits

Below is 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. The script originates from the forum post here:

https://forum.directadmin.com/threads/how-to-limit-mysql-usage-for-users.34588/open in new window

Copy and paste the code below to both of the following files:

• /usr/local/directadmin/scripts/custom/database_user_create_post.sh
• /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;

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

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;