快捷導(dǎo)航

詳解MySQL如何使用離線模式維護(hù)服務(wù)器

更新時(shí)間：2023年10月10日 10:13:25 作者：愛(ài)可生開(kāi)源社區(qū)

這篇文章主要為大家介紹了MySQL如何使用離線模式維護(hù)服務(wù)器，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

離線模式

作為 DBA，最常見(jiàn)的任務(wù)之一就是批量處理 MySQL 服務(wù)的啟停或其他一些活動(dòng)。在停止 MySQL 服務(wù)前，我們可能需要檢查是否有活動(dòng)連接；如果有，我們可能需要把它們?nèi)繗⑺馈Ｍǔ?，我們使?nbsp;pt-kill 殺死應(yīng)用連接或使用 SELECT 語(yǔ)句查詢準(zhǔn)備殺死語(yǔ)句。例如：

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 有一個(gè)名為 offline_mode 的變量來(lái)將服務(wù)器設(shè)置為維護(hù)模式。設(shè)置此選項(xiàng)后，它會(huì)立即斷開(kāi)所有不具有 SYSTEM_VARIABLES_ADMIN 和 CONNECTION_ADMIN 權(quán)限的客戶端連接，并且不允許新連接，除非用戶擁有這些權(quán)限。如果您手動(dòng)終止連接或使用 pt-kill，則無(wú)法避免新連接的創(chuàng)建。但通過(guò)使用這種模式，我們可以避免新的連接。這是一個(gè)全局動(dòng)態(tài)變量，我們可以在服務(wù)器運(yùn)行時(shí)設(shè)置此模式。

啟用 offline_mode

要啟用 offline_mode，用戶帳戶必須具有 SYSTEM_VARIABLES_ADMIN* 權(quán)限和 CONNECTION_ADMIN 權(quán)限（或已棄用的 SUPER 權(quán)限，它涵蓋了這兩個(gè)權(quán)限）。CONNECTION_ADMIN 從 MySQL 8.0.31 開(kāi)始是必需的，并建議在所有版本中使用，以防止意外鎖定。讓我們測(cè)試一下。

要對(duì)此進(jìn)行測(cè)試，請(qǐng)創(chuàng)建一個(gè)新用戶 app_user，該用戶僅具有 DDL/DML 權(quán)限。

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)

使用 aap_user 用戶啟動(dòng) sysbench 工具

[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...

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)

當(dāng) sysbench 運(yùn)行時(shí)，設(shè)置 offline_mode=ON，來(lái)自 sysbech with Sysbench") 的所有連接都將被終止。您將在 sysbench 中看到錯(cuò)誤。

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)

如果您在 offline_mode=1 時(shí)嘗試使用 app_user 連接數(shù)據(jù)庫(kù)，它將不允許連接并收到一條錯(cuò)誤消息，表明服務(wù)器當(dāng)前處于離線模式。這個(gè) offline_mode 不會(huì)影響復(fù)制?？梢钥吹缴厦娴?processlist 日志，當(dāng)我們?cè)O(shè)置 offline_mode=1 時(shí)，復(fù)制線程并沒(méi)有斷開(kāi)。要禁用 offline_mode，請(qǐng)將值設(shè)置為 0。

mysql> set global offline_mode=0;
Query OK, 0 rows affected (0.00 sec)

結(jié)論

offline_mode 是將服務(wù)器置于維護(hù)模式的一個(gè)不錯(cuò)的選擇。只需確保應(yīng)用程序用戶沒(méi)有管理員權(quán)限，只有管理員有。我們可以在以下情況下使用 offline_mode：

在將數(shù)據(jù)庫(kù)服務(wù)器取出進(jìn)行維護(hù)或其相關(guān)活動(dòng)之前，請(qǐng)?jiān)谂渲梦募羞M(jìn)行更改并保持此模式，直到所有活動(dòng)完成。
在從服務(wù)器進(jìn)行備份時(shí)，我們可以設(shè)置這個(gè) offline_mode，以避免服務(wù)器的負(fù)載，使備份速度更快。
如果由于大量查詢而導(dǎo)致副本上出現(xiàn)大量復(fù)制，我們可以設(shè)置此模式，直到復(fù)制與主服務(wù)器同步。
當(dāng)您想立即終止所有應(yīng)用程序連接時(shí)。

譯自：https://www.percona.com/blog/mysql-offline-mode-to-disconnect...

以上就是MySQL 如何使用離線模式維護(hù)服務(wù)器的詳細(xì)內(nèi)容，更多關(guān)于MySQL 如何使用離線模式維護(hù)服務(wù)器的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: