How to see log files in MySQL?

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

 Answers

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';

---

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.

---

From the MySQL reference manual:

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

Check /var/lib/mysql folder.

---

shell> mysqladmin flush-logs


shell> mv host_name.err-old backup-directory

How to enable MySQL Query Log?

How do I enable the MySQL function that logs each SQL query statement received from clients and the time that query statement has submitted? Can I do that in phpmyadmin or NaviCat? How do I analyse the log?

 Answers

First, Remember that this logfile can grow very large on a busy server.

For mysql < 5.1.29:

To enable the query log, put this in /etc/my.cnf in the [mysqld] section

log   = /path/to/query.log  #works for mysql < 5.1.29

Also, to enable it from MySQL console

SET general_log = 1;

See http://dev.mysql.com/doc/refman/5.1/en/query-log.html

For mysql 5.1.29+

With mysql 5.1.29+ , the log option is deprecated. To specify the logfile and enable logging, use this in my.cnf in the [mysqld] section:

general_log_file = /path/to/query.log
general_log      = 1

Alternately, to turn on logging from MySQL console (must also specify log file location somehow, or find the default location):

SET global general_log = 1;

Also note that there are additional options to log only slow queries, or those which do not use indexes.

---

I use this method for logging when I want to quickly optimize different page loads. It's a little tip...

Logging to a TABLE

SET global general_log = 1;
SET global log_output = 'table';

You can then select from my mysql.general_log table to retrieve recent queries.

I can then do something similar to tail -f on the mysql.log, but with more refinements...

select * from mysql.general_log 
where  event_time  > (now() - INTERVAL 8 SECOND) and thread_id not in(9 , 628)
and argument <> "SELECT 1" and argument <> "" 
and argument <> "SET NAMES 'UTF8'"  and argument <> "SHOW STATUS"  
and command_type = "Query"  and argument <> "SET PROFILING=1"

This makes it easy to see my queries that I can try and cut back. I use 8 seconds interval to only fetch queries executed within the last 8 seconds.

---

You can disable or enable the general query log (which logs all queries) with

SET GLOBAL general_log = 1 # (or 0 to disable)

---

I also wanted to enable the mysql log file to see the queries and i have resolved this with the below instructions

1 - Go to /etc/mysql/mysql.conf.d 
2 - open the mysqld.cnf 
and enable the below lines

general_log_file = /var/log/mysql/mysql.log 
general_log = 1

3 - restart the mysql with this command /etc/init.d/mysql restart 
4 - go to /var/log/mysql/ and check the logs

---

for mysql>=5.5 only for slow queries (1 second and more) my.cfg

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

---

Not exactly an answer to the question because the question already has great answers. This is a side info. Enabling general_log really put a dent on MySQL performance. I left general_log =1 accidentally on a production server and spent hours finding out why performance was not comparable to a similar setup on other servers. Then I found this which explains the impact of enabling general log. 

Gist of the story, don't put general_log=1 in the .cnf file. Instead use set global general_log =1 for a brief duration just to log enough to find out what you are trying to find out and then turn it off.

---

I had to drop and recreate the general log at one point. During the recreation, character sets got messed up and I ended up having this error in the logs:

[ERROR] Incorrect definition of table mysql.general_log: expected the type of column 'user_host' at position 1 to have character set 'utf8' but found character set 'latin1'

So if the standard answer of "check to make sure logging is on" doesn't work for you, check to make sure your fields have the right character set.

Where can I find mysql logs in phpmyadmin?

where can I find the mysql logs (errors, queries, etc) in the phpmyadmin interface?

 Answers

Open your PHPMyAdmin, don't select any database and look for Binary Log tab . You can select different logs from a drop down list and press GO Button to view them.

---

In phpMyAdmin 4.0, you go to Status > Monitor. In there you can enable the slow query log and general log, see a live monitor, select a portion of the graph, see the related queries and analyse them.

---

If you are using XAMPP as your server, you'll find a logs directory as a child of the XAMPP directory. If you have not tried XAMPP, which runs on any system (Windows, Mac OS & Linux) find more here: http://www.apachefriends.org/en/xampp.html