MySQL Log Management

In MySQL, there are 4 different logs, namely error log, binary log, query log, and slow query log.

Error log

The error log records information about when MySQL starts and stops, and when a critical error occurs during the run of the server.

To view the path to the error log file

MySQL>like'log_error%';  +---------------+---------------+| variable_name | Value         |+---------------+---------------+| log_error     |. \huey-pc.err |+---------------+---------------+ 

To modify the path of the error log file

To edit the My.ini, modify the Log-error parameter:

# Error logging.log-error= "Huey-pc.err"

Viewing error log files

The error file is a normal text file and can be viewed directly.

Binary log

The binary log records all DDL (data definition Language) statements and DML (data manipulation language) statements, but does not include data query statements.

View Log_bin Status

MySQL>like'log_bin%';  +---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| Log_bin                         | OFF   | | log_bin_basename                |       | | log_bin_index | | | |       log_bin_trust_function_creators | OFF   | | log_bin_use_v1_row_events       | OFF   |+---------------------------------+-------+

Enable the binary logging feature

Edit My.ini:

# Binary logging.log-bin= "Huey-pc-bin"

Save the My.ini changes and restart the MySQL service.

viewing binary logs

Use the Mysqlbinlog tool to view the contents of the binary log:

D:\ProgramData\MySQL\MySQL Server 5.6\data>mysqlbinlog huey-pc-bin.000001

Purge binary logs

Clear all logs (no master-slave replication relationships exist):

MySQL>RESET MASTER;

Clears all logs before the specified log:

MySQL>to'huey-pc-bin.000005';

Clear all logs before a point in time:

MySQL>'2015-01-01 00:00:00';

Clear all logs before n days:

MySQL>current_date-tenday;

Expire_logs_days parameters

Configuring the Expire_logs_days parameter in My.ini specifies the number of days that a binary log is valid, and MySQL automatically deletes the outdated binary log. The Expire_logs_days setting takes effect when the server starts or when MySQL switches the binary log, so if the binary logs are not growing and switching, the server does not clear the old entries.

# valid days for binary logs expire_logs_days = 5

Query log

The query log is turned off by default. The query log records all the operations of the client, and the binary log does not record statements that only query the data. It is often not recommended to enable query logging when the concurrency environment is prone to large amounts of disk I/O, resulting in large volumes of log information, which affects MySQL performance.

To see if the query log is enabled and the path to the query log

MySQL>like'general_log%';  +------------------+-------------+| Variable_name    | Value       |+------------------+-------------+| general_log      | On          | | general_log_file | Huey-pc.log |+------------------+-------------+

Enable the query log and set the path to the query log

Edit My.ini, modify the General-log parameter to 1, set the Log-output parameter (log output type) and General_log_file parameter (query log path):

# General logging.:log-output=filegeneral-log=1general_log_file= "Huey-pc.log"

Save the My.ini changes and restart the MySQL service.

Close query Log

Edit My.ini, set the General-log parameter to 0:

# General logging.log-output=nonegeneral-log=0general_log_file= "Huey-pc.log"

Save the My.ini changes and restart the MySQL service.

Slow query log

The slow query log records All SQL statements that have executed longer than long_query_time seconds.

To see if the slow query log is enabled and the path of the slow query log

MySQL>like'slow_query_log%';  +---------------------+------------------+| Variable_name       | Value            |+---------------------+------------------+| slow_query_log      | OFF              | | slow_query_log_file | Huey-pc-slow.log |+---------------------+------------------+

Enable slow query log and set path for slow query log

Edit My.ini, set the Slow-query-log parameter to 1, set the Log-output parameter (log output type), slow-query-log_file parameter (slow query log path), and Long_query_time parameters:

# Slow logging.log-output=fileslow-query-log=1slow_query_log_file= "Huey-pc-slow.log" long_query_time=10

Save the My.ini changes and restart the MySQL service.

Turn off slow query log

Edit My.ini, set the Slow-query-log parameter to 0:

# Slow logging.log-output=noneslow-query-log=0slow_query_log_file= "Huey-pc-slow.log" long_query_time=10

MySQL Log Management