再说MySQL中的 table_id

最近线上一个实例出现了主从数据不一致的情况,也即从库丢失数据的情况。根本原因:由于table_list-gt;table_id为uint,而m_table

【背景】
最近线上一个实例出现了主从数据不一致的情况,也即从库丢失数据的情况。根本原因:”由于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 NginxPHP5 (PHP-FPM)MySQL

Ubuntu 14.04下搭建MySQL主从服务器

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

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

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