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;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.
You want the query log - but obviously doing this on a heavy production server could be... unwise.
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
You can simply parse the query log in real time. If on linux, you can use tail -f to see the log live
Also, you can try some free software from these guys:
http://hackmysql.com/mysqlsla
There is a commercial product
http://www.webyog.com/en/
MySqlAdministrator have some useful build in features (including logs view), but for logs it have to be run on same machine as database
