MySQL Max Connections – Per Client or Global Setting?

max-connectionsMySQL

So I just need to clear this up.

For a test, I set max_connections=10 in my.cnf.

I then ran 10 scripts concurrently that each take over 20 seconds to finish.

I monitored my threads and noticed that when I hit 10 the Too many connections error fired.

That's fine, but I'm only one person. If there were multiple clients connected would they have 10 connected threads each, or between them?

Thanks

Best Answer

The option max_connections is a global setting.

You are limited to a total of 10 connections, no matter how many users are authenticating.

If you want to limit a specific user, such as myuser@localhost, to 10 connections, you would specify that with the GRANT command

GRANT USAGE ON *.* TO `myuser@localhost` MAX_USER_CONNECTIONS 10;

or hack it in like this

UPDATE mysql.user SET max_user_connections = 10
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you want 200 connections total, just set max_connections to 200.

You don't even have to restart mysqld.

Just do the following

STEP 01 : Add max_connections=200 to my.cnf under [mysqld] group header

STEP 02 : Login as root@localhost and run SET GLOBAL max_connections=200;

Related Question