MySQL好玩新特性：离线模式

在以前，当需要对MySQL数据库进行维护操作时，通常需要先进行主从切换，然后修改设置并重启实例，关闭网络监听，只允许从本地socket方式登入，再进行相应的维护操作；有时候甚至还要修改相应的防火墙，或者干脆关闭前端业务服务，总体比较麻烦。

从MySQL 5.7开始，支持设置为离线模式（offline_mode），再有维护操作需求就不用这么麻烦了。只需在线动态修改，可立即生效，非常的简单粗暴：

1. mysql> set global offline_mode = on; -- 打开离线模式，拒绝外部请求 
2.  
3. mysql> set global offline_mode = off; -- 关闭离线模式，允许外部连接请求 

当设置为离线模式后，普通用户将无法继续发起连接请求，甚至当前正在执行的SQL也会立即被终止并被断开连接。

1. 无法创建新连接

1. $ mysql -h127.0.0.1 -uyejr -pxx -P4306 sbtest 
2. mysql: [Warning] Using a password on the command line interface can be insecure. 
3. ERROR 3032 (HY000): The server is currently in offline mode 

2. 即便是普通用户通过本地socket连接，当启用离线模式后，也会被断开

1. $ mysql -S/data/MySQL/mysql.sock 
2. ... 
3. mysql> show processlist; 
4. +-----+------+-----------+------+---------+------+-------+------------------+ 
5. | Id  | User | Host      | db   | Command | Time | State | Info             | 
6. +-----+------+-----------+------+---------+------+-------+------------------+ 
7. | 304 | yejr | localhost | NULL | Query   |    0 | init  | show processlist | 
8. +-----+------+-----------+------+---------+------+-------+------------------+ 
9. 1 row in set (0.00 sec) 
10.  
11. [sbtest]>select *,sleep(10) from t1 limit 3; -- 正在运行的SQL会立即被终止，并断开连接 
12. ERROR 2013 (HY000): Lost connection to MySQL server during query 
13. No connection. Trying to reconnect... 
14. ERROR 3032 (HY000): The server is currently in offline mode 
15. ERROR: 
16. Can't connect to the server 

3. 正在运行中的sysbench压测，也会被立即断开

1. [ 1s ] thds: 16 tps: 442.02 qps: 9078.28 (r/w/o: 6382.37/1795.94/899.96) lat (ms,99%,99%,99.9%): 150.29/150.29/150.29 err/s: 0.00 reconn/s: 0.00 
2. [ 2s ] thds: 16 tps: 471.23 qps: 9387.56 (r/w/o: 6576.19/1868.91/942.46) lat (ms,99%,99%,99.9%): 61.08/61.08/65.65 err/s: 0.00 reconn/s: 0.00 
3. [ 3s ] thds: 16 tps: 386.03 qps: 7712.68 (r/w/o: 5399.48/1541.14/772.07) lat (ms,99%,99%,99.9%): 82.96/82.96/84.47 err/s: 0.00 reconn/s: 0.00 
4. [ 4s ] thds: 16 tps: 547.00 qps: 10894.97 (r/w/o: 7609.98/2190.99/1094.00) lat (ms,99%,99%,99.9%): 65.65/65.65/68.05 err/s: 0.00 reconn/s: 0.00 
5. FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT' 
6. (last message repeated 1 times) 
7. FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest1 WHERE id=4822870' 
8. FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT' 
9. FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'UPDATE sbtest1 SET k=k+1 WHERE id=2265001' 
10. FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest8 WHERE id BETWEEN 3389984 AND 3390083' 

另外，从MySQL 8.0开始，对于离线模式又做了些改进和完善，比如新引入 CONNECTION_ADMIN权限等，细化离线模式的权限管理模式。

简单几点小结关于离线模式：

1. 必须要有 CONNECTION_ADMIN 以及 CONNECTION_ADMIN权限 或者 SUPER权限（SUPER权限在未来会被废弃，而细分成更多细粒度权限），才能在线设置离线模式。
2. 复制线程不会受到离线模式影响，还能正常工作。
3. 当设置为离线模式时，没有授予 CONNECTION_ADMIN 或 SUPER 权限的普通用户，正在执行的SQL会被立即终止，连接也会被立即断开。
4. 当设置为离线模式时，拥有 CONNECTION_ADMIN 或 SUPER 权限的用户，不会被断开连接。
5. 当设置离线模式的用户不具备 SYSTEM_USER 权限（只拥有 CONNECTION_ADMIN 以及 CONNECTION_ADMIN权限）的话，拥有 SYSTEM_USER 权限的活跃用户连接不会被断开（因为想要断开 SYSTEM_USER 权限级别用户连接同样需要至少有 SYSTEM_USER 权限），详见下面的案例。

有 u1 和 u2 两个用户，授权模式不同

1. mysql> show grants for u1; 
2. +----------------------------------------+ 
3. | Grants for u1@%                        | 
4. +----------------------------------------+ 
5. | GRANT USAGE ON *.* TO `u1`@`%`         | 
6. | GRANT SELECT ON `sbtest`.* TO `u1`@`%` | 
7. +----------------------------------------+ 
8.  
9. mysql> show grants for u2; 
10. +----------------------------------------+ 
11. | Grants for u2@%                        | 
12. +----------------------------------------+ 
13. | GRANT USAGE ON *.* TO `u2`@`%`         | 
14. | GRANT SYSTEM_USER ON *.* TO `u2`@`%`   | 
15. | GRANT SELECT ON `sbtest`.* TO `u2`@`%` | 
16. +----------------------------------------+ 

用户 yejr 的授权模式如下

1. +--------------------------------------------------------------------+ 
2. | Grants for yejr@%                                                  | 
3. +--------------------------------------------------------------------+ 
4. | GRANT USAGE ON *.* TO `yejr`@`%`                                   | 
5. | GRANT CONNECTION_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `yejr`@`%` | 
6. | GRANT ALL PRIVILEGES ON `sbtest`.* TO `yejr`@`%`                   | 
7. +--------------------------------------------------------------------+ 

当 yejr 用户设置离线模式后，u2 用户的连接不会被断开（但不能再建立新连接），而 u1 用户的连接会被断开

1. # 三个用户先分别建立连接 
2. $ jobs 
3. [1]   Stopped                 mysql -h127.0.0.1 -uyejr -pxx -P4306 sbtest 
4. [2]-  Stopped                 mysql -h127.0.0.1 -uu2 -pxx -P4306 sbtest 
5. [3]+  Stopped                 mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest 
6.  
7. # 设置离线模式 
8. $ fg 1 
9. mysql -h127.0.0.1 -uyejr -pxx -P4306 sbtest 
10. [yejr@db160] [sbtest]>set global offline_mode=on; 
11. Query OK, 0 rows affected (0.00 sec) 
12.  
13. # u1用户被断开连接 
14. $ fg 3 
15. mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest 
16. [u1@db160] [sbtest]>select 1; 
17. ERROR 2013 (HY000): Lost connection to MySQL server during query 
18. No connection. Trying to reconnect... 
19. ERROR 3032 (HY000): The server is currently in offline mode 
20. ERROR: 
21. Can't connect to the server 
22.  
23. # u2用户不会被断开连接 
24. $ fg 2 
25. mysql -h127.0.0.1 -uu2 -pxx -P4306 sbtest 
26.  
27. [u2@db160] [sbtest]>select 1; 
28. +---+ 
29. | 1 | 
30. +---+ 
31. | 1 | 
32. +---+ 
33.  
34. # 但u1/u2用户均不能再建立新链接 
35. $ mysql -h127.0.0.1 -uu2 -pxx -P4306 sbtest 
36. mysql: [Warning] Using a password on the command line interface can be insecure. 
37. ERROR 3032 (HY000): The server is currently in offline mode 
38.  
39. $ mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest 
40. mysql: [Warning] Using a password on the command line interface can be insecure. 
41. ERROR 3032 (HY000): The server is currently in offline mode 

是不是有点好玩呀~

结合前面的两篇文章 MySQL 8.0不再担心被垃圾SQL搞爆内存 以及 InnoDB buffer pool size进度更透明 可以看到MySQL 8.0在各个细节方面做的是越来越好了。

延伸阅读

• #sysvar_offline_mode, https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
• Changes in MySQL 8.0.31, https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html

 

原文地址：https://mp.weixin.qq.com/s/5atNn31T6ol5NLCn9OT1nQ