写点什么

MySQL 好玩新特性:离线模式

作者:GreatSQL
  • 2023-05-12
    福建
  • 本文字数:3378 字

    阅读完需:约 11 分钟

  • 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 sbtestmysql: [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 queryNo connection. Trying to reconnect...ERROR 3032 (HY000): The server is currently in offline modeERROR: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.00FATAL: 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权限等,细化离线模式的权限管理模式。


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


  1. 必须要有 CONNECTION_ADMIN 以及 CONNECTION_ADMIN权限 或者 SUPER权限(SUPER权限在未来会被废弃,而细分成更多细粒度权限),才能在线设置离线模式。

  2. 复制线程不会受到离线模式影响,还能正常工作。

  3. 当设置为离线模式时,没有授予 CONNECTION_ADMINSUPER 权限的普通用户,正在执行的 SQL 会被立即终止,连接也会被立即断开。

  4. 当设置为离线模式时,拥有 CONNECTION_ADMINSUPER 权限的用户,不会被断开连接。

  5. 当设置离线模式的用户不具备 SYSTEM_USER 权限(只拥有 CONNECTION_ADMIN 以及 CONNECTION_ADMIN权限)的话,拥有 SYSTEM_USER 权限的活跃用户连接不会被断开(因为想要断开 SYSTEM_USER 权限级别用户连接同样需要至少有 SYSTEM_USER 权限),详见下面的案例。


u1u2 两个用户,授权模式不同


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 1mysql -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 3mysql -h127.0.0.1 -uu1 -pxx -P4306 sbtest[u1@db160] [sbtest]>select 1;ERROR 2013 (HY000): Lost connection to MySQL server during queryNo connection. Trying to reconnect...ERROR 3032 (HY000): The server is currently in offline modeERROR:Can't connect to the server
# u2用户不会被断开连接$ fg 2mysql -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 sbtestmysql: [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 sbtestmysql: [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

发布于: 刚刚阅读数: 3
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
MySQL好玩新特性:离线模式_MySQL_GreatSQL_InfoQ写作社区