• 记一次MySQL主从双写导致的数据丢失问题

    记一次MySQL主从双写导致的数据丢失问题

    目录

    1. 问题起源

    不久前用户反馈部门的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在某些特殊参数或者条件下的正常表现？对于这个问题，本文将可能的给出这个问题的详细解释和分析。

    2. 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)的行为？MySQL5.7之后引入了一个新的参数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，在不是可以标识数据行或者有变更的情况下不做记录。

    可以看出binlog_row_image可以有效控制Binlog的大小，但是如果要保证数据的一致性，最好的值就是设置为full。

    2.2 slave_rows_search_algorithms 参数

    前文提到了IMAGE 与binlog_row_image 相关的内容。本节开始将主要介绍Relay Log的重放的时候，对于被重放的记录的查找逻辑。对于DELETE和UPDATE操作，需要先对数据库中的记录进行检索以确定需要执行Binlog重放的数据。如果从库的表上没有主键或唯一键时，则需要根据每一个行记录BEFOR IMAGE在所有数据中进行一次全表扫描。在大多数情况下这种开销非常巨大，会导致从库和主库的巨大延迟。从MySQL5.6 开始提供了参数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文档也给出了如下的说明：

    Index used / option value INDEX_SCAN,HASH_SCAN INDEX_SCAN,TABLE_SCAN
    Primary key or unique key Index scan Index scan
    (Other) Key Hash scan over index Index scan
    No index Hash scan Table scan
    • 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。如果没有主键或者唯一键，则通过二级索引完成这个工作。如果什么都没有，则使用全表扫描的方式。

    1. 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有巨大延迟。

    3. 总结

    至此前文提出的几个问题都基本清楚了，可以总结出如下的几点内容：

    1. 在有主键或者唯一键的情况下，Slave 重放Binlog并不会去比较检索到的记录的每一列是否和BI相同，因此如果Slave和Master存在数据不一致，会直接覆盖Slave的数据而不会报错。
    2. 在没有主键或者唯一键的情况下，Hash Scan / Hash Scan Over Index 的执行效率 在理论上分析高于 Table Scan 和Index Scan 。
    3. 在没有主键或者唯一键的情况下，Slave选择的二级索引是第一个所有的列都在BI中存在的索引，不一定是Master执行计划所选择的索引。

    最后本文所有分析的源码都是基于mysql-5.7.28版本。限于作者的水平有限，如果文章中有错误之处，望大家不吝指正。

    4. 参考文献

    [1]. 从库数据的查找和参数slave_rows_search_algorithms. https://cloud.tencent.com/developer/article/1492071

    [2]. MySQL无主键延迟优化（slave_rows_search_algorithms). https://www.centos.bz/2018/01/mysql无主键延迟优化（slave_rows_search_algorithms）

    [3]. MySQL 5.7贴心参数之binlog_row_image. https://www.cnblogs.com/gomysql/p/6155160.html

    [4]. 技术分享 | delete大表slave回放巨慢的问题分析. https://zhuanlan.zhihu.com/p/73822875

    [5]. Causes and Workarounds for Slave Performance Too Slow with Row-Based Events. https://www.percona.com/blog/2018/05/03/slave-performance-too-slow-with-row-based-events-causes-and-workarounds/

    [6]. 技术分享 | 从库数据的查找和参数 slave_rows_search_algorithms. https://zhuanlan.zhihu.com/p/82160262

    [7]. 技术分享 | HASH_SCAN BUG 之迷惑行为大赏. https://opensource.actionsky.com/20190531-hash_scan-bug/
  • 相关阅读:
    Git与GitHub（利用git上传本地文件到GitHub上面）
    PHP之上传文件upload.php
    PHP之数据库连接配置文件
    MUI 之picker，dialog，a标签——刷新页面问题（保留picker选中的数据）
    MUI自定义select down 下拉框
    Google按ESC退出全屏（带iframe网站）解决问题方案
    提高开发效率 -> 图片
    sublime text
    头脑风暴
    http://www.uupoop.com/ps/
  • 原文地址：https://www.cnblogs.com/bush2582/p/13609238.html
Copyright © 2020-2023  润新知