mysql table_id 文章搜刮

浅浅的花香味﹌ 2022-06-04 05:11 172阅读 0赞

唉，祸不单行，刚遇到死锁的问题，有遇到table\_id的问题……

*  [MySQL Binlog中TABLE ID源码分析][MySQL Binlog_TABLE ID]
 *  [淘宝物流mysql slave数据丢失详细原因][mysql slave]
 *  [MySQL table\_id原理及风险分析][MySQL table_id]
 *  [FAQ系列 | table id问题导致主从复制失败][FAQ_ _ table id]
 *  [【MySQL】再说MySQL中的 table\_id][MySQL_MySQL_ table_id]

结论：  
1. 增大table cache  
2. 定期检查table\_id，其值不能超过uint的范围（重启主库）  
3. 将RPL\_TABLE\_LIST这个内部数据结构里面的table\_id类型改为ulong（修改MySQL源码）

--------------------

话外：  
使用strace查看问题mysql的输出，观察到relay log正常被read，但binlog一直会被从第一个文集轮询被打开

102300 10:32:03.110995 open("/data/dir/mysql-bin.000018", O_RDONLY|O_CLOEXEC) = 136 <0.000014>
    102300 10:32:03.111123 open("/data/dir/mysql-bin.000019", O_RDONLY|O_CLOEXEC) = 136 <0.000009>
    102300 10:32:03.111242 open("/data/dir/mysql-bin.000020", O_RDONLY|O_CLOEXEC) = 136 <0.000007>
    102300 10:32:03.111351 open("/data/dir/mysql-bin.000021", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    64972 10:32:03.196014 open("/data/dir/mysql-bin.000018", O_RDONLY|O_CLOEXEC) = 136 <0.000013>
    64972 10:32:03.196147 open("/data/dir/mysql-bin.000019", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    64972 10:32:03.196253 open("/data/dir/mysql-bin.000020", O_RDONLY|O_CLOEXEC) = 136 <0.000007>
    64972 10:32:03.196356 open("/data/dir/mysql-bin.000021", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    64972 10:32:25.839283 open("/data/dir/mysql-bin.000018", O_RDONLY|O_CLOEXEC) = 136 <0.000012>
    64972 10:32:25.839430 open("/data/dir/mysql-bin.000019", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    64972 10:32:25.839539 open("/data/dir/mysql-bin.000020", O_RDONLY|O_CLOEXEC) = 136 <0.000007>
    64972 10:32:25.839646 open("/data/dir/mysql-bin.000021", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    102300 10:32:33.111911 open("/data/dir/mysql-bin.000018", O_RDONLY|O_CLOEXEC) = 136 <0.000011>
    102300 10:32:33.112030 open("/data/dir/mysql-bin.000019", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    102300 10:32:33.112137 open("/data/dir/mysql-bin.000020", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    102300 10:32:33.112242 open("/data/dir/mysql-bin.000021", O_RDONLY|O_CLOEXEC) = 136 <0.000007>
    64972 10:32:33.196552 open("/data/dir/mysql-bin.000018", O_RDONLY|O_CLOEXEC) = 136 <0.000012>
    64972 10:32:33.196671 open("/data/dir/mysql-bin.000019", O_RDONLY|O_CLOEXEC) = 136 <0.000007>
    64972 10:32:33.196776 open("/data/dir/mysql-bin.000020", O_RDONLY|O_CLOEXEC) = 136 <0.000008>
    64972 10:32:33.196882 open("/data/dir/mysql-bin.000021", O_RDONLY|O_CLOEXEC) = 136 <0.000007>

这点比较奇怪，按照之前bolg说法，是ulong存到unint类型被阶段，但这个地方看到的是binlog rotate……

尴尬的实例被重建……

[MySQL Binlog_TABLE ID]: http://blog.chinaunix.net/uid-26896862-id-3329896.html
[mysql slave]: http://blog.csdn.net/gua___gua/article/details/51407891
[MySQL table_id]: http://www.cnblogs.com/yuyue2014/p/3721172.html
[FAQ_ _ table id]: https://mp.weixin.qq.com/s/8Ue3m1V315-6mt-8jRRnyw
[MySQL_MySQL_ table_id]: https://yq.aliyun.com/articles/27773