mysql 复制检查工具mk-table-checksum replicate选项

原文：http://www.maatkit.org/doc/mk-table-checksum.html#replicate

中文介绍： http://www.bigheaddba.net/article/y2009/387_mysql%E7%AE%A1%E7%90%86%E5%BF%85%E5%A4%87%E5%B7%A5%E5%85%B7maatkit%E8%AF%A6%E8%A7%A3%E4%B9%8B%E5%8D%81%E4%B8%80%EF%BC%88mk-table-checksum%EF%BC%89.html

 

mysql复制 官方部分文档参考

http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-howto 版本较老

http://blog.chinaunix.net/u3/103972/article.php?frmid=137328 

 

高性能mysql这本书很好。

 

--replicate

type: string

Replicate checksums to slaves (disallows --algorithm CHECKSUM).

This option enables a completely different checksum strategy for a consistent, lock-free checksum across a master and its slaves . Instead of running the checksum queries on each server, you run them only on the master. You specify a table, fully qualified in db.table format, to insert the results into. The checksum queries will insert directly into the table, so they will be replicated through the binlog to the slaves.

 

When the queries are finished replicating, you can run a simple query on each slave to see which tables have differences from the master. With the --replicate-check option, mk-table-checksum can run the query for you to make it even easier. See CONSISTENT CHECKSUMS for details.

If you find tables that have differences, you can use the chunk boundaries in a WHERE clause with mk-table-sync to help repair them more efficiently . See mk-table-sync for details.

The table must have at least these columns: db, tbl, chunk, boundaries, this_crc, master_crc, this_cnt, master_cnt. The table may be named anything you wish. Here is a suggested table structure, which is automatically used for --create-replicate-table (MAGIC_create_replicate):

  CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)
  );

Be sure to choose an appropriate storage engine for the checksum table. If you are checksumming InnoDB tables, for instance, a deadlock will break replication if the checksum table is non-transactional, because the transaction will still be written to the binlog. It will then replay without a deadlock on the slave and break replication with "different error on master and slave." This is not a problem with mk-table-checksum, it's a problem with MySQL replication, and you can read more about it in the MySQL manual.

This works only with statement-based replication (mk-table-checksum will switch the binlog format to STATEMENT for the duration of the session if your server uses row-based replication) .

In contrast to running the tool against multiple servers at once, using this option eliminates the complexities of synchronizing checksum queries across multiple servers, which normally requires locking and unlocking, waiting for master binlog positions, and so on. Thus, it disables --lock , --wait , and --slave-lag (but not --check-slave-lag , which is a way to throttle the execution speed).

The checksum queries actually do a REPLACE into this table, so existing rows need not be removed before running. However, you may wish to do this anyway to remove rows related to tables that don't exist anymore. The --empty-replicate-table option does this for you.

Since mk-table-checksum uses USE to select the table's database as its default database before executing the checksum query, the checksum queries should replicate to slaves even if --binlog-do-db settings on the master filter out the checksum table's database. For more information on how --binlog-do-db works, see http://dev.mysql.com/doc/en/binary-log.html .

If the slaves have any --replicate-do-X or --replicate-ignore-X options, you should be careful not to checksum any databases or tables that exist on the master and not the slaves. Changes to such tables may not normally be executed on the slaves because of the --replicate options, but the checksum queries modify the contents of the table that stores the checksums, not the tables whose data you are checksumming. Therefore, these queries will be executed on the slave, and if the table or database you're checksumming does not exist, the queries will cause replication to fail. For more information on replication rules, see http://dev.mysql.com/doc/en/replication-rules.html .

The table specified by --replicate will never be checksummed itself.