MySQL show processlist description, mysqlprocesslist

MySQL show processlist description, mysqlprocesslist

Show processlist and show full processlist

The output result of the processlist command shows which threads are running. You can view not only the number of current connections, but also the current connection status to help identify problematic query statements.

If it is a root account, you can see the current connection of all users. If it is another common account, only the connections occupied by the account can be seen. Showprocesslist can only list the current 100 entries. If you want to list all of them, you can use the show full processlist command.

mysql> show processlist;+----+------+--------------------+------+---------+-------+-------+------------------+| Id | User | Host               | db   | Command | Time  | State | Info             |+----+------+--------------------+------+---------+-------+-------+------------------+|  1 | root | localhost          | NULL | Sleep   |    12 |       | NULL             ||  2 | root | 192.168.100.1:7437 | test | Sleep   |  8035 |       | NULL             ||  3 | root | 192.168.100.1:7438 | NULL | Sleep   | 24348 |       | NULL             ||  5 | root | 192.168.100.1:7443 | NULL | Sleep   | 24317 |       | NULL             ||  7 | root | 192.168.100.1:7450 | test | Sleep   | 24272 |       | NULL             ||  9 | root | 192.168.100.1:5152 | test | Query   |     0 | init  | show processlist |+----+------+--------------------+------+---------+-------+-------+------------------+6 rows in setmysql> show full processlist;+----+------+--------------------+------+---------+-------+-------+-----------------------+| Id | User | Host               | db   | Command | Time  | State | Info                  |+----+------+--------------------+------+---------+-------+-------+-----------------------+|  1 | root | localhost          | NULL | Sleep   |    19 |       | NULL                  ||  2 | root | 192.168.100.1:7437 | test | Sleep   |  8042 |       | NULL                  ||  3 | root | 192.168.100.1:7438 | NULL | Sleep   | 24355 |       | NULL                  ||  5 | root | 192.168.100.1:7443 | NULL | Sleep   | 24324 |       | NULL                  ||  7 | root | 192.168.100.1:7450 | test | Sleep   | 24279 |       | NULL                  ||  9 | root | 192.168.100.1:5152 | test | Query   |     0 | init  | show full processlist |+----+------+--------------------+------+---------+-------+-------+-----------------------+6 rows in set

Meaning of each column:

①. Id column. You can use the connection_id () function to view the connection_id assigned by the system when logging on to mysql.
②. The user column displays the current user. If it is not root, this command only displays SQL statements with the user permission range
③ The host column shows the port from which the statement was sent and can be used to track users with problematic statements.
4. the db column shows which database the process is currently connected.
⑤ The command column displays the commands executed by the current connection. The values are sleep, query, and connect.
6. The time column shows the duration of this state, in seconds.
7. The state column displays the status of the SQL statement that uses the current connection. It is a very important column. State describes a state in statement execution. An SQL statement, taking the query as an example, may be completed only after the status is copying to tmp table, sorting result, sending data, etc.
The explain. info column displays this SQL statement, which is an important basis for determining the problem statement.

In the master-slave replication environment, show processlist or show full processlist is helpful for determining the status, such as the following state column:

 

Go back to the Linux series article outline: workshop!