Filter MySQL slow query logs using commands
An exception occurred on a web server.
All requests are blocked due to CPU depletion, which may take 5 to 10 minutes.
During this period, the overall database load is normal.
Use the command to query slow logs.
Specify an IP address, locate the query time greater than 100 s, and display the currently executed SQL
Cat slow-log | sed '/#/{N; s/\ n // g}' | grep "Query_time: [1-9] [0-9] \ {2 \}\. "-n20
Or
Cat slow-log | sed '/#/{N; s/\ n // g}' | grep "127 \. 0 \. 0 \. 1. * Query_time: [1-9] [0-9] \ {2 \}\. "-A5
-Bash-4.1 $ cat slow-log | sed '/#/{N; s/\ n // g}' | grep "Query_time: [1-9] [0-9] \ {2 \}\."
# Time: 151117 15:49:01 # User @ Host: xx [xx] @ [IP] Id: 12827130 # Query_time: 103.301519 Lock_time: 0.000080 Rows_sent: 716360 Rows_examined: 1782770
# Time: 151117 15:57:41 # User @ Host: xx [xx] @ [IP] Id: 12827130 # Query_time: 102.565315 Lock_time: 0.000088 Rows_sent: 1066410 Rows_examined: 1066410
# Time: 151117 16:33:51 # User @ Host: xx [xx] @ [IP] Id: 12827970 # Query_time: 265.482413 Lock_time: 0.000253 Rows_sent: 423799 Rows_examined: 5864759
# Time: 151117 16:34:02 # User @ Host: xx [xx] @ [IP] Id: 12827913 # Query_time: 314.793697 Lock_time: 0.000266 Rows_sent: 897670 Rows_examined: 6338629
This article permanently updates the link address: