Know Your MYSQL: Server Logs

MySQL has several types of logs that can help you to find out what kind of activity took place in your MySQL.

To understand this you will to need to know about the term mysqld

mysqld stands for mysql daemon which lets you to access database of MYSQL server.

Types of logs are: 

Error Logs:  Error log contains records of startup and shutdown times of mysqld (mySQL daemon). It contains errors, warnings and notices that occur while mysqld start or shutdown and while server is running and some tables need automatic check and repair process.

General Query Logs: General Query log contains log regarding each statement run by client in mysqld. It generally contains records of what mysqld is doing.

Binary Logs: Binary logs contain information regarding the events happened in the database like if any table is created, rows are inserted or updated, rows are deleted etc. It also stores the information regarding the time taken by each events due to which some changes occurred into the database. It also helps to replicate the changes of master server changes into the slave server which execute the same events in same orders to make the same changes in the slave database.

It also helps to recover the events after the last backup of the database.

Slow Query Logs: Slow query logs contain information about the query which takes more than long_query_time (minimum value is 0 and default value is 10) seconds to execute, By default it is disabled and its value is 0 and to enable it set its value to 1.

mysql > SET GLOBAL slow_query_log = ‘ON’; (to enable slow query log)

mysql > SET GLOBAL slow_query_log = ‘OFF’; (to disable slow query log)

mysql > SET GLOBAL long_query_time = ‘20’; (to increase long query time)

DDL Logs: DDL logs or metadata logs contains information of meta data of data definition statements like alter, drop etc. This log is used by mySQL itself to recover from crashes if occurred in the middle of execution of DDL statements.

How to enable General Error Logs and Slow Query Logs in table:-

To start logging in table instead of file

mysql > set global log_output = “TABLE”;

To enable general and slow query log
mysql > set global general_log = 1;
mysql > set global slow_query_log = 1;

Table name in which logging is done by default

mysql > select * from mysql.slow_log;
mysql > select * from mysql.general_log;