Often database administrators need to monitor the live SQL queries being run on their MySQL databases. There are several third-party database administration tools that allow you to do this out of box. But sometimes you may not have the budget for these tools, or you may just want to track live SQL queries only once in a while. In this article, we will learn how to do this without using any third-party tools.
How to View Live MySQL Queries
MySQL allows you to look each SQL query to its server on a general log file. You can find its location with the following SQL query, and also whether it is enabled or not.
mysql> SHOW VARIABLES LIKE "general_log%"; +------------------+----------------------------+ | Variable_name | Value | +------------------+----------------------------+ | general_log | OFF | | general_log_file | /var/run/mysqld/mysqld.log | +------------------+----------------------------+
In most cases, by default, you will find the value of general_log flag is OFF. You can enable it with the following command.
mysql> SET GLOBAL general_log = 'ON';
Once you have turned on general logging, MySQL will start logging queries in file path mentioned in general_log_file flag’s value (/var/run/mysqld/mysqld.log) above.
You can view this file using any of the file viewing tools like cat, tail, head, grep, etc.
Here is a simple command to view the latest 10 queries written to this file.
tail -f -n10 /var/run/mysqld/mysqld.log
Once you are done reading your log files, it is better to switch off general logging with the following SQL query. Else the log file will keep growing as more queries are executed on your database server and it will fill up your disk quickly.
mysql> SET GLOBAL general_log = 'OFF';
Alternatively, you can also use mysqladmin tool to get current queries being executed. It runs at specific intervals of time to display current queries. You can run it every second if you want. Here is the command to run MySQL admin tool every second. Replace username with your MySQL username.
$ mysqladmin -u username -p -i 1 processlist
Here is what the different options above mean:
-uThe mysql user you want to execute the command as-pPrompt for your password (so you don’t have to save it in a file or have the command appear in your command history)- –
i– interval in seconds. - (Optional)
--verbose– to show the full process list, displaying the entire query for each process
The only downside is that since this tool runs at regular intervals of time, if there are any fast queries that run between an interval, then they may not be captured.
For example, in the above query, the interval is 1 second and if any query takes 0.03 second, it will not be seen.
In this article, we have learnt how to view live MySQL queries.
Also read:
How to Generate Random String in MySQL
How to Import Excel File in MySQL
How to View MySQL Log Files
How to Run MySQLdump Without Locking Tables
How to Check if MySQL Database Exists
Thank you.