【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
SET TIMESTAMP=1399790652/*!*/;
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);
Query OK, 1 row affected (0.00 sec)
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中記錄的資訊:
BEGIN
/*!*/;
# 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
COMMIT/*!*/;
從解析的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';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| 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 */ |
| 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型 ,一勞永逸。
五 參考文章
[1]
[2]
[3] mysql TableMap id遞增問題
[4] MySQL Binlog中TABLE ID原始碼分析
[5] 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
SET TIMESTAMP=1399790652/*!*/;
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);
Query OK, 1 row affected (0.00 sec)
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中記錄的資訊:
BEGIN
/*!*/;
# 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
COMMIT/*!*/;
從解析的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';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| 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 */ |
| 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型 ,一勞永逸。
五 參考文章
[1]
[2]
[3] mysql TableMap id遞增問題
[4] MySQL Binlog中TABLE ID原始碼分析
[5] MySQL table_id原理及風險分析
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30633755/viewspace-2127642/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【MySQL】七、再說MySQL中的 table_idMySql
- MySQL實戰 | 06/07 簡單說說MySQL中的鎖MySql
- MySQL效能優化 - 別再只會說加索引了MySql優化索引
- 【MySQL】MySQL中的鎖MySql
- 說說Mongodb 與 MySQL的那些事MongoDBMySql
- 說說MySQL索引相關MySql索引
- MySql Binlog 說明 & Canal 整合MySql的更新異常說明 & MySql Binlog 常用命令彙總MySql
- 【MySQL】MySQL中的鎖機制MySql
- MySql(三) MySql中的鎖機制MySql
- 【MySQL】白話說MySQL(五),資料的匯出與匯入MySql
- 【大廠面試05期】說一說你對MySQL中鎖的瞭解?面試MySql
- MySQL中的_rowidMySql
- mysql中的xaMySql
- MySQL中的排序MySql排序
- mysql字符集說明MySql
- 關於MySQL中的8個 character_set 變數說明MySql變數
- 誰再說學不會 MySQL 資料庫,就把這個給他扔過去!MySql資料庫
- MySQL EXPLAIN命令的主要專案說明MySqlAI
- mysql的my.cnf解釋說明MySql
- MySQL:關於ICP特性的說明(未完)MySql
- 我想說:mysql 的 join 真的很弱MySql
- (14)mysql 中的事件MySql事件
- MYSQL中replace into的用法MySql
- MySQL中limit的用法MySqlMIT
- MySQL中的自增列MySql
- MySQL中的Statistics等待MySql
- MySQL中的source命令MySql
- MYSQL中的那些鎖MySql
- MySQL 中的 EXPLAIN 命令MySqlAI
- Mysql中的小技巧MySql
- mySQL中replace的用法MySql
- MySQL中的IO流MySql
- 阿里一面,說說你對Mysql死鎖的理解阿里MySql
- mysql支援原生json使用說明MySqlJSON
- 細說 MySQL 之 MEM_ROOTMySql
- 再談mysql鎖機制及原理—鎖的詮釋MySql
- mysql中 routineMySql
- 對Mysql中的read_only 只讀屬性做簡要說明MySql
- 【Mysql】MySQL 5.6中如何定位DDL被阻塞的問題MySql