[mysql] How can I view live MySQL queries?

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?

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

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 > showsqlprocesslist.out &

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.

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;

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.

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):

1. 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).

2. 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
   

3. 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);
           }
       }
     }
   }
   

Check out mtop.

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

You can log every query to a log file really easily:

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> 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> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

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.

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/

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'.