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
shell> mysqladmin flush-logs
shell> mv host_name.err-old backup-directory
In my (I have LAMP installed) /etc/mysql/my.cnf file I found following, commented lines in [mysqld] section:
general_log_file = /var/log/mysql/mysql.log
general_log = 1
I had to open this file as superuser, with terminal:
sudo geany /etc/mysql/my.cnf
(I prefer to use Geany instead of gedit or VI, it doesn't matter)
I just uncommented them & save the file then restart MySQL with
sudo service MySQL restart
Run several queries, open the above file (/var/log/mysql/mysql.log) and the log was there :)
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.
In addition to the answers above you can pass in command line parameters to the mysqld process for logging options instead of manually editing your conf file. For example, to enable general logging and specifiy a file:
mysqld --general-log --general-log-file=/var/log/mysql.general.log
Confirming other answers above, mysqld --help --verbose
gives you the values from the conf file (so running with command line options general-log is FALSE); whereas mysql -se "SHOW VARIABLES" | grep -e log_error -e general_log
gives:
general_log ON
general_log_file /var/log/mysql.general.log
Use slightly more compact syntax for the error log:
mysqld --general-log --general-log-file=/var/log/mysql.general.log --log-error=/var/log/mysql.error.log
You have to activate the query logging in mysql.
edit /etc/my.cnf
[mysqld] log=/tmp/mysql.log
restart the computer or the mysqld service
service mysqld restart
open phpmyadmin/any application that uses mysql/mysql console and run a query
cat /tmp/mysql.log
( you should see the query )
To complement loyola's answer it is worth mentioning that as of MySQL 5.1 log_slow_queries
is deprecated and is replaced with slow-query-log
Using log_slow_queries
will cause your service mysql restart
or service mysql start
to fail
From the MySQL reference manual:
By default, all log files are created in the data directory.
Check /var/lib/mysql
folder.
Source: Stackoverflow.com