MySQL is a popular open-source relational database management system (RDBMS) used by developers and administrators to manage, store, and retrieve data in a structured format. When working with MySQL, it’s essential to monitor the active processes and queries to optimize performance, identify bottlenecks, and troubleshoot issues.
In this guide, we will explain how to show MySQL processes using the MySQL Command-Line Interface (CLI) and a graphical tool called phpMyAdmin. By following these steps, you will be able to monitor and manage the running processes in your MySQL server, ensuring smooth database operations and efficient resource utilization. The desired outcome is to have a clear understanding of how to display MySQL processes and interpret the results.
Option 1: MySQL CLI – Using the ‘SHOW PROCESSLIST’ Command
- Open the terminal on your Linux, macOS, or Windows machine.
- Connect to your MySQL server by typing the following command:
mysql -u USERNAME -p
Replace “USERNAME” with your MySQL username and press Enter. You will be prompted to enter your password.
- After logging in to the MySQL server, type the following command to display the active processes:
SHOW PROCESSLIST;
- Press Enter.
The terminal will display a table with information about the currently running processes, including their IDs, user, host, database, command, time, state, and query.
+----+---------------+-------------------+-------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------------+-------------------+-------------+---------+------+-------+------------------+ | 23 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 46 | wordpressuser | 192.168.0.5:38876 | wordpressdb | Sleep | 69 | | NULL | | 51 | root | localhost | wordpressdb | Sleep | 34 | | NULL | +----+---------------+-------------------+-------------+---------+------+-------+------------------+ 3 rows in set (0.00 sec)
- To exit the MySQL CLI, type exit and press Enter.
If you type SHOW PROCESSLIST\G in the MySQL CLI, it will display the currently running processes in a vertical format instead of the default tabular format. Each process will be listed one after another with the process details stacked vertically, making it easier to read when dealing with long queries or a large number of columns.
SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 23
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 46
User: wordpressuser
Host: 192.168.0.5:38876
db: wordpressdb
Command: Sleep
Time: 73
State:
Info: NULL
*************************** 3. row ***************************
Id: 51
User: root
Host: localhost
db: wordpressdb
Command: Sleep
Time: 38
State:
Info: NULL
3 rows in set (0.00 sec)
You have an option to run processlist in mysqladmin command. Below example show processlist every two second :
To display the MySQL process list and update it every 2 seconds, allowing you to monitor the running processes in real time, use the following command:
mysqladmin -u root -p -i 2 processlist
+----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+
Option 2: phpMyAdmin – Accessing the Process List
- Open your web browser and log in to your phpMyAdmin interface. The URL typically looks like http://your-server-address/phpmyadmin.
- After logging in, click on the “Status” tab in the top menu.
- Under the “Status” tab, click on the “Processes” sub-tab.
- The process list will be displayed in a table format, showing information about the currently running processes, including their IDs, user, host, database, command, time, state, and query.
- To refresh the process list, click on the “Refresh” button located below the table.
Commands Mentioned:
- mysql – A command-line tool for connecting to and managing MySQL servers.
- SHOW PROCESSLIST; – A MySQL command for displaying the currently running processes in a MySQL server.
Conclusion
By following this guide, you have successfully displayed MySQL processes using both MySQL CLI and phpMyAdmin. Monitoring and managing these processes is crucial for maintaining optimal database performance, identifying slow queries, and resolving potential issues.
Understanding how to use the ‘SHOW PROCESSLIST’ command and the process list in phpMyAdmin will empower you to handle your MySQL server more effectively, resulting in better resource utilization and smoother database operations.
We invite you to share your thoughts, comments, and suggestions for improvements to this guide. Your feedback is invaluable in helping us provide the most accurate and helpful information possible.
