再说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)

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

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

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

更多详情见请继续阅读下一页的精彩内容：

  • 1
  • 2
  • 下一页

相关内容