Show Mysql Log

Sometimes, You use database programer interface like JDBC,ODBC,
Or Use Database ORM frameWork, When some errors occured,
If you dont know what had happend, at this moment,
You want to see SQL source that had excuted. So what should we do.
This is only Used on Linux.(contains all the linux distributions like ubuntu, redhat, centos and so on)

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(If there have this file).
step2: Go to mysql conf file(/etc/mysql/my.cnf ) and add following lines

To enable general query log add following
[mysqld] label is necessary, if mysql.log file is exsits, delete it, and restart mysql service.

1
2
3
[mysqld]
general_log_file = /var/log/mysql/mysql.log
general_log = 1 (if not ok?  general_log=on)

step3: save the file and restart mysql using following commands

restart mysql service

1
service mysql restart

If after you set my.cnf file, you cannot restart mysql, Just run these sql at your mysql console:

1
2
set global general_log = 'on';
set global general_log_file = "/var/log/mysql/mysql.log";

If you want show them on terminal real time, Just use tail command. Like this :

run in sudo command.

1
2
$ cd /var/log/mysql
$ sudo tail -f mysql.log

if you want stop real time show log when the pid is over, use tail like this:

1
$ sudo tail -f --pid=12345 mysql.log

get pid

1
fuser 9000/tcp

use ccze to make log humanrity

1
$ sudo tail -f mysql.log | ccze