MySQL Logs

MySQL is a widely used relational database management system. A MySQL log system manages multiple types of logs, which record activities and events occurring during the MySQL server running. These logs are essential for database management, performance optimization, and troubleshooting. Common MySQL logs include:

Error logs:
- These logs record the problems that occur when a MySQL server is started, running, or stopped, and the errors or warnings that occur during server running.
- They are useful for diagnosing and solving problems.
- They are usually named hostname.err and located in the data folder in the MySQL installation directory. hostname indicates the corresponding MySQL server name.
General query logs:
- These logs record all client connections received by and SQL statements executed in a MySQL server.
- They can be used to analyze the performance and execution of queries.
- General query logging is disabled by default and can be enabled manually.
Slow query logs:
- These logs record the query statements whose execution time exceeds the specified threshold (long_query_time).
- You can use these logs to locate the query statements that take a long time to execute, which is key for optimizing performance.
- This logging function is disabled by default and can be enabled manually.
Binary logs:
- These logs record all modification operations performed on database data, such as UPDATE, INSERT, and DELETE.
- They are mainly used for data backup, restoration, and replication.
- You can configure the log-bin parameter to enable this function.
Relay logs:
- In the MySQL replication architecture, relay logs are used to copy events from binary log files of the primary server to the standby server for execution.
- They facilitate primary/standby replication for MySQL.
Transaction logs:
- For storage engines (such as InnoDB) that support transactions, transaction logs record operations such as starting, submitting, and rolling back transactions.
- These logs are used to improve database consistency and durability.
Undo logs:
- Undo logs are mainly about InnoDB and records the modification operations that ongoing transactions execute on a database.
- They are helpful in rolling back transactions or implementing multi-version concurrency control (MVCC).

Collection Method

Install ICAgent and configure ECS text log ingestion by referring to Installing ICAgent (Intra-Region Hosts) and Ingesting ECS Text Logs to LTS. Then, ICAgent will collect ECS text logs and report them to LTS.

MySQL Log Examples and Fields

This section describes the examples and fields of error logs, general query logs, and slow query logs.

Error log

2023-03-29T14:30:15.123456Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11

**Table 1** Error log fields
Field	Description
2023-03-29T14:30:15.123456Z	Timestamp of the log entry. The value is in the ISO 8601 format and contains the time zone information (Z indicates GMT).
0	Thread ID. For an error log, this value is usually 0, indicating that the log is a global message and is not related to a specific client connection.
[ERROR]	Log level. ERROR indicates an error message.
InnoDB: Unable to lock ./ibdata1, error: 11	Detailed error message. This example indicates that InnoDB cannot lock the ibdata1 file and returns error code 11 (indicating that the resource is temporarily unavailable).

General query log

190109 14:23:00     3 Connect   root@localhost on testdb  
               3 Query     SELECT * FROM users WHERE id = 1

**Table 2** Query log fields
Field	Description
190109 14:23:00	Timestamp of the log entry, in the YYMMDD HH:MM:SS format.
3	Thread ID. 3 indicates that the operation is performed by the client connection whose thread ID is 3.
Connect	Operation type. Connect indicates a client connection.
root@localhost	User name and host name for the connection.
on testdb	Name of the database used for the connection.
Query	Operation type. Query indicates that a SQL query is performed.
SELECT * FROM users WHERE id = 1	Executed SQL statement.

Slow query log

190109 14:23:00     3 Connect   root@localhost on testdb  
               3 Query     SELECT * FROM users WHERE id = 1

**Table 3** Slow query log fields
Field	Description	Example
Time	Timestamp of the log entry.	2023-03-29T15:00:01.234567Z
User@Host	Information about the user and host that perform the query.	root[root] @ localhost [127.0.0.1]
Id	Thread ID.	3
Query_time	Query execution duration, in seconds.	5.000234
Lock_time	Duration, in seconds, to wait for a table lock in the query.	0.000123
Rows_sent	Number of lines returned by the query.	1000
Rows_examined	Number of lines checked in the query (that is, the number of lines scanned, possibly including lines that are not returned).	100000
SET timestamp=	Timestamp of the current session (usually for the consistency of replication logs).	1617012001
SELECT ...	Executed SQL query.	SELECT * FROM big_table WHERE some_column = 'value'

Log Location

The common location of an error log is /var/log/mysql/error.log. You can determine the exact location of the error log by checking the log-error parameter in the MySQL configuration file (usually /etc/my.cnf, /etc/mysql/my.cnf, or ~/.my.cnf).
If general query logging has been enabled, the location of a general query log is specified by the general_log_file parameter in the configuration file. The default location is /var/log/mysql/general.log.
If slow query logging has been enabled, the location of a slow query log is specified by the slow_query_log_file parameter in the configuration file. The default location is /var/log/mysql/mysql-slow.log.
If binary logging has been enabled, the location of a binary log is specified by the log-bin parameter in the configuration file. The location is usually /var/lib/mysql/.