Besides, table_id in MySQL

Besides, table_id in MySQL

Background]
Recently, a master-slave data inconsistency occurred in an online instance, that is, data loss from the slave database. Root Cause: "Because table_list-> table_id is uint and m_table_id is ulong, the table map id of assign in the master database is always increasing
When the number exceeds 2 ^ 32, the slave database overflows, causing all the table IDs of the slave database to be lost in row mode, resulting in inconsistency between the master and slave databases. "
[Problem Analysis]
I. table_id Introduction
When MySQL enables the log 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 in 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 'angyi'; insert into t1 values (8) |
| Mysql-bin.000003 | 265 | Xid | 2 | 292 | COMMIT/* xid = 12 */|
| Mysql-bin.000003 | 292 | Query | 2 | 369 | use 'angyi'; flush tables |
| Mysql-bin.000003 | 369 | Query | 2 | 439 | BEGIN |
| Mysql-bin.000003 | 439 | Query | 2 | 528 | use 'angyi'; insert into t1 values (9) |
| Mysql-bin.000003 | 528 | Xid | 2 | 555 | COMMIT/* xid = 15 */|
+ ------------------ + ----- + ------------- + ----------- + ------------- + ------------------------------------------ +
8 rows in set (0.00 sec)
The binlog log event record 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
COMMIT /*! */;
From the log analysis, DML records the original SQL statement, that is, it is recorded in QUERY_EVENT.

When the binlog format of the database is in row mode
Execute the 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.20.02 ';
+ ------------------ + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| 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 'ed ed to number 18
#140511 14:31:43 server id 2 end_log_pos 253 Write_rows: table id 18 flags: STMT_END_F
Binlog'
Txlvuxmcaaaakwaaansaaaabiaaaaaaaeabnlhbmd5aqacddeaaqmaaq =
TxlvUxcCAAAAIgAAAP0AAAAAABIAAAAAAAEAAf/+ BgAAAA =
'/*! */;
### Insert into 'angyi'. '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 /*! */;
The parsed binlog shows that in row mode, DML operations are recorded as TABLE_MAP_EVENT + ROW_LOG_EVENT (including WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT ).
Why should an update be divided into two events in ROW mode: One Table_map and the other Update_rows. Imagine if a single update updates 10000 pieces of data, does the corresponding table structure need to be recorded 10000 times? In fact, it is an operation on the same table, so here binlog only records the Table_map used to record the information about the table structure, while the Update_rows later records the row information of the updated data. They are connected by table_id.

Ii. Features of table_id
1 table_id is not fixed. It is allocated temporarily when the table is loaded into the memory (table_definition_cache) and is a variable that keeps increasing.
2. When a new table change is made, if it is not in the cache, the load table def operation will be triggered. At this time, + 1 will be triggered on the basis of the last table_id, as the id of the new table def.
3. flush tables. Subsequent table update operations also trigger the growth of table_id.
4 if the table def cache size is too small, frequent SWAps will occur, resulting in faster growth of table_id.
Example
Root @ rac2 [yangyi]> show binlog events in 'mysql-bin.20.02 ';
+ ------------------ + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| 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 'angyi'; 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)

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

For more details, please continue to read the highlights on the next page:

1 2 Next Page