MySQL Log Management and mysql Log Management

MySQL Log Management and mysql Log Management

In MySQL, there are four different types of logs: error logs, binary logs, query logs, and slow query logs.

 

Error Log

The error log records information related to MySQL startup and shutdown, and serious errors during server running.

View the path of the Error Log File

mysql> SHOW VARIABLES LIKE 'log_error%';+---------------+---------------+| Variable_name | Value         |+---------------+---------------+| log_error     | .\HUEY-PC.err |+---------------+---------------+

Modify the path of the Error Log File

Edit my. ini and modify the log-error parameter:

# Error Logging.log-error="HUEY-PC.err"

View Error Log Files

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

 

Binary log

Binary logs record all DDL (Data Definition Language) Statements and DML (data manipulation language) statements, but do not include data query statements.

View log_bin status

mysql> SHOW VARIABLES 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 binary log

Edit my. ini:

# Binary Logging.log-bin="HUEY-PC-bin"

Save my. ini changes and restart MySQL.

View binary logs

Use mysqlbinlog to view the binary log Content:

D:\ProgramData\MySQL\MySQL Server 5.6\data>mysqlbinlog HUEY-PC-bin.000001

Clear binary logs

Clear all logs (no master-slave replication relationship exists ):

mysql> RESET MASTER;

Clear all logs before the specified log:

mysql> PURGE MASTER LOGS TO 'HUEY-PC-bin.000005';

Clear all logs before a time point:

mysql> PURGE MASTER LOGS BEFORE '2015-01-01 00:00:00';

Clear all logs n days ago:

mysql> PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL 10 DAY;

Expire_logs_days Parameter

In my. ini, configure the expire_logs_days parameter to specify the valid days of binary logs. MySQL automatically deletes expired binary logs. The expire_logs_days setting takes effect when the server is started or when MySQL switches binary logs. Therefore, if binary logs are not increased or switched, the server does not clear old entries.

# Effective days of binary logs expire_logs_days = 5

 

Query logs

By default, log query is disabled. The query log records all client operations, while the binary log does not record statements that only query data. In a concurrent environment, a large amount of log information is easily generated, resulting in a large number of disk I/O, which affects MySQL performance. Generally, it is not recommended to enable query logs.

Check whether the log query is enabled and the log query path

mysql> SHOW VARIABLES LIKE 'general_log%';+------------------+-------------+| Variable_name    | Value       |+------------------+-------------+| general_log      | ON          || general_log_file | HUEY-PC.log |+------------------+-------------+

Enable log query and set the log query path

Edit my. ini, set the general-log parameter to 1, and 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 my. ini changes and restart MySQL.

Disable log query

Edit my. ini and set the general-log parameter to 0:

# General logging.log-output=NONEgeneral-log=0general_log_file="HUEY-PC.log"

Save my. ini changes and restart MySQL.

 

Slow query log

The slow query log records all SQL statements whose execution time exceeds long_query_time.

Check whether the slow query log is enabled and the path of the slow query log

mysql> SHOW VARIABLES 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 the path of slow query log

Edit my. ini, set the slow-query-log parameter to 1, and set the log-output parameter (log output type), slow-query-log_file parameter (slow query log Path), and long_query_time parameter:

# Slow logging.log-output=FILEslow-query-log=1slow_query_log_file="HUEY-PC-slow.log"long_query_time=10

Save my. ini changes and restart MySQL.

Disable slow query logs

Edit my. ini and 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