MySQL Basics (eight) MySQL log

MySQL log type:     1, query log (usually not turned on, not recommended)     2, slow query log (recommended)      3, error log (recommended)     4, binary log (recommended)     5, relay log     6 , transaction log (Help transactional storage engine complete acid test) mysql> show global variables like  '%log% '; #查看和日志相关的变量      Query log:     general_log=on| off# whether to open the query log general_log_file=path# query log file path log_output=file| table| none# output mode, default is file     slow query log: Execution time exceeds the specified length of the query operation Mysql> show global variables  LIKE  ' Long_query_time '; #查看超时时长服务器变量:     slow_query_log=on| off# whether to turn on slow query log     log-slow-queries=PATH   #慢查询日志存储路径, default to hostname-slow.log     log_output=file| table# output mode, default to filemysqldumpslow# slow query log analysis tool      error log: 1. Log the event information generated during MySQL startup and shutdown 2, Log error messages generated during MySQL Operation 3. In the master-slave architecture, start the information Server variable generated from the server thread from the server:     log_error=off| on| path       #启动错误日志, if you do not specify an error log location will use the default location     log_warnings=0|1         #是否记录警告信息         binary logs: SQL statements that record data changes or that can cause data to change          features: Used to implement a copy of the data through events in the replay log file     mysql>  SHOW MASTER LOGS; #查看可使用的二进制日志文件列表     mysql> SHOW MASTER  STATUS, #查看正在使用的二进制日志文件     mysql> SHOW BINLOG EVENTS; #查看二进制日志文件内容       Server variables:       sql_log_bin=off| on# whether to open binary log       log_bin=off| on# If the binary log is turned on, it must be enabled to take effect       binlog_format=statement| row| mixed# binary log format       max_binlog_size= #单个二进制文件最大大小              1, up to maximum hours auto-scrolling   2, auto-scrolling after restart        sync_binlog=0|1#Whether to write synchronously, the default is to write     1 asynchronously, synchronous writes can affect server performance   2, asynchronous writes can result in data loss      Binary file Format:     statement-based logging:statement     benefits: Data recorded in the same volume more       Cons: There is no guarantee that the playback data and source data are exactly the same         such as:mysql> insert into  Test value (1,now ()); #每一次获取的系统时间都不相同基于行 (data) record:row     advantages: can ensure that the playback of data and source data are basically the same       Cons: Less data recorded in the same volume          mixed mode: MIXED, the system determines that the storage is based on that way   Composition of the     binaries:         log file: mysql-bin.00000x# binary file           index File: mysql-bin.index# Save the list of currently available binary log files      Format of binary log events: mysqlbinlog# binary file Analysis tool      usage format: mysqlbinlog [--start-position= event start position]  Binary log files      example:mysqlbinlog mysql-bin.000003#at 655#170220  8:33:37   server  id 1&nbSp; end_log_pos 731  query  thread_id=12  exec_time=0  error _code=0set timestamp=1487550817/*!*/; begin/*!*/; start position of event: at 655 event occurred: 170220  8:33:37server id 1:server id, When you do a MySQL cluster, you must ensure that the Server id global unique event ends: The type of the end_log_pos 731 event: the thread on which the server executes this event when the query event occurs id:thread_id= 12 statement execution and writing it to a time difference error code in a binary log:error_code     relay log: Master-slave replication from the service log, primarily for saving read events from binary log files       transaction log: Self-managing and using      Cleanup log:purge binary logs to  by transactional storage ' mysql-bin.010 '; #清除指定日志之前的二进制日志PURGE  BINARY LOGS BEFORE  ' 2008-04-02 22:46:26 ';    #清除指定时间之前的二进制日志

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1933882

MySQL Basics (eight) MySQL log