一個(gè)Kill不掉的MySQL會(huì)話

作者：秦廣飛

愛(ài)可生 DBA 團(tuán)隊(duì)成員，負(fù)責(zé)項(xiàng)目日常問(wèn)題處理及公司平臺(tái)問(wèn)題排查，對(duì)數(shù)據(jù)庫(kù)有興趣，對(duì)技術(shù)有想法。一入 IT 深似海，從此節(jié)操是路人。

本文來(lái)源：愛(ài)可生開源社區(qū)

1背景

照例要先講下本文檔背景的，不過(guò)在介紹背景之前，先簡(jiǎn)單說(shuō)下 MySQL 主從切換的過(guò)程。

正常來(lái)說(shuō)，當(dāng)要發(fā)生主從切換時(shí)，主庫(kù)要做下面幾個(gè)動(dòng)作：

斷開流量入口（解綁 VIP）

設(shè)置只讀

Kill 掉數(shù)據(jù)庫(kù)殘留連接

而從庫(kù)，要做下面幾個(gè)動(dòng)作：

補(bǔ)全與主庫(kù)差異的 binlog 日志

關(guān)閉只讀

清除復(fù)制信息

開啟流量入口（綁定 VIP）

我們公司自研的數(shù)據(jù)庫(kù)集群管理平臺(tái) 云樹 DMP[1] 大概也是這么個(gè)切換過(guò)程，而這個(gè)切換過(guò)程跟本文的關(guān)聯(lián)點(diǎn)，就在主庫(kù) Kill 掉殘留連接上。

偶然間發(fā)現(xiàn)，DMP 在切換過(guò)程中 Kill 殘留連接時(shí)，日志中有時(shí)會(huì)出現(xiàn) warn 信息：[warn] kill process warning：Error 1094：Unknown thread id：4

后來(lái)觀察到，MySQL 5.7 的主從切換時(shí)，就不會(huì)出現(xiàn)這個(gè) warning 信息，而 MySQL 8.0 就會(huì)穩(wěn)定復(fù)現(xiàn)。進(jìn)一步測(cè)試驗(yàn)證后，終于發(fā)現(xiàn)了這個(gè) Unknown thread id 的真面目，就是 USER 為 event_scheduler 的這個(gè)"連接"。

2什么是 event_scheduler？

event_scheduler 到底是什么呢？畢竟從 processlist 信息中可以看到，它與普通的會(huì)話似乎不太一樣。

其實(shí)它是 MySQL 中的一個(gè)特殊線程，主要負(fù)責(zé)執(zhí)行 MySQL 事件調(diào)度器所創(chuàng)建的事件。我們知道 MySQL 是有 event 的，可以像 Linux 中 crontab 一樣，定時(shí)執(zhí)行一些任務(wù)。

The MySQL Event Scheduler manages the scheduling and execution of events, that is, tasks that run according to a schedule

當(dāng) MySQL 事件調(diào)度器啟用時(shí) event_scheduler=ON，MySQL 就會(huì)在后臺(tái)啟動(dòng)一個(gè) event_scheduler 線程，并且 event_scheduler 線程將一直運(yùn)行，直到 MySQL 服務(wù)停止。該線程會(huì)負(fù)責(zé)檢查當(dāng)前時(shí)間和已定義的事件，如果事件需要執(zhí)行，則 event_scheduler 線程將啟動(dòng)一個(gè)新的會(huì)話來(lái)執(zhí)行事件。

需要注意的是，在 MySQL 5.7中，event_scheduler 默認(rèn)是關(guān)閉的，而 MySQL 8.0 中則默認(rèn)打開了，而這也就是為什么在 MySQL 5.7 的切換過(guò)程中沒(méi)有發(fā)現(xiàn) warning 信息的原因。

3為什么 Kill 不掉？

了解 event_scheduler 大概是什么之后，我們?cè)賮?lái)看看，為什么 Kill 時(shí)，會(huì)報(bào) Unknown thread id。

注意看 processlist 信息，我們發(fā)現(xiàn) event_scheduler 的 COMMAND 值為 Daemon。從字面意思上看，Daemon 為后臺(tái)守護(hù)的意思，其實(shí)在 MySQL 中，當(dāng)在后臺(tái)運(yùn)行一些特殊的功能時(shí)，會(huì)話 COMMAND 可能被標(biāo)記為 Daemon（實(shí)際工作場(chǎng)景中，只注意到過(guò) event_scheduler）。

因?yàn)檫@類會(huì)話并不是由用戶直接發(fā)起的連接，而是 MySQL 內(nèi)部的線程，所以無(wú)法像普通會(huì)話一樣被 Kill 掉。

官方文檔中，給出的信息較少，大家有興趣的可以自己翻下代碼。

4如何使用定時(shí)任務(wù)？

具體如何使用定時(shí)任務(wù)，其實(shí)網(wǎng)上也有很多資料，如果真有需要使用的，建議最好參考官方文檔。下面我們簡(jiǎn)單使用下 event 看看效果。

啟用/關(guān)閉/禁用

--修改變量event_scheduler來(lái)動(dòng)態(tài)啟用或者關(guān)閉event
mysql>showvariableslike'%event_scheduler%';
+-----------------+-------+
|Variable_name|Value|
+-----------------+-------+
|event_scheduler|ON|
+-----------------+-------+
1rowinset(0.00sec)

mysql>

--關(guān)閉
mysql>SETGLOBALevent_scheduler=0;

--啟用
mysql>SETGLOBALevent_scheduler=1;

--禁用event_scheduler，只能在配置文件中設(shè)置event_scheduler為disable并重啟服務(wù)，而不能動(dòng)態(tài)修改
[mysqld]
event_scheduler=DISABLED

創(chuàng)建

--準(zhǔn)備測(cè)試表和數(shù)據(jù)
mysql>CREATETABLElogs(idINT(11)primarykeyAUTO_INCREMENT,log_messageVARCHAR(255)NOTNULL,log_timeTIMESTAMPNOTNULL);
QueryOK,0rowsaffected,1warning(0.02sec)

mysql>INSERTINTOlogs(log_message,log_time)VALUES
->('君不見黃河之水天上來(lái)，奔流到海不復(fù)回','2023-06-070900'),
->('君不見高堂明鏡悲白發(fā)，朝如青絲暮成雪','2023-06-072300'),
->('人生得意須盡歡，莫使金樽空對(duì)月','2023-06-080100'),
->('天生我材必有用，千金散盡還復(fù)來(lái)','2023-06-081800'),
->('烹羊宰牛且為樂(lè)，會(huì)須一飲三百杯','2023-06-092300'),
->('鐘鼓饌玉不足貴，但愿長(zhǎng)醉不復(fù)醒','2023-06-091100'),
->('古來(lái)圣賢皆寂寞，惟有飲者留其名','2023-06-102300'),
->('陳王昔時(shí)宴平樂(lè)，斗酒十千恣歡謔','2023-06-110100'),
->('主人何為言少錢，徑須沽取對(duì)君酌','2023-06-121800'),
->('五花馬、千金裘','2023-06-132300'),
->('呼兒將出換美酒，與爾同銷萬(wàn)古愁','2023-06-141100');
QueryOK,11rowsaffected(0.01sec)
Records:11Duplicates:0Warnings:0

mysql>

--創(chuàng)建event，實(shí)現(xiàn)定時(shí)將該日志表中7天之前的數(shù)據(jù)刪除
--為了快速看到效果，我們每分鐘執(zhí)行一次，一次刪除1行
mysql>CREATEEVENTdelete_logs_event
->ONSCHEDULEEVERY1MINUTESTARTS'2023-06-190000'
->DO
->DELETEFROMlogs
->WHERElog_time

	

	查看

	
--執(zhí)行showevents查看，需要先進(jìn)到event所在的schema
mysql>useuniverse
mysql>showeventsG
***************************1.row***************************
Db:universe
Name:delete_logs_event
Definer:root@localhost
Timezone:SYSTEM
Type:RECURRING
Executeat:NULL
Intervalvalue:1
Intervalfield:MINUTE
Starts:2023-06-190000
Ends:NULL
Status:ENABLED
Originator:1862993913
character_set_client:utf8mb4
collation_connection:utf8mb4_0900_ai_ci
DatabaseCollation:utf8mb4_bin
1rowinset(0.00sec)

mysql>

--通過(guò)information_schema.events可以看到更詳細(xì)的信息
mysql>select*frominformation_schema.eventsG
***************************1.row***************************
EVENT_CATALOG:def
EVENT_SCHEMA:universe
EVENT_NAME:delete_logs_event
DEFINER:root@localhost
TIME_ZONE:SYSTEM
EVENT_BODY:SQL
EVENT_DEFINITION:DELETEFROMlogs
WHERElog_time

--查看表中是否被定時(shí)刪除
mysql>select*fromlogs;
+----+--------------------------------------------------------+---------------------+
|id|log_message|log_time|
+----+--------------------------------------------------------+---------------------+
|2|君不見高堂明鏡悲白發(fā)，朝如青絲暮成雪|2023-06-072300|
|3|人生得意須盡歡，莫使金樽空對(duì)月|2023-06-080100|
|4|天生我材必有用，千金散盡還復(fù)來(lái)|2023-06-081800|
|5|烹羊宰牛且為樂(lè)，會(huì)須一飲三百杯|2023-06-092300|
|6|鐘鼓饌玉不足貴，但愿長(zhǎng)醉不復(fù)醒|2023-06-091100|
|7|古來(lái)圣賢皆寂寞，惟有飲者留其名|2023-06-102300|
|8|陳王昔時(shí)宴平樂(lè)，斗酒十千恣歡謔|2023-06-110100|
|9|主人何為言少錢，徑須沽取對(duì)君酌|2023-06-121800|
|10|五花馬、千金裘|2023-06-132300|
|11|呼兒將出換美酒，與爾同銷萬(wàn)古愁|2023-06-141100|
+----+--------------------------------------------------------+---------------------+
10rowsinset(0.00sec)

mysql>

--查看showprocesslist中event_scheduler的信息，可以看到stats為Waitingfornextactivation
mysql>select*frominformation_schema.processlistwhereuser='event_scheduler';
+-------+-----------------+-----------+------+---------+------+-----------------------------+------+
|ID|USER|HOST|DB|COMMAND|TIME|STATE|INFO|
+-------+-----------------+-----------+------+---------+------+-----------------------------+------+
|12869|event_scheduler|localhost|NULL|Daemon|58|Waitingfornextactivation|NULL|
+-------+-----------------+-----------+------+---------+------+-----------------------------+------+
1rowinset(0.00sec)

mysql>

--我們?cè)趶膸?kù)上看下event的信息，可以看到STATUS為SLAVESIDE_DISABLED，因此不用擔(dān)心從庫(kù)重復(fù)執(zhí)行event
mysql>select*frominformation_schema.eventsG
***************************1.row***************************
EVENT_CATALOG:def
EVENT_SCHEMA:universe
EVENT_NAME:delete_logs_event
DEFINER:root@localhost
TIME_ZONE:SYSTEM
EVENT_BODY:SQL
EVENT_DEFINITION:DELETEFROMlogs
WHERElog_time

	

	修改

	
--使用ALTER語(yǔ)句修改，其他高權(quán)限用戶也可以執(zhí)行，且event的用戶會(huì)變成最后一個(gè)ALTER的用戶
mysql>ALTEREVENTdelete_logs_event
->ONSCHEDULEEVERY1DAYSTARTS'2023-06-190000'
->DO
->DELETEFROMlogs
->WHERElog_time

--可以看到DEFINER已經(jīng)變成了修改的用戶，且時(shí)間間隔也修改為了1天，DELETE語(yǔ)句也去掉了LIMIT
mysql>select*frominformation_schema.eventsG
***************************1.row***************************
EVENT_CATALOG:def
EVENT_SCHEMA:universe
EVENT_NAME:delete_logs_event
DEFINER:qin@%
TIME_ZONE:SYSTEM
EVENT_BODY:SQL
EVENT_DEFINITION:DELETEFROMlogs
WHERElog_time

	

	刪除

	
mysql>dropeventdelete_logs_event;
QueryOK,0rowsaffected(0.01sec)

mysql>showeventsG
Emptyset(0.00sec)


	

	切換時(shí)注意

	當(dāng)在主庫(kù)上創(chuàng)建了 event，之后發(fā)生了主從切換。此時(shí) event 并不會(huì)隨著切換而變成在新主上執(zhí)行，且狀態(tài)也不會(huì)發(fā)生改變。

	即原主 event 的狀態(tài)還是 ENABLED，而新主 event 的狀態(tài)還是 DISABLED。

	

	5總結(jié)

	show processlist 中看到的 User 為 event_scheduler 的會(huì)話為 MySQL 內(nèi)部線程，無(wú)法被 Kill 掉。

	在主庫(kù)上創(chuàng)建的 event，定時(shí)執(zhí)行的 SQL 語(yǔ)句，在從庫(kù)上會(huì)正常隨著復(fù)制回放，但不會(huì)被重復(fù)執(zhí)行。

	主從切換后，原主上的 event 不會(huì)在新主上執(zhí)行。

	審核編輯：湯梓紅