Using MySQL Offline Mode To Disconnect All Client Connections

September 29, 2023

Author

Bhuvanes Waran

Insight for DBAs

MySQL

Share this Post:

As a DBA, one of the very frequent tasks is to stop/start MySQL service for batching or some other activities. Before stopping MySQL, we may need to check if there are any active connections; if there are, we may need to kill all those. Generally, we use pt-kill to kill the application connections or prepare kill statements using the select queries.

Example commands:

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 <br><br>select concat('kill ',id,';') from information_schema.processlist where user='app_user';

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 has a variable called offline_mode to set the server into maintenance mode. When you set this, it immediately disconnects all the client connections that don’t have SYSTEM_VARIABLES_ADMIN and CONNECTION_ADMIN privileges and does not allow new connections except if a user has them. If you are killing the connections manually or using pt-kill, you can’t avoid the new connection creation. But by using this mode, we can avoid the new connections. This is a global and dynamic variable, and we can set this mode when the server is running.

To enable the offline_mode, the user account must have the SYSTEM_VARIABLES_ADMIN privilege and the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege, which covers both of these privileges). CONNECTION_ADMIN is required from MySQL 8.0.31 and recommended in all releases to prevent accidental lockout. Let’s test this.

To test this, create a new user, “app_user,” with only the privileges for DDL/DML.

mysql> create user app_user identified by 'App@!234TEst';<br>Query OK, 0 rows affected (0.20 sec)<br><br>mysql> 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;<br>Query OK, 0 rows affected (0.00 sec)

mysql> create user app_user identified by 'App@!234TEst'; Query OK, 0 rows affected (0.20 sec) mysql> 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; Query OK, 0 rows affected (0.00 sec)

Start the sysbench tool using aap_user user.

[root@centos12 vagrant]# sysbench /usr/share/sysbench/oltp_read_write.lua --threads=10 --time=100  --mysql-db=sysbench --mysql-user=app_user --mysql-password='App@!234TEst' run<br><br>sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)<br><br>Running the test with following options:<br><br>Number of threads: 10<br><br>Initializing random number generator from current time<br><br>Initializing worker threads...

[root@centos12 vagrant]# sysbench /usr/share/sysbench/oltp_read_write.lua --threads=10 --time=100 --mysql-db=sysbench --mysql-user=app_user --mysql-password='App@!234TEst' run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 10 Initializing random number generator from current time Initializing worker threads...

mysql> show processlist;<br>+----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+<br>| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |<br>+----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+<br>| 5 | event_scheduler | localhost | NULL | Daemon | 2151 | Waiting for next activation | NULL | 2151034 | 0 | 0 |<br>| 9 | bhuvan | 192.168.33.11:50642 | NULL | Binlog Dump | 2102 | Source has sent all binlog to replica; waiting for more updates | NULL | 2102317 | 0 | 0 |<br>| 14 | bhuvan | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |<br>| 20 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 11 | 0 | 0 |<br>| 21 | app_user | localhost | sysbench | Execute | 0 | updating | DELETE FROM sbtest1 WHERE id=5000 | 6 | 0 | 0 |<br>| 23 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 8 | 0 | 0 |<br>| 24 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 18 | 0 | 0 |<br>| 25 | app_user | localhost | sysbench | Execute | 0 | updating | UPDATE sbtest1 SET c='99153469917-25523144931-18125321038-96151238215-88445737418-14906501975-136014 | 13 | 0 | 0 |<br>| 27 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 7 | 0 | 0 |<br>| 28 | app_user | localhost | sysbench | Execute | 0 | statistics | SELECT c FROM sbtest1 WHERE id=5003 | 0 | 0 | 0 |<br>| 29 | app_user | localhost | sysbench | Execute | 0 | updating | UPDATE sbtest1 SET c='84180675456-88426921120-90373546373-84823361786-77912396694-08592771856-912331 | 13 | 0 | 0 |<br>+----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+<br>13 rows in set (0.00 sec)

mysql> show processlist; +----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined | +----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 2151 | Waiting for next activation | NULL | 2151034 | 0 | 0 | | 9 | bhuvan | 192.168.33.11:50642 | NULL | Binlog Dump | 2102 | Source has sent all binlog to replica; waiting for more updates | NULL | 2102317 | 0 | 0 | | 14 | bhuvan | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 | | 20 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 11 | 0 | 0 | | 21 | app_user | localhost | sysbench | Execute | 0 | updating | DELETE FROM sbtest1 WHERE id=5000 | 6 | 0 | 0 | | 23 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 8 | 0 | 0 | | 24 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 18 | 0 | 0 | | 25 | app_user | localhost | sysbench | Execute | 0 | updating | UPDATE sbtest1 SET c='99153469917-25523144931-18125321038-96151238215-88445737418-14906501975-136014 | 13 | 0 | 0 | | 27 | app_user | localhost | sysbench | Execute | 0 | waiting for handler commit | COMMIT | 7 | 0 | 0 | | 28 | app_user | localhost | sysbench | Execute | 0 | statistics | SELECT c FROM sbtest1 WHERE id=5003 | 0 | 0 | 0 | | 29 | app_user | localhost | sysbench | Execute | 0 | updating | UPDATE sbtest1 SET c='84180675456-88426921120-90373546373-84823361786-77912396694-08592771856-912331 | 13 | 0 | 0 | +----+-----------------+---------------------+----------+-------------+------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+ 13 rows in set (0.00 sec)

While sysbench is running, set offline_mode=ON, and all connections from sysbech will get terminated. You will see errors in sysbench.

mysql> select @@offline_mode;<br>+----------------+<br>| @@offline_mode |<br>+----------------+<br>| 0 |<br>+----------------+<br>1 row in set (0.15 sec)<br>mysql> set global offline_mode=1;<br>Query OK, 0 rows affected (0.15 sec)<br>mysql> show processlist;<br>+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+<br>| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |<br>+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+<br>| 5 | event_scheduler | localhost | NULL | Daemon | 2178 | Waiting for next activation | NULL | 2178008 | 0 | 0 |<br>| 9 | bhuvan | 192.168.33.11:50642 | NULL | Binlog Dump | 2129 | Source has sent all binlog to replica; waiting for more updates | NULL | 2129291 | 0 | 0 |<br>| 14 | bhuvan | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |<br>+----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+<br>3 rows in set (0.01 sec)

mysql> select @@offline_mode; +----------------+ | @@offline_mode | +----------------+ | 0 | +----------------+ 1 row in set (0.15 sec) mysql> set global offline_mode=1; Query OK, 0 rows affected (0.15 sec) mysql> show processlist; +----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined | +----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 2178 | Waiting for next activation | NULL | 2178008 | 0 | 0 | | 9 | bhuvan | 192.168.33.11:50642 | NULL | Binlog Dump | 2129 | Source has sent all binlog to replica; waiting for more updates | NULL | 2129291 | 0 | 0 | | 14 | bhuvan | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 | +----+-----------------+---------------------+------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+ 3 rows in set (0.01 sec)

If you try to connect the DB with app_user when offline_mode=1 , it will not allow the connections and get an error message that the server is currently in offline mode. This offline_mode won’t affect the replication. You can see the processlist logs above, and the replication thread is not disconnected when we set offline_mode=1. To disable the offline_mode, set the value 0.

mysql> set global offline_mode=0;<br>Query OK, 0 rows affected (0.00 sec)

1	mysql> set global offline_mode=0;<br>Query OK, 0 rows affected (0.00 sec)

Conclusion

The offline_mode is a good option to put the server in maintenance mode. Just make sure the application users don’t have admin privileges and only the admin does. We can use the offline_mode in the following situations:

Before taking the DB server out for maintenance or its related activities, make the changes in the configuration file and persist in this mode until all the activities are complete.

While taking the backup from the server, we can set this offline_mode to avoid the load on the server and make the backup faster.

In case there was a huge replication on the replica due to the huge amount of queries, we can set this mode until the replication gets synchronized with the primary.

When you want to terminate all the application connections immediately.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

Download Percona Monitoring and Management Today