MySQL查看历史会话执行过的SQL
网上找了下基本都是使用脚本，运行定时任务获取sql内容记录到文本里边。
我想要的效果是使用mysql系统视图来查看，其实可以实现。
通过查询视图performance_schema.events_statements_history_long来实现。
该视图启用的时候默认保留@@performance_schema_events_statements_history_long_size条记录。
默认该视图功能是禁用的：
(root@localhost 16:42:56) [(none)]> select * from performance_schema.setup_consumers where name='events_statements_history_long'; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_statements_history_long | NO | +--------------------------------+---------+ 1 row in set (0.00 sec)
可通过两种方式开启。
方式1：（What are the Recommended Configuration of the Performance Schema? (文档 ID 2229601.1)）：
[mysqld]
performance_schema_consumer_events_statements_history_long = ON
需要重启数据库生效。
方式2：
（Can't Filter on MYSQL_ERRNO or MESSAGE_TEXT Against Table events_statements_history_long (文档 ID 2759463.1)）
（How To Find Queries Generating Errors Or Warnings And How To Investigate the MEM Event "SQL Statement Generates Errors Or Warnings"? (文档 ID 2070773.1)）
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';
(root@localhost 17:01:44) [(none)]> select * from performance_schema.setup_consumers where name='events_statements_history_long'; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_statements_history_long | YES | +--------------------------------+---------+ 1 row in set (0.00 sec)
简单的查询：
(root@localhost 17:08:02) [(none)]> select THREAD_ID ,FORMAT_PICO_TIME(TIMER_START) TIMER_START ,FORMAT_PICO_TIME(TIMER_END) TIMER_END ,FORMAT_PICO_TIME(TIMER_WAIT) TIMER_WAIT ,left(DIGEST_TEXT,100),MESSAGE_TEXT from performance_schema.events_statements_history_long ; +-----------+-------------+-----------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------+ | THREAD_ID | TIMER_START | TIMER_END | TIMER_WAIT | left(DIGEST_TEXT,100) | MESSAGE_TEXT | +-----------+-------------+-----------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------+ | 61 | 3.69 s | 3.69 s | 483.83 us | SELECT @@`version_comment` LIMIT ? | NULL | | 61 | 3.70 s | 3.70 s | 203.63 us | SELECT SYSTEM_USER ( ) | NULL | | 61 | 9.35 s | 9.35 s | 5.70 ms | SELECT * FROM `performance_schema` . `setup_consumers` WHERE NAME = ? | NULL | | 61 | 8.68 min | 8.68 min | 86.19 us | NULL | NULL | | 62 | 25.39 min | 25.39 min | 388.93 us | SELECT @@`version_comment` LIMIT ? | NULL | | 62 | 25.39 min | 25.39 min | 134.54 us | SELECT SYSTEM_USER ( ) | NULL | | 62 | 25.40 min | 25.40 min | 957.61 us | UPDATE `performance_schema` . `setup_consumers` SET `ENABLED` = ? WHERE NAME = ? | Rows matched: 1 Changed: 0 Warnings: 0 | | 62 | 25.50 min | 25.50 min | 111.92 ms | SELECT `h` . `SQL_TEXT` , `d` . `DIGEST_TEXT` , `d` . `DIGEST` , `d` . `SCHEMA_NAME` , `d` . `SUM_ER | NULL | | 62 | 25.52 min | 25.52 min | 1.66 ms | SELECT `h` . `SQL_TEXT` , `d` . `DIGEST_TEXT` , `d` . `DIGEST` , `d` . `SCHEMA_NAME` , `d` . `SUM_ER | NULL | | 62 | 25.54 min | 25.54 min | 1.62 ms | SELECT `h` . `SQL_TEXT` , `d` . `DIGEST_TEXT` , `d` . `DIGEST` , `d` . `SCHEMA_NAME` , `d` . `SUM_ER | NULL | | 62 | 25.57 min | 25.57 min | 1.61 ms | SELECT `h` . `SQL_TEXT` , `d` . `DIGEST_TEXT` , `d` . `DIGEST` , `d` . `SCHEMA_NAME` , `d` . `SUM_ER | NULL | | 62 | 25.64 min | 25.64 min | 1.71 ms | SELECT `h` . `SQL_TEXT` , `d` . `DIGEST_TEXT` , `d` . `DIGEST` , `d` . `SCHEMA_NAME` , `d` . `SUM_ER | NULL | | 62 | 25.93 min | 25.93 min | 1.19 ms | SELECT `THREAD_ID` , `TIMER_START` , `TIMER_END` , `TIMER_WAIT` , LEFT ( `DIGEST_TEXT` , ? ) FROM `p | NULL | | 62 | 28.98 min | 28.98 min | 733.06 us | SELECT * FROM `performance_schema` . `setup_consumers` WHERE NAME = ? | NULL | | 62 | 29.82 min | 29.82 min | 40.82 ms | SHOW VARIABLES LIKE ? | NULL | | 62 | 30.92 min | 30.92 min | 67.79 ms | DESC `performance_schema` . `events_statements_history_long` | NULL | | 62 | 31.41 min | 31.41 min | 2.37 ms | SELECT `THREAD_ID` , `TIMER_START` , `TIMER_END` , `TIMER_WAIT` , LEFT ( `DIGEST_TEXT` , ? ) , MESSA | NULL | | 62 | 32.05 min | 32.05 min | 1.65 ms | SELECT `THREAD_ID` , `FORMAT_PICO_TIME` ( `TIMER_START` ) , `FORMAT_PICO_TIME` ( `TIMER_END` ) , `TI | NULL | | 62 | 32.24 min | 32.24 min | 1.04 ms | SELECT `THREAD_ID` , `FORMAT_PICO_TIME` ( `TIMER_START` ) `TIMER_START` , `FORMAT_PICO_TIME` ( `TIME | NULL | +-----------+-------------+-----------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------+ 19 rows in set (0.00 sec)
至此。