MySQL Log management

One: part of the log

1.1 error Log

1.2 General Query Log

1.3 Slow Query Log

1.4 Binary Log

1.5 Relay Log

1.6 transaction Log

Two: Storage Engine introduction

2.1 MyISAM

2.2 InnoDB

2.3 Mrg_myisam

2.4 CSV

2.5 ARCHIVE
2.6 MEMORY

2.7 Blackhole

1.1 error log:

Mainly used to record error messages in server operation

information generated during server startup or shutdown

information generated when the server process is started from the server

Information generated when the event Scheduler runs an event

warning Message

1.2 General Query log:

Any query statement generated by the log information to be recorded, so the information is relatively large, so the general query log is not open by default

1.3 Slow query log:

The General query log and slow query log are query logs, but slow query logging is a long query time, beyond the length of the definition of long_query_time, the default is ten seconds, slow query, you can start the slow query log, To record which queries are performing slowly, to help us locate the problem, to locate the server performance problem,

However, the slow execution of queries does not mean that the query itself is slow, possibly because other resource consumption causes it to not be able to obtain a valid resource and cannot execute the query, the query time evaluation of the slow query is based on it starting from the start of the query, the query ends this time, not in the time to execute on CPU

1.4 Binary Log

1.4.1 two every piece of information or operation recorded in the log, we call it a binary log

1.4.2 Key Features of the binary log

1. Copying

2. Instant Restore

1.4.3 What is the binary log event based on?

1. can be based on statement:statement

2. can also be based on row:row

3. Mixed Mode (statement-based and Row-based):mixed

1.4.4 based on mercy? Or is it a statement-based good?

Statement based: A statement may manipulate a large amount of data, but may result in imprecise values, such as operations with the current time

based on line: Copy the result of the statement directly, copying the relevant data instead of the statement to produce the exact value

Mixing mode:mixed

1.4.5 where binary logs are stored

The binary log itself will scroll, the default is stored in the data directory, but it is recommended that the data directory should not be placed on the same storage device, because each time the storage device IO operations (that is, the write operation of each data), will cause the binary log write operation, so both will produce IO, that means, put on the same disk, two IO will generate competition, so this itself will bring low performance situation, Therefore, it is recommended not to store on the same disk,

And in order to avoid the data file is the device corruption, also cause the binary log file corruption, so separate storage

(Summary: Two reasons:1 performance.) 2: Data reliability, security)

1.4.6 View binary logs identified by the server on the current system

mysql> SHOW BINARY logs;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/EE/wKioL1VXecrTIsBSAAFWCrRqtW0032.jpg "title=" 1.png " alt= "Wkiol1vxecrtisbsaafwcrrqtw0032.jpg"/>

1.4.7 Binary log currently in use

mysql> SHOW MASTER STATUS;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/F4/wKiom1VXeOWCg3e0AACym968lWY507.jpg "title=" 2.png " alt= "Wkiom1vxeowcg3e0aacym968lwy507.jpg"/>

1.4.8 to view events in a log

syntax:mysql>show BINLOG EVENTS in ' binary file name ' [fromposition];

Mysql> SHOW BINLOG EVENTS in ' mysql-bin.000006 ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/EE/wKioL1VXerjh0sVSAAENxe8HJvk953.jpg "title=" 3.png " alt= "Wkiol1vxerjh0svsaaenxe8hjvk953.jpg"/>

Mysql> SHOW BINLOG EVENTS in ' mysql-bin.000006 ' from 379;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/EE/wKioL1VXeu6TrtBvAADJEOeN6lE081.jpg "title=" 4.png " alt= "Wkiol1vxeu6trtbvaadjeoen6le081.jpg"/>

1.4.9 After the backup, you need to clear the log file before a log file

Mysql> PURGE BINARY LOGS to ' mysql-bin.000006 ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/F4/wKiom1VXeazwNF8pAADac-BCMQM817.jpg "title=" 5.png " alt= "Wkiom1vxeazwnf8paadac-bcmqm817.jpg"/>

1.4.10 manually Scroll through the binary log

Mysql> FLUSH LOGS;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/EE/wKioL1VXe2GTQjZkAADUihvXtVQ947.jpg "title=" 6.png " alt= "Wkiol1vxe2gtqjzkaaduihvxtvq947.jpg"/>

1.4.11 View the contents of the database Output binary log file in detail

Mysqlbinlog--start-position--stop-position--start-datetime ' yyyy-mm-dd hh:mm:ss '--stop-datetime ' yyyy-MM- DD Hh:mm:ss '

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/EE/wKioL1VXe_Ciin02AAGOa06g6zk166.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1vxe_ciin02aagoa06g6zk166.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/F4/wKiom1VXenbDK80rAADNWCfnT0I166.jpg "style=" float: none; "title=" 8.png "alt=" Wkiom1vxenbdk80raadnwcfnt0i166.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/EE/wKioL1VXe_Hj0pQMAAFOtCgS4j8976.jpg "style=" float: none; "title=" 9.png "alt=" Wkiol1vxe_hj0pqmaafotcgs4j8976.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/F4/wKiom1VXenfwDSuHAABVz4kS1_Y076.jpg "style=" float: none; "title=" 10.png "alt=" Wkiom1vxenfwdsuhaabvz4ks1_y076.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/EE/wKioL1VXfAawp2I4AAFmp7PSR5g982.jpg "title=" 11.png "alt=" Wkiol1vxfaawp2i4aafmp7psr5g982.jpg "/>

1.4.12 and log description of the relevant server variable

Mysql> SHOW GLOBAL VARIABLES like '%log% ';

Binlog_format: Binary log format

Binlog_cache_size: The binary log cache size, the upper limit of its value depends on the binlog_stmt_cache_size

Binlog_stmt_cache_size: Transaction-related binary log statement cache size

Log_bin: Whether binary logging is enabled, so the cache size (binlog_stmt_cache_size) abovedepends on this directive

Note that the binary log function is turned on, does not mean that it will certainly record the binary log, it is the total switch, is to record the binary log, if you want to record, it may give us the specified binary log file in the log. This log_bin It can also accept a file path, indicating that the binary log file to another location, or write only on it is recorded in the MySQL data directory, called mysql-bin0001 0002, which specifies whether the binary is enabled, in which file it is recorded, and whether the log is actually logged depends on the other specified Sql_log_bin

Sql_log_bin: is used to control whether the binary log file is logged, and log_bin is the output path of the specified binary file, if you set its value to off, even if you write a lot of DML or DDL Statement, and it does not log related information. This directive is very important, especially in the future when the database recovery, for example,the MySQL server crashes, I made a logical backup, I need to import the backup file into the database, restore the data to a certain moment in the process of recovery, The function of the binary log should be turned off, and when the restore is complete, turn on

Sync_binlog: How long to synchronize a binary log

Expire_logs_days: The log expires in days, for example, the value of the log file is automatically deleted after more than a year, so the binary log can be automatically cleaned up

1.4.13 Why do binary logs have to be cached?

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/F4/wKiom1VXfMvi1QBdAAOCIJuUP-I703.jpg "style=" float: none; "title=" 12.png "alt=" Wkiom1vxfmvi1qbdaaocijuup-i703.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/EE/wKioL1VXfkWivMIwAAHXoIMiJMc052.jpg "style=" float: none; "title=" 13.png "alt=" Wkiol1vxfkwivmiwaahxoimijmc052.jpg "/>

1.5 Relay Log

From the server, copy the event from the binary log file of the primary server and save it as a log file

1.6 transaction log:ACID the random IO Convert to sequential IO

Transactional storage engine to ensure atomicity, consistency, isolation, and durability

Innodb_flush_log_at_ trx_commit={0|1|2}

0: No seconds sync, and disk flush operation is performed

1: synchronize each transaction and perform a disk flush operation

2: synchronize per transaction, but do not perform disk flush operations

Innodb_log_buf fer_size: Memory Cache size

Innodb_log_fi le_size: Log file size

Innodb_log_files_in_group : The log is full, you need to write to another file, these files constitute a transaction log group, where the value is 2 , there are two log files

Innodb_log_group_home_dir: The location of the transaction log, the default is "./", this current directory refers to the data directory

innodb_mirrored_log_groups: Whether to mirror the log group

2. Storage Engine Introduction

2.1MyISAM :mysql5.5.8 before the default storage engine

Data storage format:

table format . frm

data Files . MYD

index file . MYI

Features:

1. does not support transactions, so there is no transaction-based characteristics of ACID, so there is a lot less overhead so the performance is good, but not necessarily good performance, that is, the transactional storage engine may not be poor performance

2. Because it does not support transactions, many of its operations are coarse, for example, foreign keys are not supported.

3.MyISAM By default, only table locks are supported, the lock granularity is large, resulting in a non-strong concurrency capability. The lock itself is shared, such as read lock, so in the read operation very much scene,MyISAM has very good performance and for read and write operations are as many times,MyISAM performance is poor

4. Support index Type comparison,B- tree Index, full-text (fulltext) index, spatial index, table compression

2.2InnoDB :

table format . frm

tablespace . IBD By default , MySQL puts all the transaction tables into a table space, and we want to use many of the advanced features of InnoDB, such as table compression, single table import and export, backup For example, a table space per table

Transactional engine

row-level locks

Support B -Tree index, clustered index, adaptive Hash index

using table spaces, support for raw disk devices, which can place data on disk partitions without a file system

2.3mrg_myisam :

ability to combine two or more than two MyISAM tables are merged into a single table processing

2.4CSV : use text files to store data for portability, but not managed for poor performance

2.5ARCHIVE : The main purpose of the archive is to compress the data in the table for data mining purposes only .

2.6MEMORY : memory storage engine, which puts all the data in the table in memory, performance is particularly good, but the data security is not guaranteed

2.6BLACKHOLE : Black hole storage Engine

This article is from the "Everyman" blog, please make sure to keep this source http://caoyt.blog.51cto.com/9978141/1652007

MySQL Log management