Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My MySQL server has been unusually been using a higher load of CPU. So I looked around how to log each queries and I found a lot of tutorials that were saying the same thing: http://www.howtogeek.com/howto/database/monitor-all-sql-queries-in-mysql/

I followed the tutorial but the log file did not contain any queries. One thing I thought I should mention is that, the /var/logs/ the folder mysql did not exist, so I made the folder and created a log file called mysql.log in it.

A little background of my box:

  • Arch Linux
  • Nginx
  • PHP/Ruby/Python/Perl
  • MySQL
share|improve this question
    
Well what do you have in your mysql config? Do you have an uncommetteded log =? –  Zoredache Jul 9 '12 at 17:46
    
Setting logging via config needs to restart server to take effect. Otherwise, global variables take effect immediately. –  Kondybas Jul 9 '12 at 17:53
    
Sorry, should have mentioned that. No originally I did not have an uncommented line for log = . So I did add in that part, this is what I put: log = /var/log/mysql/mysql.log. –  samwell Jul 9 '12 at 17:56
    
@Kondybas, I did restart both MySQL and the whole server, and I still got the same results –  samwell Jul 9 '12 at 17:57
    
recommendations to turn on the general log are not helpful for performance troubleshooting. Enable the slow query log instead as nojak suggests –  Gavin Towey Jul 10 '12 at 2:32
add comment

2 Answers 2

By default mysql keep logs in the working directory, usually /var/db/mysql. All you need to enable logging is to set variable general_log to "ON". Also, you can set general_log_file variable to the desired place.

SET @@global.general_log = 1;
SET @@global.general_log_file = '/where/to/store/log/file.txt';
share|improve this answer
    
I am not able to find neither 'general_log' in my.cnf or the folder /var/db/mysql/. I did a select on both of those variables, select @@global.general_log; => 1, and select @@global.general_log_file; => /var/log/mysql/mysql.log. –  samwell Jul 9 '12 at 18:07
    
You have to SET, not SELECT –  Kondybas Jul 9 '12 at 18:52
    
Shouldn't I be able to select the variable and see what is currently stored? –  samwell Jul 9 '12 at 19:08
    
    
@chudapati09 You should use SHOW VARIABLES LIKE '%log%' to view the variables. –  mwasif Jul 10 '12 at 3:45
add comment

Check out the Slow Query Log option.

You can find detailed information on dev.mysql.com

share|improve this answer
    
This is a better option than the general log for performance issues. –  Gavin Towey Jul 10 '12 at 2:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.