Mysql – how to disconnect users to MySQL but still allow admin login

blockMySQL

Silly questions from a beginner: I wonder what is the best approach to : disconnect all users to MysQL but still allow console interactive connection, or admin connected from a MySQL Workbench?

I found various posts about killing connections, but if you just kill them, the users/applications may attempt to connect again.

I suppose I could kill the connections and remove the users that I want to block from mysql.user table, then after I've done the changes, put them back? I'd think there must be a better approach..
many thanks

–edit–

Thanks for the replies! I tried the 1st suggestion, ie. set max_user_connections = 1.
When I executed that set cmd, I have 2 connections: i) console login as root, from where I ran this cmd; ii) MySQL Workbench as a user running on a desktop PC.

The problem is that after the execution, I could still run select queries from the MySql workbench as the user. It is AFTER I closed MySQL workbench on the PC, then restarted, a new connection was refused.

In short, running this cmd does not disconnect existing connections, but will only stop new connections.

Does it mean I must kill all user connections first, then set max_user_connections=1?

Best Answer

From your shell type the following:

From mysql console

set global max_user_connections=1;

That's it!

Related Topic