~percona-toolkit-dev/percona-toolkit/docu-ptc-rbr-limitation
» Revision
34
: docs/user/pt-table-checksum.rst
« back to all changes in this revision
Viewing changes to docs/user/pt-table-checksum.rst
- browse files at revision 34
- compare with another revision
- download diff
- download tarball
- view history from revision 34
- Committer: Daniel Nichter
- Date: 2011-07-14 19:08:47 UTC
- Revision ID: daniel@percona.com-20110714190847-lggalkuvdrh7c4jp
Add standard pkg files (COPYING, README, etc.), percona-toolkit.pod, and user docs. Remove dev/docs/html.
- files added:
- COPYING
- config/sphinx-build
- config/sphinx-build/_static
- config/sphinx-build/_templates
- files removed:
- docs/dev/html
- docs/dev/html/files
- docs/dev/html/files/modules
- docs/dev/html/files/tools
- docs/dev/html/index
- docs/dev/html/javascript
- docs/dev/html/search
- docs/dev/html/styles
- files modified:
- .bzrignore
added
removed
docs/user/pt-table-checksum.rst
1
2
#################
3
pt-table-checksum
4
#################
5
6
.. highlight:: perl
7
8
9
****
10
NAME
11
****
12
13
14
pt-table-checksum - Perform an online replication consistency check, or
15
checksum MySQL tables efficiently on one or many servers.
16
17
18
********
19
SYNOPSIS
20
********
21
22
23
Usage: pt-table-checksum [OPTION...] DSN [DSN...]
24
25
pt-table-checksum checksums MySQL tables efficiently on one or more hosts.
26
Each host is specified as a DSN and missing values are inherited from the
27
first host. If you specify multiple hosts, the first is assumed to be the
28
master.
29
30
STOP! Are you checksumming a slave(s) against its master? Then be sure to learn
31
what "--replicate" does. It is probably the option you want to use.
32
33
34
.. code-block:: perl
35
36
pt-table-checksum --replicate=mydb.checksum master-host
37
... time passses, replication catches up ...
38
pt-table-checksum --replicate=mydb.checksum --replicate-check 2 \
39
master-host
40
41
42
Or,
43
44
45
.. code-block:: perl
46
47
pt-table-checksum h=host1,u=user,p=password h=host2 ...
48
49
50
Or,
51
52
53
.. code-block:: perl
54
55
pt-table-checksum host1 host2 ... hostN | pt-checksum-filter
56
57
58
See "SPECIFYING HOSTS" for more on the syntax of the host arguments.
59
60
61
*****
62
RISKS
63
*****
64
65
66
The following section is included to inform users about the potential risks,
67
whether known or unknown, of using this tool. The two main categories of risks
68
are those created by the nature of the tool (e.g. read-only tools vs. read-write
69
tools) and those created by bugs.
70
71
pt-table-checksum executes queries that cause the MySQL server to checksum its
72
data. This can cause significant server load. It is read-only unless you use
73
the "--replicate" option, in which case it inserts a small amount of data
74
into the specified table.
75
76
At the time of this release, we know of no bugs that could cause serious harm to
77
users. There are miscellaneous bugs that might be annoying.
78
79
The authoritative source for updated information is always the online issue
80
tracking system. Issues that affect this tool will be marked as such. You can
81
see a list of such issues at the following URL:
82
`http://www.percona.com/bugs/pt-table-checksum <http://www.percona.com/bugs/pt-table-checksum>`_.
83
84
See also "BUGS" for more information on filing bugs and getting help.
85
86
87
***********
88
DESCRIPTION
89
***********
90
91
92
pt-table-checksum generates table checksums for MySQL tables, typically
93
useful for verifying your slaves are in sync with the master. The checksums
94
are generated by a query on the server, and there is very little network
95
traffic as a result.
96
97
Checksums typically take about twice as long as COUNT(\*) on very large InnoDB
98
tables in my tests. For smaller tables, COUNT(\*) is a good bit faster than
99
the checksums. See "--algorithm" for more details on performance.
100
101
If you specify more than one server, pt-table-checksum assumes the first
102
server is the master and others are slaves. Checksums are parallelized for
103
speed, forking off a child process for each table. Duplicate server names are
104
ignored, but if you want to checksum a server against itself you can use two
105
different forms of the hostname (for example, "localhost 127.0.0.1", or
106
"h=localhost,P=3306 h=localhost,P=3307").
107
108
If you want to compare the tables in one database to those in another database
109
on the same server, just checksum both databases:
110
111
112
.. code-block:: perl
113
114
pt-table-checksum --databases db1,db2
115
116
117
You can then use pt-checksum-filter to compare the results in both databases
118
easily.
119
120
pt-table-checksum examines table structure only on the first host specified,
121
so if anything differs on the others, it won't notice. It ignores views.
122
123
The checksums work on MySQL version 3.23.58 through 6.0-alpha. They will not
124
necessarily produce the same values on all versions. Differences in
125
formatting and/or space-padding between 4.1 and 5.0, for example, will cause
126
the checksums to be different.
127
128
129
****************
130
SPECIFYING HOSTS
131
****************
132
133
134
pt-table-checksum connects to a theoretically unlimited number of MySQL
135
servers. You specify a list of one or more host definitions on the command
136
line, such as "host1 host2". Each host definition can be just a hostname, or it
137
can be a complex string that specifies connection options as well. You can
138
specify connection options two ways:
139
140
141
\*
142
143
Format a host definition in a key=value,key=value form. If an argument on the
144
command line contains the letter '=', pt-table-checksum will parse it into
145
its component parts. Examine the "--help" output for details on the allowed
146
keys.
147
148
Specifying a list of simple host definitions "host1 host2" is equivalent to the
149
more complicated "h=host1 h=host2" format.
150
151
152
153
\*
154
155
With the command-line options such as "--user" and "--password". These
156
options, if given, apply globally to all host definitions.
157
158
159
160
In addition to specifying connection options this way, pt-table-checksum
161
allows shortcuts. Any options specified for the first host definition on the
162
command line fill in missing values in subsequent ones. Any options that are
163
still missing after this are filled in from the command-line options if
164
possible.
165
166
In other words, the places you specify connection options have precedence:
167
highest precedence is the option specified directly in the host definition, next
168
is the option specified in the first host definition, and lowest is the
169
command-line option.
170
171
You can mix simple and complex host definitions and/or command-line arguments.
172
For example, if all your servers except one of your slaves uses a non-standard
173
port number:
174
175
176
.. code-block:: perl
177
178
pt-table-checksum --port 4500 master h=slave1,P=3306 slave2 slave3
179
180
181
If you are confused about how pt-table-checksum will connect to your servers,
182
give the "--explain-hosts" option and it will tell you.
183
184
185
***************
186
HOW FAST IS IT?
187
***************
188
189
190
Speed and efficiency are important, because the typical use case is checksumming
191
large amounts of data.
192
193
\ ``pt-table-checksum``\ is designed to do very little work itself, and generates
194
very little network traffic aside from inspecting table structures with \ ``SHOW
195
CREATE TABLE``\ . The results of checksum queries are typically 40-character or
196
shorter strings.
197
198
The MySQL server does the bulk of the work, in the form of the checksum queries.
199
The following benchmarks show the checksum query times for various checksum
200
algorithms. The first two results are simply running \ ``COUNT(col8)``\ and
201
\ ``CHECKSUM TABLE``\ on the table. \ ``CHECKSUM TABLE``\ is just \ ``CRC32``\ under the
202
hood, but it's implemented inside the storage engine layer instead of at the
203
MySQL layer.
204
205
206
.. code-block:: perl
207
208
ALGORITHM HASH FUNCTION EXTRA TIME
209
============== ============= ============== =====
210
COUNT(col8) 2.3
211
CHECKSUM TABLE 5.3
212
BIT_XOR FNV_64 12.7
213
ACCUM FNV_64 42.4
214
BIT_XOR MD5 --optimize-xor 80.0
215
ACCUM MD5 87.4
216
BIT_XOR SHA1 --optimize-xor 90.1
217
ACCUM SHA1 101.3
218
BIT_XOR MD5 172.0
219
BIT_XOR SHA1 197.3
220
221
222
The tests are entirely CPU-bound. The sample data is an InnoDB table with the
223
following structure:
224
225
226
.. code-block:: perl
227
228
CREATE TABLE test (
229
col1 int NOT NULL,
230
col2 date NOT NULL,
231
col3 int NOT NULL,
232
col4 int NOT NULL,
233
col5 int,
234
col6 decimal(3,1),
235
col7 smallint unsigned NOT NULL,
236
col8 timestamp NOT NULL,
237
PRIMARY KEY (col2, col1),
238
KEY (col7),
239
KEY (col1)
240
) ENGINE=InnoDB
241
242
243
The table has 4303585 rows, 365969408 bytes of data and 173457408 bytes of
244
indexes. The server is a Dell PowerEdge 1800 with dual 32-bit Xeon 2.8GHz
245
processors and 2GB of RAM. The tests are fully CPU-bound, and the server is
246
otherwise idle. The results are generally consistent to within a tenth of a
247
second on repeated runs.
248
249
\ ``CRC32``\ is the default checksum function to use, and should be enough for most
250
cases. If you need stronger guarantees that your data is identical, you should
251
use one of the other functions.
252
253
254
*******************
255
ALGORITHM SELECTION
256
*******************
257
258
259
The "--algorithm" option allows you to specify which algorithm you would
260
like to use, but it does not guarantee that pt-table-checksum will use this
261
algorithm. pt-table-checksum will ultimately select the best algorithm possible
262
given various factors such as the MySQL version and other command line options.
263
264
The three basic algorithms in descending order of preference are CHECKSUM,
265
BIT_XOR and ACCUM. CHECKSUM cannot be used if any one of these criteria
266
is true:
267
268
269
.. code-block:: perl
270
271
* L<"--where"> is used.
272
* L<"--since"> is used.
273
* L<"--chunk-size"> is used.
274
* L<"--replicate"> is used.
275
* L<"--count"> is used.
276
* MySQL version less than 4.1.1.
277
278
279
The BIT_XOR algorithm also requires MySQL version 4.1.1 or later.
280
281
After checking these criteria, if the requested "--algorithm" remains then it
282
is used, otherwise the first remaining algorithm with the highest preference
283
is used.
284
285
286
********************
287
CONSISTENT CHECKSUMS
288
********************
289
290
291
If you are using this tool to verify your slaves still have the same data as the
292
master, which is why I wrote it, you should read this section.
293
294
The best way to do this with replication is to use the "--replicate" option.
295
When the queries are finished running on the master and its slaves, you can go
296
to the slaves and issue SQL queries to see if any tables are different from the
297
master. Try the following:
298
299
300
.. code-block:: perl
301
302
SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
303
this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
304
AS crc_diff
305
FROM checksum
306
WHERE master_cnt <> this_cnt OR master_crc <> this_crc
307
OR ISNULL(master_crc) <> ISNULL(this_crc);
308
309
310
The "--replicate-check" option can do this query for you. If you can't use
311
this method, try the following:
312
313
314
\*
315
316
If your servers are not being written to, you can just run the tool with no
317
further ado:
318
319
320
.. code-block:: perl
321
322
pt-table-checksum server1 server2 ... serverN
323
324
325
326
327
\*
328
329
If the servers are being written to, you need some way to make sure they are
330
consistent at the moment you run the checksums. For situations other than
331
master-slave replication, you will have to figure this out yourself. You may be
332
able to use the "--where" option with a date or time column to only checksum
333
data that's not recent.
334
335
336
337
\*
338
339
If you are checksumming a master and slaves, you can do a fast parallel
340
checksum and assume the slaves are caught up to the master. In practice, this
341
tends to work well except for tables which are constantly updated. You can
342
use the "--slave-lag" option to see how far behind each slave was when it
343
checksummed a given table. This can help you decide whether to investigate
344
further.
345
346
347
348
\*
349
350
The next most disruptive technique is to lock the table on the master, then take
351
checksums. This should prevent changes from propagating to the slaves. You can
352
just lock on the master (with "--lock"), or you can both lock on the master
353
and wait on the slaves till they reach that point in the master's binlog
354
("--wait"). Which is better depends on your workload; only you know that.
355
356
357
358
\*
359
360
If you decide to make the checksums on the slaves wait until they're guaranteed
361
to be caught up to the master, the algorithm looks like this:
362
363
364
.. code-block:: perl
365
366
For each table,
367
Master: lock table
368
Master: get pos
369
In parallel,
370
Master: checksum
371
Slave(s): wait for pos, then checksum
372
End
373
Master: unlock table
374
End
375
376
377
378
379
What I typically do when I'm not using the "--replicate" option is simply run
380
the tool on all servers with no further options. This runs fast, parallel,
381
non-blocking checksums simultaneously. If there are tables that look different,
382
I re-run with "--wait"=600 on the tables in question. This makes the tool
383
lock on the master as explained above.
384
385
386
******
387
OUTPUT
388
******
389
390
391
Output is to STDOUT, one line per server and table, with header lines for each
392
database. I tried to make the output easy to process with awk. For this reason
393
columns are always present. If there's no value, pt-table-checksum prints
394
'NULL'.
395
396
The default is column-aligned output for human readability, but you can change
397
it to tab-separated if you want. Use the "--tab" option for this.
398
399
Output is unsorted, though all lines for one table should be output together.
400
For speed, all checksums are done in parallel (as much as possible) and may
401
complete out of the order in which they were started. You might want to run
402
them through another script or command-line utility to make sure they are in the
403
order you want. If you pipe the output through pt-checksum-filter, you
404
can sort the output and/or avoid seeing output about tables that have no
405
differences.
406
407
The columns in the output are as follows. The database, table, and chunk come
408
first so you can sort by them easily (they are the "primary key").
409
410
Output from "--replicate-check" and "--checksum" are different.
411
412
413
DATABASE
414
415
The database the table is in.
416
417
418
419
TABLE
420
421
The table name.
422
423
424
425
CHUNK
426
427
The chunk (see "--chunk-size"). Zero if you are not doing chunked checksums.
428
429
430
431
HOST
432
433
The server's hostname.
434
435
436
437
ENGINE
438
439
The table's storage engine.
440
441
442
443
COUNT
444
445
The table's row count, unless you specified to skip it. If \ ``OVERSIZE``\ is
446
printed, the chunk was skipped because the actual number of rows was greater
447
than "--chunk-size" times "--chunk-size-limit".
448
449
450
451
CHECKSUM
452
453
The table's checksum, unless you specified to skip it or the table has no rows.
454
some types of checksums will be 0 if there are no rows; others will print NULL.
455
456
457
458
TIME
459
460
How long it took to checksum the \ ``CHUNK``\ , not including \ ``WAIT``\ time.
461
Total checksum time is \ ``WAIT + TIME``\ .
462
463
464
465
WAIT
466
467
How long the slave waited to catch up to its master before beginning to
468
checksum. \ ``WAIT``\ is always 0 for the master. See "--wait".
469
470
471
472
STAT
473
474
The return value of MASTER_POS_WAIT(). \ ``STAT``\ is always \ ``NULL``\ for the
475
master.
476
477
478
479
LAG
480
481
How far the slave lags the master, as reported by SHOW SLAVE STATUS.
482
\ ``LAG``\ is always \ ``NULL``\ for the master.
483
484
485
486
487
***************************
488
REPLICATE TABLE MAINTENANCE
489
***************************
490
491
492
If you use "--replicate" to store and replicate checksums, you may need to
493
perform maintenance on the replicate table from time to time to remove old
494
checksums. This section describes when checksums in the replicate table are
495
deleted automatically by pt-table-checksum and when you must manually delete
496
them.
497
498
Before starting, pt-table-checksum calculates chunks for each table, even
499
if "--chunk-size" is not specified (in that case there is one chunk: "1=1").
500
Then, before checksumming each table, the tool deletes checksum chunks in the
501
replicate table greater than the current number of chunks. For example,
502
if a table is chunked into 100 chunks, 0-99, then pt-table-checksum does:
503
504
505
.. code-block:: perl
506
507
DELETE FROM replicate table WHERE db=? AND tbl=? AND chunk > 99
508
509
510
That removes any high-end chunks from previous runs which no longer exist.
511
Currently, this operation cannot be disabled.
512
513
If you use "--resume", "--resume-replicate", or "--modulo", then
514
you need to be careful that the number of rows in a table does not decrease
515
so much that the number of chunks decreases too, else some checksum chunks may
516
be deleted. The one exception is if only rows at the high end of the range
517
are deleted. In that case, the high-end chunks are deleted and lower chunks
518
remain unchanged. An increasing number of rows or chunks should not cause
519
any adverse affects.
520
521
Changing the "--chunk-size" between runs with "--resume",
522
"--resume-replicate", or "--modulo" can cause odd or invalid checksums.
523
You should not do this. It won't work with the resume options. With
524
"--modulo", the safest thing to do is manually delete all the rows in
525
the replicate table for the table in question and start over.
526
527
If the replicate table becomes cluttered with old or invalid checksums
528
and the auto-delete operation is not deleting them, then you will need to
529
manually clean up the replicate table. Alternatively, if you specify
530
"--empty-replicate-table", then the tool deletes every row in the
531
replicate table.
532
533
534
***********
535
EXIT STATUS
536
***********
537
538
539
An exit status of 0 (sometimes also called a return value or return code)
540
indicates success. If there is an error checksumming any table, the exit status
541
is 1.
542
543
When running "--replicate-check", if any slave has chunks that differ from
544
the master, the exit status is 1.
545
546
547
*******
548
QUERIES
549
*******
550
551
552
If you are using innotop (see `http://code.google.com/p/innotop <http://code.google.com/p/innotop>`_),
553
mytop, or another tool to watch currently running MySQL queries, you may see
554
the checksum queries. They look similar to this:
555
556
557
.. code-block:: perl
558
559
REPLACE /*test.test_tbl:'2'/'5'*/ INTO test.checksum(db, ...
560
561
562
Since pt-table-checksum's queries run for a long time and tend to be
563
textually very long, and thus won't fit on one screen of these monitoring
564
tools, I've been careful to place a comment at the beginning of the query so
565
you can see what it is and what it's doing. The comment contains the name of
566
the table that's being checksummed, the chunk it is currently checksumming,
567
and how many chunks will be checksummed. In the case above, it is
568
checksumming chunk 2 of 5 in table test.test_tbl.
569
570
571
*******
572
OPTIONS
573
*******
574
575
576
"--schema" is restricted to option groups Connection, Filter, Output, Help, Config, Safety.
577
578
"--empty-replicate-table", "--resume" and "--resume-replicate" are mutually exclusive.
579
580
This tool accepts additional command-line arguments. Refer to the
581
"SYNOPSIS" and usage information for details.
582
583
584
--algorithm
585
586
type: string
587
588
Checksum algorithm (ACCUM|CHECKSUM|BIT_XOR).
589
590
Specifies which checksum algorithm to use. Valid arguments are CHECKSUM,
591
BIT_XOR and ACCUM. The latter two do cryptographic hash checksums.
592
See also "ALGORITHM SELECTION".
593
594
CHECKSUM is built into MySQL, but has some disadvantages. BIT_XOR and ACCUM are
595
implemented by SQL queries. They use a cryptographic hash of all columns
596
concatenated together with a separator, followed by a bitmap of each nullable
597
column that is NULL (necessary because CONCAT_WS() skips NULL columns).
598
599
CHECKSUM is the default. This method uses MySQL's built-in CHECKSUM TABLE
600
command, which is a CRC32 behind the scenes. It cannot be used before MySQL
601
4.1.1, and various options disable it as well. It does not simultaneously count
602
rows; that requires an extra COUNT(\*) query. This is a good option when you are
603
using MyISAM tables with live checksums enabled; in this case both the COUNT(\*)
604
and CHECKSUM queries will run very quickly.
605
606
The BIT_XOR algorithm is available for MySQL 4.1.1 and newer. It uses
607
BIT_XOR(), which is order-independent, to reduce all the rows to a single
608
checksum.
609
610
ACCUM uses a user variable as an accumulator. It reduces each row to a single
611
checksum, which is concatenated with the accumulator and re-checksummed. This
612
technique is order-dependent. If the table has a primary key, it will be used
613
to order the results for consistency; otherwise it's up to chance.
614
615
The pathological worst case is where identical rows will cancel each other out
616
in the BIT_XOR. In this case you will not be able to distinguish a table full
617
of one value from a table full of another value. The ACCUM algorithm will
618
distinguish them.
619
620
However, the ACCUM algorithm is order-dependent, so if you have two tables
621
with identical data but the rows are out of order, you'll get different
622
checksums with ACCUM.
623
624
If a given algorithm won't work for some reason, pt-table-checksum falls back to
625
another. The least common denominator is ACCUM, which works on MySQL 3.23.2 and
626
newer.
627
628
629
630
--arg-table
631
632
type: string
633
634
The database.table with arguments for each table to checksum.
635
636
This table may be named anything you wish. It must contain at least the
637
following columns:
638
639
640
.. code-block:: perl
641
642
CREATE TABLE checksum_args (
643
db char(64) NOT NULL,
644
tbl char(64) NOT NULL,
645
-- other columns as desired
646
PRIMARY KEY (db, tbl)
647
);
648
649
650
In addition to the columns shown, it may contain any of the other columns listed
651
here (Note: this list is used by the code, MAGIC_overridable_args):
652
653
654
.. code-block:: perl
655
656
algorithm chunk-column chunk-index chunk-size columns count crc function lock
657
modulo use-index offset optimize-xor chunk-size-limit probability separator
658
save-since single-chunk since since-column sleep sleep-coef trim wait where
659
660
661
Each of these columns corresponds to the long form of a command-line option.
662
Each column should be NULL-able. Column names with hyphens should be enclosed
663
in backticks (e.g. \`chunk-size\`) when the table is created. The data type does
664
not matter, but it's suggested you use a sensible data type to prevent garbage
665
data.
666
667
When \ ``pt-table-checksum``\ checksums a table, it will look for a matching entry
668
in this table. Any column that has a defined value will override the
669
corresponding command-line argument for the table being currently processed.
670
In this way it is possible to specify custom command-line arguments for any
671
table.
672
673
If you add columns to the table that aren't in the above list of allowable
674
columns, it's an error. The exceptions are \ ``db``\ , \ ``tbl``\ , and \ ``ts``\ . The \ ``ts``\
675
column can be used as a timestamp for easy visibility into the last time the
676
\ ``since``\ column was updated with "--save-since".
677
678
This table is assumed to be located on the first server given on the
679
command-line.
680
681
682
683
--ask-pass
684
685
group: Connection
686
687
Prompt for a password when connecting to MySQL.
688
689
690
691
--check-interval
692
693
type: time; group: Throttle; default: 1s
694
695
How often to check for slave lag if "--check-slave-lag" is given.
696
697
698
699
--[no]check-replication-filters
700
701
default: yes; group: Safety
702
703
Do not "--replicate" if any replication filters are set. When
704
--replicate is specified, pt-table-checksum tries to detect slaves and look
705
for options that filter replication, such as binlog_ignore_db and
706
replicate_do_db. If it finds any such filters, it aborts with an error.
707
Replication filtering makes it impossible to be sure that the checksum
708
queries won't break replication or simply fail to replicate. If you are sure
709
that it's OK to run the checksum queries, you can negate this option to
710
disable the checks. See also "--replicate-database".
711
712
713
714
--check-slave-lag
715
716
type: DSN; group: Throttle
717
718
Pause checksumming until the specified slave's lag is less than "--max-lag".
719
720
If this option is specified and "--throttle-method" is set to \ ``slavelag``\
721
then "--throttle-method" only checks this slave.
722
723
724
725
--checksum
726
727
group: Output
728
729
Print checksums and table names in the style of md5sum (disables
730
"--[no]count").
731
732
Makes the output behave more like the output of \ ``md5sum``\ . The checksum is
733
first on the line, followed by the host, database, table, and chunk number,
734
concatenated with dots.
735
736
737
738
--chunk-column
739
740
type: string
741
742
Prefer this column for dividing tables into chunks. By default,
743
pt-table-checksum chooses the first suitable column for each table, preferring
744
to use the primary key. This option lets you specify a preferred column, which
745
pt-table-checksum uses if it exists in the table and is chunkable. If not, then
746
pt-table-checksum will revert to its default behavior. Be careful when using
747
this option; a poor choice could cause bad performance. This is probably best
748
to use when you are checksumming only a single table, not an entire server. See
749
also "--chunk-index".
750
751
752
753
--chunk-index
754
755
type: string
756
757
Prefer this index for chunking tables. By default, pt-table-checksum chooses an
758
appropriate index for the "--chunk-column" (even if it chooses the chunk
759
column automatically). This option lets you specify the index you prefer. If
760
the index doesn't exist, then pt-table-checksum will fall back to its default
761
behavior. pt-table-checksum adds the index to the checksum SQL statements in a
762
\ ``FORCE INDEX``\ clause. Be careful when using this option; a poor choice of
763
index could cause bad performance. This is probably best to use when you are
764
checksumming only a single table, not an entire server.
765
766
767
768
--chunk-range
769
770
type: string; default: open
771
772
Set which ends of the chunk range are open or closed. Possible values are
773
one of MAGIC_chunk_range:
774
775
776
.. code-block:: perl
777
778
VALUE OPENS/CLOSES
779
========== ======================
780
open Both ends are open
781
openclosed Low end open, high end closed
782
783
784
By default pt-table-checksum uses an open range of chunks like:
785
786
787
.. code-block:: perl
788
789
`id` < '10'
790
`id` >= '10' AND < '20'
791
`id` >= '20'
792
793
794
That range is open because the last chunk selects any row with id greater than
795
(or equal to) 20. An open range can be a problem in cases where a lot of new
796
rows are inserted with IDs greater than 20 while pt-table-checksumming is
797
running because the final open-ended chunk will select all the newly inserted
798
rows. (The less common case of inserting rows with IDs less than 10 would
799
require a \ ``closedopen``\ range but that is not currently implemented.)
800
Specifying \ ``openclosed``\ will cause the final chunk to be closed like:
801
802
803
.. code-block:: perl
804
805
`id` >= '20' AND `id` <= N
806
807
808
N is the \ ``MAX(\`id\`)``\ that pt-table-checksum used when it first chunked
809
the rows. Therefore, it will only chunk the range of rows that existed when
810
the tool started and not any newly inserted rows (unless those rows happen
811
to be inserted with IDs less than N).
812
813
See also "--chunk-size-limit".
814
815
816
817
--chunk-size
818
819
type: string
820
821
Approximate number of rows or size of data to checksum at a time. Allowable
822
suffixes are k, M, G. Disallows \ ``--algorithm CHECKSUM``\ .
823
824
If you specify a chunk size, pt-table-checksum will try to find an index that
825
will let it split the table into ranges of approximately "--chunk-size"
826
rows, based on the table's index statistics. Currently only numeric and date
827
types can be chunked.
828
829
If the table is chunkable, pt-table-checksum will checksum each range separately
830
with parameters in the checksum query's WHERE clause. If pt-table-checksum
831
cannot find a suitable index, it will do the entire table in one chunk as though
832
you had not specified "--chunk-size" at all. Each table is handled
833
individually, so some tables may be chunked and others not.
834
835
The chunks will be approximately sized, and depending on the distribution of
836
values in the indexed column, some chunks may be larger than the value you
837
specify.
838
839
If you specify a suffix (one of k, M or G), the parameter is treated as a data
840
size rather than a number of rows. The output of SHOW TABLE STATUS is then used
841
to estimate the amount of data the table contains, and convert that to a number
842
of rows.
843
844
845
846
--chunk-size-limit
847
848
type: float; default: 2.0; group: Safety
849
850
Do not checksum chunks with this many times more rows than "--chunk-size".
851
852
When "--chunk-size" is given it specifies an ideal size for each chunk
853
of a chunkable table (in rows; size values are converted to rows). Before
854
checksumming each chunk, pt-table-checksum checks how many rows are in the
855
chunk with EXPLAIN. If the number of rows reported by EXPLAIN is this many
856
times greater than "--chunk-size", then the chunk is skipped and \ ``OVERSIZE``\
857
is printed for the \ ``COUNT``\ column of the "OUTPUT".
858
859
For example, if you specify "--chunk-size" 100 and a chunk has 150 rows,
860
then it is checksummed with the default "--chunk-size-limit" value 2.0
861
because 150 is less than 100 \* 2.0. But if the chunk has 205 rows, then it
862
is not checksummed because 205 is greater than 100 \* 2.0.
863
864
The minimum value for this option is 1 which means that no chunk can be any
865
larger than "--chunk-size". You probably don't want to specify 1 because
866
rows reported by EXPLAIN are estimates which can be greater than or less than
867
the real number of rows in the chunk. If too many chunks are skipped because
868
they are oversize, you might want to specify a value larger than 2.
869
870
You can disable oversize chunk checking by specifying "--chunk-size-limit" 0.
871
872
See also "--unchunkable-tables".
873
874
875
876
--columns
877
878
short form: -c; type: array; group: Filter
879
880
Checksum only this comma-separated list of columns.
881
882
883
884
--config
885
886
type: Array; group: Config
887
888
Read this comma-separated list of config files; if specified, this must be the
889
first option on the command line.
890
891
892
893
--[no]count
894
895
Count rows in tables. This is built into ACCUM and BIT_XOR, but requires an
896
extra query for CHECKSUM.
897
898
This is disabled by default to avoid an extra COUNT(\*) query when
899
"--algorithm" is CHECKSUM. If you have only MyISAM tables and live checksums
900
are enabled, both CHECKSUM and COUNT will be very fast, but otherwise you may
901
want to use one of the other algorithms.
902
903
904
905
--[no]crc
906
907
default: yes
908
909
Do a CRC (checksum) of tables.
910
911
Take the checksum of the rows as well as their count. This is enabled by
912
default. If you disable it, you'll just get COUNT(\*) queries.
913
914
915
916
--create-replicate-table
917
918
Create the replicate table given by "--replicate" if it does not exist.
919
920
Normally, if the replicate table given by "--replicate" does not exist,
921
\ ``pt-table-checksum``\ will die. With this option, however, \ ``pt-table-checksum``\
922
will create the replicate table for you, using the database.table name given to
923
"--replicate".
924
925
The structure of the replicate table is the same as the suggested table
926
mentioned in "--replicate". Note that since ENGINE is not specified, the
927
replicate table will use the server's default storage engine. If you want to
928
use a different engine, you need to create the table yourself.
929
930
931
932
--databases
933
934
short form: -d; type: hash; group: Filter
935
936
Only checksum this comma-separated list of databases.
937
938
939
940
--databases-regex
941
942
type: string
943
944
Only checksum databases whose names match this Perl regex.
945
946
947
948
--defaults-file
949
950
short form: -F; type: string; group: Connection
951
952
Only read mysql options from the given file. You must give an absolute
953
pathname.
954
955
956
957
--empty-replicate-table
958
959
DELETE all rows in the "--replicate" table before starting.
960
961
Issues a DELETE against the table given by "--replicate" before beginning
962
work. Ignored if "--replicate" is not specified. This can be useful to
963
remove entries related to tables that no longer exist, or just to clean out the
964
results of a previous run.
965
966
If you want to delete entries for specific databases or tables you must
967
do this manually.
968
969
970
971
--engines
972
973
short form: -e; type: hash; group: Filter
974
975
Do only this comma-separated list of storage engines.
976
977
978
979
--explain
980
981
group: Output
982
983
Show, but do not execute, checksum queries (disables "--empty-replicate-table").
984
985
986
987
--explain-hosts
988
989
group: Help
990
991
Print connection information and exit.
992
993
Print out a list of hosts to which pt-table-checksum will connect, with all
994
the various connection options, and exit. See "SPECIFYING HOSTS".
995
996
997
998
--float-precision
999
1000
type: int
1001
1002
Precision for \ ``FLOAT``\ and \ ``DOUBLE``\ number-to-string conversion. Causes FLOAT
1003
and DOUBLE values to be rounded to the specified number of digits after the
1004
decimal point, with the ROUND() function in MySQL. This can help avoid
1005
checksum mismatches due to different floating-point representations of the same
1006
values on different MySQL versions and hardware. The default is no rounding;
1007
the values are converted to strings by the CONCAT() function, and MySQL chooses
1008
the string representation. If you specify a value of 2, for example, then the
1009
values 1.008 and 1.009 will be rounded to 1.01, and will checksum as equal.
1010
1011
1012
1013
--function
1014
1015
type: string
1016
1017
Hash function for checksums (FNV1A_64, MURMUR_HASH, SHA1, MD5, CRC32, etc).
1018
1019
You can use this option to choose the cryptographic hash function used for
1020
"--algorithm"=ACCUM or "--algorithm"=BIT_XOR. The default is to use
1021
\ ``CRC32``\ , but \ ``MD5``\ and \ ``SHA1``\ also work, and you can use your own function,
1022
such as a compiled UDF, if you wish. Whatever function you specify is run in
1023
SQL, not in Perl, so it must be available to MySQL.
1024
1025
The \ ``FNV1A_64``\ UDF mentioned in the benchmarks is much faster than \ ``MD5``\ . The
1026
C++ source code is distributed with Maatkit. It is very simple to compile and
1027
install; look at the header in the source code for instructions. If it is
1028
installed, it is preferred over \ ``MD5``\ . You can also use the MURMUR_HASH
1029
function if you compile and install that as a UDF; the source is also
1030
distributed with Maatkit, and it is faster and has better distribution
1031
than FNV1A_64.
1032
1033
1034
1035
--help
1036
1037
group: Help
1038
1039
Show help and exit.
1040
1041
1042
1043
--ignore-columns
1044
1045
type: Hash; group: Filter
1046
1047
Ignore this comma-separated list of columns when calculating the checksum.
1048
1049
This option only affects the checksum when using the ACCUM or BIT_XOR
1050
"--algorithm".
1051
1052
1053
1054
--ignore-databases
1055
1056
type: Hash; group: Filter
1057
1058
Ignore this comma-separated list of databases.
1059
1060
1061
1062
--ignore-databases-regex
1063
1064
type: string
1065
1066
Ignore databases whose names match this Perl regex.
1067
1068
1069
1070
--ignore-engines
1071
1072
type: Hash; default: FEDERATED,MRG_MyISAM; group: Filter
1073
1074
Ignore this comma-separated list of storage engines.
1075
1076
1077
1078
--ignore-tables
1079
1080
type: Hash; group: Filter
1081
1082
Ignore this comma-separated list of tables.
1083
1084
Table names may be qualified with the database name.
1085
1086
1087
1088
--ignore-tables-regex
1089
1090
type: string
1091
1092
Ignore tables whose names match the Perl regex.
1093
1094
1095
1096
--lock
1097
1098
Lock on master until done on slaves (implies "--slave-lag").
1099
1100
This option can help you to get a consistent read on a master and many slaves.
1101
If you specify this option, pt-table-checksum will lock the table on the
1102
first server on the command line, which it assumes to be the master. It will
1103
keep this lock until the checksums complete on the other servers.
1104
1105
This option isn't very useful by itself, so you probably want to use "--wait"
1106
instead.
1107
1108
Note: if you're checksumming a slave against its master, you should use
1109
"--replicate". In that case, there's no need for locking, waiting, or any of
1110
that.
1111
1112
1113
1114
--max-lag
1115
1116
type: time; group: Throttle; default: 1s
1117
1118
Suspend checksumming if the slave given by "--check-slave-lag" lags.
1119
1120
This option causes pt-table-checksum to look at the slave every time it's about
1121
to checksum a chunk. If the slave's lag is greater than the option's value, or
1122
if the slave isn't running (so its lag is NULL), pt-table-checksum sleeps for
1123
"--check-interval" seconds and then looks at the lag again. It repeats until
1124
the slave is caught up, then proceeds to checksum the chunk.
1125
1126
This option is useful to let you checksum data as fast as the slaves can handle
1127
it, assuming the slave you directed pt-table-checksum to monitor is
1128
representative of all the slaves that may be replicating from this server. It
1129
should eliminate the need for "--sleep" or "--sleep-coef".
1130
1131
1132
1133
--modulo
1134
1135
type: int
1136
1137
Do only every Nth chunk on chunked tables.
1138
1139
This option lets you checksum only some chunks of the table. This is a useful
1140
alternative to "--probability" when you want to be sure you get full coverage
1141
in some specified number of runs; for example, you can do only every 7th chunk,
1142
and then use "--offset" to rotate the modulo every day of the week.
1143
1144
Just like with "--probability", a table that cannot be chunked is done every
1145
time.
1146
1147
1148
1149
--offset
1150
1151
type: string; default: 0
1152
1153
Modulo offset expression for use with "--modulo".
1154
1155
The argument may be an SQL expression, such as \ ``WEEKDAY(NOW())``\ (which returns
1156
a number from 0 through 6). The argument is evaluated by MySQL. The result is
1157
used as follows: if chunk_num % "--modulo" == "--offset", the chunk will
1158
be checksummed.
1159
1160
1161
1162
--[no]optimize-xor
1163
1164
default: yes
1165
1166
Optimize BIT_XOR with user variables.
1167
1168
This option specifies to use user variables to reduce the number of times each
1169
row must be passed through the cryptographic hash function when you are using
1170
the BIT_XOR algorithm.
1171
1172
With the optimization, the queries look like this in pseudo-code:
1173
1174
1175
.. code-block:: perl
1176
1177
SELECT CONCAT(
1178
BIT_XOR(SLICE_OF(@user_variable)),
1179
BIT_XOR(SLICE_OF(@user_variable)),
1180
...
1181
BIT_XOR(SLICE_OF(@user_variable := HASH(col1, col2... colN))));
1182
1183
1184
The exact positioning of user variables and calls to the hash function is
1185
determined dynamically, and will vary between MySQL versions. Without the
1186
optimization, it looks like this:
1187
1188
1189
.. code-block:: perl
1190
1191
SELECT CONCAT(
1192
BIT_XOR(SLICE_OF(MD5(col1, col2... colN))),
1193
BIT_XOR(SLICE_OF(MD5(col1, col2... colN))),
1194
...
1195
BIT_XOR(SLICE_OF(MD5(col1, col2... colN))));
1196
1197
1198
The difference is the number of times all the columns must be mashed together
1199
and fed through the hash function. If you are checksumming really large
1200
columns, such as BLOB or TEXT columns, this might make a big difference.
1201
1202
1203
1204
--password
1205
1206
short form: -p; type: string; group: Connection
1207
1208
Password to use when connecting.
1209
1210
1211
1212
--pid
1213
1214
type: string
1215
1216
Create the given PID file. The file contains the process ID of the script.
1217
The PID file is removed when the script exits. Before starting, the script
1218
checks if the PID file already exists. If it does not, then the script creates
1219
and writes its own PID to it. If it does, then the script checks the following:
1220
if the file contains a PID and a process is running with that PID, then
1221
the script dies; or, if there is no process running with that PID, then the
1222
script overwrites the file with its own PID and starts; else, if the file
1223
contains no PID, then the script dies.
1224
1225
1226
1227
--port
1228
1229
short form: -P; type: int; group: Connection
1230
1231
Port number to use for connection.
1232
1233
1234
1235
--probability
1236
1237
type: int; default: 100
1238
1239
Checksums will be run with this percent probability.
1240
1241
This is an integer between 1 and 100. If 100, every chunk of every table will
1242
certainly be checksummed. If less than that, there is a chance that some chunks
1243
of some tables will be skipped. This is useful for routine jobs designed to
1244
randomly sample bits of tables without checksumming the whole server. By
1245
default, if a table is not chunkable, it will be checksummed every time even
1246
when the probability is less than 100. You can override this with
1247
"--single-chunk".
1248
1249
See also "--modulo".
1250
1251
1252
1253
--progress
1254
1255
type: array; default: time,30
1256
1257
Print progress reports to STDERR. Currently, this feature is only for when
1258
"--throttle-method" waits for slaves to catch up.
1259
1260
The value is a comma-separated list with two parts. The first part can be
1261
percentage, time, or iterations; the second part specifies how often an update
1262
should be printed, in percentage, seconds, or number of iterations.
1263
1264
1265
1266
--quiet
1267
1268
short form: -q; group: Output
1269
1270
Do not print checksum results.
1271
1272
1273
1274
--recheck
1275
1276
Re-checksum chunks that "--replicate-check" found to be different.
1277
1278
1279
1280
--recurse
1281
1282
type: int; group: Throttle
1283
1284
Number of levels to recurse in the hierarchy when discovering slaves.
1285
Default is infinite.
1286
1287
See "--recursion-method".
1288
1289
1290
1291
--recursion-method
1292
1293
type: string
1294
1295
Preferred recursion method for discovering slaves.
1296
1297
Possible methods are:
1298
1299
1300
.. code-block:: perl
1301
1302
METHOD USES
1303
=========== ================
1304
processlist SHOW PROCESSLIST
1305
hosts SHOW SLAVE HOSTS
1306
1307
1308
The processlist method is preferred because SHOW SLAVE HOSTS is not reliable.
1309
However, the hosts method is required if the server uses a non-standard
1310
port (not 3306). Usually pt-table-checksum does the right thing and finds
1311
the slaves, but you may give a preferred method and it will be used first.
1312
If it doesn't find any slaves, the other methods will be tried.
1313
1314
1315
1316
--replicate
1317
1318
type: string
1319
1320
Replicate checksums to slaves (disallows --algorithm CHECKSUM).
1321
1322
This option enables a completely different checksum strategy for a consistent,
1323
lock-free checksum across a master and its slaves. Instead of running the
1324
checksum queries on each server, you run them only on the master. You specify a
1325
table, fully qualified in db.table format, to insert the results into. The
1326
checksum queries will insert directly into the table, so they will be replicated
1327
through the binlog to the slaves.
1328
1329
When the queries are finished replicating, you can run a simple query on each
1330
slave to see which tables have differences from the master. With the
1331
"--replicate-check" option, pt-table-checksum can run the query for you to
1332
make it even easier. See "CONSISTENT CHECKSUMS" for details.
1333
1334
If you find tables that have differences, you can use the chunk boundaries in a
1335
WHERE clause with pt-table-sync to help repair them more efficiently. See
1336
pt-table-sync for details.
1337
1338
The table must have at least these columns: db, tbl, chunk, boundaries,
1339
this_crc, master_crc, this_cnt, master_cnt. The table may be named anything you
1340
wish. Here is a suggested table structure, which is automatically used for
1341
"--create-replicate-table" (MAGIC_create_replicate):
1342
1343
1344
.. code-block:: perl
1345
1346
CREATE TABLE checksum (
1347
db char(64) NOT NULL,
1348
tbl char(64) NOT NULL,
1349
chunk int NOT NULL,
1350
boundaries char(100) NOT NULL,
1351
this_crc char(40) NOT NULL,
1352
this_cnt int NOT NULL,
1353
master_crc char(40) NULL,
1354
master_cnt int NULL,
1355
ts timestamp NOT NULL,
1356
PRIMARY KEY (db, tbl, chunk)
1357
);
1358
1359
1360
Be sure to choose an appropriate storage engine for the checksum table. If you
1361
are checksumming InnoDB tables, for instance, a deadlock will break replication
1362
if the checksum table is non-transactional, because the transaction will still
1363
be written to the binlog. It will then replay without a deadlock on the
1364
slave and break replication with "different error on master and slave." This
1365
is not a problem with pt-table-checksum, it's a problem with MySQL
1366
replication, and you can read more about it in the MySQL manual.
1367
1368
This works only with statement-based replication (pt-table-checksum will switch
1369
the binlog format to STATEMENT for the duration of the session if your server
1370
uses row-based replication).
1371
1372
In contrast to running the tool against multiple servers at once, using this
1373
option eliminates the complexities of synchronizing checksum queries across
1374
multiple servers, which normally requires locking and unlocking, waiting for
1375
master binlog positions, and so on. Thus, it disables "--lock", "--wait",
1376
and "--slave-lag" (but not "--check-slave-lag", which is a way to throttle
1377
the execution speed).
1378
1379
The checksum queries actually do a REPLACE into this table, so existing rows
1380
need not be removed before running. However, you may wish to do this anyway to
1381
remove rows related to tables that don't exist anymore. The
1382
"--empty-replicate-table" option does this for you.
1383
1384
Since the table must be qualified with a database (e.g. \ ``db.checksums``\ ),
1385
pt-table-checksum will only USE this database. This may be important if any
1386
replication options are set because it could affect whether or not changes
1387
to the table are replicated.
1388
1389
If the slaves have any --replicate-do-X or --replicate-ignore-X options, you
1390
should be careful not to checksum any databases or tables that exist on the
1391
master and not the slaves. Changes to such tables may not normally be executed
1392
on the slaves because of the --replicate options, but the checksum queries
1393
modify the contents of the table that stores the checksums, not the tables whose
1394
data you are checksumming. Therefore, these queries will be executed on the
1395
slave, and if the table or database you're checksumming does not exist, the
1396
queries will cause replication to fail. For more information on replication
1397
rules, see `http://dev.mysql.com/doc/en/replication-rules.html <http://dev.mysql.com/doc/en/replication-rules.html>`_.
1398
1399
The table specified by "--replicate" will never be checksummed itself.
1400
1401
1402
1403
--replicate-check
1404
1405
type: int
1406
1407
Check results in "--replicate" table, to the specified depth. You must use
1408
this after you run the tool normally; it skips the checksum step and only checks
1409
results.
1410
1411
It recursively finds differences recorded in the table given by
1412
"--replicate". It recurses to the depth you specify: 0 is no recursion
1413
(check only the server you specify), 1 is check the server and its slaves, 2 is
1414
check the slaves of its slaves, and so on.
1415
1416
It finds differences by running the query shown in "CONSISTENT CHECKSUMS",
1417
and prints results, then exits after printing. This is just a convenient way of
1418
running the query so you don't have to do it manually.
1419
1420
The output is one informational line per slave host, followed by the results
1421
of the query, if any. If "--quiet" is specified, there is no output. If
1422
there are no differences between the master and any slave, there is no output.
1423
If any slave has chunks that differ from the master, pt-table-checksum's
1424
exit status is 1; otherwise it is 0.
1425
1426
This option makes \ ``pt-table-checksum``\ look for slaves by running \ ``SHOW
1427
PROCESSLIST``\ . If it finds connections that appear to be from slaves, it derives
1428
connection information for each slave with the same default-and-override method
1429
described in "SPECIFYING HOSTS".
1430
1431
If \ ``SHOW PROCESSLIST``\ doesn't return any rows, \ ``pt-table-checksum``\ looks at
1432
\ ``SHOW SLAVE HOSTS``\ instead. The host and port, and user and password if
1433
available, from \ ``SHOW SLAVE HOSTS``\ are combined into a DSN and used as the
1434
argument. This requires slaves to be configured with \ ``report-host``\ ,
1435
\ ``report-port``\ and so on.
1436
1437
This requires the @@SERVER_ID system variable, so it works only on MySQL
1438
3.23.26 or newer.
1439
1440
1441
1442
--replicate-database
1443
1444
type: string
1445
1446
\ ``USE``\ only this database with "--replicate". By default, pt-table-checksum
1447
executes USE to set its default database to the database that contains the table
1448
it's currently working on. It changes its default database as it works on
1449
different tables. This is is a best effort to avoid problems with replication
1450
filters such as binlog_ignore_db and replicate_ignore_db. However, replication
1451
filters can create a situation where there simply is no one right way to do
1452
things. Some statements might not be replicated, and others might cause
1453
replication to fail on the slaves. In such cases, it is up to the user to
1454
specify a safe default database. This option specifies a default database that
1455
pt-table-checksum selects with USE, and never changes afterwards. See also
1456
<L"--[no]check-replication-filters">.
1457
1458
1459
1460
--resume
1461
1462
type: string
1463
1464
Resume checksum using given output file from a previously interrupted run.
1465
1466
The given output file should be the literal output from a previous run of
1467
\ ``pt-table-checksum``\ . For example:
1468
1469
1470
.. code-block:: perl
1471
1472
pt-table-checksum host1 host2 -C 100 > checksum_results.txt
1473
pt-table-checksum host1 host2 -C 100 --resume checksum_results.txt
1474
1475
1476
The command line options given to the first run and the resumed run must
1477
be identical (except, of course, for --resume). If they are not, the result
1478
will be unpredictable and probably wrong.
1479
1480
"--resume" does not work with "--replicate"; for that, use
1481
"--resume-replicate".
1482
1483
1484
1485
--resume-replicate
1486
1487
Resume "--replicate".
1488
1489
This option resumes a previous checksum operation using "--replicate".
1490
It is like "--resume" but does not require an output file. Instead,
1491
it uses the checksum table given to "--replicate" to determine where to
1492
resume the checksum operation.
1493
1494
1495
1496
--save-since
1497
1498
When "--arg-table" and "--since" are given, save the current "--since"
1499
value into that table's \ ``since``\ column after checksumming. In this way you can
1500
incrementally checksum tables by starting where the last one finished.
1501
1502
The value to be saved could be the current timestamp, or it could be the maximum
1503
existing value of the column given by "--since-column". It depends on what
1504
options are in effect. See the description of "--since" to see how
1505
timestamps are different from ordinary values.
1506
1507
1508
1509
--schema
1510
1511
Checksum \ ``SHOW CREATE TABLE``\ instead of table data.
1512
1513
1514
1515
--separator
1516
1517
type: string; default: #
1518
1519
The separator character used for CONCAT_WS().
1520
1521
This character is used to join the values of columns when checksumming with
1522
"--algorithm" of BIT_XOR or ACCUM.
1523
1524
1525
1526
--set-vars
1527
1528
type: string; default: wait_timeout=10000; group: Connection
1529
1530
Set these MySQL variables. Immediately after connecting to MySQL, this
1531
string will be appended to SET and executed.
1532
1533
1534
1535
--since
1536
1537
type: string
1538
1539
Checksum only data newer than this value.
1540
1541
If the table is chunk-able or nibble-able, this value will apply to the first
1542
column of the chunked or nibbled index.
1543
1544
This is not too different to "--where", but instead of universally applying a
1545
WHERE clause to every table, it selectively finds the right column to use and
1546
applies it only if such a column is found. See also "--since-column".
1547
1548
The argument may be an expression, which is evaluated by MySQL. For example,
1549
you can specify \ ``CURRENT_DATE - INTERVAL 7 DAY``\ to get the date of one week
1550
ago.
1551
1552
A special bit of extra magic: if the value is temporal (looks like a date or
1553
datetime), then the table is checksummed only if the create time (or last
1554
modified time, for tables that report the last modified time, such as MyISAM
1555
tables) is newer than the value. In this sense it's not applied as a WHERE
1556
clause at all.
1557
1558
1559
1560
--since-column
1561
1562
type: string
1563
1564
The column name to be used for "--since".
1565
1566
The default is for the tool to choose the best one automatically. If you
1567
specify a value, that will be used if possible; otherwise the best
1568
auto-determined one; otherwise none. If the column doesn't exist in the table,
1569
it is just ignored.
1570
1571
1572
1573
--single-chunk
1574
1575
Permit skipping with "--probability" if there is only one chunk.
1576
1577
Normally, if a table isn't split into many chunks, it will always be
1578
checksummed regardless of "--probability". This setting lets the
1579
probabilistic behavior apply to tables that aren't divided into chunks.
1580
1581
1582
1583
--slave-lag
1584
1585
group: Output
1586
1587
Report replication delay on the slaves.
1588
1589
If this option is enabled, the output will show how many seconds behind the
1590
master each slave is. This can be useful when you want a fast, parallel,
1591
non-blocking checksum, and you know your slaves might be delayed relative to the
1592
master. You can inspect the results and make an educated guess whether any
1593
discrepancies on the slave are due to replication delay instead of corrupt data.
1594
1595
If you're using "--replicate", a slave that is delayed relative to the master
1596
does not invalidate the correctness of the results, so this option is disabled.
1597
1598
1599
1600
--sleep
1601
1602
type: int; group: Throttle
1603
1604
Sleep time between checksums.
1605
1606
If this option is specified, pt-table-checksum will sleep the specified
1607
number of seconds between checksums. That is, it will sleep between every
1608
table, and if you specify "--chunk-size", it will also sleep between chunks.
1609
1610
This is a very crude way to throttle checksumming; see "--sleep-coef" and
1611
"--check-slave-lag" for techniques that permit greater control.
1612
1613
1614
1615
--sleep-coef
1616
1617
type: float; group: Throttle
1618
1619
Calculate "--sleep" as a multiple of the last checksum time.
1620
1621
If this option is specified, pt-table-checksum will sleep the amount of
1622
time elapsed during the previous checksum, multiplied by the specified
1623
coefficient. This option is ignored if "--sleep" is specified.
1624
1625
This is a slightly more sophisticated way to throttle checksum speed: sleep a
1626
varying amount of time between chunks, depending on how long the chunks are
1627
taking. Even better is to use "--check-slave-lag" if you're checksumming
1628
master/slave replication.
1629
1630
1631
1632
--socket
1633
1634
short form: -S; type: string; group: Connection
1635
1636
Socket file to use for connection.
1637
1638
1639
1640
--tab
1641
1642
group: Output
1643
1644
Print tab-separated output, not column-aligned output.
1645
1646
1647
1648
--tables
1649
1650
short form: -t; type: hash; group: Filter
1651
1652
Do only this comma-separated list of tables.
1653
1654
Table names may be qualified with the database name.
1655
1656
1657
1658
--tables-regex
1659
1660
type: string
1661
1662
Only checksum tables whose names match this Perl regex.
1663
1664
1665
1666
--throttle-method
1667
1668
type: string; default: none; group: Throttle
1669
1670
Throttle checksumming when doing "--replicate".
1671
1672
At present there is only one method: \ ``slavelag``\ . When "--replicate" is
1673
used, pt-table-checksum automatically sets "--throttle-method" to
1674
\ ``slavelag``\ and discovers every slave and throttles checksumming if any slave
1675
lags more than "--max-lag". Specify \ ``-throttle-method none``\ to disable
1676
this behavior completely, or specify "--check-slave-lag" and
1677
pt-table-checksum will only check that slave.
1678
1679
See also "--recurse" and "--recursion-method".
1680
1681
1682
1683
--trim
1684
1685
Trim \ ``VARCHAR``\ columns (helps when comparing 4.1 to >= 5.0).
1686
1687
This option adds a \ ``TRIM()``\ to \ ``VARCHAR``\ columns in \ ``BIT_XOR``\ and \ ``ACCUM``\
1688
modes.
1689
1690
This is useful when you don't care about the trailing space differences between
1691
MySQL versions which vary in their handling of trailing spaces. MySQL 5.0 and
1692
later all retain trailing spaces in \ ``VARCHAR``\ , while previous versions would
1693
remove them.
1694
1695
1696
1697
--unchunkable-tables
1698
1699
group: Safety
1700
1701
Checksum tables that cannot be chunked when "--chunk-size" is specified.
1702
1703
By default pt-table-checksum will not checksum a table that cannot be chunked
1704
when "--chunk-size" is specified because this might result in a huge,
1705
non-chunkable table being checksummed in one huge, memory-intensive chunk.
1706
1707
Specifying this option allows checksumming tables that cannot be chunked.
1708
Be careful when using this option! Make sure any non-chunkable tables
1709
are not so large that they will cause the tool to consume too much memory
1710
or CPU.
1711
1712
See also "--chunk-size-limit".
1713
1714
1715
1716
--[no]use-index
1717
1718
default: yes
1719
1720
Add FORCE INDEX hints to SQL statements.
1721
1722
By default \ ``pt-table-checksum``\ adds an index hint (\ ``FORCE INDEX``\ for MySQL
1723
v4.0.9 and newer, \ ``USE INDEX``\ for older MySQL versions) to each SQL statement
1724
to coerce MySQL into using the "--chunk-index" (whether the index is
1725
specified by the option or auto-detected). Specifying \ ``--no-use-index``\ causes
1726
\ ``pt-table-checksum``\ to omit index hints.
1727
1728
1729
1730
--user
1731
1732
short form: -u; type: string; group: Connection
1733
1734
User for login if not current user.
1735
1736
1737
1738
--[no]verify
1739
1740
default: yes
1741
1742
Verify checksum compatibility across servers.
1743
1744
This option runs a trivial checksum on all servers to ensure they have
1745
compatible CONCAT_WS() and cryptographic hash functions.
1746
1747
Versions of MySQL before 4.0.14 will skip empty strings and NULLs in
1748
CONCAT_WS, and others will only skip NULLs. The two kinds of behavior will
1749
produce different results if you have any columns containing the empty string
1750
in your table. If you know you don't (for instance, all columns are
1751
integers), you can safely disable this check and you will get a reliable
1752
checksum even on servers with different behavior.
1753
1754
1755
1756
--version
1757
1758
group: Help
1759
1760
Show version and exit.
1761
1762
1763
1764
--wait
1765
1766
short form: -w; type: time
1767
1768
Wait this long for slaves to catch up to their master (implies "--lock"
1769
"--slave-lag").
1770
1771
Note: the best way to verify that a slave is in sync with its master is to use
1772
"--replicate" instead. The "--wait" option is really only useful if
1773
you're trying to compare masters and slaves without using "--replicate",
1774
which is possible but complex and less efficient in some ways.
1775
1776
This option helps you get a consistent checksum across a master server and its
1777
slaves. It combines locking and waiting to accomplish this. First it locks the
1778
table on the master (the first server on the command line). Then it finds the
1779
master's binlog position. Checksums on slaves will be deferred until they reach
1780
the same binlog position.
1781
1782
The argument to the option is the number of seconds to wait for the slaves to
1783
catch up to the master. It is actually the argument to MASTER_POS_WAIT(). If
1784
the slaves don't catch up to the master within this time, they will unblock
1785
and go ahead with the checksum. You can tell whether this happened by
1786
examining the STAT column in the output, which is the return value of
1787
MASTER_POS_WAIT().
1788
1789
1790
1791
--where
1792
1793
type: string
1794
1795
Do only rows matching this \ ``WHERE``\ clause (disallows "--algorithm" CHECKSUM).
1796
1797
You can use this option to limit the checksum to only part of the table. This
1798
is particularly useful if you have append-only tables and don't want to
1799
constantly re-check all rows; you could run a daily job to just check
1800
yesterday's rows, for instance.
1801
1802
This option is much like the -w option to mysqldump. Do not specify the WHERE
1803
keyword. You may need to quote the value. Here is an example:
1804
1805
1806
.. code-block:: perl
1807
1808
pt-table-checksum --where "foo=bar"
1809
1810
1811
1812
1813
--[no]zero-chunk
1814
1815
default: yes
1816
1817
Add a chunk for rows with zero or zero-equivalent values. The only has an
1818
effect when "--chunk-size" is specified. The purpose of the zero chunk
1819
is to capture a potentially large number of zero values that would imbalance
1820
the size of the first chunk. For example, if a lot of negative numbers were
1821
inserted into an unsigned integer column causing them to be stored as zeros,
1822
then these zero values are captured by the zero chunk instead of the first
1823
chunk and all its non-zero values.
1824
1825
1826
1827
1828
***********
1829
DSN OPTIONS
1830
***********
1831
1832
1833
These DSN options are used to create a DSN. Each option is given like
1834
\ ``option=value``\ . The options are case-sensitive, so P and p are not the
1835
same option. There cannot be whitespace before or after the \ ``=``\ and
1836
if the value contains whitespace it must be quoted. DSN options are
1837
comma-separated. See the percona-toolkit manpage for full details.
1838
1839
1840
\* A
1841
1842
dsn: charset; copy: yes
1843
1844
Default character set.
1845
1846
1847
1848
\* D
1849
1850
dsn: database; copy: yes
1851
1852
Default database.
1853
1854
1855
1856
\* F
1857
1858
dsn: mysql_read_default_file; copy: yes
1859
1860
Only read default options from the given file
1861
1862
1863
1864
\* h
1865
1866
dsn: host; copy: yes
1867
1868
Connect to host.
1869
1870
1871
1872
\* p
1873
1874
dsn: password; copy: yes
1875
1876
Password to use when connecting.
1877
1878
1879
1880
\* P
1881
1882
dsn: port; copy: yes
1883
1884
Port number to use for connection.
1885
1886
1887
1888
\* S
1889
1890
dsn: mysql_socket; copy: yes
1891
1892
Socket file to use for connection.
1893
1894
1895
1896
\* u
1897
1898
dsn: user; copy: yes
1899
1900
User for login if not current user.
1901
1902
1903
1904
1905
***********
1906
DOWNLOADING
1907
***********
1908
1909
1910
Visit `http://www.percona.com/software/ <http://www.percona.com/software/>`_ to download the latest release of
1911
Percona Toolkit. Or, to get the latest release from the command line:
1912
1913
1914
.. code-block:: perl
1915
1916
wget percona.com/latest/percona-toolkit/PKG
1917
1918
1919
Replace \ ``PKG``\ with \ ``tar``\ , \ ``rpm``\ , or \ ``deb``\ to download the package in that
1920
format. You can also get individual tools from the latest release:
1921
1922
1923
.. code-block:: perl
1924
1925
wget percona.com/latest/percona-toolkit/TOOL
1926
1927
1928
Replace \ ``TOOL``\ with the name of any tool.
1929
1930
1931
***********
1932
ENVIRONMENT
1933
***********
1934
1935
1936
The environment variable \ ``PTDEBUG``\ enables verbose debugging output to STDERR.
1937
To enable debugging and capture all output to a file, run the tool like:
1938
1939
1940
.. code-block:: perl
1941
1942
PTDEBUG=1 pt-table-checksum ... > FILE 2>&1
1943
1944
1945
Be careful: debugging output is voluminous and can generate several megabytes
1946
of output.
1947
1948
1949
*******************
1950
SYSTEM REQUIREMENTS
1951
*******************
1952
1953
1954
You need Perl, DBI, DBD::mysql, and some core packages that ought to be
1955
installed in any reasonably new version of Perl.
1956
1957
1958
****
1959
BUGS
1960
****
1961
1962
1963
For a list of known bugs, see `http://www.percona.com/bugs/pt-table-checksum <http://www.percona.com/bugs/pt-table-checksum>`_.
1964
1965
Please report bugs at `https://bugs.launchpad.net/percona-toolkit <https://bugs.launchpad.net/percona-toolkit>`_.
1966
Include the following information in your bug report:
1967
1968
1969
\* Complete command-line used to run the tool
1970
1971
1972
1973
\* Tool "--version"
1974
1975
1976
1977
\* MySQL version of all servers involved
1978
1979
1980
1981
\* Output from the tool including STDERR
1982
1983
1984
1985
\* Input files (log/dump/config files, etc.)
1986
1987
1988
1989
If possible, include debugging output by running the tool with \ ``PTDEBUG``\ ;
1990
see "ENVIRONMENT".
1991
1992
1993
*******
1994
AUTHORS
1995
*******
1996
1997
1998
Baron Schwartz
1999
2000
2001
***************
2002
ACKNOWLEDGMENTS
2003
***************
2004
2005
2006
Claus Jeppesen, Francois Saint-Jacques, Giuseppe Maxia, Heikki Tuuri,
2007
James Briggs, Martin Friebe, and Sergey Zhuravlev
2008
2009
2010
*********************
2011
ABOUT PERCONA TOOLKIT
2012
*********************
2013
2014
2015
This tool is part of Percona Toolkit, a collection of advanced command-line
2016
tools developed by Percona for MySQL support and consulting. Percona Toolkit
2017
was forked from two projects in June, 2011: Maatkit and Aspersa. Those
2018
projects were created by Baron Schwartz and developed primarily by him and
2019
Daniel Nichter, both of whom are employed by Percona. Visit
2020
`http://www.percona.com/software/ <http://www.percona.com/software/>`_ for more software developed by Percona.
2021
2022
2023
********************************
2024
COPYRIGHT, LICENSE, AND WARRANTY
2025
********************************
2026
2027
2028
This program is copyright 2007-2011 Baron Schwartz, 2011 Percona Inc.
2029
Feedback and improvements are welcome.
2030
2031
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
2032
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
2033
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
2034
2035
This program is free software; you can redistribute it and/or modify it under
2036
the terms of the GNU General Public License as published by the Free Software
2037
Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
2038
systems, you can issue \`man perlgpl' or \`man perlartistic' to read these
2039
licenses.
2040
2041
You should have received a copy of the GNU General Public License along with
2042
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
2043
Place, Suite 330, Boston, MA 02111-1307 USA.
2044
2045
2046
*******
2047
VERSION
2048
*******
2049
2050
2051
Percona Toolkit v1.0.0 released 2011-08-01
2052
Loggerhead is a web-based interface for Breezy
Version: 2.0.1