Mysql管理必备工具Maatkit详解之十一（mk-table-checksum）

2009年08月19日　作者： 大头刚　

mk-table-checksum - 检查数据库复制模式里，master和slave的表是否一致，安装方法可以参考。

mysql在5.1之前，其replication都是采用的STATEMENT模式，对表的数据是否一致要求并不严格，所以对数据一致性要求比较严格的应用，定期检查数据一致性是很有必要的，mk-table-checksum 是一个很不错的检查工具。

例如：
我要检查master=192.168.0.1，slave=192.168.0.2的两台mysql的test库里的test表是否一致，命令如下：

mk-table-checksum h=192.168.0.1,u=sg,p=sg109504 h=192.168.0.2,u=sg,p=xxxx -d test -t test
DATABASE TABLE CHUNK HOST        ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
test     test     0  192.168.0.1 MyISAM       NULL       1592576808    0    0 NULL NULL
test     test     0  192.168.0.2 MyISAM       NULL       1592576808    0    0 NULL NULL
 
mk-table-checksum h=192.168.0.1,u=sg,p=sg109504 h=192.168.0.2,u=sg,p=xxxx -d test -t test -r
DATABASE TABLE CHUNK HOST        ENGINE      COUNT                         CHECKSUM TIME WAIT STAT  LAG
test     test     0  192.168.0.1 MyISAM        385 cd0abd260b2f12c95af05278c114a84b    0    0 NULL NULL
test     test     0  192.168.0.2 MyISAM        385 cd0abd260b2f12c95af05278c114a84b    0    0 NULL NULL

解释下输出的意思：
DATABASE：数据库名
TABLE：表名
CHUNK：checksum时的近似数值
HOST：MYSQL的地址
ENGINE：表引擎
COUNT：表的行数
CHECKSUM：校验值
TIME：所用时间
WAIT：等待时间
STAT：MASTER_POS_WAIT()返回值
LAG：slave的延时时间

这里需要顺带介绍下mk-table-checksum的过滤工具mk-checksum-filter，例如我只想知道上面的例子中，test库中哪些表不是一致的。只需要加一个管道符。

mk-table-checksum h=10.0.0.156,u=sg,p=sg109504 h=10.0.0.159,u=sg,p=sg109504 -d test -r|mk-checksum-filter
test login_log          0 192.168.0.1 MyISAM     133737 7336e1638a33bbc6a203a41f30b5a6fe    1    0 NULL NULL
test login_log          0 192.168.0.2 MyISAM     133735 609c44faeb584a2c1a92f0a37349a3ea    1    0 NULL NULL

需要提醒一下，在使用mk-table-checksum进行操作的时候，会对表加一个表级锁，所以一般这类检查最好是在业务比较闲的时候进行。下面给

出这2个工具的帮助。

mk-table-checksum --help
mk-table-checksum checksums MySQL tables efficiently on one or more HOSTs.  Each
HOST is specified as a DSN and missing values are inherited from the first HOST.
If you specify multiple HOSTs, the first is assumed to be the master.  For more
details, please use the --help option, or try 'perldoc mk-table-checksum' for
complete documentation.

Usage: mk-table-checksum [OPTION]... HOST [HOST...]

Options:
  --algorithm     -a  Checksum algorithm (ACCUM|CHECKSUM|BIT_XOR)
  --askpass           Prompt for a password when connecting to MySQL
  --checksum          Print checksums and table names in the style of md5sum
                      (disables --count)
  --chunksize     -C  Approximate number of rows or size of data to checksum at
                      a time. Allowable suffixes are k, M, G. Disallows -a
                      CHECKSUM.
  --columns           Checksum only this comma-separated list of columns
  --[no]count     -r  Count rows in tables. This is built into ACCUM and
                      BIT_XOR, but requires an extra query for CHECKSUM.
  --[no]crc       -c  Do a CRC (checksum) of tables (default yes)
  --databases     -d  Only checksum this comma-separated list of databases
  --defaults-file -F  Only read mysql options from the given file. You must give
                      an absolute pathname.
  --emptyrepltbl      Empty table given by --replicate before starting
  --engine        -e  Do only this comma-separated list of storage engines
  --explain           Show, but do not execute, checksum queries (disables
                      --emptyrepltbl)
  --explainhosts      Print connection information and exit
  --float-precision   Precision for FLOAT and DOUBLE column comparisons
  --function      -f  Hash function for checksums (FNV_64, SHA1, MD5, etc)
  --help              Show this help message
  --ignoredb      -g  Ignore this comma-separated list of databases
  --ignoreengine  -E  Ignore this comma-separated list of storage engines
                      (default FEDERATED,MRG_MyISAM)
  --ignoretbl     -n  Ignore this comma-separated list of tables
  --lock          -k  Lock on master until done on slaves (implies --slavelag)
  --[no]optxor    -o  Optimize BIT_XOR with user variables (default yes)
  --password      -p  Password to use when connecting
  --port          -P  Port number to use for connection
  --quiet         -q  Do not print checksum results
  --replcheck         Check results in --replicate table, to the specified depth
  --replicate     -R  Replicate checksums to slaves (disallows -a CHECKSUM)
  --separator     -s  The separator character used for CONCAT_WS() (default #)
  --setvars           Set these MySQL variables (default wait_timeout=10000)
  --slavelag      -l  Report how far slaves lag master
  --sleep             Sleep time between checksums
  --sleep-coef        Calculate --sleep as a multiple of the last checksum time
  --socket        -S  Socket file to use for connection
  --tab           -b  Print tab-separated output, not column-aligned output
  --tables        -t  Do only this comma-separated list of tables
  --trim              Trim VARCHAR columns (helps when comparing 4.1 to >= 5.0).
  --user          -u  User for login if not current user
  --[no]verify    -v  Verify checksum compatibility across servers (default yes)
  --version           Output version information and exit
  --wait          -w  Wait this long for slaves to catch up to their master
                      (implies --lock --slavelag).  Optional suffix s=seconds,
                      m=minutes, h=hours, d=days; if no suffix, s is used.
  --where         -W  Do only rows matching this WHERE clause (disallows
                      --algorithm CHECKSUM)

DSN syntax is key=value[,key=value...]  Allowable DSN keys:
  KEY  COPY  MEANING
  ===  ====  =============================================
  A    yes   Default character set
  D    yes   Database to use
  F    yes   Only read default options from the given file
  P    yes   Port number to use for connection
  S    yes   Socket file to use for connection
  h    yes   Connect to host
  p    yes   Password to use when connecting
  u    yes   User for login if not current user
  If the DSN is a bareword, the word is treated as the 'h' key.

Options and values after processing arguments:
  --algorithm         (No value)
  --askpass           FALSE
  --checksum          FALSE
  --chunksize         (No value)
  --columns           (No value)
  --count             FALSE
  --crc               TRUE
  --databases         (No value)
  --defaults-file     (No value)
  --emptyrepltbl      FALSE
  --engine            (No value)
  --explain           FALSE
  --explainhosts      FALSE
  --float-precision   (No value)
  --function          (No value)
  --help              TRUE
  --ignoredb
  --ignoreengine      FEDERATED,MRG_MyISAM
  --ignoretbl
  --lock              FALSE
  --optxor            TRUE
  --password          (No value)
  --port              (No value)
  --quiet             FALSE
  --replcheck         (No value)
  --replicate         (No value)
  --separator         #
  --setvars           wait_timeout=10000
  --slavelag          FALSE
  --sleep             (No value)
  --sleep-coef        (No value)
  --socket            (No value)
  --tab               FALSE
  --tables            (No value)
  --trim              FALSE
  --user              (No value)
  --verify            TRUE
  --version           FALSE
  --wait              (No value)
  --where             (No value)

mk-checksum-filter --help
mk-checksum-filter filters checksums from mk-table-checksum and prints those
that differ.  With no FILE, or when FILE is -, read standard input.  For more
details, please use the --help option, or try 'perldoc mk-checksum-filter' for
complete documentation.

Usage: mk-checksum-filter  FILE

Options:
  --equaldbs -d  This comma-separated list of databases are equal
  --header   -h  Preserves headers output by mk-table-checksum
  --help         Show this help message
  --ignoredb -i  Ignore the database name when comparing lines
  --master   -m  The name of the master server
  --unique   -u  Show unique differing host/db/table names
  --verbose  -v  Output all lines, even those that have no differences, except
                 for header lines
  --version      Output version information and exit
  --ignoredb and --equaldbs are mutually exclusive.

Options and values after processing arguments:
  --equaldbs
  --header       FALSE
  --help         TRUE
  --ignoredb     FALSE
  --master
  --unique       (No value)
  --verbose      FALSE
  --version      FALSE