My.cnf slow query log issues

I'm having trouble with the my.cnf file for MySQL.  I cannot seem to get the slow query log to work.

MySQL version 5.1.52, CentOS.  I've tried using the WebMIN inteface to make the changes but it does work.  After making the changes and restarting the changes revert to OFF for some statements.  

So went to the CNF file.  This file path /etc/my.cnf.  It seems to read this file on startup because it enforces the timeout values.  

It creates the error log file specifed in the attached copy of the cnf,
 log-error=/var/log/mysql_errors.log.  But it does not create the slow query log.  Does anyone have the log section form a successful my.cnf?

Thanks.
my-new.txt
WaterstoneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tsmgeekCommented:
problem is that all mysql variables use _ not -
tsmgeekCommented:
ok sos not all are but some are

[mysqld]
datadir=/var/lib/mysql
log-slow-queries
long_query_time=5
log-queries-not-using-indexes
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
#skip-innodb
innodb_file_per_table
skip-bdb

max_connections=400
max_user_connections=400
max_connect_errors=999999

Open in new window


WaterstoneAuthor Commented:
Thanks. I've played with both formats, - & _ .  

Can you tell me how you think the following lines should be adjusted?  I'm confused because this line seems to work:  log-error=/var/log/mysql_errors.log.  Not sure if I need all the lines, if they are in the correct order, if they need reformatting.  I originally took this from a Windows my.ini and it worked, but I've modified it to try and get it to work.

slow-query-log=ON
log-output=FILE
log-queries-not-using-indexes=ON
slow-query-log-file=/var/log/mysql_slow_query.log
log-slow-admin-statement=ON
long-query-time=5
log-slow-slave-statements=ON
general-log=ON
log-slow-queries=ON
log-error=/var/log/mysql_errors.log
log=ON
general-log-file=/var/log/mysql_general.log
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

dnadavisCommented:
add this to your /etc/my.cnf file(mysql conf file)

log-slow-queries =/var/log/log-slow-queries.log(path to the file where to store the slow queries .Give 777 permission to this file chown 777 <filename>)
long_query_time = 2  ( if it is 2 mysql will log all those queries taking more than 2 ms )
log-queries-not-using-indexes  ( if you add this too in your conf file it will consider the queries that are not using indexes )

restart mysqld .
Go to your mysql .
try this there
mysql>show global variables ;
check  whether log_slow_queries  =  ON.
if it is ON then you can start querying and if any slow queries comes you can see them in the log file you mentioned in your my.cnf file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wolfgang_93Commented:
Just some minor comments on the parameters suggested by dnadavis (which look correct by the
way):
-  This would be for MySQL version 5 or greater. If you are running an older version of MySQL,
    I vaguely remember the config parameters to set slow query log is a little different.
-  If you set long_query_time = 2, it means log queries that take more than 2 seconds, not milliseconds.
   I personally choose 10 seconds for our MySQL systems.
-  I would not recommend log-queries-not-using-indexes because there is no rule that an index
   always makes for an optimal query. If a query is fast with no indexes, why should I care that it
   does not have any?
WaterstoneAuthor Commented:
Sorry I left this for so long.  Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.