Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand table_id in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

An introduction to table_id

When MySQL turns on logging mode, binlog records all changes to the database. Binlog is divided into two modes: statement mode and row mode.

When the binlog format of the database is statement mode

Example: execute a statement in the database

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)

The log event record of binlog is as follows:

# 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

Com _ MIT _ blank /

From the log analysis, the DML is recorded as the original SQL, that is, recorded in the QUERY_EVENT.

When the binlog format of the database is row mode

Perform insert operation

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)

Information recorded in 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

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

# at 253

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

Com _ MIT _ blank /

From the parsed binlog, you can see that in row mode, the DML operation is recorded as: TABLE_MAP_EVENT+ROW_LOG_EVENT (including WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT).

Why does a update need to be decomposed into two event in ROW mode: one Table_map and one Update_rows. Let's imagine that if an update updates 10000 pieces of data, does the corresponding table structure information need to be recorded 10000 times? It is actually an operation on the same table, so here binlog only records a Table_map to record information about the table structure, while the subsequent Update_rows records the row information of the updated data. They communicate with each other through table_id.

Characteristics of two table_id

1 table_id is not fixed, it is temporarily allocated when the table is loaded into memory (table_definition_cache), and is a growing variable.

2 when there is a new table change, which is not in cache, an operation of load table def will be triggered, which will be used as the id of the new table def based on the previous last table_id.

3 flush tables, and then the update to the table will also trigger the growth of table_id.

4 if the table def cache is too small, there will be frequent swapping in and out, resulting in faster table_id growth.

Examples

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)

Three table_id changes in the process of master-slave replication

The information of each dml operation table is recorded in the hash data structure of table_mapping. The key of hash is the table_id,hash of ulong type, and the value of TABLE* is the data structure of TABLE* (including all kinds of information of the table, including database name, table name, number of fields, field type, etc.), hash through the set_table () method, and obtain the corresponding table information according to the table_id through the get_table () method.

When the log of the primary database is passed to the standby database, each log_event applies the event to the local database through the do_apply_event () method. When event in apply relay log, do_apply_event () assigns ulong-type m_table_id (table_id of binlog record) to uint-type table_id in the RPL_TABLE_LIST structure. The core problem arises: if the value of table_id in binlog is greater than max (uint), truncation occurs when the variable is passed.

However, MySQL uses set_table (table_id) to construct hash and get_table (m_table_id) to take values from the hash table. The key used in the two stages cannot get the expected value because of data truncation. In other words, the hash pair of key-value built with uint type table_id cannot be queried with ulong type m_table_id.

Four risks and solutions

From the second and third points, we know that when the table_id grows too fast, it will cause the application binlog from the library to be unable to parse to the corresponding table, resulting in data inconsistency.

Solution:

1 increase the size of table cache.

(2) restart the main database to return table_id to 0, which has a relatively high cost. When this problem occurs, the master / backup is inconsistent and the online environment cannot be switched.

3 modify the MySQL source code, change the uint type table_id in the RPL_TABLE_LIST structure to ulong type, once and for all.

This is the end of "how to understand table_id in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.