Checking MySQL Logs

Types of MySQL Logs

General Log

• Records all queries executed in MySQL.

---

Slow Query Log

• Logs queries that take longer to execute than the time specified in the long_query_time parameter.

---

Error Log

• Records issues that occur during the start, operation, and shutdown of mysqld.

---

Binary Log / Relay Log

• Logs modified data or executed SQL statements.
• Records only DML queries (specifically, INSERT, UPDATE, DELETE).
• The contents of the Binary Log and Relay Log are identical:
  • Binary Log: Stored on the master server.
  • Relay Log: Stored on the slave server.

 

---

 

General Log

Overview

• A log of all general queries.

Check General Log Variables

SHOW VARIABLES LIKE "general_log%";

By default, MySQL disables logging.

 

To enable real-time logging, set the general_log variable to ON and specify the log file path:

 

 

mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log_file='/var/lib/mysql/localhost.log';

Open a new terminal, navigate to the log file path, and use tail -f to monitor logs in real time:

 

tail -f /var/lib/mysql/localhost.log

Slow Query Log
Check the current status and storage location of the slow query log:

 

 

SHOW VARIABLES LIKE '%slow_query%';

Check the configured time threshold for slow queries. In the example below, queries taking longer than 10 seconds are logged:

 

SHOW VARIABLES LIKE '%long_query%';

Update Time Threshold
To modify the threshold, use the following command:

 

SET GLOBAL long_query_time = 5;
Logged File Information
Time: The time the query ended (subtract Query_time to calculate the start time).
User@Host: The user account that executed the query.
Query_time: Total execution time of the query.
Rows_examined: The number of records accessed by the query.
Rows_sent: The number of records actually returned by the query.

Error Log

 

Check the current status and storage location of the error log:

 

SHOW VARIABLES LIKE '%log_error%';

 

Common Error Messages

• Informational and error messages related to MySQL startup.
• Transaction recovery messages from InnoDB during abnormal shutdowns.
• Issues encountered during query processing.
• Abnormal connection termination messages.
• Monitoring or status-checking results from InnoDB.
• MySQL shutdown messages.

 

Configure Log Output
Save logs to a table:

SET GLOBAL log_output = 'TABLE';

Save logs to a file:

 

SET GLOBAL log_output = 'FILE';