mysql查看日志,如何在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

解决方案

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.

Click here to visit the blog