Day 15 Server Logs

There are 6 types of logs available for MySQL environment, Logs are important information for DBA to troubleshoot or understand the system. MySQL track the information in those logs.

Following are the list of logs:

Error Log (– start/stop/errors)

Query Log (trace)

Bin Log ( Transaction/Replication)

Slow Log (Slow queries)

Relay log (Replication)

DDL log (metadata log)

 

By default when MySQL Starts except Error Log all other logs are disabled, we have to enable those.

Error Log:

Error log are Basic log which provides information about Mysql Activity during start and during stop of MySQL Processes. It also capture errors while MySQL is running.

 

Query Log:

Query log provides detail statement level information about all the SQL Queries runs on the MySQL server, it will be as is information about queries which is executed on Mysql. As it tracks all the activity and captured the size of the file grows heavily and if this log enabled for long and heavily active system, it will occupy all your system space quickly. So be aware before enabling this log.

This log can be enabled in my.cnf file on [mysqld] group

[mysqld]

Log=1

 

Bin Log:

This log is very important and captures transaction activity on the system. Any DML operations performed on this system is stored in this log in Binary format. Binary format is safe and consumes less space in compare to query log also as it tracks only transactions.

This log is also mandatory to enable replication on the system, Replication works on Bin log transactions, Binlog transferred the transactions from master to slave and those transactions is tracked in Relay log.

This log is also enabled in my.cnf file on [mysqld] group

[mysqld]

log-bin=1

We can also set the format we would like:

SET GLOBAL binlog_format = {Statement/Row/Mixed}

 

Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-based logging format is the default.

 

Relay log:

This log is only be used for replication and as stated on Bin log discussion, Relay log is keeping track of transactions applied at slaves.

 

Slow Query Log:

This is very important log for performance point of view, the events will be captured in this log on either one of the situations:

When Query duration is more than 10 seconds( long_query_time seconds)

When query is not having indexes or no indexes being used on the table (log_queries_not_using_indexes)

Other conditions where Query can be tuned

 

DDL Log:

This log maintains the DDL operations of the system. This DDL is useful for Recovery purpose, during system crash or server down without proper shutdown mysql at the middle of incomplete DDL Operations this logs captured the information and helps the system to recovery it at the stable state. This log stored the data in Binary format only. Generally this log file is empty or does not exist if system starts /shutdown normally. As stated this is used only for recovery time.

The location of this log would be at data directory and name would be ddl_log.log

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/server-administration.html#server-logs

Advertisements
This entry was posted in Error Log, MySQL, Others, Performance Tuning, Transaction Log, Troubleshooting and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s