MySQL Log system

Log Categories of MySQL logs:

• Server logs
• • Records special events during the process startup process, helping to analyze problems that the MySQL service encounters.
  • Capture specific SQL statements as needed to track business SQL that may have problems with performance.
• Transaction log--record content does not log status
• • Record all changes to data by the application
  • Can be used for data recovery
  • Can be used for data synchronization between instances

Server logs:

1. Service error Log--log_error
2. Slow query log
3. Comprehensive query Log

Transaction log:

1. Storage Engine transaction Log
2. Binary log

Server error log:

• Record an important message when the instance starts running
• Configuration parameters
• • Log_error=/data/mysql_data/node-1/mysqld.log
• The content is not all error messages
• If the mysqld process does not start correctly, first look at the error log

MySQL>Show global variables like"Log_error";+---------------+--------------------------+|Variable_name|Value|+---------------+--------------------------+|Log_error| /var/Log/Mysql/Error.Log |+---------------+--------------------------+1Rowinch Set(0.00sec) # #tail-F error.LogReal-time monitoring of this file;

Tail-f Error.log Real-time monitoring of this file; slow query log

• Record SQL statements with execution times exceeding a certain threshold
• Configuration parameters
• • Slow_query_log =1-----Open
  • Slow_query_log_file =/data/mysql_data/node-1/mysql-slow.log--Position
  • Long_query_time=5-----Threshold Time
• SQL to analyze possible performance issues in the system

#查看慢日志是否打开以及日志路径

Mysql>Show global variables like '%slow%';+---------------------+----------------------------------+|Variable_name|Value|+---------------------+----------------------------------+|Log_slow_queries| OFF                              ||Slow_launch_time| 2                                ||Slow_query_log| OFF                              ||Slow_query_log_file| /var/Lib/Mysql/itcast01-Slow.Log |+---------------------+----------------------------------+4Rowsinch Set(0.00Sec

#打开慢查询日志

Mysql> SetGlobal Slow_query_log=1; Query OK,0Rows Affected (0.00sec) MySQL>Show global variables like '%slow%';+---------------------+----------------------------------+|Variable_name|Value|+---------------------+----------------------------------+|Log_slow_queries|  on                               ||Slow_launch_time| 2                                ||Slow_query_log|  on                               ||Slow_query_log_file| /var/Lib/Mysql/itcast01-Slow.Log |+---------------------+----------------------------------+4Rowsinch Set(0.00Sec

#查看慢查询日志的阈值

Mysql>Show global variables like 'Long_query_time';+-----------------+-----------+|Variable_name|Value|+-----------------+-----------+|Long_query_time| 10.000000 |+-----------------+-----------+

Set the threshold, usually 5 seconds;

Mysql> SetGlobal Long_query_time= 1; Query OK,0Rows Affected (0.00sec) MySQL>Show global variables like 'Long_query_time';+-----------------+----------+|Variable_name|Value|+-----------------+----------+|Long_query_time| 1.000000 |+-----------------+----------+1Rowinch Set(0.00Sec

Comprehensive query Log

• If on, all SQL statements in the system will be logged
• Configuration parameters
• • General_log = 1
  • General_log_file =/data/mysql_data/node-1/mysql-gen.log
• Occasionally used to help analyze system problems, with performance implications

Mysql>Show global variables like '%general%';+------------------+-----------------------------+|Variable_name|Value|+------------------+-----------------------------+|General_log| OFF                         ||General_log_file| /var/Lib/Mysql/itcast01.Log |+------------------+-----------------------------+2Rowsinch Set(0.00Sec

Output of the query log and file switching

• Log output parameters
• • Log_output = {File|table|none}
• If the log file is too large, you can periodically truncate and switch the new file
• • Flush logs;

Mysql>Show global variables like '%log_output%';+---------------+-------+|Variable_name|Value|+---------------+-------+|Log_output| FILE  |+---------------+-------+1Rowinch Set(0.00Sec

The storage Engine transaction log---Only records data changes;

• Some storage engines have redo logs (redo log)
• such as Innodb,tokudb and other Wal (Write Ahead Log) mechanism storage engine
• The log is persisted with the commit of the transaction, ensuring that the exception recovery does not lose data.
• Better log Sequential write performance

InnoDB transaction log Reuse mechanism

• InnoDB transaction log Alternate reuse with two sets of files

Situation One:

　　

Situation Two:

Therefore, in the frequently written online business, it is recommended to the size of the ib_logfile is bigger.; this facilitates concurrency; binary log Binlog

• Binlog (binary log)
• The contents of SQL statements or data logic changes that record data that causes data changes
• MySQL service layer record, unrelated storage engine
• Binlog's main role:
• • Restore data based on backup
  • Database Master-Slave synchronization
  • Mining Analytic SQL statements

Open Binlog

• Parameters
• • Log_bin = c:/tmp/mylog/mysql-bin (static parameter, set to non 0, is considered binlog open; Set to 1, directory in MySQL data directory)
  • Sql_log_bin = 1 (MySQL select session whether to record Binlog)
  • Sync_binlog = 1 (mysql,binlog persistent mode; 0, do not actively flush to disk, but by the dirty data refresh to brush; 1, each of the active flush to the disk, 100, is the active flush to disk every 100 bar)

Mysql>Show global variables like 'Log_bin';+---------------+-------+|Variable_name|Value|+---------------+-------+|Log_bin| OFF   |+---------------+-------+1Rowinch Set(0.00Sec

Binlog Management

• Main parameters
• • Max_binlog_size = 100MB
  • Expire_logs_days =7---------How many days of Binlog files are automatically saved
• Binlog always generates new files and does not reuse
• Manual Cleaning Binlog
• • Purge binary logs to ' mysql-bin.000009 '
  • Purge binary logs before ' 2015-06-01 22:45:34 '

View Binlog Content

• Logs (log)
• • Show Binlog events in ' mysql-bin.000011 '
  • Show Binlog events in ' mysql-bin.000011 ' from (position) limit 3;
• Mysqlbinlog Tools
• • Mysqlbinlog c:/tmp/mylog/mysql-bin.000001--start-datetime| --stop--datetime--start-position|--stop-position

Binlog format

• Main parameters
• • Binlog_format={row|satement|mixed}
• View Binlog content for row mode
• • Mysqlbinlog--base64-output=decode-rows-v c:/tmp/mylog/mysql-bin.000001

Binaryin'mysql-bin.000001'

Binlog only records changes to the data, but there are limitations: if you use UUID () to produce a different value each time, to insert the data, Binlog, to record the INSERT INTO tab values (UUID ()); Binlog cannot get the original data, because each UUID () produces a different value. So MySQL allows you to record binlog in another format, recording only data changes, and you can set Binlog_format to row; While the Binlog_format is set to Satement, the specific operation statement is recorded, and Binlog_format is set to Mixed, MySQL automatically judge the use of row, or satement; Summary

• Service log:
• • Log_error
  • • Record an important message when the instance starts running.
    • The content is not all error messages,
    • Mysqld cannot start to view the error log first;
  • Slow query log;
  • Comprehensive query Log
  • • All SQL statements in the system are logged,
    • Occasionally used to help analyze system problems and have an impact on performance;
• Output of the log:
• • Log_output=file,table,none;
  • If the log file is too large, you can periodically truncate and switch the new file, flush logs;
• Transaction log:
• • Redo Log
  • • Record data changes only
    • InnoDB transaction log reuse mechanism, Ib_logfile conference is advantageous to write multi-service;
  • Bin Log
  • • Record the contents of the SQL statement or data change that caused the data change,
    • At the MySQL service layer record,
    • Mainly used for:
    • • Restore data based on backup,
      • Database master-Slave synchronization,
      • Mining analytic SQL;
    • Main parameters:
    • • Row only records data changes,
      • Satement records the SQL that caused the data change,
      • Max mixed two kinds;

MySQL Log system