编程知识 cdmana.com

What have I talked with Alibaba P9 about MySQL logs?

MySQL journal

Speaking of MySQL Log , There are three types of logs for MySQL It's very important , The three kinds of logs are :Binlog、Undo Log and Redo Log.

because Binlog and UndoLog There are similar places , therefore , We will introduce them in the following order MySQL Three principles of logging in :Undo Log——> Redo Log ——> Binlog.

Undo Log journal

What is? Undo Log

seeing the name of a thing one thinks of its function ,Undo Log It literally means the log of undo operation , It means to make MySQL The data in is returned to a certain state .

stay MySQL In the database , Before the business begins ,MySQL The records to be modified will be saved to Undo Log in , If the database crashes or the transaction needs to be rolled back ,MySQL By using Undo Log journal , Roll back the data in the database to the previous state .

MySQL newly added 、 When modifying and deleting data , Before the transaction starts , Will write the information to Undo Log in . Transaction commit , It's not going to be deleted immediately Undo Log, InnoDB The storage engine will store the transaction corresponding to Undo Log Put it in the list to be deleted , And then it'll go through the backstage purge thread Delete the list to be deleted . here , It is worth noting that :Undo Log It's a kind of Logic log , It records a process of change . such as ,MySQL Execute one delete operation ,Undo Log It will record a insert operation ;MySQL Execute one insert operation ,Undo Log It will record a delete operation ;MySQL Execute one update operation ,Undo Log It will record the opposite update operation .

Undo Log Managing and recording log information in the form of segments , stay InnoDB In the data file of the storage engine , It contains a kind of rollback segment Rollback segment for , It contains 1024 individual undo log senment.

Undo Log effect

Undo Log about MySQL In terms of implementing transactions , Play a vital role , It implements the atomicity of transactions and multi version concurrency control , That is what we often say MVCC.

  • Realize the atomicity of transactions

Undo Log Can achieve MySQL The atomicity of transactions , In the course of a transaction , If MySQL An error occurred or the user manually rolled back the transaction ( Yes rollback operation ),MySQL You can use Undo Log Logs restore the data in the database to its previous state .

  • Realization MVCC Mechanism

Undo Log stay MySQL Of InnoDB Multi version concurrency control is implemented in the storage engine (MVCC) Mechanism . Before transaction is committed ,Undo Log Saved version data before submission ,Undo Log The data in can be used as a copy or snapshot of the old version data for other concurrent transactions to read .


 About MySQL journal , Ali and I P9 What did you talk about ?

Business A After manually opening the transaction , Yes goods In the data table id by 1 Data update operation , First, the data hit by the update is written to Undo Buffer in . In the transaction A Before submitting , here , Business B Manually open transaction , Yes goods In the data sheet id by 1 Data query operation , The business at this time B Will read Undo Log And return it to the client , This is it. MySQL Medium MVCC Mechanism .

Can be in MySQL To view the control in the Undo Log Log parameters .

show variables like '%innodb_undo%';

Redo Log journal

Said the MySQL Medium Undo Log, Let's see MySQL Medium Redo Log journal .

What is? Redo Log

seeing the name of a thing one thinks of its function Redo Log The literal meaning of redo log is redo log , It refers to the ability to re perform some operation on the database in case of an unexpected situation . stay MySQL in , Any data modified in the transaction , Will write the latest data to Redo Log Backup in .

stay MySQL in , With the execution of transaction operations , It will produce Redo Log journal , When a transaction is committed Redo Log And write it in Redo Buffer,Redo Buffer It is not written to disk immediately as the transaction is committed , But after the web page of transaction operation is written to disk ,Redo Log The mission of , here ,Redo Log The space occupied by logs can be reused , It will be generated later Redo Log Log overlay .

Redo Log Principle

Redo Log Be able to achieve transaction persistence , Prevent at the point of failure , There are dirty pages not written to the table ibd In file , It's restarting MySQL In service , according to Redo Log To redo , To persist uncommitted transactions . This process can be simplified as shown in the figure below .


 About MySQL journal , Ali and I P9 What did you talk about ?

Redo Log The mechanism of writing

Redo Log The contents of the file are written to the file in a sequential loop , When it's full, it goes back to the first file , Write overlay .


 About MySQL journal , Ali and I P9 What did you talk about ?

  • Write Pos Is the location of the current record , Move back as you write , Write to the end of the last file and go back to 0 The beginning of file No ;
  • CheckPoint Is the current location to erase , It's also going back and forth , Before erasing a record, update the record to the number of
    According to the document ;

Write Pos and CheckPoint What's left in between , It can be used to record new operations . If Write Pos Catch up CheckPoint, It means it's full , Now you need to move back CheckPoint To erase data .

Every InnoDB The storage engine has at least 1 Redo log filegroups (group), Each filegroup has at least 2 Redo log files , The default is ib_logfile0 and ib_logfile1 .

Can be in MySQL To view the control, use the following command Redo Log Parameters of .

show variables like '%innodb_log%';

Redo Log Write mechanism

stay Redo Log Log information from Redo Buffer Persist to Redo Log when , Specific persistence strategies can be implemented through innodb_flush_log_at_trx_commit Parameter setting , The specific strategies are as follows .

  • 0: Commit per second Redo buffer ->OS cache -> flush cache to disk, One second of transaction data may be lost . By Backstage Master Thread every 1 One operation per second .
  • 1( The default value is ): Every transaction commit execution Redo Buffer -> OS cache -> flush cache to disk, This is the safest way , The worst performance .
  • 2: Every transaction commit execution Redo Buffer -> OS cache, And then the backstage Master Thread at every 1 Seconds to perform OS cache -> flush cache to disk The operation of .

Generally, it is recommended to choose the value 2, because MySQL Hang up, no data loss , The whole server will be lost if it hangs up 1 Second transaction commit data .

Binlog journal

What is? Binlog

Binlog Record all MySQL Binary log of database table structure change and table data modification , It doesn't record select and show Logs of such query operations .Binlog Logs are recorded in the form of events , It also includes the elapsed time of statement execution . Turn on Binlog There are two most important usage scenarios for logs .

  • Master slave copy : Open in main library Binlog function , In this way, the main library can Binlog Pass it to the slave library , Get it from the library Binlog After the implementation of data recovery to achieve master-slave data consistency .
  • Data recovery : adopt mysqlbinlog And so on

About Binlog See my published books for the usage scenarios of 《MySQL A complete collection of Technology : Development 、 Optimization and operation and maintenance 》 A Book .

Binlog File record mode

Binlog What are the file recording modes STATEMENT、ROW and MIXED Three , The specific meaning is as follows .

ROW Pattern

ROW(row-based replication, RBR): The log records the modification of each line of data , And then in slave Modify the same data on both sides .

advantage : Can clearly record the modification details of each row of data , It can completely realize master-slave data synchronization and data recovery .

shortcoming : The batch operation , There will be a lot of logs , In especial alter table It's going to make the logs soar .

STATMENT Pattern

STATMENT(statement-based replication, SBR): Every piece of modified data SQL Will be recorded master Of Binlog in ,slave When copying SQL The process resolves to the original master The same... That the end has performed SQL Re execution . abbreviation SQL Sentence copying .

advantage : Small amount of logs , Reduce disk IO, Speed up storage and recovery

shortcoming : In some cases, the master-slave data is inconsistent , such as last_insert_id()、now() Such as function .

MIXED Pattern

MIXED(mixed-based replication, MBR): Mixed use of the above two modes , You usually use STATEMENT Mode save binlog, about STATEMENT Mode can not be copied by operation using ROW Mode save binlog,MySQL It will be executed according to SQL Statement to select write mode .

Binlog File structure

about MySQL Of Binlog There are three versions of the file structure , See the picture below .


 About MySQL journal , Ali and I P9 What did you talk about ?


 About MySQL journal , Ali and I P9 What did you talk about ?


 About MySQL journal , Ali and I P9 What did you talk about ?

About Binlog Specific information about the file structure , You can refer to MySQL Official documents of , The specific links are :https://dev.mysql.com/doc/internals/en/event-header-fields.html

Binlog Write mechanism

Triggered by recording mode and operation event Event generation log event( Event triggered execution mechanism ).

Log time generated during transaction execution (log event) Write buffer , Each transaction thread has a buffer .Log Event Save in a binlog_cache_mngr In the data structure , There are two buffers in the structure , One is stmt_cache, Used to store information that does not support transactions ; The other is trx_cache, Used to store information supporting transactions .

The transaction will be generated in the commit phase log event Write to external binlog In file . Different transactions serialize log event write in Binlog In file , So a transaction contains log event The information in binlog The file is continuous , No other transactions will be inserted in the middle log event.

Binlog File operations

Binlog Status view

show variables like 'log_bin';

Turn on Binlog function , Need modification my.cnf or my.ini The configuration file , stay [mysqld] Add below log_bin=mysql_bin_log, restart
MySQL service .

binlog-format=ROWlog-bin=mysqlbinlog

Use show binlog events command

show binary logs; // Equivalent to show master logs;show master status;show binlog events;show binlog events in 'mysqlbinlog.000001';

Use mysqlbinlog command

mysqlbinlog " file name "mysqlbinlog " file name " > "test.sql"

Use binlog Restore data

// Recover at a specified time mysqlbinlog --start-datetime="2021-02-28 18:00:00" --stopdatetime="2021-03-01 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123456// Recover by event location number mysqlbinlog --start-position=1789 --stop-position=2674 mysqlbinlog.000001| mysql -uroot -p123456

Delete Binlog file

purge binary logs to 'mysqlbinlog.000001'; // Delete the specified file purge binary logs before '2021-03-01 00:00:00'; // Delete files before the specified time reset master; // Clear all files 

Can be set by expire_logs_days Parameter to start the automatic cleaning function . The default value is 0 It means not enabled . Set to greater than 0 The integer of represents the number of days beyond binlog The file will be automatically cleared .

版权声明
本文为[Kick the farmer]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/04/20210421220807769h.html

Scroll to Top