Server Logs in
MySQL
MySQL
server has several logs in which we can find what activity is taking place.
They are
1) Binary Log
2) Relay Log
3) Error Log
4) Slow Query Log
5) General Query Log
1)
Binary Log
It contains event that describe
database changes such as table creation operations or changes to table data. It
also contains the statements that might have not made changes.
For replication binary logs on
master server provides a record of the data changes to be sent to slave server.
The binary log is not used for statements SELECT , SHOW.
To enable a binary log edit the
following in my.cnf file and start the server
--log-bin=mysql-bin
There
are three types of binlog format in which information are stored, they are
statement , row &
mixed
to enable in the my.cnf file
binlog-format='type'
Binary logs can be seen by,
mysql>mysqlbinlog
/path_of/file; for statement &
mysql>mysqlbinlog
-base64-output=Decode-rows -v
/path_of/file;
for row & mixed.
2)
Relay Log
It consists of set of files
containing events that describes database changes and an index file that
contains name of all used relay log files.
By default relay log file names have
host_name-relay-bin.nnnnnn where host_name is the name of the slave
server host and nnnnnn is the sequence number (000001).And
relay log index have host_name-relay-bin.index.
3)
Error Log
Contains information indicating when
mysqld was started and stopped and also any crictical errors that occurred
while server running.
When
mysqld notice a table that need to checked o repaired it writes messages to
error log.
Mysqld writes error log messages as
follows,
Without --log-error, writes to
console,
With –log-error=file_name , writes
to file_name,
4)
Slow Query Log
It consists of sql statements that
took more than long-query-time seconds to execute and required atleast
min-examined-row-limit rows to be executed. Default sec for long-query-time is
10 sec
and
default value for min-examined-row-limit
is 0.
mysqld writes a statement to slow
query log after it has been executed & after all the locks have been
released so log order might differ from execution order.
By default slow-query-log is disabled, to
enable or disable,
--slow-query-log = 0 | 1 (0-disable, 1-enable)
Default name is host_name-slow.log
Change
by, --slow-query-log-file = file_name
5)
General Query Log
It is a general record of what
mysqld is doing. Server writes information to this log when clients connects or
disconnects and it logs each sql statement received from client.
By default general query log is
disabled, to enable or disable,
--general-query-log = 0 | 1
Default name is host_name.log
Change
by, --general-log-file = file_name
