Investigating mysql


Query List

To view mysql query list run the following command from the command line: 

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time;



mysqladmin pr | grep Query | sort -nk12
Example output: 
| 5950 | user1      | localhost | example_com | Query   | 0    | checking query cache for query | SELECT m.filename, m.datedir, m.description, m.media_type, m.preview
| 5977 | root          | localhost |               | Query   | 0    |                                | show processlist                                                                                     |
| 5032 | user2 | localhost | new_shop | Query   | 49   | Sending data                   | SELECT m.*, mm.is_winner, (SELECT COUNT(*) FROM votes v WHERE month = '8' AND v.season = '0' AND v.m |

All Processes

If you need to investigate mysql you can troubleshoot by investigating the processlist 

show processlist;
You should see an output similar to the following: 

+--------+---------+-----------+------+---------+-------+-------+------------------+ 
| Id     | User    | Host      | db   | Command | Time  | State | Info             | 
+--------+---------+-----------+------+---------+-------+-------+------------------+ 
| 109530 | root   | localhost | NULL | Query   |     0 | NULL  | show processlist | 
| 106092 | wp_usr | localhost | wp   | Sleep   | 25140 |       | NULL             |
| 106090 | wp_usr | localhost | wp   | Sleep   | 25140 |       | NULL             |
| 106091 | wp_usr | localhost | wp   | Sleep   | 25139 |       | NULL             |
| 109751 | root   | localhost | NULL | Sleep   |   244 |       | NULL             |

Process list WITHOUT sleeping processes

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id;

Header Description
ID Connection identifier
User MySQL user who issued the statement
Host Host name of the client issuing the statement
db Default database, if one is selected, otherwise NULL
Command The type of command the thread is executing
Time Time in seconds that the thread has been in its current state
State An action, event, or state that indicates what the thread is doing
Info The statement the thread is executing

mysql Common Commands

Command Description
Connect Replication slave is connected to its master
Connect Out Replication slave is in the process of connecting to its master
Drop DB Executing an operation to drop a database
Error Error
Execute Executing a prepared statement
Fetch Fetching the results of an executed prepared statement
Kill Killing another thread
Query Executing a statement
Quit In the process of terminating the thread
Sleep Waiting for the client to send a new statement
Table Dump Sending the contents of a table to a slave

source: https://mariadb.com/kb/en/mariadb/thread-command-values/