1.Error Log

• Record the Error, Warning, Note and other information during MySQL operation. If there is a system Error or a record has a problem, you can view the Error log.

MySQL's error log defaults to hostname Err is stored in the MySQL log directory and can be viewed through the following statement

• During default installation, the error log is usually set in the log error configuration item of mysqld in the configuration file (my.cnf); Using MySQL system variable log_error can view the file name of the error log

• You can modify the address of the error log in my Add -- log error = [filename] to CNF to enable mysql error log. The configuration is as follows:

  log_error = /data/mysql_data/error.log
  

2.Slow Log

• MySQL slow query log records SQL statements whose execution time exceeds the specified threshold. It can be used to monitor query statements that need to be optimized.
• If slow query logging is enabled, any execution time exceeds long_query_time and the number of executions reaches min_ examined_ row_ The query statements of limit times will be recorded in the slow query log.

Check whether the log function is enabled: show variables like "%slow%";
quote

slow_query_log Configure as ON ， Description enable slow log

• View the default slow query time: show variables like "%long_query%";

• Check the output of the log file:

For the analysis of slow query logs, mysqldumpslow or PT query digest tools are recommended

3.Relay Log

• MySQL relay log only exists on the slave node in the master-slave replication structure. It is used to save data change events transmitted from the master node, and then apply these events to the slave node.
• Similar to binary logs, relay logs are also composed of a group of numbered files; It also contains an index file that records all used relay log files. Relay log files are stored in the data directory by default.
  

Including relay_log and relay_log_ The index corresponds to the file name and index file name of the relay log respectively. The format of the relay log file is the same as that of the binary log file, so you can also use the mysqlbinlog tool to read it

4.Binlog

• What is binlog

  binlog Records changes in database table structure and table data, such as update/delete/insert/truncate/create，It won't record select
  
  

• What does binlog look like

  binlog We can simply understand it as: storing the information of each change SQL sentence
   By default binlog The log is in binary format and cannot be viewed directly. You can use mysqlbinlog To see, mysqlbinlog yes mysql An official binlog View tools.
  
  

• What is binlog used for

  It has two main functions: replication and recovery of data
  1.MySQL When companies use it, it is often a master-slave structure. The slave server needs to be consistent with the data of the master server, which is through binlog To achieve
  2.The data in the database has been killed. We can binlog To recover the data. because binlog Changes to the database table are recorded, so we can use binlog Copy(Master slave replication)And restore data.
  

5.Redo Log

• What is redo log

update table set name='ws' where id = 3
quote
When MySQL executes this SQL statement, it must first find out the record with id=3, and then change the name field. Is that all right?
quote
In fact, the basic storage structure of MySQL is page (records are stored inside the page), so MySQL first finds the page where the record is located, then loads the page into memory and modifies the corresponding record.

Question 1: what if the data is changed in memory and the disk has not been dropped in time, and the database hangs at this time? Obviously, this change is lost.

• Role of redo log

In fact, when writing redo log, there will also be a buffer. The buffer is written first, and then it really falls into the disk. As for when to drop the disk from the buffer, there will be a configuration for us to configure.
quote
The role of redo log is to write the memory when we modify it, but the data has not been really written to the disk. At this time, our database hangs, and we can recover the data according to the redo log.
quote
Because the redo log is a sequential IO, the writing speed is very fast, and the redo log records physical changes (xxx modification is made on page xxxx). The file size is very small and the recovery speed is very fast.
quote

• When will it be released

  After the dirty page of the corresponding transaction is written to disk, redo log The mission of redo log is completed, and the space occupied by redo log can be reused (overwritten).
  

Redo Log & Binlog

1. Stored content

      redo log It records the physical changes of the data, binlog What is recorded is the logical change of data
   

2. function

      redo log The role of is for persistence. After writing the memory, if the database hangs, we can    redo log To recover the data that has not been brushed to the disk in time,
      binlog The role of is replication and recovery. The master-slave server needs to maintain data consistency through binlog To synchronize data.
   

3. Details of binlog and redo log writing

      redo log yes MySQL of Innodb Generated by the engine.
      binlog whether MySQL You can use any engine.
   

4. Write time

     redo log When the transaction starts, it starts to record each change information
     and binlog It is recorded when the transaction is committed.
   

Question 2: if every request needs to drop the data immediately to the disk, the speed will be very slow, MySQL It may not hold. therefore MySQL How did you do it?

MySQL Introduced redo log，The memory is finished, and then a copy will be written redo log，This redo log It records what changes have been made on a page this time.

6.Undo log

1. undo log It has two main functions: rollback and multi version control(MVCC)

During data modification, not only redo log but also undo log are recorded. If the transaction fails or rolls back for some reason, undo log can be used for rollback
Undo log mainly stores logical logs. For example, if we want to insert a piece of data, the undo log will record a corresponding delete log.
When we want to update a record, it will record an opposite update record. Because undo log stores the data before modification, which is equivalent to a previous version. MVCC realizes that reading and writing are not blocked. When reading, just return the data of the previous version.

1. When did it happen

    Generate the current version before the transaction starts undo log，undo Will also produce redo To guarantee undo log Reliability of
   

2. When will it be released

    After the transaction is committed, undo log It can not be deleted immediately, but put into the linked list to be cleaned up purge The thread determines whether it is being used by other transactions undo The version information before the last transaction of the table in the segment determines whether it can be cleaned up undo log Log space for.
   

application

1. How does MySQL ensure consistency

*From the database level, the database ensures consistency through atomicity, isolation and persistence. stay ACID Among the four characteristics, C(uniformity)Is the purpose, A(Atomicity),I(Isolation),D(persistence)Is a means to ensure consistency, the means provided by the database.

*MySQL Guaranteed by two-stage submission redo log and binlog The data are consistent.

*Phase 1: InnoDB redo log Write disk, InnoDB Transaction entry prepare state

*Phase 2: binlog Write disk, InnoDB Transaction entry commit state

*Each transaction binlog At the end of, a XID event，Indicates whether the transaction is committed successfully, that is, during the recovery process, binlog the last one XID event All subsequent content should be cleared

2. How does MySQL ensure atomicity

utilize Innodb of undo log. 

undo log Rollback log is the key to atomicity

When the transaction is rolled back, all the successfully executed transactions can be revoked sql Statement, which needs to record the corresponding log information you want to roll back.

3. How does MySQL ensure persistence

utilize Innodb of redo log
InnoDB There are transactions: persistence depends on redo log If the memory is written successfully, but the data has not been really brushed to the disk, if the database hangs at this time, we can rely on redo log To recover the data in memory, which realizes persistence)
use redo log The advantage is that redo log Compared with the data page, the disk brushing efficiency is high, which is shown as follows:
      1.redo log The volume is small. After all, only what page has been modified is recorded. Therefore, the volume is small and the disk brushing is fast.
      2.redo log Is to append all the way to the end, which belongs to sequence IO. The efficiency is obviously better than random IO Come on.