If I want to talk to you about the locking mechanism in InnoDB, it will inevitably involve the MySQL log system, binlog, redo log, undo log, etc. I saw these three logs summarized by some friends. Not bad, hurry up and share it with your friends.
The log is an important part of themysqldatabase, recording various status information during the operation of the database.mysqlLogs mainly include error logs, query logs, slow query logs, transaction logs, and binary logs.
As a developer, what we need to focus on is the binary log (binlog) and transaction log (includingredo logandundo log). This article will introduce these three types of logs in detail next.
binlogis used to record write operations (excluding queries) information performed by the database and is saved on the disk in binary form.binlogis the logical log ofmysqland is recorded by theServerlayer. Themysqldatabase using any storage engine will recordbinlogLog.
Logical log: It can be understood that what is recorded is the sql statement.
Physical log:mysqlThe data is ultimately saved In the data page, the physical log records the data page changes.
binlogis written by appending. Eachbinlogfile can be set through themax_binlog_sizeparameter. When the file size reaches the given value, a new file will be generated to save the log.
In actual applications,binloghas two main usage scenarios, namely master-slave replication and data recovery.
Master-slave replication: Openbinlogon theMasterside, and then sendbinlogto eachSlaveside,Slaveside replaysbinlogto achieve master-slave data consistency.
Data recovery: Recover data by using themysqlbinlogtool.
For theInnoDBstorage engine, it will only be recorded when the transaction is committedbiglog, the record is still in the memory at this time, so when wasbiglogflushed to the disk?
mysqlControl the flushing timing ofbiglogthrough thesync_binlogparameter, the value range is0-N:
0: No mandatory requirement, the system will decide when to write to the disk;
1: Every timecommit##binlogmust be written to the disk when #;
binlogwill be written to the disk for every N transactions.
sync_binlogis1, which is alsoMySQL 5.7.7Default value for subsequent versions. However, setting a larger value can improve database performance. Therefore, in actual situations, you can also increase the value appropriately and sacrifice a certain degree of consistency to obtain better performance.
binlogThe log has three formats, namelySTATMENT,ROWandMIXED.
MySQL 5.7.7, the default format isSTATEMENT, afterMySQL 5.7.7, the default value isROW. The log format is specified bybinlog-format.
STATMENT: Replication based onSQLstatements (statement-based replication, SBR), each statement will be modified The SQL statements of the data will be recorded inbinlog.
ROW: Row-based replication (row-based replication, RBR), does not record the context information of each SQL statement, only It is necessary to record which data has been modified.
MIXED: Mixed-based replication based onSTATMENTandROW, MBR), general copying usesSTATEMENTmode to savebinlog, for operations that cannot be copied inSTATEMENTmode, useROWmode Savebinlog
So how does
mysqlensure consistency?
The simple way is to flush all data pages involved in modifications to disk every time a transaction is committed. However, doing so will cause serious performance problems, mainly reflected in two aspects:
BecauseInnodbperforms disk interaction in units ofpages, and a transaction is likely to only modify a few data pages. Bytes, it would be a waste of resources to flush the complete data page to the disk at this time!
A transaction may involve modifying multiple data pages, and these data pages are not physically continuous. The performance of using random IO writing is too poor!
So
mysqldesignedredo log. Specifically, it only records what modifications the transaction has made to the data page, so It can perfectly solve the performance problem (relatively speaking, the file is smaller and it is sequential IO).
redo logincludes two parts: one is the log buffer in memory (redo log buffer), The other is the log file on disk (redo logfile).
mysqlEach time aDMLstatement is executed, the record is first written toredo log buffer, and then at a later point in time, multiple records are written at once. Operation records are written toredo log file. This technology of writing logs first and then writing to disk is theWAL (Write-Ahead Logging)technology often mentioned in
MySQL.
In computer operating systems, buffer data in user space (user space) generally cannot be written directly to the disk, and must pass through the operating system kernel space (kernel space) buffer (OS Buffer).
Therefore,redo log bufferwritingredo logfileactually writesOS Bufferfirst, and then callsthrough the system fsync()Flash it toredo log file
, the process is as follows:
mysqlSupport Three timings for writingredo log buffertoredo log filecan be configured through theinnodb_flush_log_at_trx_commitparameter. The meaning of each parameter value is as follows:
As mentioned earlier,redo logactually records changes to the data page, and this It is not necessary to save all the change records, soredo logis implemented using a fixed size and cyclic writing method. When writing to the end, it will return to the beginning to write the log in a loop. As shown below:
At the same time, we can easily know that in innodb, there areredo logthat need to be flushed, and also There aredata pagesthat also need to be flushed. The main significance ofredo logis to reduce the requirement fordata pagesto be flushed**.
In the above figure,write posrepresents theLSN(logical sequence number) position of theredo logcurrent record,check pointindicates theLSN(logical sequence number) position corresponding toredo logafter the data page change record is flushed.
write posThe part betweencheck pointis the empty part ofredo log, used to record new records;## Between #check pointandwrite posis theredo logchange record of the data page to be written to the disk. Whenwrite poscatches up withcheck point, it will first pushcheck pointforward, vacate the position, and then record a new log.
innodb, no matter whether it was shut down normally or abnormally last time, the recovery operation will always be performed. Becauseredo logrecords the physical changes of the data page, the recovery speed is much faster than the logical log (such asbinlog).
innodb, it will first check theLSNof the data page in the disk. If theLSNof the data page is less than thein the log LSN, the recovery will start fromcheckpoint.
checkpointis in progress before the downtime, and the disk brushing progress of the data page exceeds the disk brushing progress of the log page. At this time, data will appear. TheLSNrecorded in the page is greater than theLSNin the log. At this time, the part that exceeds the progress of the log will not be redone, because this itself means that what has been done does not need to be redone. Do.
binlogandredo log:The binloglog is only used for archiving, and relying solely onbinlogdoes not have thecrash-safecapability.
But onlyredo logwill not work, becauseredo logis unique toInnoDB, and the records in the log will be overwritten after being written to disk. Therefore, bothbinlogandredo logneed to be recorded at the same time to ensure that when the database is down and restarted, the data will not be lost.
One of the four major characteristics of database transactions is atomicity. Specifically, atomicity refers to a series of operations on the database, either all succeed or all fail. There may be partial success.
In fact, the bottom layer of atomicity is achieved throughundo log.undo logmainly records the logical changes of data. For example, anINSERTstatement corresponds to aundo logofDELETE, for each # The ##UPDATEstatement corresponds to an oppositeUPDATE'sundo log, so that when an error occurs, you can roll back to the data state before the transaction.
undo logis also the key toMVCC(multi-version concurrency control) implementation.
The above is the detailed content of What is the difference between binlog/redolog/undolog in MySQL?. For more information, please follow other related articles on the PHP Chinese website!