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