Thursday, 13 February 2014

MySQL Logs

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

There are three types of binlog format in which information are stored, they are
                        statement , row & mixed

            to enable in the my.cnf file

            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