How to see log files in MySQL

Question

I ve read that Mysql server creates a log file where it keeps a record of all activities - like when and what queries execute     Can anybody tell me where it exists in my system  How can I read it   Basically  I need to back up the database with different input  backup between two dates  so I think I need to use log file here  that s why I want to do it     I think this log must be secured somehow because sensitive information such as usernames and password may be logged  if any query require this   so may it be secured  not easily able to be seen   I have root access to the system  how can I see the log    When I try to open  var log mysql log it is empty   This is my config file    client  port          3306 socket         var run mysqld mysqld sock   mysqld safe  socket         var run mysqld mysqld sock nice          0   mysqld  log    var log mysql mysql log  binlog-do-db zero    user          mysql socket         var run mysqld mysqld sock port          3306 basedir        usr datadir        var lib mysql tmpdir         tmp skip-external-locking  bind-address          127 0 0 1       Fine Tuning   key buffer        16M max allowed packet    16M thread stack          192K thread cache size         8  general log file           var log mysql mysql log general log               1

User · Answer

From the MySQL reference manual      By default  all log files are created in the data directory    Check  var lib mysql folder

User · Answer

You have to activate the query logging in mysql    edit  etc my cnf    mysqld  log  tmp mysql log  restart the computer or the mysqld service    service mysqld restart  open phpmyadmin any application that uses mysql mysql console and run a query cat  tmp mysql log   you should see the query

User · Answer

Here is a simple way to enable them  In mysql we need to see often 3 logs which are mostly needed during any project development    The Error Log  It contains information about errors that occur while the server is running  also server start and stop  The General Query Log  This is a general record of what mysqld is doing  connect  disconnect  queries  The Slow Query Log   t consists of  slow  SQL statements  as indicated by its name     By default no log files are enabled in MYSQL  All errors will be shown in the syslog   var log syslog    To Enable them just follow below steps   step1  Go to this file   etc mysql conf d mysqld safe syslog cnf  and remove or comment those line   step2  Go to mysql conf file   etc mysql my cnf  and add following lines  To enable error log add following    mysqld safe  log error  var log mysql mysql error log   mysqld  log error  var log mysql mysql error log   To enable general query log add following  general log file           var log mysql mysql log general log               1   To enable Slow Query Log add following  log slow queries          var log mysql mysql-slow log long query time   2 log-queries-not-using-indexes   step3  save the file and restart mysql using following commands  service mysql restart   To enable logs at runtime  login to mysql client  mysql -u root -p  and give   SET GLOBAL general log    ON   SET GLOBAL slow query log    ON     Finally one thing I would like to mention here is I read this from a blog  Thanks  It works for me   Click here to visit the blog

User · Answer

In addition to the answers above you can pass in command line parameters to the mysqld process for logging options instead of manually editing your conf file  For example  to enable general logging and specifiy a file   mysqld --general-log --general-log-file  var log mysql general log   Confirming other answers above  mysqld --help --verbose gives you the values from the conf file  so running with command line options general-log is FALSE   whereas mysql -se  SHOW VARIABLES    grep -e log error -e general log gives   general log     ON general log file         var log mysql general log   Use slightly more compact syntax for the error log   mysqld --general-log --general-log-file  var log mysql general log --log-error  var log mysql error log

User · Answer

The MySQL logs are determined by the global variables such as    log error for the error message log  general log file for the general query log file  if enabled by general log   slow query log file for the slow query log file  if enabled by slow query log     To see the settings and their location  run this shell command   mysql -se  SHOW VARIABLES    grep -e log error -e general log -e slow query log   To print the value of error log  run this command in the terminal   mysql -e  SELECT   GLOBAL log error    To read content of the error log file in real time  run   sudo tail -f   mysql -Nse  SELECT   GLOBAL log error     Note  Hit Control-C when finish  When general log is enabled  try   sudo tail -f   mysql -Nse  SELECT CONCAT   datadir    general log file        To use mysql with the password access  add -p or -pMYPASS parameter  To to keep it remembered  you can configure it in your    my cnf  e g    client  user root password root   So it ll be remembered for the next time

User · Answer

To complement loyola s answer it is worth mentioning that as of MySQL 5 1 log slow queries is deprecated and is replaced with slow-query-log  Using log slow queries will cause your service mysql restart or service mysql start to fail

User · Answer

shell gt  mysqladmin flush-logs   shell gt  mv host name err-old backup-directory

User · Answer

In my  I have LAMP installed   etc mysql my cnf file I found following  commented lines in  mysqld  section  general log file           var log mysql mysql log general log               1  I had to open this file as superuser  with terminal  sudo geany  etc mysql my cnf   I prefer to use Geany instead of gedit or VI  it doesn t matter  I just uncommented them  amp  save the file then restart MySQL with sudo service MySQL restart  Run several queries  open the above file   var log mysql mysql log  and the log was there

[mysql] How to see log files in MySQL?

Examples related to mysql

Examples related to logging