MySQL 5.7 增强的离线分析工具innochecksum

更新时间：2022-03-21 14:55:21

最近正准备写个工具来分析ibd文件中的数据分布，刚扫了下MySQL5.7的代码，发现这个功能已经在5.7.2版本中完成了；在新版本中增强了innochecksum的一些分析功能，完全可以满足我的需求

文档见：http://dev.mysql.com/doc/refman/5.7/en/innochecksum.html

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5912

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5841

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5684

唯一的缺点就是：文件不可以打开！换句话说，可能需要关闭实例才能使用如下功能！！！

以下列出了几个感兴趣的命令，具体的可以参考文档！

输出ibd中总的page数：

$sudo ./innochecksum --count ../data/sbtest/sbtest1.ibd
Number of pages:8192

可以指定checksum的算法(crc32/innodb/none)：

$sudo ./innochecksum  --page=20 --strict-check=crc32  ../data/sbtest/sbtest1.ibd      
Fail: page 20 invalid
Exceeded the maximum allowed checksum mismatch count::0

指定–no-check –write 重写无效的page checksum值，也可以为–write指定值innodb/crc32/none 来写入page中指定的值（这应该属于危险操作）

使用–page-type-summary输出各种page类型的个数：

$sudo ./innochecksum  --page-type-summary  ../data/sbtest/sbtest1.ibd 

File::../data/sbtest/sbtest1.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
    7383        Index page
       0        Undo log page
       1        Inode page
       0        Insert buffer free list page
     806        Freshly allocated page
       1        Insert buffer bitmap
       0        System page
       0        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

$sudo ./innochecksum  --page-type-summary  ../data/ibdata1           

File::../data/ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
    1028        Index page
     408        Undo log page
       5        Inode page
     468        Insert buffer free list page
  325655        Freshly allocated page
       1        Insert buffer bitmap
     112        System page
       2        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 102 insert, 306 update, 0 other
Undo page state: 0 active, 150 cached, 0 to_free, 199 to_purge, 0 prepared, 59 other

指定 –page-type-dump则将每个page的detail都输出到指定文件中

$sudo ./innochecksum --page-type-dump=a.log  ../data/sbtest/sbtest1.ibd

$head -n 20 a.log 

Filename::../data/sbtest/sbtest1.ibd
==============================================================================
        PAGE_NO         |               PAGE_TYPE                       |       EXTRA INFO
==============================================================================
#::       0             |               File Space Header               |       -
#::       1             |               Insert Buffer Bitmap            |       -
#::       2             |               Inode page                      |       -
#::       3             |               Index page                      |       index id=65, page level=2, No. of records=7, garbage=0, -
#::       4             |               Index page                      |       index id=66, page level=1, No. of records=518, garbage=102, -
#::       5             |               Index page                      |       index id=65, page level=0, No. of records=36, garbage=7696, -
#::       6             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::       7             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::       8             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::       9             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::      10             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::      11             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::      12             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -
#::      13             |               Index page                      |       index id=65, page level=0, No. of records=73, garbage=0, -

指定–log输出每个page的checksum值

$sudo ./innochecksum --log=b.log  ../data/sbtest/sbtest1.ibd
$head -n 10 b.log  
InnoDB File Checksum Utility.
Filename = ../data/sbtest/sbtest1.ibd
Innochecksum: checking pages in range 0 to 8191
page::0; log sequence number:first = 260537423; second = 260537423
page::0; old style : calculated = 3962072366; recorded = 3962072366
page::0; new style: calculated = 1038926020; crc32 = 1327342271; recorded = 1038926020
page::1; log sequence number:first = 261782093; second = 261782093
page::1; old style : calculated = 3860212858; recorded = 3860212858
page::1; new style: calculated = 1118813775; crc32 = 142105317; recorded = 1118813775
page::2; log sequence number:first = 92384856; second = 92384856

另外之前一直无法支持的压缩表checksum的问题也得到了解决，和正常的ibd文件一样解析

上一篇 : ：changes of mysql 5.6.20下一篇 : [MySQL5.6] MySQL 5.6.17新特性：online optimize table （以及其他主要bugfix）

MySQL 5.7 增强的离线分析工具innochecksum

相关阅读

推荐文章