MySQL 5.7 – Server Logs

MySQL can write to various types of logs;

• • Error Log – Records diagnostic messages regarding startup, shutdown and abnormal conditions.
    • Windows – host_name.err or errors can also logged to the event viewer
    • Linux – host_name.err or log-error=file_name option to log files.

• • General Query Log – Records all statements that the server receives from clients. The general query logs can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
    • By default, the general query log is disabled.
    • The global general_log and general_log_file server variables provide runtime control over the general log.
    • Set general_log=0 or OFF to disable the log and 1 or ON to enable it.
    • Set general_log_file=file_name to specify the log file. If a file is already open, it is closed and the new log file is opened. If no file name is given then by default host_name.log is created under data directory.
    • The output is written to any destination specified by –log-output option which can be table, file or None.

• Slow Query Log –  Records of queries that takes a long time to execute.
  • By default long query time is defined as 10 seconds. This can be changed by long_query_time server variable.
  • Enable Log by slow_query_log= 0(OFF) or 1(ON) and slow_query_log_file= file_name system variables.
  • If you specify no name for the slow query log file, the default name is host_name-slow.log under data directory.
  • Can log non-indexed queries with –log-queries-not-using-indexes.
  • Written to log files or tables as slow_log table.
  • mysqldumpslow utility can summarize the log contents.
• Binary Log – Records that modify data and also used for replication.
  • example the server logs update and delete statements. These statements written to binary logs only after they executed as single or in a group.
  • The binary log has two important purposes: For replication and data recovery operations.
  • Turn on binary logs with –log-bin[=base_name] option. if no base_name then default name is value of the pid-file option followed by -bin.
  • Created under datadir setting for the MySQL server.
  • Binary logs rotated when MySQL server is restarted, maximum size reached or flush log SQL command is issued.
  • Old logs are not deleted by default but use the deletion policy example expire_logs_days=7 or purge binary log statements.
  • mysqlbinlog to see data in binary logs.
• Relay Log – Data changes received from a replication master server.
  • The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files. These files are part of replication process.
  • File Names like host_name-relay-bin.nnnn in data directory. nnnn is the sequence number.
• DDL Log ( Meta data) – Metadata operations performed by DDL statements.
  • Example – drop table, alter table etc..
  • Record of meta data written to ddl_log.log in data directory and is a binary file.
  • Currently, ddl_log.log can hold up to 1048573 entries, equivalent 4 GB in size. you need to rename or remove the file to record further DDL statements.

Log files can take up large amount of space so a recommended strategy is to enable the binary and slow query logs initially. But if server is not running as intended then enable general log to debug.  To know more about MySQL server log , please read from Here.

On Windows environment, default installation option has the following settings.

At database Level, you can set various variables like below.

Mandy!!!