How can I view live MySQL queries

Question

How can I trace MySQL queries on my Linux server as they happen   For example I d love to set up some sort of listener  then request a web page and view all of the queries the engine executed  or just view all of the queries being run on a production server   How can I do this

User · Answer

Run this convenient SQL query to see running MySQL queries. It can be run from any environment you like, whenever you like, without any code changes or overheads. It may require some MySQL permissions configuration, but for me it just runs without any special setup.

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

The only catch is that you often miss queries which execute very quickly, so it is most useful for longer-running queries or when the MySQL server has queries which are backing up - in my experience this is exactly the time when I want to view "live" queries.

You can also add conditions to make it more specific just any SQL query.

e.g. Shows all queries running for 5 seconds or more:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;

e.g. Show all running UPDATEs:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND INFO LIKE '%UPDATE %';

For full details see: http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html

User · Answer

You can log every query to a log file really easily   mysql gt  SHOW VARIABLES LIKE  general log      ------------------ ----------------------------    Variable name      Value                         ------------------ ----------------------------    general log        OFF                            general log file    var run mysqld mysqld log    ------------------ ----------------------------   mysql gt  SET GLOBAL general log    ON     Do your queries  on any db   Grep or otherwise examine  var run mysqld mysqld log   Then don t forget to   mysql gt  SET GLOBAL general log    OFF     or the performance will plummet and your disk will fill

User · Answer

I m in a particular situation where I do not have permissions to turn logging on  and wouldn t have permissions to see the logs if they were turned on   I could not add a trigger  but I did have permissions to call show processlist   So  I gave it a best effort and came up with this   Create a bash script called  showsqlprocesslist       bin bash  while   1 -le 1   do          mysql --port      --protocol tcp --password      --user      --host      -e  show processlist G    grep Info   grep -v processlist   grep -v  Info  NULL   done   Execute the script     showsqlprocesslist  gt  showsqlprocesslist out  amp    Tail the output   tail -f showsqlprocesslist out   Bingo bango   Even though it s not throttled  it only took up 2-4  CPU on the boxes I ran it on   I hope maybe this helps someone

User · Answer

I ve been looking to do the same  and have cobbled together a solution from various posts  plus created a small console app to output the live query text as it s written to the log file  This was important in my case as I m using Entity Framework with MySQL and I need to be able to inspect the generated SQL   Steps to create the log file  some duplication of other posts  all here for simplicity     Edit the file located at   C  Program Files  x86  MySQL MySQL Server 5 5 my ini   Add  log development log  to the bottom of the file   Note saving this file required me to run my text editor as an admin   Use MySql workbench to open a command line  enter the password   Run the following to turn on general logging which will record all queries ran   SET GLOBAL general log    ON    To turn off   SET GLOBAL general log    OFF     This will cause running queries to be written to a text file at the following location   C  ProgramData MySQL MySQL Server 5 5 data development log  Create   Run a console app that will output the log information in real time   Source available to download here  Source    using System  using System Configuration  using System IO  using System Threading   namespace LiveLogs ConsoleApp     class Program         static void Main string   args                   Console sizing can cause exceptions if you are using a             small monitor  Change as required           Console SetWindowSize 152  58           Console BufferHeight   1500           string filePath   ConfigurationManager AppSettings  MonitoredTextFilePath             Console Title   string Format  Live Logs  0    filePath            var fileStream   new FileStream filePath  FileMode Open  FileAccess ReadWrite  FileShare ReadWrite               Move to the end of the stream so we do not read in existing            log text  only watch for new text           fileStream Position   fileStream Length           StreamReader streamReader              Commented lines are for duplicating the log output as it s written to             allow verification via a diff that the contents are the same and all             is being output              var fsWrite   new FileStream   C  DuplicateFile txt   FileMode Create              var sw   new StreamWriter fsWrite            int rowNum   0           while  true                        streamReader   new StreamReader fileStream                string line              string rowStr               while  streamReader Peek      -1                                rowNum                     line   streamReader ReadLine                    rowStr   rowNum ToString                     string output   String Format   0   1   t 2    rowStr PadLeft 6   0    DateTime Now ToLongTimeString    line                    Console WriteLine output                       sw WriteLine output                                 sw Flush                 Thread Sleep 500

User · Answer

In addition to previous answers describing how to enable general logging  I had to modify one additional variable in my vanilla MySql 5 6 installation before any SQL was written to the log   SET GLOBAL log output    FILE     The default setting was  NONE

User · Answer

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 gt  amp 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

User · Answer

Gibbs MySQL Spyglass  AgilData launched recently the Gibbs MySQL Scalability Advisor  a free self-service tool  which allows users to capture a live stream of queries to be uploaded to Gibbs  Spyglass  which is Open Source  will watch interactions between your MySQL Servers and client applications  No reconfiguration or restart of the MySQL database server is needed  either client or app    GitHub  AgilData gibbs-mysql-spyglass  Learn more  Packet Capturing MySQL with Rust  Install command   curl -s https   raw githubusercontent com AgilData gibbs-mysql-spyglass master install sh   bash

User · Answer

Check out mtop

User · Answer

This is the easiest setup on a Linux Ubuntu machine I have come across  Crazy to see all the queries live   Find and open your MySQL configuration file  usually  etc mysql my cnf on Ubuntu  Look for the section that says    Logging and Replication           Logging and Replication     Both location gets rotated by the cronjob    Be aware that this log type is a performance killer   log    var log mysql mysql log   Just uncomment the    log    variable to turn on logging  Restart MySQL with this command   sudo  etc init d mysql restart   Now we   re ready to start monitoring the queries as they come in  Open up a new terminal and run this command to scroll the log file  adjusting the path if necessary   tail -f  var log mysql mysql log   Now run your application  You   ll see the database queries start flying by in your terminal window   make sure you have scrolling and history enabled on the terminal   FROM http   www howtogeek com howto database monitor-all-sql-queries-in-mysql

User · Answer

From a command line you could run   watch --interval  your-interval-in-seconds   mysqladmin -u root -p your-root-pw  processlist   grep  your-db-name     Replace the values  x  with your values   Or even better     mysqladmin -u root -p -i 1 processlist

User · Answer

You can run the MySQL command SHOW FULL PROCESSLIST  to see what queries are being processed at any given time  but that probably won t achieve what you re hoping for   The best method to get a history without having to modify every application using the server is probably through triggers  You could set up triggers so that every query run results in the query being inserted into some sort of history table  and then create a separate page to access this information   Do be aware that this will probably considerably slow down everything on the server though  with adding an extra INSERT on top of every single query     Edit  another alternative is the General Query Log  but having it written to a flat file would remove a lot of possibilities for flexibility of displaying  especially in real-time  If you just want a simple  easy-to-implement way to see what s going on though  enabling the GQL and then using running tail -f on the logfile would do the trick

User · Answer

Even though an answer has already been accepted  I would like to present what might even be the simplest option     mysqladmin -u bob -p -i 1 processlist   This will print the current queries on your screen every second    -u The mysql user you want to execute the command as -p Prompt for your password  so you don t have to save it in a file or have the command appear in your command history  i The interval in seconds  Use the --verbose flag to show the full process list  displaying the entire query for each process   Thanks  nmat    There is a possible downside  fast queries might not show up if they run between the interval that you set up  IE  My interval is set at one second and if there is a query that takes  02 seconds to run and is ran between intervals  you won t see it   Use this option preferably when you quickly want to check on running queries without having to set up a listener or anything else

[mysql] How can I view live MySQL queries?

Examples related to mysql

Examples related to monitoring