How to log queries in MySql (Mysql 5.1.4)

This article based is based on:

MySql version: 5.1.4 (logging on a file)

OS: Ubuntu 10.04

Stack: lampp

note: Mysql version 5.1.6 and above support logging on file and table[2].

There are 2 methods:

Method 1: Enable logging on run time
1. Enter mysql prompt

mysql -u root -p

2. Enable logging

mysql> SET GLOBAL general_log = 'ON'

3. Find out the default mysql log file

mysql> show variables like '%log_file';
+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| general_log_file    | /opt/lampp/var/mysql/ruby-desktop.log      |
| slow_query_log_file | /opt/lampp/var/mysql/ruby-desktop-slow.log |
+---------------------+--------------------------------------------+

4. View the log on a terminal

sudo tail -f /opt/lampp/var/mysql/ruby-desktop.log

Method 2: Enable logging in my.cnf

1. edit my.cnf

sudo vim /opt/lampp/etc/my.cnf

2. enable logging, under [mysqld] section of the file.

# The MySQL server

[mysqld]

general_log = 1

3. Find out the default mysql log file. (inside mysql terminal type the following)

mysql> show variables like '%log_file';

+---------------------+--------------------------------------------+

| Variable_name | Value |

+---------------------+--------------------------------------------+

| general_log_file | /opt/lampp/var/mysql/ruby-desktop.log |

| slow_query_log_file | /opt/lampp/var/mysql/ruby-desktop-slow.log |

+---------------------+--------------------------------------------+

4. view the log on a terminal

sudo tail -f /opt/lampp/var/mysql/ruby-desktop.log

Reference documents:

1. logging in file / table (based on MySQL version ) (mysql >= 5.1.6) (you can view the output in mysql query browser too.)
http://lserinol.blogspot.com/2009/02/mysql-general-query-log.html

2. logging variables
http://bugs.mysql.com/bug.php?id=32748

3. The General Query log (always check the documentation for the mysql version installed in your system )
http://dev.mysql.com/doc/refman/5.1/en/query-log.html

4. Logging queries with MySQL
http://www.electrictoolbox.com/logging-queries-with-mysql/