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.

Log all queries in mysql

Is it possible for me to turn on audit logging on my mysql database?
I basically want to monitor all queries for an hour, and dump the log to a file.

 Answers

Start mysql with the --log option:

mysqld --log=log_file_name

or place the following in your my.cnf file:

log = log_file_name

Either one will log all queries to log_file_name.

You can also log only slow queries using the --log-slow-queries option instead of --log. By default, queries that take 10 seconds or longer are considered slow, you can change this by setting long_query_time to the number of seconds a query must take to execute before being logged.

---

Besides what i came across here, running the following was the simplest way to dump queries to a log file without restarting

SET global log_output = 'FILE';
SET global general_log_file='/Applications/MAMP/logs/mysql_general.log';
SET global general_log = 1;

can be turned off with

SET global general_log = 0;

---

Enable the log for table

mysql> SET GLOBAL general_log = 'ON';
mysql> SET global log_output = 'table';

View log by select query

select * from mysql.general_log

---

For the record, general_log and slow_log were introduced in 5.1.6:

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

5.2.1. Selecting General Query and Slow Query Log Output Destinations

As of MySQL 5.1.6, MySQL Server provides flexible control over the destination of output to the general query log and the slow query log, if those logs are enabled. Possible destinations for log entries are log files or the the general_log and slow_log tables in the mysql database

---

In case using AWS RDS MYSQL, step by step guide here.

When set as 'file' output you can view the log directly from AWS RDS "Log" Console.

AWS RDS MYSQL Logging

Logging slow queries with MySQL

MySQL has a slow query log which can be enabled if you need to track down slow queries which are causing issues for a website or application.

Enable the slow query log

MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries; from MySQL 5.1.0 you can change this dynamically without having to restart.

To make the change permanent whenever the MySQL server is started, and for MySQL prior 5.1.0, edit your my.cnf file (on Linux boxes this is usually somewhere like /etc/my.cnf or /etc/mysql/my.cnf) and uncomment the "log_slow_queries" line or add it if it's not present.

On a Debian box, for example, the line to uncomment looks like so:

log_slow_queries = /var/log/mysql/mysql-slow.log

You can change the log file name to something else or leave it blank so it uses the default. The default is to log the queries into a file in the MySQL data directory. On my Debian test box this was "mysqld-slow.log".

To enable or disable the setting dynamically in MySQL 5.1.0 run the following query to enable it:

set global log_slow_queries = ON;

and to disable it:

set global log_slow_queries = OFF;

Setting the long query time

You can also specify how long a quey needs to run for before it is logged with the "long_query_time" setting. By default this is 10 seconds.

In the my.cnf file, to change it to e.g. 5 seconds add the following:

long_query_time = 5

This can be changed dynamically in MySQL 5.0.0+ (and possibly earlier versions) by running the following query:

set global long_query_time = 5;

This will only work for new connections; any connections which have already been established will continue to use the old setting. Once the user disconnects and reconnects their new connection will use the new setting.

Errors when changing settings dynamically

If you get the following error message when attempting to change the log_slow_queries setting dynamically then you are using a version of MySQL that does not support changing the setting dynamically:

ERROR 1193 (HY000): Unknown system variable 'log_slow_queries'

The long_query_time value must be a whole number; if it's not (e.g. you attempt set global long_query_time = 2.5;) then you'll get this error:

#1232 - Incorrect argument type to variable 'long_query_time'

Note also that if you set the long_query_time to 0 it will not fail, but the actual setting applied will be 1 and not 0.

If you get the following error then you have omitted the "global" keyword when setting it:

ERROR 1229 (HY000): Variable 'log_slow_queries' is a GLOBAL variable and should be set with SET GLOBAL

 

Related posts:

• Location of MySQL's my.cnf file

Logging queries with MySQL

It is possible to log all queries done by the MySQL server. To enable MySQL logging, the MySQL manual indicates you should add --log[=file_name] when starting mysqld (the manual page is here). This isn't really the best options, as most Linux distributions use init scripts to start up MySQL, and this means either modifying them, or calling mysqld from the command line, which isn't really the best way of doing this when the init scripts do the job of starting and stopping services so nicely.

So instead, it is easier to modify the my.cnf file (located at /etc/my.cnf on many Linux distros) and add the logging option there. This is as simple as adding the following line under the [mysqld] section, creating a new [mysqld] section if one isn't present:

[mysqld]
log=/tmp/mysql.log

You can put the log file anywhere you want, but it must be in a location that the mysql daemon can write to, and the file must be writeable by mysqld. When doing this myself I initially put the file at /var/log/mysql.log but got the following error message in the mysqld.log logfile:

071023 17:11:03 mysqld started
/usr/libexec/mysqld: File '/var/log/mysql.log' not found (Errcode: 13)
071023 17:11:03 [ERROR] Could not use /var/log/mysql.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

I then did touch /var/log/mysql.log and restarted MySQL but got the same error message. So then chown mysql:mysql /var/log/mysql.log but still got the error message. As soon as I relocated the file to /tmp it worked fine.

As far as security is concerned, you should make sure the permissions for this file are 0700, so that only the mysql daemon can read the file, and no one else can. This is because every single query will be logged, and you don't want ordinary users to view the log file because queries may contain sensitive information, such as logins and passwords.

Another thing to remember is that every single query is logged (yes, this is the second time I've mentioned this). So you really only want to be logging MySQL queries when you are doing debugging, as the file will quickly become very large on a production server. You also have to restart MySQL each time you want to change the setting, so on a production server you would need to adjust these sorts of settings only during quiet periods.