mysql algorithm copy_mysql 复制检查工具mk-table-checksum 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.