MySQL查看当前正在连接的会话执行过的SQL
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;
效果：
(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)
一点点积累。
