Mysql管理必备工具Maatkit详解之十一（mk-table-checksum）
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 STATLAG
test test 0192.168.0.1 MyISAM NULL 1592576808 0 0 NULL NULL
test test 0192.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 STATLAG
test test 0192.168.0.1 MyISAM 385 cd0abd260b2f12c95af05278c114a84b 0 0 NULL NULL
test test 0192.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 ... HOST
Options:
--algorithm -aChecksum 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 -CApproximate 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
--count -rCount rows in tables. This is built into ACCUM and
BIT_XOR, but requires an extra query for CHECKSUM.
--crc -cDo a CRC (checksum) of tables (default yes)
--databases -dOnly checksum this comma-separated list of databases
--defaults-file -FOnly read mysql options from the given file. You must give
an absolute pathname.
--emptyrepltbl Empty table given by --replicate before starting
--engine -eDo 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 -fHash function for checksums (FNV_64, SHA1, MD5, etc)
--help Show this help message
--ignoredb -gIgnore this comma-separated list of databases
--ignoreengine-EIgnore this comma-separated list of storage engines
(default FEDERATED,MRG_MyISAM)
--ignoretbl -nIgnore this comma-separated list of tables
--lock -kLock on master until done on slaves (implies --slavelag)
--optxor -oOptimize BIT_XOR with user variables (default yes)
--password -pPassword to use when connecting
--port -PPort number to use for connection
--quiet -qDo not print checksum results
--replcheck Check results in --replicate table, to the specified depth
--replicate -RReplicate checksums to slaves (disallows -a CHECKSUM)
--separator -sThe separator character used for CONCAT_WS() (default #)
--setvars Set these MySQL variables (default wait_timeout=10000)
--slavelag -lReport how far slaves lag master
--sleep Sleep time between checksums
--sleep-coef Calculate --sleep as a multiple of the last checksum time
--socket -SSocket file to use for connection
--tab -bPrint tab-separated output, not column-aligned output
--tables -tDo only this comma-separated list of tables
--trim Trim VARCHAR columns (helps when comparing 4.1 to >= 5.0).
--user -uUser for login if not current user
--verify -vVerify checksum compatibility across servers (default yes)
--version Output version information and exit
--wait -wWait 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 -WDo only rows matching this WHERE clause (disallows
--algorithm CHECKSUM)
DSN syntax is key=value[,key=value...]Allowable DSN keys:
KEYCOPYMEANING
====================================================
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-filterFILE
Options:
--equaldbs -dThis comma-separated list of databases are equal
--header -hPreserves headers output by mk-table-checksum
--help Show this help message
--ignoredb -iIgnore the database name when comparing lines
--master -mThe name of the master server
--unique -uShow unique differing host/db/table names
--verbose-vOutput 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
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/111930/showart_2184939.html
页:
[1]