【MySQL】再說MySQL中的 table_id

【背景】

最近線上一個執行個體出現了主從資料不一緻的情況,也即從庫丢失資料的情況。根本原因:"由于table_list->table_id為uint,而m_table_id為ulong，主庫上assign的table map　id 總是一直遞增的

當超過2^32後，備庫出現溢出，導緻row模式下備庫對應table id的事件全部丢失,産生主備不一緻。"

【問題分析】

一 table_id 介紹

    當MySQL 開啟日志模式時，binlog會記錄所有對資料庫的變更操作。binlog 分兩種模式 statement 模式和row 模式。

當資料庫的binlog format 是statement 模式時

例子：資料庫中執行 一條語句

root@rac2 [yangyi]> insert into t1 values(9);                

Query OK, 1 row affected (0.00 sec)

root@rac2 [yangyi]> show binlog events in 'mysql-bin.000003';

+------------------+-----+-------------+-----------+-------------+----------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                   |

| mysql-bin.000003 |   4 | Format_desc |         2 |         106 | Server ver: 5.1.68-log, Binlog ver: 4  |

| mysql-bin.000003 | 106 | Query       |         2 |         176 | BEGIN                                  |

| mysql-bin.000003 | 176 | Query       |         2 |         265 | use `yangyi`; insert into t1 values(8) |

| mysql-bin.000003 | 265 | Xid         |         2 |         292 | COMMIT /* xid=12 */                    |

| mysql-bin.000003 | 292 | Query       |         2 |         369 | use `yangyi`; flush tables             |

| mysql-bin.000003 | 369 | Query       |         2 |         439 | BEGIN                                  |

| mysql-bin.000003 | 439 | Query       |         2 |         528 | use `yangyi`; insert into t1 values(9) |

| mysql-bin.000003 | 528 | Xid         |         2 |         555 | COMMIT /* xid=15 */                    |

8 rows in set (0.00 sec)

binlog 的log event 記錄如下:

#140511 14:44:12 server id 2  end_log_pos 439   Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1399790652/*!*/;

BEGIN

/*!*/;

# at 439

#140511 14:44:12 server id 2  end_log_pos 528   Query   thread_id=1     exec_time=0     error_code=0

insert into t1 values(9)

# at 528

#140511 14:44:12 server id 2  end_log_pos 555   Xid = 15

COMMIT/*!*/;

從日志分析來看 ，DML會記錄為原始的SQL，也就是記錄在QUERY_EVENT中。 

當資料庫的binlog format 是row模式時

執行insert 操作

root@rac2 [yangyi]> insert into t1 values(6);                

root@rac2 [yangyi]> show binlog events in 'mysql-bin.000002';          

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

| mysql-bin.000002 |   4 | Format_desc |         2 |         106 | Server ver: 5.1.68-log, Binlog ver: 4 |

| mysql-bin.000002 | 106 | Query       |         2 |         176 | BEGIN                                 |

| mysql-bin.000002 | 176 | Table_map   |         2 |         219 | table_id: 18 (yangyi.t1)              |

| mysql-bin.000002 | 219 | Write_rows  |         2 |         253 | table_id: 18 flags: STMT_END_F        |

| mysql-bin.000002 | 253 | Xid         |         2 |         280 | COMMIT /* xid=61 */                   |

5 rows in set (0.00 sec)

binlog中記錄的資訊：

# at 176

# at 219

#140511 14:31:43 server id 2  end_log_pos 219   Table_map: `yangyi`.`t1` mapped to number 18

#140511 14:31:43 server id 2  end_log_pos 253   Write_rows: table id 18 flags: STMT_END_F

BINLOG '

TxlvUxMCAAAAKwAAANsAAAAAABIAAAAAAAEABnlhbmd5aQACdDEAAQMAAQ==

TxlvUxcCAAAAIgAAAP0AAAAAABIAAAAAAAEAAf/+BgAAAA==

'/*!*/;

### INSERT INTO `yangyi`.`t1`

### SET

###   @1=6 /* INT meta=0 nullable=1 is_null=0 */

# at 253

#140511 14:31:43 server id 2  end_log_pos 280   Xid = 61

   從解析的binlog中可以看出row模式下,DML操作會記錄為:TABLE_MAP_EVENT+ROW_LOG_EVENT（包括WRITE_ROWS_EVENT ，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT).

   為什麼一個update在ROW模式下需要分解成兩個event：一個Table_map，一個Update_rows。我們想象一下，一個update如果更新了10000條資料，那麼對應的表結構資訊是否需要記錄10000次?其實是對同一個表的操作，是以這裡binlog隻是記錄了一個Table_map用于記錄表結構相關資訊,而後面的Update_rows記錄了更新資料的行資訊。他們之間是通過table_id來聯系的。 

二 table_id 的特性

  1 table_id 并不是固定的,它是當表被載入記憶體(table_definition_cache)時，臨時配置設定的，是一個不斷增長的變量。  

  2 當有新的table變更時，在cache中沒有，就會觸發一次load table def的操作，此時就會在原先最後一次table_id基礎上+1，做為新的table def的id。

  3 flush tables，之後對表的更新操作也會觸發table_id 的增長。

  4 如果table def cache過小，就會出現頻繁的換入換出，進而導緻table_id增長比較快。

例子

root@rac2 [yangyi]> show binlog events in 'mysql-bin.000002';

| mysql-bin.000002 | 280 | Query       |         2 |         357 | use `yangyi`; flush tables            |

| mysql-bin.000002 | 357 | Query       |         2 |         427 | BEGIN                                 |

| mysql-bin.000002 | 427 | Table_map   |         2 |         470 | table_id: 19 (yangyi.t1)              |

| mysql-bin.000002 | 470 | Write_rows  |         2 |         504 | table_id: 19 flags: STMT_END_F        |

| mysql-bin.000002 | 504 | Xid         |         2 |         531 | COMMIT /* xid=65 */                   |

10 rows in set (0.00 sec)

三 table_id在主從複制過程中轉變  

     每一個dml操作表的資訊都被會記錄table_mapping的hash資料結構中,hash的key就是ulong型的table_id,hash的值就是TABLE*的資料結構(包含了表的各種資訊，包括資料庫名，表名，字段數，字段類型等)，通過set_table()方法來hash,通過get_table()方法來根據table_id獲得對應的表資訊。

    當主庫的日志傳遞到備庫時，每一個log_event都是通過do_apply_event()方法來将event應用到本地資料庫中。在apply relay log中的event時,do_apply_event()将ulong型的m_table_id(binlog記錄的table_id)指派給RPL_TABLE_LIST結構中的uint型的table_id。核心問題出現了: 如果binlog 中的table_id 的值大于max(uint)，在變量傳遞是，就會發生截斷。

而MySQL内部使用set_table(table_id)構造hash，使用get_table(m_table_id)從hash表中取值,在兩個階段用到的key因為發生了資料截斷,是以必然也就不能取到預期的值。也就是說之前用uint型的table_id建構出來的key-value的hash對，用ulong型的m_table_id是無法查詢到的。

四 風險與解決

  從第二，三點我們知道當table_id 過快增長,會導緻從庫應用binlog無法解析到對應的表，造成資料不一緻的情況。

解決方法:

 1 加大 table cache 的大小。

 2 重新開機主庫使table_id 歸0，缺點 成本比較高，出現此問題的時候，主備已經不一緻，線上環境 不能完成切換。

 3 修改MySQL源碼,将 RPL_TABLE_LIST結構中的uint型的table_id修改為ulong型 ，一勞永逸。

五 參考文章