1、 Definition of persistence

Once the transaction is committed , Then all the changes will be saved to the database . Even if the system crashes , Modified data is not lost . At the same time, the database connection , There is one parameter by default autocommit=1（ If you want to turn it off , want set autocommit=0, Then you have to manually open and close ）, Means to execute one at a time sql If the start transaction statement is not displayed （begin or start transaction） Will start a transaction implicitly .

2、 Realization

The implementation of persistence depends on the daily report system , One is redolog, One is binlog, Let's familiarize ourselves with the concept .
1、redolog： Called redo log , When a record needs to be modified ,InnoDB The engine will first write this record to redo log Inside .redo log It's a physical format log , It records the changes to each page . One field is WAL(write ahead log), It means to write a log first , Write the data , It has two states , One is prepare, One is commit.
2、binlog： Recorded mysql All operations changed , But does not contain select and show This kind of operation that does not change the data itself . But it doesn't mean that the data itself will not be recorded without modification binlog journal .
binlog The role of logs
recovery （recover）： Data recovery
Copy （replication）： Similar to recovery , Use master to copy

At this time you may have questions , Why do I need two log, Not one ？ Next, let's explain this problem , This is also the principle of persistence implementation .
because MySQL It comes with the storage engine , and redo log yes InnoDB Peculiar . At the very beginning MySQL Not in it InnoDB engine , Self contained MyISAM There is no crash-safe Ability ,binlog Logs can only be used for archiving . In order to make MySQL have crash-safe Ability, so introduce InnoDB, and InnoDB Of crash-safe Ability dependence redo log, So there are two sets of logs .
Redo log and binlog All are transaction logs , What's the difference between them ？
1、binlog yes mysql Self contained , He will record the log files of all storage engines . and redo log yes InnoDB Peculiar , He only records the log files generated by the modified storage engine
The records are different ：binlog It's a logical log , Record the specific operation of this statement .2、Redo log It's the house log , What is recorded is the change of each page .
Write in different ways ：redo log It's circular , There's only so much space .binlog Use additional write , When one binlog When the file is written to a certain size, it will switch to the next file .
First write redo log Post write binlog What will happen ？ Suppose that redo log. finish writing sth. ,binlog Before I finished writing ,MySQL Abnormal process restart . Because of what we said earlier ,redo log After you've written , Even if the system crashes , Still able to recover data , So the line after recovery c The value of is 1. But because of binlogi I didn't finish writing crash 了 , Now binlog! There is no record of this statement . therefore , When you back up the logs later , Saved up binlog! There is no such sentence in it . And then you'll see , If you need to use this binlog To restore the temporary storage , Because of the binlog The loss of , This temporary library will be short of this update , The value of the recovered line is , It is different from the value of the original library .
First write binlog Post write redo log How to raise it ？ If in binlog After you've written crash, because redo log Not yet , This transaction is invalid after crash recovery , So this line c The value of is 0. however binlog. It has been recorded “ hold c from 0 Change to 1” This journal . therefore , Use after binlog When it comes to recovery, one more transaction comes out , The restored line c The value is 1, It is different from the value of the original library .
So the right order should be ：