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 need to know about the term mysqld
mysqld stands for MySQL daemon which lets you access the database of MySQL server
There are many types MySQL Server Logs we can generate:
The 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 are running and some tables need automatic check and repair process.
General Query Logs:
General Query log contains log regarding each statement run by the client in mysqld. It generally contains records of what mysqld is doing.
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 stores the information regarding the time taken by each event due to which some changes occurred in the database and it also helps to replicate the changes of master server changes into the slave server which executes the same events in same orders to make the same changes in the slave database.
It 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 or metadata logs contains information of metadata 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;
Default Table name in which log data we will get
mysql > select * from mysql.slow_log; mysql > select * from mysql.general_log;