SEARCH

How-To Geek

Monitor All SQL Queries in MySQL

Microsoft’s SQL Server has a tool called Profiler that you can use to monitor every SQL query that hits the database. This is extremely useful for programmers as well as database administrators to troubleshoot the exact queries generated by an application.

Having switched to using MySQL on a frequent basis, this was one of the first things I wanted to figure out how to do. How else can you see the actual SQL code generated by WordPress or phpBB?

The first thing we’ll need to do is turn on logging of queries in MySQL. Be warned that this should only be done in development… it really slows things down to log every single query to a file.

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)

I’m impressed, phpbb3 has fairly tight, optimized SQL code. WordPress, on the other hand, is very inefficient.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 05/16/07

Comments (12)

  1. supermansghost

    This is an awesome tip.

  2. JTMcNaught

    This is exactly what I needed to help me optimize my growing website. Thanks to How-To-Geek, many hours of frustration of mine have been saved and now I can finally analyse the freqency and stress loads caused by all of the different queries hitting the MYSQL server and finally resolve some of the slowdowns and performance issues due to the load.

    With appreciation!

    JTMcNaught

  3. Alain

    Thank you, I was trying to remember how to do this.
    The information is concise and precise. Right to the point.

  4. Jaris

    Thank you!
    Exactly what I needed!

  5. battisti

    Exacty what im looking for!

  6. BARBARA

    I MESSED UP MY PC AS FAR AS THE SYSTEM RESTORE..IT WONT GO BACK TO RESTORE EXCEPT THE DAY BEFORE ONLY ..HOW DO I FIX THIS? DESPERATLY IN NEED OF HELP..TY

  7. Stephan

    thank you!
    that was just what i needed :-)

  8. sathya

    Hi,
    System: Ubuntu 10.10 – the Maverick Meerkat – released in October 2010

    Wanted to do the same and opened up /etc/mysql/my.cnf. But due non writeable permission i have changed the my.cnf to “chmod 777 my.cnf”. But the whole mysql is not working. So again I changed it to -rw-r–r– and still not working. mysql command gives following error

    “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)”

    Please help. I have lot of data my mysql.

  9. Thavva

    How to do this on windows machine? I did not see .cnf file.

  10. martin

    Thavva,

    If you download the MYSQL administrator you can use it to see the location of your cnf file under service control – configure service.

    also if you add the entry log = ( with no parameters ) the general log is written to the default location and can be viewed by the MYSQL administrator – server logs option.

  11. Dharmender Tuli

    Thanx man i got the right stuff.

  12. Dave

    @Barbara – It obviously broke your caps lock too..

Enter Your Email Here to Get Access for Free:

Go check your email!