How to profile MySQL

Question

How do I profile a MySQL database. I want to see all the SQL being run against a database.

I know you can do this:

• set profiling=1;
• Run your slow query (eg SELECT * FROM messages WHERE fromaddress='xxx';
• SHOW PROFILES;

But this seem to only apply to stuff run on the command line, I want to see the results from running a website.

Answer 1

You want the query log - but obviously doing this on a heavy production server could be... unwise.

Answer 2

That worked for me on Ubuntu.

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 

or in newer versions of mysql, comment OUT this lines of codes

general_log_file        = /var/log/mysql/mysql.log general_log             = 1 log_error                = /var/log/mysql/error.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