[MySQL] Limit connection per user

Is a single website wreaking havoc on your MySQL server by saturating all its sockets ? If so you need to set in place some limits.

The max_user_connections variable

You are likely familiar with the max_connections setting, but there is also the less know max_user_connections. Its name is self-explanatory.

Limit for a specific user

If you need to be more granular, it’s also possible to limit connections for a specific user, like this:

GRANT ALL ON DBNAME.* TO XXX@YYY with MAX_USER_CONNECTIONS 100;

You can use other limit options like MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR or MAX_CONNECTIONS_PER_HOUR depending your needs.

Usual environment variables

Unix system use a lot of environment variables. Most of them are initialized when you log in and are maintained until you log out. This is a list of the most usual/usefull variables to know. Keep in mind, depending on the OS/distribution and your shell, some may or may not exist:

PATH – List directories the shell searches for commands
HOME – User's home directory
TERM – Indicate which terminal emulator is used
PS1 – Shell prompt in the Bourne shell and variants
MAIL – Path to user's mailbox
TEMP – Path to directory where processes can store temporary files
TZ – Timezone settings
PWD – Path of the current directory
HISTFILE – Name of the file in which command history is saved
HISTFILESIZE - Maximum number of lines contained in the history file
HOSTNAME - System's host name
LD_LIBRARY_PATH - List of directories where libraries should be searched for
USER - Current logged in user's name
DISPLAY - Network name of the X11 display to connect to, if available
SHELL - Indicate current shell path
TERMCAP – Display termcap database entry for the current term
OSTYPE – Type of operating system
MACHTYPE – The CPU architecture that the system is running on
EDITOR – The user's preferred text editor
PAGER –	The user's preferred text pager
MANPATH – List of directories to search for manual pages

You can also list all the environement variables on your current session, using the printenv command.