MySQL查看当前正在连接的会话执行过的SQL

最新推荐文章于 2023-04-25 20:01:54 发布

PiscesCanon

最新推荐文章于 2023-04-25 20:01:54 发布

阅读量32

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/u014346634/article/details/140018825

版权

MySQL查看当前正在连接的会话执行过的SQL

版本信息：

(root@localhost 23:43:30) [mysql]> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.43 sec)

防爬虫：https://www.cnblogs.com/PiscesCanon/p/17294651.html

1.获取THREAD_ID号

首先，获取会话的thread_id号，如果你能直接操作你要查询的会话，可以使用如下方式：

(root@localhost 23:26:41) [mysql]> select ps_current_thread_id();
+------------------------+
| ps_current_thread_id() |
+------------------------+
|                    128 |
+------------------------+
1 row in set (0.00 sec)

或者：

(root@localhost 23:28:22) [mysql]> select thread_id from performance_schema.threads where PROCESSLIST_ID = connection_id();
+-----------+
| thread_id |
+-----------+
|       128 |
+-----------+
1 row in set (0.00 sec)

如果是程序连接的，那么通过如下方式获取：

其中，子查询中where的条件越多越好，这样可以更加精确定位到你要的会话。

(root@localhost 23:32:09) [mysql]> select thread_id from performance_schema.threads where PROCESSLIST_ID in (select id from performance_schema.processlist where user='root' and host='192.168.1.179:34415');
+-----------+
| thread_id |
+-----------+
|       131 |
+-----------+
1 row in set (0.00 sec)

2.查询视图获取

如果是当前或者上次执行的SQL(包括执行失败)，可以使用如下查询：

关于视图performance_schema.events_statements_current，点击官网查看详细信息。

默认该视图只保留每个thread @@performance_schema_events_statements_history_size条记录(我的环境是该变量是10)。

(root@localhost 23:38:23) [mysql]> select * from performance_schema.events_statements_current where THREAD_ID = 131 \G
*************************** 1. row ***************************
              THREAD_ID: 131
               EVENT_ID: 3
           END_EVENT_ID: 3
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:97
            TIMER_START: 306364568264781000
              TIMER_END: 306364632376136000
             TIMER_WAIT: 64111355000
              LOCK_TIME: 15000000
               SQL_TEXT: select * from zkm.test244
                 DIGEST: bf8258b4bfb5ea0d5c64f50cd1ed9f2e34e59e37fb4d42b116daa78e829fdbc6
            DIGEST_TEXT: SELECT * FROM `zkm` . `test244`
         CURRENT_SCHEMA: NULL
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 1146
      RETURNED_SQLSTATE: 42S02
           MESSAGE_TEXT: Table 'zkm.test244' doesn't exist
                 ERRORS: 1
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 2032
               CPU_TIME: 0
       EXECUTION_ENGINE: PRIMARY
1 row in set (0.00 sec)

如果是查询会话执行过的全部SQL(包括执行失败)，可以这么查询(字段可以自己根据需要添加)：

关于视图performance_schema.events_statements_history，点击官网查看详细信息。

(root@localhost 23:43:09) [mysql]> select TIMER_START,TIMER_END,SQL_TEXT,MESSAGE_TEXT from performance_schema.events_statements_history where thread_id=131;
+--------------------+--------------------+----------------------------------+-----------------------------------+
| TIMER_START        | TIMER_END          | SQL_TEXT                         | MESSAGE_TEXT                      |
+--------------------+--------------------+----------------------------------+-----------------------------------+
| 305868934761736000 | 305869186839229000 | select @@version_comment limit 1 | NULL                              |
| 305870150600829000 | 305870150829451000 | select USER()                    | NULL                              |
| 306364568264781000 | 306364632376136000 | select * from zkm.test244        | Table 'zkm.test244' doesn't exist |
| 306689832599139000 | 306689833652352000 | select * from zkm.test1          | Table 'zkm.test1' doesn't exist   |
| 306694033041805000 | 306694033911856000 | select * from zkm.test2          | NULL                              |
| 306697525763344000 | 306697537398594000 | select * from zkm.test           | NULL                              |
+--------------------+--------------------+----------------------------------+-----------------------------------+
6 rows in set (0.00 sec)

网上比较美观使用的脚本如下(可以用thread_id或者processlist_id)：传送门

SET @dt_ts=UNIX_TIMESTAMP(NOW());
SELECT 
     MAX(sh.TIMER_START) into @dt_timer
FROM performance_schema.threads AS t
INNER JOIN performance_schema.events_statements_history AS sh
ON t.`THREAD_ID`=sh.`THREAD_ID`
WHERE t.PROCESSLIST_ID=CONNECTION_ID();

SELECT 
SH.CURRENT_SCHEMA AS database_name,
REPLACE(REPLACE(REPLACE(SH.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') AS executed_sql,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time,
(SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds,
SH.TIMER_WAIT/1000000000000 AS wait_seconds,
SH.LOCK_TIME/1000000000000 AS lock_seconds,
SH.ROWS_AFFECTED AS affected_rows,
SH.ROWS_SENT AS send_rows
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.THREAD_ID=135
AND SH.TIMER_START<@dt_timer
ORDER BY SH.TIMER_START ASC;

View Code

效果：

(root@localhost 23:52:27) [mysql]> SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());
+-------------------------------+
| @dt_ts:=UNIX_TIMESTAMP(NOW()) |
+-------------------------------+
|                    1680796349 |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

(root@localhost 23:52:29) [mysql]> SELECT 
    -> @dt_timer:=MAX(SH.TIMER_START)
    -> FROM performance_schema.threads AS T1
    -> INNER JOIN performance_schema.events_statements_history AS SH
    -> ON T1.`THREAD_ID`=SH.`THREAD_ID`
    -> WHERE T1.PROCESSLIST_ID=CONNECTION_ID();
+--------------------------------+
| @dt_timer:=MAX(SH.TIMER_START) |
+--------------------------------+
|             307241314149983000 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

(root@localhost 23:52:29) [mysql]> 
(root@localhost 23:52:29) [mysql]> SELECT 
    -> SH.CURRENT_SCHEMA AS database_name,
    -> REPLACE(REPLACE(REPLACE(SH.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') AS executed_sql,
    -> FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time,
    -> FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time,
    -> (SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds,
    -> SH.TIMER_WAIT/1000000000000 AS wait_seconds,
    -> SH.LOCK_TIME/1000000000000 AS lock_seconds,
    -> SH.ROWS_AFFECTED AS affected_rows,
    -> SH.ROWS_SENT AS send_rows
    -> FROM performance_schema.threads AS T1
    -> INNER JOIN performance_schema.events_statements_history AS SH
    -> ON T1.`THREAD_ID`=SH.`THREAD_ID`
    -> WHERE T1.THREAD_ID=131
    -> AND SH.TIMER_START<@dt_timer
    -> ORDER BY SH.TIMER_START ASC;
+---------------+----------------------------------+---------------------+---------------------+--------------+--------------+--------------+---------------+-----------+
| database_name | executed_sql                     | start_time          | end_time            | used_seconds | wait_seconds | lock_seconds | affected_rows | send_rows |
+---------------+----------------------------------+---------------------+---------------------+--------------+--------------+--------------+---------------+-----------+
| NULL          | select @@version_comment limit 1 | 2023-04-06 23:29:37 | 2023-03-30 21:33:58 |       0.2521 |       0.2521 |       0.0000 |             0 |         1 |
| NULL          | select USER()                    | 2023-04-06 23:29:38 | 2023-03-30 21:33:58 |       0.0002 |       0.0002 |       0.0000 |             0 |         1 |
| NULL          | select * from zkm.test244        | 2023-04-06 23:37:52 | 2023-03-30 21:25:43 |       0.0641 |       0.0641 |       0.0000 |             0 |         0 |
| NULL          | select * from zkm.test1          | 2023-04-06 23:43:18 | 2023-03-30 21:20:18 |       0.0011 |       0.0011 |       0.0000 |             0 |         0 |
| NULL          | select * from zkm.test2          | 2023-04-06 23:43:22 | 2023-03-30 21:20:14 |       0.0009 |       0.0009 |       0.0000 |             0 |         1 |
| NULL          | select * from zkm.test           | 2023-04-06 23:43:25 | 2023-03-30 21:20:10 |       0.0116 |       0.0116 |       0.0000 |             0 |         1 |
| NULL          | select connection_id()           | 2023-04-06 23:46:48 | 2023-03-30 21:16:47 |       0.0003 |       0.0003 |       0.0000 |             0 |         1 |
| NULL          | select ps_current_thread_id()    | 2023-04-06 23:47:36 | 2023-03-30 21:16:00 |       0.0002 |       0.0002 |       0.0000 |             0 |         1 |
+---------------+----------------------------------+---------------------+---------------------+--------------+--------------+--------------+---------------+-----------+
8 rows in set (0.00 sec)

一点点积累。