关于mysql:一个-Kill-不掉的-MySQL-会话

40次阅读

共计 7784 个字符，预计需要花费 20 分钟才能阅读完成。

到底是什么起因，导致主从切换过程中存在一个无奈 kill 的会话？

作者：秦广飞

爱可生 DBA 团队成员，负责我的项目日常问题解决及公司平台问题排查，对数据库有趣味，对技术有想法。一入 IT 深似海，从此节操是路人。

本文起源：原创投稿

  • 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

背景

照例要先讲下本文档背景的，不过在介绍背景之前，先简略说下 MySQL 主从切换的过程。

失常来说，当要产生主从切换时，主库要做上面几个动作：

  1. 断开流量入口（解绑 VIP）
  2. 设置只读
  3. Kill 掉数据库残留连贯

而从库，要做上面几个动作：

  1. 补全与主库差别的 binlog 日志
  2. 敞开只读
  3. 革除复制信息
  4. 开启流量入口（绑定 VIP）

咱们公司自研的数据库集群治理平台 云树® DMP 大略也是这么个切换过程，而这个切换过程跟本文的关联点，就在主库 Kill 掉残留连贯上。

偶然间发现，DMP 在切换过程中 Kill 残留连贯时，日志中有时会呈现 warn 信息：[warn] kill process warning：Error 1094：Unknown thread id：4

起初察看到，MySQL 5.7 的主从切换时，就不会呈现这个 warning 信息，而 MySQL 8.0 就会稳固复现。进一步测试验证后，终于发现了这个 Unknown thread id 的真面目，就是 USER 为 event_scheduler 的这个 ” 连贯 ”。

什么是 event_scheduler？

event_scheduler 到底是什么呢？毕竟从 processlist 信息中能够看到，它与一般的会话仿佛不太一样。

其实它是 MySQL 中的一个非凡线程，次要负责执行 MySQL 事件调度器所创立的事件。咱们晓得 MySQL 是有 event 的，能够像 Linux 中 crontab 一样，定时执行一些工作。

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

当 MySQL 事件调度器启用时 event_scheduler=ON，MySQL 就会在后盾启动一个 event_scheduler 线程，并且 event_scheduler 线程将始终运行，直到 MySQL 服务进行。该线程会负责查看以后工夫和已定义的事件，如果事件须要执行，则 event_scheduler 线程将启动一个新的会话来执行事件。

须要留神的是，在 MySQL 5.7 中，event_scheduler 默认是敞开的，而 MySQL 8.0 中则默认关上了，而这也就是为什么在 MySQL 5.7 的切换过程中没有发现 warning 信息的起因。

为什么 Kill 不掉？

理解 event_scheduler 大略是什么之后，咱们再来看看，为什么 Kill 时，会报 Unknown thread id。

留神看 processlist 信息，咱们发现 event_scheduler 的 COMMAND 值为 Daemon。从字面意思上看，Daemon 为后盾守护的意思，其实在 MySQL 中，当在后盾运行一些非凡的性能时，会话 COMMAND 可能被标记为 Daemon（理论工作场景中，只留神到过 event_scheduler）。

因为这类会话并不是由用户间接发动的连贯，而是 MySQL 外部的线程，所以无奈像一般会话一样被 Kill 掉。

官网文档中，给出的信息较少，大家有趣味的能够本人翻下代码。

如何应用定时工作？

具体如何应用定时工作，其实网上也有很多材料，如果真有须要应用的，倡议最好参考官网文档。上面咱们简略应用下 event 看看成果。

启用 / 敞开 / 禁用 

-- 批改变量 event_scheduler 来动静启用或者敞开 event
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> 

-- 敞开
mysql> SET GLOBAL event_scheduler = 0;

-- 启用
mysql> SET GLOBAL event_scheduler = 1;

-- 禁用 event_scheduler，只能在配置文件中设置 event_scheduler 为 disable 并重启服务，而不能动静批改
[mysqld]
event_scheduler=DISABLED

创立 

-- 筹备测试表和数据
mysql> CREATE TABLE logs(id INT(11) primary key AUTO_INCREMENT,log_message VARCHAR(255) NOT NULL,log_time TIMESTAMP NOT NULL);
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO logs (log_message, log_time) VALUES
    -> ('君不见黄河之水天上来，奔流到海不复回', '2023-06-07 09:01:00'),
    -> ('君不见高堂明镜悲白发，朝如青丝暮成雪', '2023-06-07 23:02:00'),
    -> ('人生得意须尽欢，莫使金樽空对月', '2023-06-08 01:03:00'),
    -> ('天生我材必有用，千金散尽还复来', '2023-06-08 18:04:00'),
    -> ('烹羊宰牛且为乐，会须一饮三百杯', '2023-06-09 23:05:00'),
    -> ('钟鼓馔玉有余贵，但愿长醉不复醒', '2023-06-09 11:06:00'),
    -> ('古来圣贤皆寂寞，惟有饮者留其名', '2023-06-10 23:02:00'),
    -> ('陈王昔时宴平乐，斗酒十千恣欢谑', '2023-06-11 01:03:00'),
    -> ('客人何为言少钱，径须沽取对君酌', '2023-06-12 18:04:00'),
    -> ('五花马、千金裘', '2023-06-13 23:05:00'),
    -> ('呼儿将出换美酒，与尔同销万古愁', '2023-06-14 11:06:00');
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> 

-- 创立 event，实现定时将该日志表中 7 天之前的数据删除
-- 为了疾速看到成果，咱们每分钟执行一次，一次删除 1 行
mysql> CREATE EVENT delete_logs_event
    ->   ON SCHEDULE EVERY 1 MINUTE STARTS '2023-06-19 00:00:00'
    ->   DO
    ->     DELETE FROM logs
    ->     WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY) limit 1;
Query OK, 0 rows affected (0.01 sec)

查看 

-- 执行 show events 查看，须要先进到 event 所在的 schema
mysql> use universe
mysql> show events\G
*************************** 1. row ***************************
                  Db: universe
                Name: delete_logs_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2023-06-19 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1862993913
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_bin
1 row in set (0.00 sec)

mysql>

-- 通过 information_schema.events 能够看到更具体的信息
mysql> select * from information_schema.events\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: universe
          EVENT_NAME: delete_logs_event
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: DELETE FROM logs
    WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY) limit 1
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: MINUTE
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
              STARTS: 2023-06-19 00:00:00
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2023-06-18 23:54:23
        LAST_ALTERED: 2023-06-18 23:54:23
       LAST_EXECUTED: 2023-06-19 00:00:00
       EVENT_COMMENT: 
          ORIGINATOR: 1862993913
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb4_bin
1 row in set (0.00 sec)

mysql> 

-- 查看表中是否被定时删除
mysql> select * from logs;
+----+--------------------------------------------------------+---------------------+
| id | log_message                                            | log_time            |
+----+--------------------------------------------------------+---------------------+
|  2 | 君不见高堂明镜悲白发，朝如青丝暮成雪                   | 2023-06-07 23:02:00 |
|  3 | 人生得意须尽欢，莫使金樽空对月                         | 2023-06-08 01:03:00 |
|  4 | 天生我材必有用，千金散尽还复来                         | 2023-06-08 18:04:00 |
|  5 | 烹羊宰牛且为乐，会须一饮三百杯                         | 2023-06-09 23:05:00 |
|  6 | 钟鼓馔玉有余贵，但愿长醉不复醒                         | 2023-06-09 11:06:00 |
|  7 | 古来圣贤皆寂寞，惟有饮者留其名                         | 2023-06-10 23:02:00 |
|  8 | 陈王昔时宴平乐，斗酒十千恣欢谑                         | 2023-06-11 01:03:00 |
|  9 | 客人何为言少钱，径须沽取对君酌                         | 2023-06-12 18:04:00 |
| 10 | 五花马、千金裘                                         | 2023-06-13 23:05:00 |
| 11 | 呼儿将出换美酒，与尔同销万古愁                         | 2023-06-14 11:06:00 |
+----+--------------------------------------------------------+---------------------+
10 rows in set (0.00 sec)

mysql> 

-- 查看 show processlist 中 event_scheduler 的信息，能够看到 stats 为 Waiting for next activation
mysql> select * from information_schema.processlist where user='event_scheduler';
+-------+-----------------+-----------+------+---------+------+-----------------------------+------+
| ID    | USER            | HOST      | DB   | COMMAND | TIME | STATE                       | INFO |
+-------+-----------------+-----------+------+---------+------+-----------------------------+------+
| 12869 | event_scheduler | localhost | NULL | Daemon  |   58 | Waiting for next activation | NULL |
+-------+-----------------+-----------+------+---------+------+-----------------------------+------+
1 row in set (0.00 sec)

mysql>

-- 咱们在从库上看下 event 的信息，能够看到 STATUS 为 SLAVESIDE_DISABLED，因而不必放心从库反复执行 event
mysql> select * from information_schema.events\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: universe
          EVENT_NAME: delete_logs_event
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: DELETE FROM logs
    WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY) limit 1
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: MINUTE
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
              STARTS: 2023-06-19 00:00:00
                ENDS: NULL
              STATUS: SLAVESIDE_DISABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2023-06-18 23:54:23
        LAST_ALTERED: 2023-06-18 23:54:23
       LAST_EXECUTED: NULL
       EVENT_COMMENT: 
          ORIGINATOR: 1862993913
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb4_bin
1 row in set (0.00 sec)

批改 

-- 应用 ALTER 语句批改，其余高权限用户也能够执行，且 event 的用户会变成最初一个 ALTER 的用户
mysql> ALTER EVENT delete_logs_event
    ->   ON SCHEDULE EVERY 1 DAY STARTS '2023-06-19 00:00:00'
    ->   DO
    ->     DELETE FROM logs
    ->     WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
Query OK, 0 rows affected (0.01 sec)

mysql>

-- 能够看到 DEFINER 曾经变成了批改的用户，且工夫距离也批改为了 1 天，DELETE 语句也去掉了 LIMIT
mysql> select * from information_schema.events\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: universe
          EVENT_NAME: delete_logs_event
             DEFINER: qin@%
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: DELETE FROM logs
    WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY)
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: DAY
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
              STARTS: 2023-06-19 00:00:00
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2023-06-18 23:54:23
        LAST_ALTERED: 2023-06-19 00:23:03
       LAST_EXECUTED: 2023-06-19 00:23:00
       EVENT_COMMENT: 
          ORIGINATOR: 1862993913
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb4_bin
1 row in set (0.00 sec)

删除 

mysql> drop event delete_logs_event;
Query OK, 0 rows affected (0.01 sec)

mysql> show events\G
Empty set (0.00 sec)

切换时留神

  1. 当在主库上创立了 event，之后产生了主从切换。此时 event 并不会随着切换而变成在新主上执行，且状态也不会产生扭转。
  2. 即原主 event 的状态还是 ENABLED，而新主 event 的状态还是 DISABLED。

总结

  1. show processlist 中看到的 User 为 event_scheduler 的会话为 MySQL 外部线程，无奈被 Kill 掉。
  2. 在主库上创立的 event，定时执行的 SQL 语句，在从库上会失常随着复制回放，但不会被反复执行。
  3. 主从切换后，原主上的 event 不会在新主上执行。

正文完
mysql
发表至： mysql
2023-07-05
 0
关于mysql:MySQL-复制主副表的问题详解
关于mysql:mysql事务对效率的影响
mysqlMVCC
关于mysql:手把手教你分析Mysql死锁问题