当我执行 SHOW PROCESSLIST
查询时,只返回运行 SQL 查询的 info 列中前100个字符。
是否可以更改 MySQL 配置或发出不同类型的请求以查看完整查询(我正在查看的查询超过100个字符)?
当我执行 SHOW PROCESSLIST
查询时,只返回运行 SQL 查询的 info 列中前100个字符。
是否可以更改 MySQL 配置或发出不同类型的请求以查看完整查询(我正在查看的查询超过100个字符)?
SHOW FULL PROCESSLIST
FULL
,则在“Info”字段中仅显示每个语句的前100个字符。Show Processlist从另一个表中获取信息。以下是如何提取数据并查看包含整个查询的'INFO'列:
select * from INFORMATION_SCHEMA.PROCESSLIST where db = 'somedb';
您可以根据需求添加任何条件或忽略条件。
查询的输出结果如下:
+-------+------+-----------------+--------+---------+------+-----------+----------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-------+------+-----------------+--------+---------+------+-----------+----------------------------------------------------------+
| 5 | ssss | localhost:41060 | somedb | Sleep | 3 | | NULL |
| 58169 | root | localhost | somedb | Query | 0 | executing | select * from sometable where tblColumnName = 'someName' |
select id pid, user, concat('CALL mysql.rds_kill(', id, ');'), time, state, info from information_schema.processlist where info is not null order by time desc;
- spen.smith查看来自SHOW PROCESSLIST的完整查询:
SHOW FULL PROCESSLIST;
或者
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
如果想在Shell会话中继续获取更新的进程(例如,每2秒),而无需手动交互,请使用:
watch -n 2 'mysql -h 127.0.0.1 -P 3306 -u some_user -psome_pass some_database -e "show full processlist;"'
show [full] processlist
唯一的缺点是你不能筛选输出结果。另一方面,使用 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
可以去除任何你不想看到的内容:
SELECT * from INFORMATION_SCHEMA.PROCESSLIST
WHERE DB = 'somedatabase'
AND COMMAND <> 'Sleep'
AND HOST NOT LIKE '10.164.25.133%' \G
info
列:使用\G
而不是;
来终止查询。 - Araxiamytop(1)
。 - sjas我刚刚在MySQL文档中看到,SHOW FULL PROCESSLIST
默认只列出来自您当前用户连接的线程。
来自 MySQL SHOW FULL PROCESSLIST 文档的引用:
如果您拥有 PROCESS 权限,可以查看所有线程。
因此,您可以在mysql.user
表中启用Process_priv
列。记得在执行后执行FLUSH PRIVILEGES
:)
SHOW FULL PROCESSLIST
这将显示更多信息的完整进程列表。
bash
## identify the query id
mysql -e 'SHOW processlist'
## show only the related sql query for this process omitting the other columns
## change: YOUR_QUERY_ID_YOU_LOOKED_UP
mysql -sre 'SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=YOUR_QUERY_ID_YOU_LOOKED_UP'|tr '\n' ' ';echo
## bonus: get mysql EXPLAIN for this query (when its too long to copy properly)
## change: YOUR_DATABASE,YOUR_QUERY_ID_YOU_LOOKED_UP
mysql -e "USE YOUR_DATABASE;EXPLAIN $(mysql -sre 'SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=YOUR_QUERY_ID_YOU_LOOKED_UP'|tr '\n' ' ';echo)"
mysql
-- identify the query id
SHOW processlist;
/*
show only the related sql query for this process omitting the other columns
change: YOUR_QUERY_ID_YOU_LOOKED_UP
*/
SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=YOUR_QUERY_ID_YOU_LOOKED_UP \G
SHOW FULL PROCESSLIST
命令,查询在达到一定的长度后被截断。有没有办法让它显示更完整的内容? - wizonesolutionsSHOW FULL PROCESSLIST
命令需要在末尾加上分号;
,对吗? - Rakibul Haq