MySQL中主從雙寫導致數據丟失如何解決

本篇文章給大家分享的是有關 MySQL中主從雙寫導致數據丟失如何解決，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

 一、問題起源

不久前用戶反饋部門的 MySQL  數據庫發生了數據更新丟失。為了解決這個問題，當時對用戶使用的場景進行了分析。發現可能是因為用戶在兩臺互為主從的機器上都進行了寫入導致的數據丟失。

如圖所示，是正常和異常情況下應用寫入數據庫的示例。隨后在更加深入調查問題的過程中，DBA 發現了故障引起數據丟失的原因：

如圖 1-2 所示為故障具體過程的還原。從圖中可以看出在第 3 步 DP 上的寫入操作，在恢復 DA 到 DP 的同步之后，覆蓋了第 4 步 DA  上的寫入。因此導致了最終兩臺機器數據不一致，并且有一部分數據更新丟失。

在這里相信讀者都會有一個疑問， 在第 4 步之后數據變成了(id : 1 ,name : name4)，那么第 3 步操作的時候寫入的語句是 update  t20200709 set name = 'name3' where id =1 and name='name2'，在第 5 步恢復同步的時候這條語句在 DA  上重放應該不會被成功執行，畢竟 Where 條件都不匹配了。而且在 DP 產生的 Binlog 中，確實也記錄了 SQL 語句的 Where  條件，無論從哪個角度上來看第 3 步的 SQL 語句都不應該被重放成功。

### UPDATE `test`.`t20200709` ### WHERE ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ ###   @2='name2' /* VARSTRING(255) meta=255 nullable=1 is_null=0 */ ### SET ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ ###   @2='name3' /* VARSTRING(255) meta=255 nullable=1 is_null=0 */ # at 684315240

那么這個問題難道是 MySQL 自身的 Bug，抑或是 MySQL  在某些特殊參數或者條件下的正常表現?對于這個問題，本文將可能的給出這個問題的詳細解釋和分析。

二、Row 格式下 RelayLog 的重放

2.1 BEFOR IMAGE && AFTER IMAGE && binlog_row_image 參數

在最后解釋本文最初提出的問題前，需要先來看下 RelayLog 是怎么被重放的。一般情況下，當有 DML 語句變更數據庫中的數據的時候，Binlog  會記錄下事件描述信息、BEFORE IMAGE 和 AFTER IMAGE 等信息。在這里有一個概念 BEFORE IMAGE 和 AFTER IMAGE  需要先介紹下：

1. BEFORE IMAGE : 前鏡像，既數據修改前的樣子。

2. AFTER IMAGE : 后鏡像，既數據修改后的樣子。

為了方便理解，這里貼一個 Binlog 的例子。假設當前有表 t20200709，然后表中數據如下：

mysql> select * from t20200709 ; +----+-------+ | id | name  | +----+-------+ |  1 | name4 | +----+-------+ 1 rows in set (0.00 sec)

之后執行 SQL 語句 update t20200709 set name =1 where id = 1;

mysql> update t20200709 set name =1 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

然后來看下 Binlog 中的記錄：

#200715 17:28:28 server id 15218  end_log_pos 400 CRC32 0xe4dedec0     Update_rows: table id 4034114356 flags: STMT_END_F ### UPDATE `test`.`t20200709` ### WHERE ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ ###   @2='name4' /* VARSTRING(255) meta=255 nullable=1 is_null=0 */ ### SET ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ ###   @2='1' /* VARSTRING(255) meta=255 nullable=1 is_null=0 */ # at 400

可以見得，在修改之前 name 字段的值是 name4，在 Binlog 中用 Where 條件 @2='name4' 來指明，而修改后的 name  的值是 '1'，在 Binlog 中就是 @2='1' 來指明。因此 BEFORE IMAGE 就是 Binlog 中 WHERE 到 SET 的部分。而  AFTER IMAGE 就是 SET 之后的部分。

那么 DELETE，UPDATE 和 INSERT 語句被記錄在 Binlog 中的時候，是否都有 BEFORE IMAGE 和 AFTER  IMAGE?其實不是所有的 DML 事件類型都擁有兩個 IMAGE 的，參見圖 2-2 可知只有 UPDATE 語句，會同時擁有 BEFORE IMAGE 和  AFTER IMAGE。

BEFOR IMAGE 和 AFTER IMAGE 默認會記錄所有的列的變更，因此會導致 Binlog 的內容變得很大。那么有沒有參數可以控制  IMAGE(對于 BEFOR IMAGE 和 AFTER IMAGE 以下合并簡稱為 IMAGE)的行為?MySQL 5.7 之后引入了一個新的參數  binlog_row_image 。

參數說明：

binlog_row_image:https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_row_image

用于控制 IMAGE 的行為。binlog_row_image 參數的值有三個：

1. full：Log all columns in both the before image and the after image.  既所有的列的值的變更，都會在 IMAGE 中記錄。系統默認是 full。

2. minimal：Log only those columns in the before image that are required to  identify the row to be changed; log only those columns in the after image where  a value was specified by the SQL statement, or generated by auto-increment.  BEFOR IMAGE 只記錄哪些能夠唯一標識數據的列，比如主鍵，唯一鍵等。AFTER IMAGE 只記錄了變更的列。可以看出，minimal 會有效的減少  Binlog 的大小。

3. noblob：Log all columns (same as full), except for BLOB and TEXT columns  that are not required to identify rows, or that have not changed. 對于其他列的行為都和  full 參數一樣。但是對于 BLOB 和 TEXT，在不是可以標識數據行或者有變更的情況下不做記錄。

參數說明：

BLOB:https://dev.mysql.com/doc/refman/5.7/en/blob.html

TEXT:https://dev.mysql.com/doc/refman/5.7/en/blob.html

可以看出 binlog_row_image 可以有效控制Binlog的大小，但是如果要保證數據的一致性，最好的值就是設置為 full。

2.2 slave_rows_search_algorithms 參數

前文提到了 IMAGE 與 binlog_row_image 相關的內容。本節開始將主要介紹 Relay Log  的重放的時候，對于被重放的記錄的查找邏輯。對于 DELETE 和 UPDATE 操作，需要先對數據庫中的記錄進行檢索以確定需要執行 Binlog  重放的數據。如果從庫的表上沒有主鍵或唯一鍵時，則需要根據每一個行記錄 BEFOR IMAGE  在所有數據中進行一次全表掃描。在大多數情況下這種開銷非常巨大，會導致從庫和主庫的巨大延遲。從 MySQL 5.6 開始提供了參數  slave_rows_search_algorithms

參數說明：slave_rows_search_algorithms:https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_slave_rows_search_algorithms

用于控制在 Relay Log 執行重放的時候對于記錄的檢索行為。其基本的思路是收集每條記錄的 BEFOR IMAGE 信息，然后根據 BEFOR  IMAGE 的信息在被重放的表中檢索對應的記錄。根據 MySQL 的文檔，檢索數據的方式有如下的幾種：

1. INDEX_SCAN

2. TABLE_SCAN

3. HASH_SCAN

如上三個方式可以兩兩組合并賦值給 slave_rows_search_algorithms 參數。MySQL 文檔也給出了如下的說明：

• The default value is INDEX_SCAN,TABLE_SCAN, which means that all searches  that can use indexes do use them, and searches without any indexes use table  scans.

• To use hashing for any searches that do not use a primary or unique key, set  INDEX_SCAN,HASH_SCAN. Specifying INDEX_SCAN,HASH_SCAN has the same effect as  specifying INDEX_SCAN,TABLE_SCAN,HASH_SCAN, which is allowed.

• Do not use the combination TABLE_SCAN,HASH_SCAN. This setting forces hashing  for all searches. It has no advantage over INDEX_SCAN,HASH_SCAN, and it can lead  to “record not found” errors or duplicate key errors in the case of a single  event containing multiple updates to the same row, or updates that are  order-dependent.

1. INDEX_SCAN,TABLE_SCAN: 可以看出在默認的情況下，既 INDEX_SCAN,TABLE_SCAN  如果有主鍵或者唯一鍵，則通過主鍵或者唯一鍵來查詢數據并重放 AFTER  IMAGE。如果沒有主鍵或者唯一鍵，則通過二級索引完成這個工作。如果什么都沒有，則使用全表掃描的方式。

2. INDEX_SCAN,HASH_SCAN : 在表有主鍵或者唯一鍵的情況下， INDEX_SCAN,HASH_SCAN  配置也是使用的主鍵或者唯一鍵去定位數據。在表有二級索引或者完全沒有索引的情況下會使用 HASH_SCAN 的方法。

可以見得 Slave 檢索需要重放的數據的時候，三個檢索方式的優先級是 Index Scan > Hash Scan > Table  Scan

相信讀者在這里會有 2 個疑問：

1. Hash Scan 的原理是什么?它和 Table Scan 以及 Index Scan 有什么區別?文檔中還提到了 Hash scan over  index 這個和 Index 有什么關系?

2. 前文提到在表沒有主鍵或者唯一鍵的時候，會通過二級索引來定位數據。假設表中有 N 個二級索引(包括單列索引和聯合索引)，哪個二級索引會被選中?

2.3 Hash Scan && Table Scan && Index Scan 實現

分析 MySQL 源碼可知最后決定使用哪個檢索方式是在函數  Rows_log_event::decide_row_lookup_algorithm_and_key 里面實現的。

9745 void 9746 Rows_log_event::decide_row_lookup_algorithm_and_key() 9747 { 9748 ... ...  9781   /* PK or UK => use LOOKUP_INDEX_SCAN */ 9782   this->m_key_index= search_key_in_table(table, cols, (PRI_KEY_FLAG | UNIQUE_KEY_FLAG)); 9783   if (this->m_key_index != MAX_KEY) 9784   { 9785     DBUG_PRINT("info", ("decide_row_lookup_algorithm_and_key: decided - INDEX_SCAN")); 9786     this->m_rows_lookup_algorithm= ROW_LOOKUP_INDEX_SCAN; 9787     goto end; 9788   } ... ...  9790 TABLE_OR_INDEX_HASH_SCAN: ... ...  9808 TABLE_OR_INDEX_FULL_SCAN: ... ...  9827 end: ... ...

在 9782 行會先檢索表中是否有主鍵和唯一鍵。之后在 TABLE_OR_INDEX_HASH_SCAN 和  TABLE_OR_INDEX_FULL_SCAN 決定最后使用哪種檢索方式。在 do_apply_event 函數中，會根據  decide_row_lookup_algorithm_and_key 的結果去調用函數：

11286     switch (m_rows_lookup_algorithm) 11287     { 11288       case ROW_LOOKUP_HASH_SCAN: 11289         do_apply_row_ptr= &Rows_log_event::do_hash_scan_and_update; 11290         break; 11291 11292       case ROW_LOOKUP_INDEX_SCAN: 11293         do_apply_row_ptr= &Rows_log_event::do_index_scan_and_update; 11294         break; 11295 11296       case ROW_LOOKUP_TABLE_SCAN: 11297         do_apply_row_ptr= &Rows_log_event::do_table_scan_and_update; 11298         break; 11299 11300       case ROW_LOOKUP_NOT_NEEDED: 11301         DBUG_ASSERT(get_general_type_code() == binary_log::WRITE_ROWS_EVENT); 11302 11303         /* No need to scan for rows, just apply it */ 11304         do_apply_row_ptr= &Rows_log_event::do_apply_row; 11305         break; 11306 11307       default: 11308         DBUG_ASSERT(0); 11309         error= 1; 11310         goto AFTER_MAIN_EXEC_ROW_LOOP; 11311         break; 11312     }

可以見得：

1. do_hash_scan_and_update: 對應 hash_scan 方式。

2. do_index_scan_and_update: 對應 index_scan 方式。

3. do_table_scan_and_update:對應 table_scan 方式。

接下來分別介紹下這三個函數所完成的內容。

2.3.1 do_hash_scan_and_update

do_hash_scan_and_update 函數主要實現了 Hash Scan 檢索數據的功能。在實現方式上又可以分為 H --> Hash  Scan 和 Hi --> Hash over Index 兩種方式。首先來看下 Hash Scan 的實現方法，圖 2-5 給出 Hash Scan  的實現邏輯。

可以見得 Binlog 中的 BI 在 Slave 上會被處理到一個 Hash  表中。因為沒有合適的索引可以使用，所以使用全表掃描的方式每獲取一條記錄就根據記錄的值計算一個 hash 值，然后在 BI 的 Hash 表中匹配。如果匹配到了  BI，則重放并刪除 Hash 表中的記錄。

如果 test06 表中 id 列上有索引，那么在 Slave 重放的使用會使用 Hi --> Hash over index 的方式。如圖 2-6  所示給出了 Hash over Index 方式(以下均簡稱 Hi)的實現邏輯。

可以見得如果通過 Hi 方式進行重放，則會對使用的二級索引生成一個 m_distinct_keys 結構，這個結構存放著這個 BI  中這個索引所有的去重值。然后對于 Slave 上的 test06 表通過 m_distinct_keys 中的每一個值在二級索引上進行遍歷，遍歷獲取的記錄與  m_hash 中的結果對比并執行重放邏輯。

ps : 對于 Hash Scan 方式還要一個比較迷惑的特性，讀者可以參考下這篇文章[技術分享 | HASH_SCAN BUG 之迷惑行為大賞]

2.3.2 do_index_scan_and_update

Index Scan 方式會通過索引檢索 Slave 上需要重放的數據。通過索引檢索數據的方式又可以分為：

1. 通過主鍵/唯一鍵索引檢索數據。

2. 通過二級索引檢索數據。

在通過主鍵或者唯一鍵索引檢索數據的時候會調用 do_index_scan_and_update  函數，在函數邏輯中直接通過主鍵/唯一鍵索引返回了記錄然后重放 Binlog。

而在通過二級索引檢索數據的時候，會對二級索引返回的數據與 BI 中每一條記錄做比較，如果一致就會重放 Binlog。

至此可以發現 Index Scan 下對于主鍵/唯一鍵和二級索引的實現邏輯有一些不同。對于主鍵/唯一鍵，對于索引到的記錄并不會和 BI  中的每一個列做比較，而二級索引獲取到的數據會與 BI 中每一個列做比較，如果不一致而不會重放并報錯。

2.3.3 do_table_scan_and_update

Table Scan 的實現相對簡單，如果沒有任何的索引可以使用，只能通過全表掃描的方式獲取每一行數據并和BI中的每一行做比較。因此如果 Slave  上的數據和 Master 上的數據不一致，也會如圖 2-9 中所示一樣報錯。關于 Table Scan 更加具體的實現方式，讀者可以參考 MySQL 源碼  sql/log_event.cc 文件中的 do_table_scan_and_update 函數，在這里就不過多的展開。

2.3.4 小結

至此可以回答本文之前提出的這個問題了：

Hash scan 方法的原理是什么?它和 Table scan 以及 Index scan 有什么區別?文檔中還提到了 Hash scan over  index 這個和 Index 又有什么關系?

可以見得，Hash Scan 的原理是將 BI 每一行的內容都放入一個 Hash 表中。如果可以使用二級索引(既 Hash scan over index  這個方式)，則額外的對 BI 中二級索引的值生成一個 Hash 結構，并且將 BI 中二級索引列的去重值放入這個 Hash  結構中。之后不管是通過全表掃描還是索引的方式獲取數據，都會通過 Hash 結構去定位 BI 中的數據。對于 Table Scan 和 Index Scan  在獲取表中的每一行之后，都需要去和 BI 中的記錄做一次查找和比較(有主鍵或者唯一鍵的時候不做比較)，而 BI 的每一行并沒有生成類似于 Hash  的結構，因此從算法的時間復雜度效率上來說是屬于 O(n^2) 的。而 Hash Scan 在獲取一條記錄之后也需要根據 BI 生成的 Hash  結構中查找記錄，但是對于 Hash 結構的查找來說效率是 O(1)，因此可以忽略不計。由此可以看出，在沒有主鍵的情況下 Hi 和 Ht 方式的效率是會比  Table Scan 和 Index Scan 來的高一些。

同時到這里，也可以回答本文開頭的問題，為什么當前表中的記錄有一列值已經和 BI 中的記錄不一致了，Binlog 中的操作還會重放。原因就是因為在默認的  INDEX_SCAN,TABLE_SCAN 方式下，對于有主鍵/唯一鍵的表不會去比較 BI 中的記錄是否和檢索到的數據一致。

2.4 Hash Scan Over Index && Index Scan 中二級索引的選擇

前文提到了在有二級索引的情況下，Hash Scan 和 Index Scan 都會選擇二級索引進行掃描。如果表中存在多個二級索引，MySQL  會選擇哪個?通過源碼分析，最后驚訝的發現，在 binlog_row_image 參數是 Full 的情況下，如果表中存在多個二級索引，MySQL  會默認選擇使用第一個索引進行重放。在 decide_row_lookup_algorithm_and_key  函數中，除了決定了使用哪種方式檢索數據以外(例如使用 Hash Scan 還是 Table Scan)，也決定了后續使用哪個索引。

如圖 2-10 給出了選擇二級索引的時候的邏輯。可以發現如果在遍歷的過程中，找到了第一個所有的列都在 BI 中 key，則會使用這個  key。給出一個例子，test06 的表結構和表中數據如下：

*************************** 1. row ***************************        Table: test06 Create Table: CREATE TABLE `test06` (   `id` int(11) NOT NULL,   `name` varchar(255) DEFAULT NULL,   `c1` int(11) DEFAULT NULL,   KEY `k1` (`id`),   KEY `k2` (`id`,`name`),   KEY `k3` (`c1`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.13 sec)  mysql> select * from test06 ; +------+-------+------+ | id   | name  | c1   | +------+-------+------+ | 2582 | name3 |    1 | | 2582 | name4 |    1 | |    1 | name1 |    0 | |    1 | name2 |    0 | |    1 | name3 |    0 | +------+-------+------+ 5 rows in set (0.00 sec)

在 Master 上執行 SQL，同時 Master 上的執行計劃如下：

delete from test06 where id = 1 and name ='name3' and c1=0; mysql> explain delete from test06 where id = 1 and name ='name3' and c1=0; +----+-------------+--------+------------+-------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref         | rows | filtered | Extra       | +----+-------------+--------+------------+-------+---------------+------+---------+-------------+------+----------+-------------+ |  1 | DELETE      | test06 | NULL       | range | k1,k2,k3      | k2   | 772     | const,const |    1 |   100.00 | Using where | +----+-------------+--------+------------+-------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set (0.00 sec)

可以見得，在 Master 上優化器選擇了 k2 這個聯合索引。通過 GDB 跟蹤 Slave 的進程，在 log_event.cc 第 9733  行打斷點：

9714   if (key_type & MULTIPLE_KEY_FLAG && table->s->keys) 9715   { 9716     DBUG_PRINT("debug", ("Searching for K.")); 9717     for (key=0,keyinfo= table->key_info ; 9718          (key < table->s->keys) && (res == MAX_KEY); 9719          key++,keyinfo++) 9720     { 9721       /* 9722         - Skip innactive keys 9723         - Skip unique keys without nullable parts 9724         - Skip indices that do not support ha_index_next() e.g. full-text 9725         - Skip primary keys 9726       */ 9727       if (!(table->s->keys_in_use.is_set(key)) || 9728           ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) || 9729           !(table->file->index_flags(key, 0, true) & HA_READ_NEXT) || 9730           (key == table->s->primary_key)) 9731         continue; 9732 9733       res= are_all_columns_signaled_for_key(keyinfo, bi_cols) ? 9734            key : MAX_KEY; 9735 9736       if (res < MAX_KEY) 9737         DBUG_RETURN(res); 9738     } 9739     DBUG_PRINT("debug", ("Not all columns signaled for K.")); 9740   }

可以觀察到這時候 m_key_index 的值是 0，并且觀察 keyinfo 變量的值為：

(gdb) print *keyinfo $4 = {key_length = 4, flags = 0, actual_flags = 0, user_defined_key_parts = 1, actual_key_parts = 1, unused_key_parts = 0, usable_key_parts = 1, block_size = 0, algorithm = HA_KEY_ALG_UNDEF, {     parser = 0x0, parser_name = 0x0}, key_part = 0x7f2f4c015a00, name = 0x7f2f4c012bb1 "k1", rec_per_key = 0x7f2f4c012bc0, m_in_memory_estimate = -1, rec_per_key_float = 0x7f2f4c012bf8, handler = {     bdb_return_if_eq = 0}, table = 0x7f2f4c92d1a0, comment = {str = 0x0, length = 0}}

接下來，刪除 k1 這個索引，再來觀察下 m_key_index 和 keyinfo 的值。

(gdb) print *keyinfo $7 = {key_length = 772, flags = 64, actual_flags = 64, user_defined_key_parts = 2, actual_key_parts = 2, unused_key_parts = 0, usable_key_parts = 2, block_size = 0, algorithm = HA_KEY_ALG_UNDEF, {     parser = 0x0, parser_name = 0x0}, key_part = 0x7f2f4c92b680, name = 0x7f2f4c92e7d1 "k2", rec_per_key = 0x7f2f4c92e7d8, m_in_memory_estimate = -1, rec_per_key_float = 0x7f2f4c92e808, handler = {     bdb_return_if_eq = 0}, table = 0x7f2f4ca9fd90, comment = {str = 0x0, length = 0}}

可以發現刪除了 k1 之后，Slave 上就選擇 k2 這個索引，和  Master上的執行計劃選擇的索引一致了。通過前面的源碼分析和調試跟蹤可以發現，MySQL 在 Slave  重放數據的時候(沒有主鍵和唯一鍵的情況)，選擇的索引是第一個所有的列都在 BI 中存在的索引。因此可能存在 Slave 上選擇的索引不是最優的導致 Slave  和 Master 有巨大延遲。

三、總結

至此前文提出的幾個問題都基本清楚了，可以總結出如下的幾點內容：

1. 在有主鍵或者唯一鍵的情況下，Slave 重放 Binlog 并不會去比較檢索到的記錄的每一列是否和BI相同，因此如果 Slave 和 Master  存在數據不一致，會直接覆蓋 Slave 的數據而不會報錯。

2. 在沒有主鍵或者唯一鍵的情況下，Hash Scan / Hash Scan Over Index 的執行效率 在理論上分析高于 Table Scan  和Index Scan 。

3. 在沒有主鍵或者唯一鍵的情況下，Slave 選擇的二級索引是第一個所有的列都在 BI 中存在的索引，不一定是 Master  執行計劃所選擇的索引。

以上就是 MySQL中主從雙寫導致數據丟失如何解決，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。