MySQL好玩新特性：离线模式

2023-05-12 09:21 由 GreatSQL 发表于 #数据库

GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
作者：Yejinrong/叶金荣
文章来源：GreatSQL社区原创

继续吹MySQL 8.0~

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

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

mysql> set global offline_mode = on; -- 打开离线模式，拒绝外部请求

mysql> set global offline_mode = off; -- 关闭离线模式，允许外部连接请求

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

1. 无法创建新连接

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

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

$ mysql -S/data/MySQL/mysql.sock
...
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 304 | yejr | localhost | NULL | Query   |    0 | init  | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

[sbtest]>select *,sleep(10) from t1 limit 3; -- 正在运行的SQL会立即被终止，并断开连接
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 3032 (HY000): The server is currently in offline mode
ERROR:
Can't connect to the server

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

[ 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
[ 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
[ 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
[ 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
FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT'
(last message repeated 1 times)
FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest1 WHERE id=4822870'
FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT'
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'
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权限等，细化离线模式的权限管理模式。

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

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

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

mysql> show grants for u1;
+----------------------------------------+
| Grants for u1@%                        |
+----------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`         |
| GRANT SELECT ON `sbtest`.* TO `u1`@`%` |
+----------------------------------------+

mysql> show grants for u2;
+----------------------------------------+
| Grants for u2@%                        |
+----------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`         |
| GRANT SYSTEM_USER ON *.* TO `u2`@`%`   |
| GRANT SELECT ON `sbtest`.* TO `u2`@`%` |
+----------------------------------------+

用户 yejr 的授权模式如下

+--------------------------------------------------------------------+
| Grants for yejr@%                                                  |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yejr`@`%`                                   |
| GRANT CONNECTION_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `yejr`@`%` |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO `yejr`@`%`                   |
+--------------------------------------------------------------------+

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

# 三个用户先分别建立连接
$ jobs
[1]   Stopped                 mysql -h127.0.0.1 -uyejr -pxx -P4306 sbtest
[2]-  Stopped                 mysql -h127.0.0.1 -uu2 -pxx -P4306 sbtest
[3]+  Stopped                 mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest

# 设置离线模式
$ fg 1
mysql -h127.0.0.1 -uyejr -pxx -P4306 sbtest
[yejr@db160] [sbtest]>set global offline_mode=on;
Query OK, 0 rows affected (0.00 sec)

# u1用户被断开连接
$ fg 3
mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest
[u1@db160] [sbtest]>select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 3032 (HY000): The server is currently in offline mode
ERROR:
Can't connect to the server

# u2用户不会被断开连接
$ fg 2
mysql -h127.0.0.1 -uu2 -pxx -P4306 sbtest

[u2@db160] [sbtest]>select 1;
+---+
| 1 |
+---+
| 1 |
+---+

# 但u1/u2用户均不能再建立新链接
$ mysql -h127.0.0.1 -uu2 -pxx -P4306 sbtest
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3032 (HY000): The server is currently in offline mode

$ mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest
mysql: [Warning] Using a password on the command line interface can be insecure.
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

Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交流群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

数据库定时备份linux篇

(数据库定时备份linux篇) 1 序言相信大家都还记得这则新闻吧，欧洲云计算巨头 OVH 位于法国斯特拉斯堡的机房发生严重火灾，大火彻底摧毁了五层高、占地 500 平方米的 SBG2 数据中心。当地报纸称 115 位消防员投入 6 个小时才将其扑灭。经过长达 6 个小时的持续燃烧，SBG2 内 ...阅读全文

通过空间占用和执行计划了解SQL Server的行存储索引

1 索引介绍索引是一种帮助查询语句能够快速定位到数据的一种技术。索引的存储方式有行存储索引、列存储索引和内存优化三种存储方式：行存储索引，使用B+树结构，行存储指的是数据存储格式为堆、聚集索引和内存优化表的表，用于OLTP场景。行存储索引按顺序排列的值列表，每个值都有指向其所在的数据页面的指针。 ...阅读全文

数据库定时备份winserver2012篇

(数据库定时备份winserver2012篇) 1 序言数据是无价的，所以生产环境中定时备份数据库显得尤为重要。备份能防止服务器故障、天灾人祸和人为误操作带来的数据丢失。上一篇文章我们说了Linux环境下的数据备份。这一篇就把之前留下的坑给填上了。这一篇我们说一说winserver2012环境 ...阅读全文

MySQL外键约束和多表查询

外键约束和多表查询一、外键是什么图解知识点外键: 多个表之间的关联字段特点1: 从表外键的值是对主表主键的引用。特点2: 从表外键类型，必须与主表主键类型一致。主从表: 外键字段所在的表是从表,依赖字段对应的表是主表多表关系: 一对一一对多多对多一对多关系: 主表是一方从表是 ...阅读全文

数据治理三大模式详解，治理新范式释放数据潜能

随着世界经济由工业经济向数字经济转型，数据逐步成为关键的生产要素，企业开始将数据作为一种战略资产进行管理。数据从业务中产生，在IT系统中承载，要对数据进行有效治理，需要业务充分参与，IT系统确保遵从，这是一个非常复杂的系统工程。数据治理架构实践证明，企业只有构筑一套企业级的数据治理综合体系，明确 ...阅读全文

Apache DolphinScheduler 开源之夏学生项目申请开启，6 大课题等你来拿万元奖金！

开源之夏 2023 学生报名已经正式开启！Apache DolphinScheduler 今年继续参与开源之夏的活动，2023 年 4 月 29 日-6 月 3 日 15:00 UTC+8，同学们可以在开源之夏官网 https://summer-ospp.ac.cn/ 找到 Apache Dolph ...阅读全文

共筑数字化未来，金山办公携手华为云完成文档中心和GaussDB适配

摘要：金山办公携手华为云完成金山办公自主研发的“WPS文档中心系统”与华为云GaussDB相互兼容性测试认证，并获得华为云授予的《技术认证书》。本文分享自华为云社区《共筑数字化未来金山办公携手华为云完成文档中心和GaussDB适配》，作者：GaussDB 数据库。近日，金山办公携手华为云完成金 ...阅读全文

数据剖析更灵活、更快捷，火山引擎 DataLeap 动态探查全面升级

更多技术交流、求职机会，欢迎关注字节跳动数据平台微信公众号，回复【1】进入官方交流群近期，火山引擎 DataLeap 上线“动态探查”能力，为用户提供全局数据视角、完善的抽样策略，提高数据探查的灵活度以及响应速率。传统的数据探查是基于库表的全量探查，由后端引擎执行，通过自动化检查数据成分、关系、 ...阅读全文

Oracle 定时任务job实际应用

(Oracle 定时任务job实际应用) 一、Oracle定时任务简介 Oracle定时任务是在oracle系统中一个非常重要的子系统，运用得当，可以大大提高我们系统运行和维护能力。oracle定时任务的功能，可以在指定的时间点自行执行任务。那么在实际工作中，什么样的场景会用到定时任务呢？下面是在 ...阅读全文

V$INDEX_USAGE_INFO中找不到监控索引数据的尴尬经历

在一个Oracle 12.1实例中，想监控一个表的索引使用情况，在system用户下执行了下面脚本以及输出的监控索引脚本后，发现V$OBJECT_USAGE下一直没有对应的记录（在system用户下查询） SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME|| ...阅读全文