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.
This is an awesome tip.
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
Thank you, I was trying to remember how to do this.
The information is concise and precise. Right to the point.
Thank you!
Exactly what I needed!
Exacty what im looking for!
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
thank you!
that was just what i needed :-)
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.
How to do this on windows machine? I did not see .cnf file.
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.
Thanx man i got the right stuff.
@Barbara – It obviously broke your caps lock too..