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:
1 2 3 | 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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 10 11 | [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... |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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.
1 2 | mysql> set global offline_mode=0; 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
