Go MySQL Log management

There are six types of logs on the MySQL server: error log, query log, slow query log, binary log, transaction log, and relay log.

Original: 1.,190,000,003,072,24e,+16 error log

The error log does not only log error messages, it logs events that are:
-Information during server startup and shutdown
-Error message during server run
-Information generated when the event Scheduler runs an event
-(if configured to start from the server) the information generated from the server process

To view the path to the error log file

In the MySQL database, the error logging feature is turned on by default.

mysql> SHOW VARIABLES LIKE ‘log_error%‘;+---------------+-----------------------------------------------+| Variable_name | Value                                         |+---------------+-----------------------------------------------+| log_error     | /opt/lampstack-5.4.22-0/mysql/data/mysqld.log |+---------------+-----------------------------------------------+

Configuration error Log

Edit my.cnf (typically in MySQL directory), modify the Log-error parameter, if not, add:
- error log file configuration

# Error Logging.log-error="filename.log"

• Description of related configuration variables
  log_error={1 | 0 | /PATH/TO/ERROR_LOG_FILENAME}
  Defines the error log file. The scope is global or session level and can be used for configuration files, which are non-dynamic variables.
  log_warnings = {1|0}
  Determines whether warning messages are logged in the error log.

Two query logs

Where the query log records the query operation, by default the query log is closed. 开启查询日志会增加很多磁盘 I/O， 所以如非出于调试目的，不建议开启查询日志.

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

mysql> SHOW VARIABLES LIKE ‘general_log%‘;+------------------+--------------------------------------------------+| Variable_name    | Value                                            |+------------------+--------------------------------------------------+| general_log      | OFF                                              || general_log_file | /opt/lampstack-5.4.22-0/mysql/data/localhost.log |+------------------+--------------------------------------------------+

Configure the query log

Edit My.cnf, 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="filename.log"

Save the MY.CNF changes and restart the MySQL service.

Three-Slow query log

A slow query is a query that executes a length of time (including waiting for Cpu/io) over the length of the variable defined by Long_query_time.慢查询日志开销比较小，可以用于定位性能问题，建议开启。

To see if 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 | /usr/local/var/mysql/upstreamdeMac-mini-slow.log |+---------------------+--------------------------------------------------+

Configure Slow query log

Edit My.cnf, set the log_slow_queries 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=FILElog_slow_queries=1    //MySQL 5.6将此参数修改为了slow_query_logslow_query_log_file="filename.log"long_query_time=10 //慢查的时长单位为秒，可以精确到小数点后6位(微秒)

Save the MY.CNF changes and restart the MySQL service.

Turn off slow query log

Set the Log_slow_queries parameter to 0:

# Slow logging.log-output=NONElog_slow_queries=0   //MySQL 5.6将此参数修改为了slow_query_logslow_query_log_file="filename.log"long_query_time=10

Save the MY.CNF changes and restart the MySQL service.

42 Binary Log

Binary Logging records all update-related operations in the MySQL database, that is, the binary log records all DDL (data definition Language) statements and DML (data manipulation language) statements, but does not include data query statements. Often used to recover databases and master-slave replication.

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 the binary logging feature

Edit my.cnf, add under [mysqld]

# Binary Logging.log-bin="filename-bin"

Save the MY.CNF changes and restart the MySQL service.

Other related configurations:

max_binlog_size={4096 .. 1073741824} ;
Sets the upper limit of the binary log file in bytes, the minimum value is 4K, the maximum value is 1G, and the default is 1G. The log information generated by an office can only be written to a binary log file, so the actual binary log file may be larger than the specified upper limit. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

viewing log files

In the data directory, a mysql-bin.index is an index file, and a binary log file that begins with a mysql-bin and ends with a number.
To view all binaries:

mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |    276665 |+------------------+-----------+1 row in set (0.03 sec)

To view the binary files currently in use:

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 |      107 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

Binary Log scrolling

When the MySQL service process starts, the current binary log file size exceeds the limit, and the FLUSH log is executed, MySQL creates a new binary log file. The new number 1 log is used to record the most recent log, and the original log name is not changed.

Manual scrolling Command:flush logs;

View Log Details

There are several ways to view Binlog logs:
1. Use the show Binlog events method to get the current and specified Binlog logs.
2. Use the Mysqlbinlog command line.

1. Show Binlog Events Mode

• View only the contents of the first Binlog file (show Binlog events)

mysql> show binlog events; #默认会返回mysql-bin.000001的日志#太多，我就不截屏了

• View the contents of the specified Binlog file (show Binlog events in ' binname.xxxxx ')

show binlog events in ‘mysql-bin.000002‘;

• Gets the contents of the specified location Binlog (show Binlog events from XXX)

in ‘mysql-bin.000002‘  from 107;

2. Mysqlbinlog command Line

Mysqlbinlog in the Mysql/bin directory

I View Binlog Log

• View the specified Binlog log

[root@localhost bin]# ./mysqlbinlog ../data/mysql-bin.000003

• View binary logs by time
  Mysqlbinlog. /data/mysql-bin.000003--start-datetime= "2015-3-11 17:00:00"
  Mysqlbinlog. /data/mysql-bin.000003--stop-datetime= "2015-3-12 17:30:00"
  Mysqlbinlog. /data/mysql-bin.000003--start-datetime= "2015-3-11 17:00:00"--stop-datetime= "2015-3-12 17:30:00"

• View binary logs in bytes
  Mysqlbinlog. /data/mysql-bin.000003--start-position=20
  Mysqlbinlog. /data/mysql-bin.000003--stop-position=200
  Mysqlbinlog. /data/mysql-bin.000003--start-position=20--stop-position=200

• Filter INSERT, UPDATE operation
  Mysqlbinlog. /data/mysql-bin.000003 | grep insert

II view Binlog Logs and output

The following reference: Extract binary logs using Mysqlbinlog

c、提取指定position位置的binlog日志并输出到压缩文件  # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz  d、提取指定position位置的binlog日志导入数据库  # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p e、提取指定开始时间的binlog并输出到日志文件 # mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql 

 

Go MySQL Log management