自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL好玩新特性：離線模式

作者：Yejinrong/葉金榮
數(shù)據(jù)庫 MySQL
從MySQL 5.7開始，支持設(shè)置為離線模式（offline_mode），再有維護(hù)操作需求就不用這么麻煩了。只需在線動態(tài)修改，可立即生效，非常的簡單粗暴。

在以前，當(dāng)需要對MySQL數(shù)據(jù)庫進(jìn)行維護(hù)操作時，通常需要先進(jìn)行主從切換，然后修改設(shè)置并重啟實例，關(guān)閉網(wǎng)絡(luò)監(jiān)聽，只允許從本地socket方式登入，再進(jìn)行相應(yīng)的維護(hù)操作；有時候甚至還要修改相應(yīng)的防火墻，或者干脆關(guān)閉前端業(yè)務(wù)服務(wù)，總體比較麻煩。

從MySQL 5.7開始，支持設(shè)置為離線模式（offline_mode），再有維護(hù)操作需求就不用這么麻煩了。只需在線動態(tài)修改，可立即生效，非常的簡單粗暴：

mysql> set global offline_mode = on; -- 打開離線模式，拒絕外部請求

mysql> set global offline_mode = off; -- 關(guān)閉離線模式，允許外部連接請求

當(dāng)設(shè)置為離線模式后，普通用戶將無法繼續(xù)發(fā)起連接請求，甚至當(dāng)前正在執(zhí)行的SQL也會立即被終止并被斷開連接。

1. 無法創(chuàng)建新連接

$ 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連接，當(dāng)啟用離線模式后，也會被斷開

$ 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開始，對于離線模式又做了些改進(jìn)和完善，比如新引入 CONNECTION_ADMIN權(quán)限等，細(xì)化離線模式的權(quán)限管理模式。

簡單幾點小結(jié)關(guān)于離線模式：

  1. 必須要有 CONNECTION_ADMIN 以及 CONNECTION_ADMIN權(quán)限 或者 SUPER權(quán)限（SUPER權(quán)限在未來會被廢棄，而細(xì)分成更多細(xì)粒度權(quán)限），才能在線設(shè)置離線模式。
  2. 復(fù)制線程不會受到離線模式影響，還能正常工作。
  3. 當(dāng)設(shè)置為離線模式時，沒有授予 CONNECTION_ADMIN 或 SUPER 權(quán)限的普通用戶，正在執(zhí)行的SQL會被立即終止，連接也會被立即斷開。
  4. 當(dāng)設(shè)置為離線模式時，擁有 CONNECTION_ADMIN 或 SUPER 權(quán)限的用戶，不會被斷開連接。
  5. 當(dāng)設(shè)置離線模式的用戶不具備 SYSTEM_USER 權(quán)限（只擁有 CONNECTION_ADMIN 以及 CONNECTION_ADMIN權(quán)限）的話，擁有 SYSTEM_USER 權(quán)限的活躍用戶連接不會被斷開（因為想要斷開 SYSTEM_USER 權(quán)限級別用戶連接同樣需要至少有 SYSTEM_USER 權(quán)限），詳見下面的案例。

有 u1 和 u2 兩個用戶，授權(quán)模式不同

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 的授權(quán)模式如下

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

當(dāng) yejr 用戶設(shè)置離線模式后，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

# 設(shè)置離線模式
$ 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

是不是有點好玩呀~

結(jié)合前面的兩篇文章 MySQL 8.0不再擔(dān)心被垃圾SQL搞爆內(nèi)存 以及 InnoDB buffer pool size進(jìn)度更透明 可以看到MySQL 8.0在各個細(xì)節(jié)方面做的是越來越好了。

延伸閱讀

  • #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

責(zé)任編輯：武曉燕 來源： GreatSQL社區(qū)
MySQL離線模式生效
相關(guān)推薦

2009-01-16 10:01:57

MySQL復(fù)制特性測試

2021-01-29 09:58:55

MySQL數(shù)據(jù)庫列

2018-09-19 16:15:18

MySQL直方圖數(shù)據(jù)庫

2021-04-02 17:05:43

Chrome瀏覽器Incognito

2022-01-19 08:51:00

Module工作區(qū)Go

2015-07-17 13:27:16

MySQL 5.7

2021-09-27 06:50:06

MySQL參數(shù)持久化

2015-07-17 13:43:09

MySQL 5.7

2015-07-17 13:45:12

MySQL 5.7

2010-05-12 17:55:30

MySQL 5.0

2009-02-09 09:38:41

新特性MySQL 6.0MySQL

2023-07-03 08:20:35

MySQL窗口函數(shù)

2018-05-30 08:38:24

數(shù)據(jù)庫MySQL 8.0新特性

2018-06-01 15:41:21

2020-05-14 11:19:19

降序索引子集

2018-05-31 12:52:01

數(shù)據(jù)庫MySQL 8.0新特性

2009-06-03 16:10:34

OpenSolaris

2015-07-17 13:59:33

MySQL 5.7

2021-02-22 11:51:15

Java開發(fā)代碼

2014-07-15 14:48:26

Java8
點贊
收藏

51CTO技術(shù)棧公眾號