Using MySQL Offline Mode to Disconnect Client Connections

As a DBA, one common task is to batch stop/start MySQL services and ensure no active connections remain; this article explains how to use the pt‑kill tool and the MySQL global variable offline_mode to place the server in maintenance mode, disconnecting non‑privileged clients.

The offline_mode variable instantly drops connections that lack SYSTEM_VARIABLES_ADMIN and CONNECTION_ADMIN privileges and prevents new connections unless the user has those privileges. From MySQL 8.0.31 onward, CONNECTION_ADMIN (or the deprecated SUPER ) is required to enable the mode.

To test the feature, a low‑privilege user app_user is created with only DDL/DML rights. The article shows how to run a sysbench workload, enable offline_mode=ON, and observe that all sysbench connections are terminated with an error while replication continues unaffected.

pt‑kill --host=192.168.11.11 --user=percona -p --sentinel /tmp/pt‑kill.sentinel2 --pid /tmp/pt‑kill.pid --victims all --match-command 'Query' --ignore-user 'pmm|rdsadmin|system_user|percona' --busy-time 10 --verbose --print --kill

SELECT concat('kill ',id,';') from information_schema.processlist where user='app_user';

mysql> create user app_user identified by 'App@!234TEst';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE on *.* to app_user;

# sysbench command example
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=10 --time=100 --mysql-db=sysbench --mysql-user=app_user --mysql-password='App@!234TEst' run

SELECT @@offline_mode;
SET GLOBAL offline_mode=1;
SELECT @@offline_mode;
SET GLOBAL offline_mode=0;

The conclusion recommends using offline_mode when performing maintenance, backups, heavy replication‑lag mitigation, or when an immediate termination of all client connections is required.

References: [1] pt‑kill documentation, [2] sysbench benchmarking guide.