how to see log files in mysql?

Good Afternoon All,
I read out that Mysql server create a log file where he keep record of all activities- like when and what query execute.
I want to see it, Can anybody tell me where it exist in my system? how can i read it? basically i need to make backup of database with different input [ backup between two dates] so i think i need to use log file here, that’s why i want to go for it…
when i wondered i got that binary log file keep username and password information too[ if any query require of this] so it is very secure, not easy seen it!! i am a admin of my system , how i can see it? 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

MySQL: how do I see which config files are used?

In order to reinstall my MySQL database, I deleted /etc/my.cnf But what are the default settings of MySQL? And how do I see which configuration files are in use? I can see that mysql –help gives me a

MySQL log files deletion

I have a master and slave database running on different nodes. The master DB is subjected to huge no. of inserts/updates. The master DB size is close to 6 GB, while the log files are now occupying a s

MySQL ReadOnly user cannot see slow query log

I have the MySQL slow query log turned on. For my main user account I can run this query and see results: select * from mysql.slow_log limit 1; I also have a readonly user account. This account has

How can I check drupal log files

how can I check drupal log files. I m using Ubuntu 10.10 + apache2 + php 5.33 + mysql for drupal 7.

I can’t see how log2 + log3 + log4 = log(n!) [closed]

I can’t see how log2 + log3 + log4 + … + log n= log(n!) The values work out, but I can’t understand why they do. Please help me out!

php Activity log to see which files are being executed

Is there any way to see active processes in PHP (like which files are being executed)?

How to stop the innodb log files?

I want to prevent mysql database from creation of innodb log files. can we delete or stop it ?

How to view snort log files

I have been working with snort-IDS. I have got some log files at /var/log/snort. The files are of type snort.log.xxxx. How do i view this file???

How to create separate log files?

I’m using Log::Log4perl to create log files, but it is appending to a single log file; instead, I want to create a separate log file for each execution of my script. How can I create separate log file

How can I see the output of console.log()?

I am using this code: <script type = text/javascript> console.log(hello world); </script> How can I see the output of console.log()? I searched on Google but did not find a solution.

Answers

You have to activate the query logging in mysql.

1.edit /etc/my.cnf

[mysqld]
log=/tmp/mysql.log

2.restart the computer or the mysqld service

service mysqld restart

3.open phpmyadmin/any application that uses mysql/mysql console and run a query

4.cat /tmp/mysql.log ( you should see the query )

From the MySQL reference manual:

“By default, all log files are created in the data directory.”

Check /var/lib/mysql

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 super user, with terminal:

sudo geany /etc/mysl/my.cnf

(I prefer to use Geany instead of gedit or VI, it doesn’t matter)

I just uncommented them & save the file then restatt mysql with

sudo service mysql restart

Run several queries, open above file (/var/log/mysql/mysql.log) and the log was there 🙂

Display the journal and you will see the output of mysqld

journalctl -f

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. o_O

Click here to visit the blog

shell> mysqladmin flush-logs


shell> mv host_name.err-old backup-directory

The MySQL logs are determined by the global variables such as:

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 ControlC when finish

When general log is enabled, try:

sudo tail -f $(mysql -Nse "SELECT CONCAT(@@datadir, @@general_log_file)")

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

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