MySQL為什么不會丟失數(shù)據(jù)

本篇內(nèi)容介紹了“MySQL為什么不會丟失數(shù)據(jù)”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

進入正題前先簡單看看MySQL的邏輯架構(gòu)，相信我用的著。

MySQL邏輯架構(gòu) 

MySQL的邏輯架構(gòu)大致可以分為三層：

• 第一層：處理客戶端連接、授權(quán)認證，安全校驗等。

• 第二層：服務(wù)器server層，負責(zé)對SQL解釋、分析、優(yōu)化、執(zhí)行操作引擎等。

• 第三層：存儲引擎，負責(zé)MySQL中數(shù)據(jù)的存儲和提取。

我們要知道MySQL的服務(wù)器層是不管理事務(wù)的，事務(wù)是由存儲引擎實現(xiàn)的，而MySQL中支持事務(wù)的存儲引擎又屬InnoDB使用的最為廣泛，所以后續(xù)文中提到的存儲引擎都以InnoDB為主。

MySQL數(shù)據(jù)更新流程

記住! 記住! 記住! 上邊這張圖，她是MySQL更新數(shù)據(jù)的基礎(chǔ)流程，其中包括redo log、bin log、undo  log三種日志間的大致關(guān)系，好了閑話少說直奔主題。

redo log(重做日志)

redo log屬于MySQL存儲引擎InnoDB的事務(wù)日志。

MySQL的數(shù)據(jù)是存放在磁盤中的，每次讀寫數(shù)據(jù)都需做磁盤IO操作，如果并發(fā)場景下性能就會很差。為此MySQL提供了一個優(yōu)化手段，引入緩存Buffer  Pool。這個緩存中包含了磁盤中部分數(shù)據(jù)頁(page)的映射，以此來緩解數(shù)據(jù)庫的磁盤壓力。

當(dāng)從數(shù)據(jù)庫讀數(shù)據(jù)時，首先從緩存中讀取，如果緩存中沒有，則從磁盤讀取后放入緩存;當(dāng)向數(shù)據(jù)庫寫入數(shù)據(jù)時，先向緩存寫入，此時緩存中的數(shù)據(jù)頁數(shù)據(jù)變更，這個數(shù)據(jù)頁稱為臟頁，Buffer  Pool中修改完數(shù)據(jù)后會按照設(shè)定的更新策略，定期刷到磁盤中，這個過程稱為刷臟頁。

MySQL宕機

如果刷臟頁還未完成，可MySQL由于某些原因宕機重啟，此時Buffer  Pool中修改的數(shù)據(jù)還沒有及時的刷到磁盤中，就會導(dǎo)致數(shù)據(jù)丟失，無法保證事務(wù)的持久性。

為了解決這個問題引入了redo log，redo  Log如其名側(cè)重于重做!它記錄的是數(shù)據(jù)庫中每個頁的修改，而不是某一行或某幾行修改成怎樣，可以用來恢復(fù)提交后的物理數(shù)據(jù)頁，且只能恢復(fù)到最后一次提交的位置。

redo log用到了WAL(Write-Ahead  Logging)技術(shù)，這個技術(shù)的核心就在于修改記錄前，一定要先寫日志，并保證日志先落盤，才能算事務(wù)提交完成。

有了redo log再修改數(shù)據(jù)時，InnoDB引擎會把更新記錄先寫在redo log中，在修改Buffer  Pool中的數(shù)據(jù)，當(dāng)提交事務(wù)時，調(diào)用fsync把redo log刷入磁盤。至于緩存中更新的數(shù)據(jù)文件何時刷入磁盤，則由后臺線程異步處理。

注意：此時redo log的事務(wù)狀態(tài)是prepare，還未真正提交成功，要等bin  log日志寫入磁盤完成才會變更為commit，事務(wù)才算真正提交完成。

這樣一來即使刷臟頁之前MySQL意外宕機也沒關(guān)系，只要在重啟時解析redo log中的更改記錄進行重放，重新刷盤即可。

大小固定

redo log采用固定大小，循環(huán)寫入的格式，當(dāng)redo log寫滿之后，重新從頭開始如此循環(huán)寫，形成一個環(huán)狀。

那為什么要如此設(shè)計呢?

因為redo log記錄的是數(shù)據(jù)頁上的修改，如果Buffer  Pool中數(shù)據(jù)頁已經(jīng)刷磁盤后，那這些記錄就失效了，新日志會將這些失效的記錄進行覆蓋擦除。

上圖中的write pos表示redo log當(dāng)前記錄的日志序列號LSN(log sequence number)，寫入還未刷盤，循環(huán)往后遞增;check  point表示redo log中的修改記錄已刷入磁盤后的LSN，循環(huán)往后遞增，這個LSN之前的數(shù)據(jù)已經(jīng)全落盤。

write pos到check point之間的部分是redo log空余的部分(綠色)，用來記錄新的日志;check point到write  pos之間是redo log已經(jīng)記錄的數(shù)據(jù)頁修改數(shù)據(jù)，此時數(shù)據(jù)頁還未刷回磁盤的部分。當(dāng)write pos追上check point時，會先推動check  point向前移動，空出位置(刷盤)再記錄新的日志。

注意：redo  log日志滿了，在擦除之前，需要確保這些要被擦除記錄對應(yīng)在內(nèi)存中的數(shù)據(jù)頁都已經(jīng)刷到磁盤中了。擦除舊記錄騰出新空間這段期間，是不能再接收新的更新請求的，此刻MySQL的性能會下降。所以在并發(fā)量大的情況下，合理調(diào)整redo  log的文件大小非常重要。

crash-safe

因為redo log的存在使得Innodb引擎具有了crash-safe的能力，即MySQL宕機重啟，系統(tǒng)會自動去檢查redo  log，將修改還未寫入磁盤的數(shù)據(jù)從redo log恢復(fù)到MySQL中。

MySQL啟動時，不管上次是正常關(guān)閉還是異常關(guān)閉，總是會進行恢復(fù)操作。會先檢查數(shù)據(jù)頁中的LSN，如果這個 LSN 小于 redo log  中的LSN，即write pos位置，說明在redo log上記錄著數(shù)據(jù)頁上尚未完成的操作，接著就會從最近的一個check  point出發(fā)，開始同步數(shù)據(jù)。

簡單理解，比如：redo log的LSN是500，數(shù)據(jù)頁的LSN是300，表明重啟前有部分數(shù)據(jù)未完全刷入到磁盤中，那么系統(tǒng)則將redo  log中LSN序號300到500的記錄進行重放刷盤。

undo log(回滾日志)

undo log也是屬于MySQL存儲引擎InnoDB的事務(wù)日志。

undo  log屬于邏輯日志，如其名主要起到回滾的作用，它是保證事務(wù)原子性的關(guān)鍵。記錄的是數(shù)據(jù)修改前的狀態(tài)，在數(shù)據(jù)修改的流程中，同時會記錄一條與當(dāng)前操作相反的邏輯日志到undo  log中。

我們舉個栗子：假如更新ID=1記錄的name字段，name原始數(shù)據(jù)為小富，現(xiàn)改name為程序員內(nèi)點事

事務(wù)執(zhí)行update X set name = 程序員內(nèi)點事 where id =1語句時，先會在undo log中記錄一條相反邏輯的update X  set name = 小富 where id =1記錄，這樣當(dāng)某些原因?qū)е路?wù)異常事務(wù)失敗，就可以借助undo  log將數(shù)據(jù)回滾到事務(wù)執(zhí)行前的狀態(tài)，保證事務(wù)的完整性。

那可能有人會問：同一個事物內(nèi)的一條記錄被多次修改，那是不是每次都要把數(shù)據(jù)修改前的狀態(tài)都寫入undo log呢?

答案是不會的!

undo log只負責(zé)記錄事務(wù)開始前要修改數(shù)據(jù)的原始版本，當(dāng)我們再次對這行數(shù)據(jù)進行修改，所產(chǎn)生的修改記錄會寫入到redo log，undo  log負責(zé)完成回滾，redo log負責(zé)完成前滾。

回滾

未提交的事務(wù)，即事務(wù)未執(zhí)行commit。但該事務(wù)內(nèi)修改的臟頁中，可能有一部分臟塊已經(jīng)刷盤。如果此時數(shù)據(jù)庫實例宕機重啟，就需要用回滾來將先前那部分已經(jīng)刷盤的臟塊從磁盤上撤銷。

前滾

未完全提交的事務(wù)，即事務(wù)已經(jīng)執(zhí)行commit，但該事務(wù)內(nèi)修改的臟頁中只有一部分數(shù)據(jù)被刷盤，另外一部分還在buffer  pool緩存上，如果此時數(shù)據(jù)庫實例宕機重啟，就需要用前滾來完成未完全提交的事務(wù)。將先前那部分由于宕機在內(nèi)存上的未來得及刷盤數(shù)據(jù)，從redo  log中恢復(fù)出來并刷入磁盤。

數(shù)據(jù)庫實例恢復(fù)時，先做前滾，后做回滾。

如果你仔細看過了上邊的 MySQL數(shù)據(jù)更新流程圖 就會發(fā)現(xiàn)，undo log、redo log、bin  log三種日志都是在刷臟頁之前就已經(jīng)刷到磁盤了的，相互協(xié)作最大限度保證了用戶提交的數(shù)據(jù)不丟失。

bin log(歸檔日志)

bin log是一種數(shù)據(jù)庫Server層(和什么引擎無關(guān))，以二進制形式存儲在磁盤中的邏輯日志。bin log記錄了數(shù)據(jù)庫所有DDL和DML操作(不包含  SELECT 和 SHOW等命令，因為這類操作對數(shù)據(jù)本身并沒有修改)。

默認情況下，二進制日志功能是關(guān)閉的。可以通過以下命令查看二進制日志是否開啟：

mysql> SHOW VARIABLES LIKE 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin       | OFF   | +---------------+-------+

bin log也被叫做歸檔日志，因為它不會像redo log那樣循環(huán)寫擦除之前的記錄，而是會一直記錄日志。一個bin  log日志文件默認最大容量1G(也可以通過max_binlog_size參數(shù)修改)，單個日志超過最大值，則會新創(chuàng)建一個文件繼續(xù)寫。

mysql> show binary logs; +-----------------+-----------+ | Log_name        | File_size | +-----------------+-----------+ | mysq-bin.000001 |      8687 | | mysq-bin.000002 |      1445 | | mysq-bin.000003 |      3966 | | mysq-bin.000004 |       177 | | mysq-bin.000005 |      6405 | | mysq-bin.000006 |       177 | | mysq-bin.000007 |       154 | | mysq-bin.000008 |       154 |

bin log日志的內(nèi)容格式其實就是執(zhí)行SQL命令的反向邏輯，這點和undo log有點類似。一般來說開啟bin  log都會給日志文件設(shè)置過期時間(expire_logs_days參數(shù)，默認永久保存)，要不然日志的體量會非常龐大。

mysql> show variables like 'expire_logs_days'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | expire_logs_days | 0     | +------------------+-------+ 1 row in set  mysql> SET GLOBAL expire_logs_days=30; Query OK, 0 rows affected

bin log主要應(yīng)用于MySQL主從模式(master-slave)中，主從節(jié)點間的數(shù)據(jù)同步;以及基于時間點的數(shù)據(jù)還原。

主從同步

通過下圖MySQL的主從復(fù)制過程，來了解下bin log在主從模式下的應(yīng)用。

• 用戶在主庫master執(zhí)行DDL和DML操作，修改記錄順序?qū)懭隻in log;

• 從庫slave的I/O線程連接上Master，并請求讀取指定位置position的日志內(nèi)容;

• Master收到從庫slave請求后，將指定位置position之后的日志內(nèi)容，和主庫bin log文件的名稱以及在日志中的位置推送給從庫;

• slave的I/O線程接收到數(shù)據(jù)后，將接收到的日志內(nèi)容依次寫入到relay log文件最末端，并將讀取到的主庫bin  log文件名和位置position記錄到master-info文件中，以便在下一次讀取用;

• slave的SQL線程檢測到relay log中內(nèi)容更新后，讀取日志并解析成可執(zhí)行的SQL語句，這樣就實現(xiàn)了主從庫的數(shù)據(jù)一致;

基于時間點還原

我們看到bin log也可以做數(shù)據(jù)的恢復(fù)，而redo log也可以，那它們有什么區(qū)別?

• 層次不同：redo log 是InnoDB存儲引擎實現(xiàn)的，bin log  是MySQL的服務(wù)器層實現(xiàn)的，但MySQL數(shù)據(jù)庫中的任何存儲引擎對于數(shù)據(jù)庫的更改都會產(chǎn)生bin log。

• 作用不同：redo log 用于碰撞恢復(fù)(crash recovery)，保證MySQL宕機也不會影響持久性;bin log  用于時間點恢復(fù)(point-in-time recovery)，保證服務(wù)器可以基于時間點恢復(fù)數(shù)據(jù)和主從復(fù)制。

• 內(nèi)容不同：redo log 是物理日志，內(nèi)容基于磁盤的頁Page;bin log的內(nèi)容是二進制，可以根據(jù)binlog_format參數(shù)自行設(shè)置。

• 寫入方式不同：redo log 采用循環(huán)寫的方式記錄;binlog 通過追加的方式記錄，當(dāng)文件大小大于給定值后，后續(xù)的日志會記錄到新的文件上。

• 刷盤時機不同：bin log在事務(wù)提交時寫入;redo log 在事務(wù)開始時即開始寫入。

bin log 與 redo log 功能并不沖突而是起到相輔相成的作用，需要二者同時記錄，才能保證當(dāng)數(shù)據(jù)庫發(fā)生宕機重啟時，數(shù)據(jù)不會丟失。

relay log(中繼日志)

relay log日志文件具有與bin log日志文件相同的格式，從上邊MySQL主從復(fù)制的流程可以看出，relay  log起到一個中轉(zhuǎn)的作用，slave先從主庫master讀取二進制日志數(shù)據(jù)，寫入從庫本地，后續(xù)再異步由SQL線程讀取解析relay  log為對應(yīng)的SQL命令執(zhí)行。

slow query log

慢查詢?nèi)罩?slow query log): 用來記錄在 MySQL 中執(zhí)行時間超過指定時間的查詢語句，在 SQL  優(yōu)化過程中會經(jīng)常使用到。通過慢查詢?nèi)罩?，我們可以查找出哪些查詢語句的執(zhí)行效率低，耗時嚴(yán)重。

出于性能方面的考慮，一般只有在排查慢SQL、調(diào)試參數(shù)時才會開啟，默認情況下，慢查詢?nèi)罩竟δ苁顷P(guān)閉的?？梢酝ㄟ^以下命令查看是否開啟慢查詢?nèi)罩荆?/p>

mysql> SHOW VARIABLES LIKE 'slow_query%'; +---------------------+--------------------------------------------------------+ | Variable_name       | Value                                                  | +---------------------+--------------------------------------------------------+ | slow_query_log      | OFF                                                    | | slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log | +---------------------+--------------------------------------------------------+

通過如下命令開啟慢查詢?nèi)罩竞?，我發(fā)現(xiàn) iZ2zebfzaequ90bdlz820sZ-slow.log 日志文件里并沒有內(nèi)容啊，可能因為我執(zhí)行的 SQL  都比較簡單沒有超過指定時間。

mysql>  SET GLOBAL slow_query_log=ON; Query OK, 0 rows affected

上邊提到超過 指定時間 的查詢語句才算是慢查詢，那么這個時間閾值又是多少嘞?我們通過 long_query_time 參數(shù)來查看一下，發(fā)現(xiàn)默認是 10  秒。

mysql> SHOW VARIABLES LIKE 'long_query_time'; +-----------------+-----------+ | Variable_name   | Value     | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+

這里我們將 long_query_time 參數(shù)改小為 0.001秒再次執(zhí)行查詢SQL，看看慢查詢?nèi)罩纠锸欠裼凶兓?/p>

mysql> SET GLOBAL long_query_time=0.001; Query OK, 0 rows affected

果然再執(zhí)行 SQL 的時，執(zhí)行時間大于 0.001秒，發(fā)現(xiàn)慢查詢?nèi)罩鹃_始記錄了。

慢查詢?nèi)罩?/p>

general query log

一般查詢?nèi)罩?general query log)：用來記錄用戶的所有操作，包括客戶端何時連接了服務(wù)器、客戶端發(fā)送的所有SQL以及其他事件，比如  MySQL 服務(wù)啟動和關(guān)閉等等。MySQL服務(wù)器會按照它接收到語句的先后順序?qū)懭肴罩疚募?/p>

由于一般查詢?nèi)罩居涗浀膬?nèi)容過于詳細，開啟后 Log  文件的體量會非常龐大，所以出于對性能的考慮，默認情況下，該日志功能是關(guān)閉的，通常會在排查故障需獲得詳細日志的時候才會臨時開啟。

我們可以通過以下命令查看一般查詢?nèi)罩臼欠耖_啟，命令如下：

mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log   | OFF   | +---------------+-------+

下邊開啟一般查詢?nèi)罩静⒉榭慈罩敬娣诺奈恢谩?/p>

mysql> SET GLOBAL general_log=on; Query OK, 0 rows affected mysql> show variables like 'general_log_file'; +------------------+---------------------------------------------------+ | Variable_name    | Value                                             | +------------------+---------------------------------------------------+ | general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log | +------------------+---------------------------------------------------+

執(zhí)行一條查詢 SQL 看看日志內(nèi)容的變化。

mysql> select * from t_config; +---------------------+------------+---------------------+---------------------+ | id                  | remark     | create_time         | last_modify_time    | +---------------------+------------+---------------------+---------------------+ | 1325741604307734530 | 我是廣播表 | 2020-11-09 18:06:44 | 2020-11-09 18:06:44 | +---------------------+------------+---------------------+---------------------+

我們看到日志內(nèi)容詳細的記錄了所有執(zhí)行的命令、SQL、SQL的解析過程、數(shù)據(jù)庫設(shè)置等等。

一般查詢?nèi)罩?/p>

error log

錯誤日志(error log): 應(yīng)該是 MySQL 中最好理解的一種日志，主要記錄 MySQL 服務(wù)器每次啟動和停止的時間以及診斷和出錯信息。

默認情況下，該日志功能是開啟的，通過如下命令查找錯誤日志文件的存放路徑。

mysql> SHOW VARIABLES LIKE 'log_error'; +---------------+----------------------------------------------------------------+ | Variable_name | Value                                                          | +---------------+----------------------------------------------------------------+ | log_error     | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err | +---------------+----------------------------------------------------------------+

注意：錯誤日志中記錄的可并非全是錯誤信息，像 MySQL 如何啟動 InnoDB 的表空間文件、如何初始化自己的存儲引擎，初始化 buffer pool  等等，這些也記錄在錯誤日志文件中。

“MySQL為什么不會丟失數(shù)據(jù)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！