Home / Showing running queries in MySQL MySQL has a statement called "show processlist" to show you the running queries on your MySQL server. This can be useful to find out what’s going on if there are some big, long queries consuming a lot of CPU cycles, or if you’re getting errors like "too many connections". The syntax is simply: which will output something along these lines: The "info"
column shows the query being executedm or NULL if there’s nothing currently happening. When running "show processlist" it will only show the first 100 characters of the query. To show the full query run "show full processlist" instead. Running the above command from the MySQL command line interface with a ; delimiter can make it difficult to read the output, especially if the queries are long and span multiple lines. Using the G delimiter instead will show the data in what is often a more
readable format, although it consumes more rows in your terminal. This is espcially useful when running "show full processlist" because some of the queries displayed may be quite long. If you are running as an ordinary user who doesn’t have the "process" privilege, then "show processlist" will only show the processes you yourself are currently running. If the user has the process privilege then they can see everything.show processlist;
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 708163 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 708174 | test | localhost | test | Query | 2 | Copying to tmp table | select dist.name, dist.filename, count[*]
from orders_header h
inner join orders_detail d on h.ord |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2 rows in set [0.00 sec]
mysql> show processlistG
*************************** 6. row ***************************
Id: 708163
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 7. row ***************************
Id: 708174
User: test
Host: localhost
db: test
Command: Query
Time: 3
State: Copying to tmp table
Info: select dist.name, dist.filename, count[*]
from orders_header h
inner join orders_detail d on h.ord
2 rows in set [0.00 sec]
strace
The quickest way to see live
MySQL/MariaDB queries is to use debugger. On Linux you can use strace
, for example:
sudo strace -e trace=read,write -s 2000 -fp $[pgrep -nf mysql] 2>&1
Since there are lot of escaped characters, you may format strace's output by piping [just add |
between these two one-liners] above into the following command:
grep --line-buffered -o '".\+[^"]"' | grep --line-buffered -o '[^"]*[^"]' | while read -r line; do printf "%b" $line; done | tr "\r\n" "\275\276" | tr -d "[:cntrl:]" | tr "\275\276" "\r\n"
So you should see fairly clean SQL queries with no-time, without touching configuration files.
Obviously this won't replace the standard way of enabling logs, which is described below [which involves reloading the SQL server].
dtrace
Use MySQL probes to view the live MySQL queries without touching the server. Example script:
#!/usr/sbin/dtrace -q
pid$target::*mysql_parse*:entry /* This probe is fired when the execution enters mysql_parse */
{
printf["Query: %s\n", copyinstr[arg1]];
}
Save above script to a file [like watch.d
], and run:
pfexec dtrace -s watch.d -p $[pgrep -x mysqld]
Learn more: Getting started with DTracing MySQL
Gibbs MySQL Spyglass
See this answer.
Logs
Here are the steps useful for development proposes.
Add these lines into your ~/.my.cnf
or global my.cnf
:
[mysqld]
general_log=1
general_log_file=/tmp/mysqld.log
Paths: /var/log/mysqld.log
or
/usr/local/var/log/mysqld.log
may also work depending on your file permissions.
then restart your MySQL/MariaDB by [prefix with sudo
if necessary]:
killall -HUP mysqld
Then check your logs:
tail -f /tmp/mysqld.log
After finish, change general_log
to 0
[so you can use it in future], then remove the file and restart SQL server again: killall -HUP mysqld
.
How can I see what queries are running?
You can run below query to get all the required information:.
select..
r. session_id,.
s. login_name,.
c. client_net_address,.
s. host_name,.
s. program_name,.
st. text, s. status..
from sys. dm_exec_requests r..
How do I check for update queries in MySQL?
MySQL Update Command Syntax UPDATE `table_name` is the command that tells MySQL to update the data in a table . SET `column_name` = `new_value' are the names and values of the fields to be affected by the update query. Note, when setting the update values, strings data types must be in single quotes.