此篇也是筆記套腹，加上自己的理解和聯(lián)系內(nèi)容窘行。
此篇涉及Mysql的恢復(fù)僧须，binlog日志和redo log 日志肤舞，非常重要紫新。

一 表和數(shù)據(jù)準(zhǔn)備

簡(jiǎn)單的建一個(gè)測(cè)試表如下：

mysql> create table Test(ID int primary key, c int);
mysql> insert into test values(1,1) ,(2,2), (3,3);

那么如下的更新語(yǔ)句：

mysql>update test set c=c+1 where id=2;

的執(zhí)行過(guò)程如何那？

Mysql邏輯架構(gòu)圖來(lái)自互聯(lián)網(wǎng)

整個(gè)過(guò)程和執(zhí)行查詢語(yǔ)句是類似的李剖，還是走上面的流程芒率，表在更新的時(shí)候，會(huì)把緩存失效篙顺，這就是mysql新版本關(guān)閉緩存的原因敲董。

一 執(zhí)行過(guò)程

1. 首先客戶端連接到連接器，連接器進(jìn)行認(rèn)證慰安，通過(guò)會(huì)會(huì)查看test表是否存在緩存，如果存在緩存則清空聪铺。
2. 接著分析器進(jìn)行詞法和語(yǔ)法分析化焕，得知是一條更新語(yǔ)句。
3. 優(yōu)化器決定使用主鍵索引
4. 執(zhí)行器會(huì)查找id為2的數(shù)據(jù)铃剔，使用索引樹來(lái)找到一行數(shù)據(jù)撒桨，如果內(nèi)存中有則直接返回?cái)?shù)據(jù)給執(zhí)行器；如果沒有键兜，會(huì)從磁盤中把數(shù)據(jù)讀入內(nèi)存凤类，然后返回?cái)?shù)據(jù)給執(zhí)行器。
5. 執(zhí)行器 拿到了需要更改的行數(shù)據(jù)之后普气，將n+1生成新行調(diào)用存儲(chǔ)引擎接口寫入這一新行谜疤。
6. 存儲(chǔ)引擎將新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄在redo log中，記錄為prepare狀態(tài)夷磕，告訴執(zhí)行器隨時(shí)可以提交履肃。
7. 執(zhí)行器生成這個(gè)操作的binlog日志，并把日志寫入到磁盤坐桩。
8. 執(zhí)行器調(diào)用存儲(chǔ)引擎的提交事務(wù)接口尺棋，將redo log 更改為commit狀態(tài)，整個(gè)操作更新完成绵跷。

這里面用到了兩類非常重要的日志redo log 和bin log日志膘螟。

二 redo log 日志

redo log日志是InnoDB這個(gè)存儲(chǔ)引擎帶的，我們看第6步碾局，存儲(chǔ)引擎將數(shù)據(jù)更新到內(nèi)存荆残，就告訴執(zhí)行器可以提交了，并沒有等數(shù)據(jù)更新到磁盤上擦俐，這類日志叫WAL日志（Write Ahead Log）脊阴，也就是在數(shù)據(jù)寫入到磁盤之前的日志。如果熟悉solr的朋友蚯瞧，會(huì)注意到solr也有類似的日志叫Tlog日志嘿期，也是WAL日志，通過(guò)這種方式可以提升數(shù)據(jù)更新的速度埋合，并且也存在著Tlog日志做數(shù)據(jù)恢復(fù)的情況备徐。所以redo log的日志和Tlog日志類似，是具有系統(tǒng)奔潰恢復(fù)數(shù)據(jù)的能力甚颂，crash-safe能力蜜猾。

至于數(shù)據(jù)什么時(shí)候真正被寫到磁盤上，有兩個(gè)時(shí)間點(diǎn)就是數(shù)據(jù)庫(kù)空閑的時(shí)候振诬，或者redo log空間不夠的時(shí)候蹭睡。redo log是一種循環(huán)日志，它的空間是受限的赶么。
可以通過(guò)以下命令來(lái)查看redo log的大小配置：

mysql> show variables like 'innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set, 1 warning (0.00 sec)

默認(rèn)的才48MB 存儲(chǔ)一個(gè)redo log文件肩豁，一組有兩個(gè)文件，所以一共才有96MB文件辫呻。對(duì)應(yīng)每秒有200-300TPS的數(shù)據(jù)庫(kù)來(lái)說(shuō)清钥，設(shè)置為4G大小是比較合理的，主要可以減少因?yàn)閞edo log滿了而去刷新數(shù)據(jù)到磁盤上的操作放闺。但是文件過(guò)大祟昭，也容易造成數(shù)據(jù)庫(kù)恢復(fù)慢，那誰(shuí)怖侦，你還記得我們solr的Tlog日志過(guò)大篡悟，導(dǎo)致solr半個(gè)多小時(shí)都沒有恢復(fù)嗎谜叹，似曾相識(shí)的場(chǎng)景。
可以更改my.cnf配置恰力，比如添加：

innodb_log_file_size =1G
innodb_log_files_in_group=4

redo log 有兩個(gè)標(biāo)志叉谜，一個(gè)是write pos，是開始寫日志的位置踩萎；check point 是當(dāng)前開始清理日志的位置停局，它們之間的空余空間是redo log日志可以寫的空間，快滿的話會(huì)停下來(lái)香府，擦除一部分記錄董栽，騰出空間。
如下圖：

圖來(lái)自極客時(shí)間MySQL課程

redo log 也是有緩存的企孩，寫redo log也不是直接寫到磁盤上锭碳，也有一個(gè)寫入到磁盤的時(shí)機(jī)問(wèn)題，通過(guò)如下參數(shù)來(lái)控制：

mysql> show variables like 'innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

如果值為1 表示每次提交事務(wù)的時(shí)候勿璃，就會(huì)將redo log的log buffer數(shù)據(jù)刷新到磁盤上擒抛，這種方式不會(huì)丟數(shù)據(jù)，但是每次交互都寫磁盤补疑，IO性能差歧沪。
設(shè)置為0 表示大概1s刷新一次磁盤，有可能數(shù)據(jù)庫(kù)會(huì)丟失1s的數(shù)據(jù)莲组。
設(shè)置為2 表示每次提交寫log buffer诊胞，但是每秒強(qiáng)制寫磁盤。
如下圖表示：

圖片來(lái)自互聯(lián)網(wǎng)

圖中 fsync 才強(qiáng)制寫磁盤锹杈，OS buffer 是寫系統(tǒng)文件緩存而已撵孤。
數(shù)據(jù)不重要的情況下，設(shè)置為2 是更優(yōu)的策略：

set @@global.innodb_flush_log_at_trx_commit=2;    

當(dāng)然如果是批量插入數(shù)據(jù)的話竭望，將commit最后一次提交的話邪码，性能會(huì)更好。

三 bin log 日志

有了redo log 日志咬清，為什么還需要bin log日志霞扬。 原因有：

1. redo log 日志是InnoDB 引擎帶的，其他引擎的沒有枫振，bin log 日志，是Mysql的服務(wù)層帶的萤彩，所有的引擎都可以用粪滤。
2. redo log 日志是個(gè)循環(huán)日志，不能做長(zhǎng)期保存雀扶，日志會(huì)被覆蓋掉杖小，無(wú)法做數(shù)據(jù)的恢復(fù)和備份肆汹，而bin log日志正是用于數(shù)據(jù)的歸檔和恢復(fù)的。
   bin log 和redo log的不同點(diǎn)還有予权，redo log記錄的是物理日志昂勉，而bin log記錄的是邏輯日志；所謂物理日志扫腺，是記錄在哪個(gè)數(shù)據(jù)頁(yè)上做xx 更改岗照；而bin log 日志記錄的是類似把特定行數(shù)據(jù)+1 ，有的格式的bin log日志記錄的直接是sql笆环，這個(gè)和它的名字不一致攒至，感覺bin log 日志更應(yīng)該是物理日志，哈哈躁劣。
   查看bin log的日志類型：

mysql> show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

MySQL5.1 以后引入了binlog_format參數(shù)迫吐，該參數(shù)有三種可選值：statement、row和mixed：

• statement就是之前的格式账忘，基于SQL語(yǔ)句來(lái)記錄志膀。
• row記錄的則是行的更改情況，但是row格式有一個(gè)不好的地方就是當(dāng)修改的行數(shù)很多時(shí)鳖擒，生成的binlog占用很大的空間溉浙，占用大量空間的同時(shí)還會(huì)耗費(fèi)大量IO資源，因此MySQL又提供了一種折中的方案——mixed败去。
• 在mixed模式下放航，MySQL默認(rèn)仍然采用statement格式進(jìn)行記錄，但是一旦它判斷可能會(huì)有數(shù)據(jù)不一致的情況發(fā)生圆裕，則會(huì)采用row格式來(lái)記錄广鳍。

查看bin log列表：

mysql> show master logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| DESKTOP-0PNJ0VF-bin.000028 |     20361 |
| DESKTOP-0PNJ0VF-bin.000029 |       178 |
| DESKTOP-0PNJ0VF-bin.000030 |       178 |
| DESKTOP-0PNJ0VF-bin.000031 |       178 |
| DESKTOP-0PNJ0VF-bin.000032 |       178 |
| DESKTOP-0PNJ0VF-bin.000033 |       178 |
| DESKTOP-0PNJ0VF-bin.000034 |       178 |
| DESKTOP-0PNJ0VF-bin.000035 |       178 |
| DESKTOP-0PNJ0VF-bin.000036 |    565052 |
| DESKTOP-0PNJ0VF-bin.000037 |       178 |
| DESKTOP-0PNJ0VF-bin.000038 |    132422 |
+----------------------------+-----------+

查看正在寫的binlog日志

mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| DESKTOP-0PNJ0VF-bin.000038 |   132422 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+

查看最近的內(nèi)容：

show binlog events in 'DESKTOP-0PNJ0VF-bin.000038';

如圖:

bin log日志

如果是語(yǔ)句格式，里面看到很多sql語(yǔ)句內(nèi)容吓妆。

四 二階段提交

剛才說(shuō)到redo log 讓數(shù)據(jù)庫(kù)是crash safe的赊时，也就是數(shù)據(jù)庫(kù)掛了，所做的更改不會(huì)丟失行拢，我們?cè)诟聰?shù)據(jù)的時(shí)候祖秒，數(shù)據(jù)在內(nèi)存中被更新，在redo log里面被記錄舟奠，如果這時(shí)候數(shù)據(jù)庫(kù)掛掉了竭缝，那么redo log里面的數(shù)據(jù)還可以用來(lái)恢復(fù)。數(shù)據(jù)是不是真的需要恢復(fù)那沼瘫，比如一個(gè)事務(wù)執(zhí)行一半還未提交抬纸，如果數(shù)據(jù)庫(kù)按照redo log 來(lái)恢復(fù)的話，結(jié)果客戶端認(rèn)為數(shù)據(jù)沒提交耿戚，所以是老的數(shù)據(jù)湿故，mysql卻把它恢復(fù)了阿趁，這個(gè)就產(chǎn)生了不一致，所以redo log 恢復(fù)的是提交的事務(wù)坛猪。

為了保證數(shù)據(jù)恢復(fù)所以mysql 在執(zhí)行更新的時(shí)候脖阵，需要在redo log中記錄兩次，一次是prepare 狀態(tài)日志墅茉，表示命黔，準(zhǔn)備好了事務(wù)可以提交了；另外一次是bin log寫入完成后躁锁，寫的redo log的commit 狀態(tài)日志纷铣。這就是兩階段提交。

4.1 只提交一次redo log產(chǎn)生情況

如果我們只提交一次redo log 日志战转，就會(huì)有兩種情況：

1. redo log 在第6步更新完內(nèi)存數(shù)據(jù)之后搜立，直接提交為commit 狀態(tài)日志，再做bin log日志的記錄槐秧。
2. 更新完內(nèi)存數(shù)據(jù)之后啄踊，先寫bin log 日志，再寫redo log日志刁标。
   第一種情況下： 更新內(nèi)存數(shù)據(jù)-----> 寫redo log commit狀態(tài) 日志---> 寫bin log 日志---->提交完成颠通。
   如果數(shù)據(jù)庫(kù)崩潰發(fā)生在寫redo log commit 狀態(tài)日志之后，寫binlog 日志之前膀懈，那么數(shù)據(jù)庫(kù)重啟的時(shí)候顿锰，發(fā)現(xiàn)redo log 有記錄commit 狀態(tài)日志就會(huì)把數(shù)據(jù)恢復(fù)； 但是我們?cè)谧鯩ysql 主備同步或恢復(fù)數(shù)據(jù)庫(kù)的時(shí)候启搂，使用的是bin log日志和備份來(lái)恢復(fù)的硼控，這時(shí)候因?yàn)閎in log 中沒有這次事務(wù)的數(shù)據(jù)，所以不會(huì)恢復(fù)胳赌，這就造成了數(shù)據(jù)的不同步牢撼。

第二種情況下： 更新內(nèi)存數(shù)據(jù)----> 寫bin log 日志--->寫redo log 日志--->提交完成。
如果數(shù)據(jù)庫(kù)崩潰 發(fā)生在寫bin log 日志之后疑苫，寫redo log日志之前熏版，那么數(shù)據(jù)庫(kù)重啟的時(shí)候，內(nèi)存中更新丟失捍掺，redo log里面沒有內(nèi)容撼短，所以不會(huì)恢復(fù)；但是在做同步 或異惩ξ穑恢復(fù)數(shù)據(jù)庫(kù)的時(shí)候阔加，使用的是bin log日志，這時(shí)候發(fā)現(xiàn)bin log有數(shù)據(jù)满钟，又會(huì)把數(shù)據(jù)恢復(fù)胜榔，這就造成了數(shù)據(jù)不同步。

也就是兩次redo log的目的是為了主庫(kù)和備份是一致的湃番，數(shù)據(jù)恢復(fù)是通過(guò)數(shù)據(jù)庫(kù)的文件備份和bin log 日志結(jié)合起來(lái)恢復(fù)的夭织。

Mysql的InnoDB 引擎還包括undo log 日志，各種寫日志的操作還是比較復(fù)雜的吠撮，暫時(shí)就寫到這里吧尊惰。