PT-TABLE-CHECKSUM(1)

📦

version 3.7.1_4 · 5 versions

3.7.1_4 · arch rolling (latest)
3.7.1 · nixos 26.05, nixpkgs unstable
3.7.1 · ubuntu stonking
3.7.1 · ubuntu resolute
3.2.1 · debian bookworm, debian trixie, ubuntu jammy, ubuntu noble

💖 NAME 💖

Bestie, welcome to PT-TABLE-CHECKSUM(1) — the User Contributed Perl Documentation page for pt-table-checksum, and it's literally here to verify MySQL replication integrity. That's the whole vibe: it checks whether your replicas actually match your source. 💅✨

✨ SYNOPSIS ✨

Okay so first, the usage line, exactly:

Usage: pt-table-checksum [OPTIONS] [DSN]

pt-table-checksum does an online replication consistency check by running checksum queries on the replication source. Those queries produce different results on replicas that are inconsistent with the source. The optional DSN specifies the source host. The tool's EXIT STATUS is non-zero if any differences are found, or if any warnings or errors occur. 🫶

The following command will connect to the replication source on localhost, checksum every table, and report the results on every detected replica:

pt-table-checksum

This tool is focused on finding data differences efficiently. If any data is different, you can resolve the problem with pt-table-sync. Slay. 🔥

⚠️ RISKS ⚠️

Percona Toolkit is mature, proven in the real world, and well tested — but all database tools can pose a risk to the system and the database server. Before using this tool, please:

Read the tool's documentation
Review the tool's known BUGS
Test the tool on a non-production server
Backup your production server and verify the backups

See also LIMITATIONS. No cap, this part matters. 🚨

🌸 DESCRIPTION 🌸

pt-table-checksum is designed to do the right thing by default in almost every case. When in doubt, use --explain to see how the tool will checksum a table. The following is a high-level overview of how the tool functions. 👑

In contrast to older versions of pt-table-checksum, this tool is focused on a single purpose, and does not have a lot of complexity or support many different checksumming techniques. It executes checksum queries on only one server, and these flow through replication to re-execute on replicas. If you need the older behavior, you can use Percona Toolkit version 1.0. 🕰️

pt-table-checksum connects to the server you specify, and finds databases and tables that match the filters you specify (if any). It works one table at a time, so it does not accumulate large amounts of memory or do a lot of work before beginning to checksum. This makes it usable on very large servers. We have used it on servers with hundreds of thousands of databases and tables, and trillions of rows. No matter how large the server is, pt-table-checksum works equally well. Literally iconic. 💪

One reason it can work on very large tables is that it divides each table into chunks of rows, and checksums each chunk with a single REPLACE..SELECT query. It varies the chunk size to make the checksum queries run in the desired amount of time. The goal of chunking the tables, instead of doing each table with a single big query, is to ensure that checksums are unintrusive and don't cause too much replication lag or load on the server. That's why the target time for each chunk is 0.5 seconds by default. ⏱️

The tool keeps track of how quickly the server is able to execute the queries, and adjusts the chunks as it learns more about the server's performance. It uses an exponentially decaying weighted average to keep the chunk size stable, yet remain responsive if the server's performance changes during checksumming for any reason. This means that the tool will quickly throttle itself if your server becomes heavily loaded during a traffic spike or a background task, for example. It's giving self-aware main character energy. 🧚

Chunking is accomplished by a technique that we used to call "nibbling" in other tools in Percona Toolkit. It is the same technique used for pt-archiver, for example. The legacy chunking algorithms used in older versions of pt-table-checksum are removed, because they did not result in predictably sized chunks, and didn't work well on many tables. All that is required to divide a table into chunks is an index of some sort (preferably a primary key or unique index). If there is no index, and the table contains a suitably small number of rows, the tool will checksum the table in a single chunk. 🍓

pt-table-checksum has many other safeguards to ensure that it does not interfere with any server's operation, including replicas. To accomplish this, pt-table-checksum detects replicas and connects to them automatically. (If this fails, you can give it a hint with the --recursion-method option.) 🦋

The tool monitors replicas continually. If any replica falls too far behind in replication, pt-table-checksum pauses to allow it to catch up. If any replica has an error, or replication stops, pt-table-checksum pauses and waits. In addition, pt-table-checksum looks for common causes of problems, such as replication filters, and refuses to operate unless you force it to. Replication filters are dangerous, because the queries that pt-table-checksum executes could potentially conflict with them and cause replication to fail. Big yikes. 💀

pt-table-checksum verifies that chunks are not too large to checksum safely. It performs an EXPLAIN query on each chunk, and skips chunks that might be larger than the desired number of rows. You can configure the sensitivity of this safeguard with the --chunk-size-limit option. If a table will be checksummed in a single chunk because it has a small number of rows, then pt-table-checksum additionally verifies that the table isn't oversized on replicas. This avoids the following scenario: a table is empty on the replication source but is very large on a replica, and is checksummed in a single large query, which causes a very long delay in replication. 🙈

There are several other safeguards. For example, pt-table-checksum sets its session-level innodb_lock_wait_timeout to 1 second, so that if there is a lock wait, it will be the victim instead of causing other queries to time out. Another safeguard checks the load on the database server, and pauses if the load is too high. There is no single right answer for how to do this, but by default pt-table-checksum will pause if there are more than 25 concurrently executing queries. You should probably set a sane value for your server with the --max-load option. 🎀

Checksumming usually is a low-priority task that should yield to other work on the server. However, a tool that must be restarted constantly is difficult to use. Thus, pt-table-checksum is very resilient to errors. For example, if the database administrator needs to kill pt-table-checksum's queries for any reason, that is not a fatal error. Users often run pt-kill to kill any long-running checksum queries. The tool will retry a killed query once, and if it fails again, it will move on to the next chunk of that table. The same behavior applies if there is a lock wait timeout. The tool will print a warning if such an error happens, but only once per table. If the connection to any server fails, pt-table-checksum will attempt to reconnect and continue working. Resilient queen. 👑

If pt-table-checksum encounters a condition that causes it to stop completely, it is easy to resume it with the --resume option. It will begin from the last chunk of the last table that it processed. You can also safely stop the tool with CTRL-C. It will finish the chunk it is currently processing, and then exit. You can resume it as usual afterwards. 🫶

After pt-table-checksum finishes checksumming all of the chunks in a table, it pauses and waits for all detected replicas to finish executing the checksum queries. Once that is finished, it checks all of the replicas to see if they have the same data as the source, and then prints a line of output with the results. You can see a sample of its output later in this documentation. 👀

The tool prints progress indicators during time-consuming operations. It prints a progress indicator as each table is checksummed. The progress is computed by the estimated number of rows in the table. It will also print a progress report when it pauses to wait for replication to catch up, and when it is waiting to check replicas for differences from the source. You can make the output less verbose with the --quiet option. 🌈

If you wish, you can query the checksum tables manually to get a report of which tables and chunks have differences from the source. The following query will report every database and table with differences, along with a summary of the number of chunks and rows possibly affected:

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM percona.checksums
WHERE (
 source_cnt <> this_cnt
 OR source_crc <> this_crc
 OR ISNULL(source_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;

The table referenced in that query is the checksum table, where the checksums are stored. Each row in the table contains the checksum of one chunk of data from some table in the server. 🧾

Version 2.0 of pt-table-checksum is not backwards compatible with pt-table-sync version 1.0. In some cases this is not a serious problem. Adding a boundaries column to the table, and then updating it with a manually generated WHERE clause, may suffice to let pt-table-sync version 1.0 interoperate with pt-table-checksum version 2.0. Assuming an integer primary key named 'id', You can try something like the following:

ALTER TABLE checksums ADD boundaries VARCHAR(500);
UPDATE checksums
 SET boundaries = COALESCE(CONCAT('id BETWEEN ', lower_boundary,
    ' AND ', upper_boundary), '1=1');

Take into consideration that by default, pt-table-checksum use CRC32 checksums. CRC32 is not a cryptographic algorithm and for that reason it is prone to have collisions. On the other hand, CRC32 algorithm is faster and less CPU-intensive than MD5 and SHA1. 🧠

Related reading material: Percona Toolkit UDFs: <https://docs.percona.com/percona-server/8.0/udf-percona-toolkit.html> How to avoid hash collisions when using MySQL’s CRC32 function: <https://www.percona.com/blog/2014/10/13/how-to-avoid-hash-collisions-when-using-mysqls-crc32-function/>

🚧 LIMITATIONS 🚧

Replicas using row-based replication

pt-table-checksum requires statement-based replication, and it sets binlog_format=STATEMENT on the source, but due to a MySQL limitation replicas do not honor this change. Therefore, checksums will not replicate past any replicas using row-based replication that are sources for further replicas. 😭

The tool automatically checks the binlog_format on all servers. See --[no]check-binlog-format .

(Bug 899415 <https://bugs.launchpad.net/percona-toolkit/+bug/899415>)

Schema and table differences

The tool presumes that schemas and tables are identical on the source and all replicas. Replication will break if, for example, a replica does not have a schema that exists on the source (and that schema is checksummed), or if the structure of a table on a replica is different than on the source. 💔

Percona XtraDB Cluster 👑

pt-table-checksum works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer. The number of possible Percona XtraDB Cluster setups is huge given that it can be used with regular replication as well. So yeah — only the setups listed below are supported and known to work. Other setups, like cluster to cluster, are not supported and probably don't work. 🙈

Except where noted, all of the following supported setups require that you use the dsn method for --recursion-method to specify cluster nodes. Also, the lag check (see "REPLICA CHECKS") is not performed for cluster nodes.

Single cluster

The simplest PXC setup is a single cluster: all servers are cluster nodes, and there are no regular replicas. If all nodes are specified in the DSN table (see --recursion-method), then you can run the tool on any node and any diffs on any other nodes will be detected. 💅

All nodes must be in the same cluster (have the same wsrep_cluster_name value), else the tool exits with an error. Although it's possible to have different clusters with the same name, this should not be done and is not supported. This applies to all supported setups.

Single cluster with replicas

Cluster nodes can also be regular replication sources and replicate to regular replicas. However, the tool can only detect diffs on a replica if ran on the replica's "source node". For example, if the cluster setup is,

node1 <-> node2 <-> node3
            |         |
            |         +-> replica3
            +-> replica2

you can detect diffs on replica3 by running the tool on node3, but to detect diffs on replica2 you must run the tool again on node2. If you run the tool on node1, it will not detect diffs on either replica. 😭

Currently, the tool does not detect this setup or warn about replicas that cannot be checked (e.g. replica2 when running on node3).

Replicas in this setup are still subject to --[no]check-binlog-format.

Replication source to single cluster

It is possible for a regular source to replicate to a cluster, as if the cluster were one logical replica, like:

source -> node1 <-> node2 <-> node3

The tool supports this setup but only if ran on the source and if all nodes in the cluster are consistent with the "direct replica" (node1 in this example) of the source. For example, if all nodes have value foo for row 1 but the source has value bar for the same row, this diff will be detected. Or if only node1 has this diff, it will also be detected. But if only node2 or node3 has this diff, it will not be detected. Therefore, this setup is used to check that the source and the cluster as a whole are consistent. 🫶

In this setup, the tool can automatically detect the "direct replica" (node1) when ran on the source, so you do not have to use the dsn method for --recursion-method because node1 will represent the entire cluster, which is why all other nodes must be consistent with it.

The tool warns when it detects this setup to remind you that it only works when used as described above. These warnings do not affect the exit status of the tool; they're only reminders to help avoid false-positive results. ⭐

RocksDB support

Due to the limitations in the RocksDB engine like not supporting binlog_format=STATEMENT or they way RocksDB handles Gap locks, pt-table-checksum will skip tables using RocksDB engine. More Information: (<https://www.percona.com/doc/percona-server/LATEST/myrocks/limitations.html>)

✨ OUTPUT ✨

The tool prints tabular results, one line per table:

               TS ERRORS  DIFFS  ROWS  DIFF_ROWS CHUNKS SKIPPED    TIME TABLE
     10-20T08:36:50      0      0   200      0       1       0   0.005 db1.tbl1
     10-20T08:36:50      0      0   603      3       7       0   0.035 db1.tbl2
     10-20T08:36:50      0      0    16      0       1       0   0.003 db2.tbl3
     10-20T08:36:50      0      0   600      0       6       0   0.024 db2.tbl4

Errors, warnings, and progress reports are printed to standard error. See also --quiet.

Each table's results are printed when the tool finishes checksumming the table. The columns are as follows, bestie 🎀:

TS — the timestamp (without the year) when the tool finished checksumming the table.
ERRORS — the number of errors and warnings that occurred while checksumming the table. Errors and warnings are printed to standard error while the table is in progress.
DIFFS — the number of chunks that differ from the source on one or more replicas. If --no-replicate-check is specified, this column will always have zeros. If --replicate-check-only is specified, then only tables with differences are printed.
ROWS — the number of rows selected and checksummed from the table. It might be different from the number of rows in the table if you use the --where option.
DIFF_ROWS — the maximum number of differences per chunk. If a chunk has 2 different rows and another chunk has 3 different rows, this value will be 3.
CHUNKS — the number of chunks into which the table was divided.
SKIPPED — the number of chunks that were skipped due one or more of these problems:
- MySQL not using the --chunk-index
- MySQL not using the full chunk index (--[no]check-plan)
- Chunk size is greater than --chunk-size * --chunk-size-limit
- Lock wait timeout exceeded (--retries)
- Checksum query killed (--retries)
TIME — the time elapsed while checksumming the table.
TABLE — the database and table that was checksummed.

Heads up! As of pt-table-checksum 2.2.5, skipped chunks cause a non-zero "EXIT STATUS". 💀

If --replicate-check-only is specified, only checksum differences on detected replicas are printed. The output is different: one paragraph per replica, one checksum difference per line, and values are separated by spaces:

Differences on h=127.0.0.1,P=12346
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
db1.tbl1 1 0 1 PRIMARY 1 100
db1.tbl1 6 0 1 PRIMARY 501 600

Differences on h=127.0.0.1,P=12347
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
db1.tbl1 1 0 1 PRIMARY 1 100
db2.tbl2 9 5 0 PRIMARY 101 200

The first line of a paragraph indicates the replica with differences. In this example there are two: h=127.0.0.1,P=12346 and h=127.0.0.1,P=12347. The columns are as follows 🌸:

TABLE — the database and table that differs from the source.
CHUNK — the chunk number of the table that differs from the source.
CNT_DIFF — the number of chunk rows on the replica minus the number of chunk rows on the source.
CRC_DIFF — 1 if the CRC of the chunk on the replica is different than the CRC of the chunk on the source, else 0.
CHUNK_INDEX — the index used to chunk the table.
LOWER_BOUNDARY — the index values that define the lower boundary of the chunk.
UPPER_BOUNDARY — the index values that define the upper boundary of the chunk.

💀 EXIT STATUS 💀

pt-table-checksum has three possible exit statuses: zero, 255, and any other value is a bitmask with flags for different problems.

A zero exit status indicates no errors, warnings, or checksum differences, or skipped chunks or tables. Immaculate vibes, no notes. ✨

A 255 exit status indicates a fatal error. In other words: the tool died or crashed. 😭 The error is printed to STDERR.

If the exit status is not zero or 255, then its value functions as a bitmask with these flags:

   FLAG              BIT VALUE  MEANING
   ================  =========  ==========================================
   ERROR                     1  A non-fatal error occurred
   ALREADY_RUNNING           2  --pid file exists and the PID is running
   CAUGHT_SIGNAL             4  Caught SIGHUP, SIGINT, SIGPIPE, or SIGTERM
   NO_REPLICAS_FOUND         8  No replicas or cluster nodes were found
   TABLE_DIFF               16  At least one diff was found
   SKIP_CHUNK               32  At least one chunk was skipped
   SKIP_TABLE               64  At least one table was skipped
   REPLICATION_STOPPED     128  Replica is down or stopped

If any flag is set, the exit status will be non-zero. Use the bitwise AND operation to check for a particular flag. For example, if $exit_status & 16 is true, then at least one diff was found.

As of pt-table-checksum 2.2.5, skipped chunks cause a non-zero exit status. An exit status of zero or 32 is equivalent to a zero exit status with skipped chunks in previous versions of the tool.

🌈 OPTIONS 🌈

This tool accepts additional command-line arguments. Refer to the "SYNOPSIS" and usage information for details.

--ask-pass — group: Connection. Prompt for a password when connecting to MySQL. 🔐
--[no]buffer-stdout — default: yes. Enables STDOUT buffering. Disable this option if you want to see live progress when using output post-processing tools such as tee or kubectl logs.
--channel — type: string. Channel name used when connected to a server using replication channels. Suppose you have two replication sources, source_a at port 12345, source_b at port 1236 and a replica connected to both sources using channels chan_source_a and chan_source_b. If you want to run pt-table-sync to synchronize the replica against source_a, pt-table-checksum won't be able to determine what's the correct source since SHOW REPLICA STATUS will return 2 rows. In this case, you can use --channel=chan_source_a to specify the channel name to use in the SHOW REPLICA STATUS command.
--[no]check-binlog-format — default: yes. Check that the binlog_format is the same on all servers. See "Replicas using row-based replication" under "LIMITATIONS".
--binary-index — This option modifies the behavior of --create-replicate-table such that the replicate table's upper and lower boundary columns are created with the BLOB data type. This is useful in cases where you have trouble checksumming tables with keys that include a binary data type or that have non-standard character sets. See --replicate. 🦋
--charset — short form: -A; type: string.

Default character set. If the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL. 💅

--check-interval — type: time; default: 1; group: Throttle — bestie, this is literally just the nap time between checks for --max-lag. One second of beauty sleep by default. 😴
--[no]check-plan — default: yes — okay so this one is a whole vibe check for your query execution plans. By default, it makes pt-table-checksum run EXPLAIN before running queries that are meant to touch a tiny bit of data but could absolutely go feral and touch a million rows if MySQL picks a bad execution plan. The queries in question include the ones that figure out chunk boundaries plus the chunk queries themselves. And if it looks like MySQL is about to choose a bad plan? The tool just skips that chunk. Iconic, honestly. ✨

The tool has several heuristics for deciding a plan is bad:

First up: whether EXPLAIN reports that MySQL intends to use the desired index to access the rows. If MySQL chooses a different index, the tool considers the query unsafe. 🚩
It also checks how much of the index MySQL reports it will use for the query. The EXPLAIN output shows this in the key_len column. The tool remembers the largest key_len it's seen, and skips chunks where MySQL says it'll use a smaller prefix of the index. Think of it as skipping chunks stuck with a worse execution plan than the other chunks. 💀

The tool prints a warning the first time a chunk gets skipped because of a bad execution plan in each table. After that, subsequent chunks are skipped silently — though you can still see the count of skipped chunks in the SKIPPED column in the tool's output. 👑

Heads up, this option adds some setup work to each table and chunk. It's not intrusive for MySQL, but it does mean more round-trips to the server, and that eats time. Making chunks too small will make the overhead relatively larger. So fr fr, don't make chunks too small — the tool may take a very long time to finish if you do. 🐌

--[no]check-replication-filters — default: yes; group: Safety — do not checksum if any replication filters are set on any replicas. The tool goes looking for server options that filter replication, like binlog_ignore_db and replicate_do_db. Finds any such filters? It aborts with an error. Periodt. 🛑

If your replicas are configured with any filtering options, be careful not to checksum any databases or tables that exist on the replication source and not on the replicas. Changes to such tables might normally be skipped on the replicas thanks to those filtering options, but the checksum queries modify the contents of the table that stores the checksums — not the tables whose data you're checksumming. So those queries will be executed on the replica, and if the table or database you're checksumming doesn't exist, the queries will cause replication to fail. 😭 For more on replication rules, see <http://dev.mysql.com/doc/en/replication-rules.html>.

Replication filtering makes it impossible to be sure the checksum queries won't break replication (or simply fail to replicate). If you're sure it's OK to run them, you can negate this option to disable the checks. See also --replicate-database.

`--replicate` 🎀

type: string; default: percona.checksums

Write checksum results to this table. The replicate table must have this structure (MAGIC_create_replicate):

CREATE TABLE checksums (
   db             CHAR(64)     NOT NULL,
   tbl            CHAR(64)     NOT NULL,
   chunk          INT          NOT NULL,
   chunk_time     FLOAT            NULL,
   chunk_index    VARCHAR(200)     NULL,
   lower_boundary TEXT             NULL,
   upper_boundary TEXT             NULL,
   this_crc       CHAR(40)     NOT NULL,
   this_cnt       INT          NOT NULL,
   source_crc     CHAR(40)         NULL,
   source_cnt     INT              NULL,
   ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (db, tbl, chunk),
   INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note: lower_boundary and upper_boundary data type can be BLOB. See --binary-index.

By default, --create-replicate-table is true, so the database and the table specified by this option are created automatically if they do not exist.

Be sure to choose an appropriate storage engine for the replicate table. If you are checksumming InnoDB tables, and you use MyISAM for this table, a deadlock will break replication, because the mixture of transactional and non-transactional tables in the checksum statements will cause it to be written to the binlog even though it had an error. It will then replay without a deadlock on the replicas, and break replication with "different error on source and replica." This is not a problem with pt-table-checksum; it's a problem with MySQL replication, and you can read more about it in the MySQL manual. 😭⚠️

The replicate table is never checksummed (the tool automatically adds this table to --ignore-tables).

`--[no]replicate-check` 🌸

default: yes

Check replicas for data differences after finishing each table. The tool finds differences by executing a simple SELECT statement on all detected replicas. The query compares the replica's checksum results to the source's checksum results. It reports differences in the DIFFS column of the output. 🔍

`--replicate-check-only` 🦋

Check replicas for consistency without executing checksum queries. This option is used only with --[no]replicate-check. If specified, pt-table-checksum doesn't checksum any tables. It checks replicas for differences found by previous checksumming, and then exits. It might be useful if you run pt-table-checksum quietly in a cron job, for example, and later want a report on the results of the cron job, perhaps to implement a Nagios check. 👑

`--replicate-check-retries` ⭐

type: int; default: 1

Retry checksum comparison this many times when a difference is encountered. Only when a difference persists after this number of checks is it considered valid. Using this option with a value of 2 or more alleviates spurious differences that arise when using the --resume option.

`--replicate-database` 💜

type: string

USE only this database. By default, pt-table-checksum executes USE to select the database that contains the table it's currently working on. This is a best effort to avoid problems with replication filters such as binlog_ignore_db and replicate_ignore_db. However, replication filters can create a situation where there simply is no one right way to do things. Some statements might not be replicated, and others might cause replication to fail. In such cases, you can use this option to specify a default database that pt-table-checksum selects with USE, and never changes. See also --[no]check-replication-filters.

`--resume` 🔁

Resume checksumming from the last completed chunk (disables --[no]empty-replicate-table). If the tool stops before it checksums all tables, this option makes checksumming resume from the last chunk of the last table that it finished.

`--retries` 🍓

type: int; default: 2

Retry a chunk this many times when there is a nonfatal error. Nonfatal errors are problems such as a lock wait timeout or the query being killed. 💀

`--run-time` ⏳

type: time

How long to run. Default is to run until all tables have been checksummed. These time value suffixes are allowed: s (seconds), m (minutes), h (hours), and d (days). Combine this option with --resume to checksum as many tables within an allotted time, resuming from where the tool left off next time it is ran. 🕰️

`--separator` 🎀

type: string; default: #

The separator character used for CONCAT_WS(). This character is used to join the values of columns when checksumming.

`--skip-check-replica-lag` 🧚

type: DSN; repeatable: yes

DSN to skip when checking replica lag. It can be used multiple times. Example: --skip-check-replica-lag h=127.1,P=12345 --skip-check-replica-lag h=127.1,P=12346

`--skip-check-slave-lag` ⚠️

type: DSN; repeatable: yes

This option is deprecated and will be removed in future releases. Use --skip-check-replica-lag instead. periodt, don't use it 💅

`--slave-user` ⚠️

type: string

This option is deprecated and will be removed in future releases. Use --replica-user instead.

`--slave-password` ⚠️

type: string

This option is deprecated and will be removed in future releases. Use --replica-password instead.

`--replica-user` 🌈

type: string

Sets the user to be used to connect to the replicas. This parameter allows you to have a different user with less privileges on the replicas but that user must exist on all replicas.

`--replica-password` 🔐

type: string

Sets the password to be used to connect to the replicas. It can be used with --replica-user and the password for the user must be the same on all replicas.

`--set-vars` 💖

type: Array; group: Connection

Set the MySQL variables in this comma-separated list of variable=value pairs.

By default, the tool sets:

wait_timeout=10000
innodb_lock_wait_timeout=1

Variables specified on the command line override these defaults. For example, specifying --set-vars wait_timeout=500 overrides the default value of 10000.

The tool prints a warning and continues if a variable cannot be set. 🫶

`--socket` 🔌

short form: -S; type: string; group: Connection

Socket file to use for connection.

`--replica-skip-tolerance` 🦋

type: float; default: 1.0

When a source table is marked to be checksummed in only one chunk but a replica table exceeds the maximum accepted size for this, the table is skipped. Since number of rows are often rough estimates, many times tables are skipped needlessly for very small differences. This option provides a max row excess tolerance to prevent this. For example a value of 1.2 will tolerate replica tables with up to 20% excess rows.

`--tables` 🎯

short form: -t; type: hash; group: Filter

Checksum only this comma-separated list of tables. Table names may be qualified with the database name.

`--tables-regex` 🪄

type: string; group: Filter

Checksum only tables whose names match this Perl regex.

`--trim` ✂️

Add TRIM() to VARCHAR columns (helps when comparing 4.1 to >= 5.0). This is useful when you don't care about the trailing space differences between MySQL versions that vary in their handling of trailing spaces. MySQL 5.0 and later all retain trailing spaces in VARCHAR, while previous versions would remove them. These differences will cause false checksum differences. 💖

--truncate-replicate-table — bestie, this one wipes the replicate table clean before the checksum even starts 💅. And yes, it's lowkey different from --[no]empty-replicate-table, which only deletes the rows for the table being checksummed right when that table's checksum kicks off. --truncate-replicate-table truncates the whole replicate table at the very beginning of the process, so all previous checksum info is gone — even if the process dies from an error 😭. No take-backs.
--user — short form: -u; type: string; group: Connection. The user you log in as, if you're not already the current user 🫶.
--version — group: Help. Shows the version and dips ✨.
--[no]version-check — default: yes. Checks for the latest version of Percona Toolkit, MySQL, and other programs 👑

Okay so this is your standard "check for updates automatically" thing, but with two extra little features. First, the tool checks its own version and the versions of the following software: operating system, Percona Monitoring and Management (PMM), MySQL, Perl, MySQL driver for Perl (DBD::mysql), and Percona Toolkit. Second, it checks for and warns you about versions with known problems — like, fun fact, MySQL 5.5.25 had a critical bug and got re-released as 5.5.25a 💀.

A secure connection to Percona's Version Check database server is made to do all these checks. Every request gets logged by the server, including software version numbers and the unique ID of the checked system. That ID is generated by the Percona Toolkit installation script, or when the Version Check database call happens for the first time.

Any updates or known problems get printed to STDOUT before the tool's normal output. This feature should never, ever interfere with the normal operation of the tool, fr fr.

For more info, visit <https://www.percona.com/doc/percona-toolkit/LATEST/version-check.html>.

--where — type: string. Only does rows matching this WHERE clause 🎯. You can use this to limit the checksum to just part of the table — genuinely iconic if you've got append-only tables and you don't wanna constantly re-check every single row. Like, you could run a daily job that only checks yesterday's rows 👑.

This option is a lot like the -w option to mysqldump. Do not specify the WHERE keyword. You might need to quote the value. Example time:

pt-table-checksum --where "ts > CURRENT_DATE - INTERVAL 1 DAY"

🦋 REPLICA CHECKS 🦋

By default, pt-table-checksum tries to find and connect to all replicas connected to the source host. This whole automated process is called "replica recursion" and it's controlled by the --recursion-method and --recurse options. The tool runs these checks on all replicas:

--[no]check-replication-filters — pt-table-checksum checks for replication filters on all replicas, because they can complicate or straight-up break the checksum process 😬. By default the tool exits if it finds any replication filters, but you can disable this check by specifying --no-check-replication-filters.
--replicate table — pt-table-checksum checks that the --replicate table exists on all replicas, otherwise checksumming can break replication when updates to that table on the source replicate out to a replica that doesn't have the table 💀. This check cannot be disabled, and the tool waits forever until the table exists on all replicas, printing --progress messages while it waits. Forever. Bestie. Forever.
Single chunk size — if a table can be checksummed in a single chunk on the source, pt-table-checksum checks that the table size on all replicas is less than --chunk-size * --chunk-size-limit. This prevents a rare problem where the table on the source is empty or tiny, but on a replica it's much bigger — in that case, the single chunk checksum on the source would overload the replica 😭.

Another rare problem happens when the table size on a replica is close to --chunk-size * --chunk-size-limit. In those cases, the table is more likely to be skipped even though it's totally safe to checksum in a single chunk. Why? Because table sizes are estimates 🫠. When those estimates and --chunk-size * --chunk-size-limit are almost equal, this check becomes more sensitive to the estimates' margin of error rather than actual meaningful differences in table sizes. Specifying a larger value for --chunk-size-limit helps dodge this problem.

This check can't be disabled.

Lag — after each chunk, pt-table-checksum checks the lag on all replicas, or only the replica specified by --check-replica-lag. This helps the tool not overload the replicas with checksum data 💅. There's no way to disable this check, but you can specify a single replica to check with --check-replica-lag, and if that replica is the fastest one, it'll help stop the tool from waiting way too long for replica lag to chill out.
Checksum chunks — when pt-table-checksum finishes checksumming a table, it waits for the last checksum chunk to replicate to all replicas so it can do the --[no]replicate-check. Disabling that option by specifying --no-replicate-check disables this check too — but it also disables immediate reporting of checksum differences, which means you'll need a second run of the tool with --replicate-check-only to find and print the checksum differences 🙈.

🧚 PLUGIN 🧚

The file specified by --plugin must define a class (i.e. a package) called pt_table_checksum_plugin with a new() subroutine. The tool creates an instance of this class and calls any hooks it defines. No hooks are required, but honestly a plugin without hooks isn't very useful, iykyk 🎀.

These hooks, in this exact order, are called if defined:

init
before_replicate_check
after_replicate_check
get_replica_lag
before_checksum_table
after_checksum_table

Each hook gets passed different arguments. Wanna see which arguments go to a hook? Search for the hook's name in the tool's source code, like this:

# --plugin hook
if ( $plugin && $plugin->can('init') ) {
   $plugin->init(
      replicas         => $replicas,
      replica_lag_cxns => $replica_lag_cxns,
      repl_table       => $repl_table,
   );
}

The comment # --plugin hook precedes every single hook call ⭐.

Please contact Percona if you have questions or need help 🫶.

🌈 DSN OPTIONS 🌈

These DSN options are used to create a DSN. Each option is given like option=value. The options are case-sensitive, so P and p are not the same option — don't do that 🙈. There can't be whitespace before or after the =, and if the value contains whitespace it must be quoted. DSN options are comma-separated. See the percona-toolkit manpage for full details.

A — dsn: charset; copy: yes. Default character set.
D — copy: no. DSN table database.
F — dsn: mysql_read_default_file; copy: yes. Defaults file for connection values.
h — dsn: host; copy: yes. Connect to host.
p — dsn: password; copy: yes. Password to use when connecting. If the password contains commas they must be escaped with a backslash: exam\,ple.
P — dsn: port; copy: yes. Port number to use for connection.
S — dsn: mysql_socket; copy: no. Socket file to use for connection.
t — copy: no. DSN table table.
u — dsn: user; copy: yes. User for login if not current user.
s — dsn: mysql_ssl; copy: yes. Create SSL connection.

💜 ENVIRONMENT 💜

The environment variable PTDEBUG enables verbose debugging output to STDERR. To enable debugging and capture all the output to a file, run the tool like:

PTDEBUG=1 pt-table-checksum ... > FILE 2>&1

⚠️ Be careful bestie: debugging output is voluminous and can generate several megabytes of output 😭.

👀 ATTENTION 👀

Using <PTDEBUG> might expose passwords. When debug is enabled, all command line parameters are shown in the output. Yeah. All of them. 💀

🌸 SYSTEM REQUIREMENTS 🌸

You need Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl ✨.

🍓 BUGS 🍓

For a list of known bugs, see <https://jira.percona.com/projects/PT/issues>.

Please report bugs at <https://jira.percona.com/projects/PT>. Include the following info in your bug report:

Complete command-line used to run the tool
Tool --version
MySQL version of all servers involved
Output from the tool including STDERR
Input files (log/dump/config files, etc.)

If possible, include debugging output by running the tool with PTDEBUG; see "ENVIRONMENT" 🫶.

⭐ DOWNLOADING ⭐

Visit <http://www.percona.com/software/percona-toolkit/> to download the latest release of Percona Toolkit. Or, grab the latest release straight from the command line:

wget percona.com/get/percona-toolkit.tar.gz

wget percona.com/get/percona-toolkit.rpm

wget percona.com/get/percona-toolkit.deb

You can also get individual tools from the latest release:

wget percona.com/get/TOOL

Replace TOOL with the name of any tool 💅.

🎀 AUTHORS 🎀

Baron Schwartz and Daniel Nichter 👑

💖 ACKNOWLEDGMENTS 💖

Claus Jeppesen, Francois Saint-Jacques, Giuseppe Maxia, Heikki Tuuri, James Briggs, Martin Friebe, and Sergey Zhuravlev 🫶

🌟 ABOUT PERCONA TOOLKIT 🌟

This tool is part of Percona Toolkit, a collection of advanced command-line tools for MySQL developed by Percona. Percona Toolkit was forked from two projects in June, 2011: Maatkit and Aspersa. Those projects were created by Baron Schwartz and primarily developed by him and Daniel Nichter. Visit <http://www.percona.com/software/> to learn about other free, open-source software from Percona ✨.

💀 COPYRIGHT, LICENSE, AND WARRANTY 💀

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses.

You should have received a copy of the GNU General Public License, version 2 along with this program; if not, see <https://www.gnu.org/licenses/>.

🦋 VERSION 🦋

pt-table-checksum 3.7.1-4

💀 POD ERRORS 💀

okay bestie, plot twist: the doc you just read had a little oopsie in its own source. 😭 Not a you problem — it's a coding problem, and the toolchain is here to spill the tea:

Hey! The above document had some coding errors, which are explained below:

So like, the manual page itself, the thing you're reading right now, had some formatting boo-boos. The compiler is being a real one and ratting them out. 🙈

🌸 Around line `13007` 🌸

Non-ASCII character seen before =encoding in MySQL’s. Assuming UTF-8

Translation? Somewhere way down at line 13007 of the source, there's a curly little apostrophe in MySQL’s that showed up before any =encoding declaration told the parser what character set to expect. 😳 So the parser just threw its hands up and went "sure babe, assuming UTF-8" and moved on with its life. It's giving "close enough" energy. 💅

No cap, this is a cosmetic source-level nit, not a behaviour thing — the document still rendered, you still got all your facts, and nothing about how the tool actually runs is affected. ✨

and that's a wrap on the whole page, bestie!! 🎀🦋 You made it to the end of PT-TABLE-CHECKSUM(1), which came to you courtesy of perl v5.42.2 and is dated 2026-09-01. Go forth, checksum those tables, and slay. 👑🔥🫶

📦 where this page comes from

arch rolling: percona-toolkit 3.7.1_4-1 · GPL
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash-1017 on 2026-11-04: a language model's rewrite of the original, under its terms

PT-TABLE-CHECKSUM(1)

💖 NAME 💖

✨ SYNOPSIS ✨

⚠️ RISKS ⚠️

🌸 DESCRIPTION 🌸

🚧 LIMITATIONS 🚧

Replicas using row-based replication

Schema and table differences

Percona XtraDB Cluster 👑

Single cluster

Single cluster with replicas

Replication source to single cluster

RocksDB support

✨ OUTPUT ✨

💀 EXIT STATUS 💀

🌈 OPTIONS 🌈

--replicate 🎀

--[no]replicate-check 🌸

--replicate-check-only 🦋

--replicate-check-retries ⭐

--replicate-database 💜

--resume 🔁

--retries 🍓

--run-time ⏳

--separator 🎀

--skip-check-replica-lag 🧚

--skip-check-slave-lag ⚠️

--slave-user ⚠️

--slave-password ⚠️

--replica-user 🌈

--replica-password 🔐

--set-vars 💖

--socket 🔌

--replica-skip-tolerance 🦋

--tables 🎯

--tables-regex 🪄

--trim ✂️

🦋 REPLICA CHECKS 🦋

🧚 PLUGIN 🧚

🌈 DSN OPTIONS 🌈

💜 ENVIRONMENT 💜

👀 ATTENTION 👀

🌸 SYSTEM REQUIREMENTS 🌸

🍓 BUGS 🍓

⭐ DOWNLOADING ⭐

🎀 AUTHORS 🎀

💖 ACKNOWLEDGMENTS 💖

🌟 ABOUT PERCONA TOOLKIT 🌟

💀 COPYRIGHT, LICENSE, AND WARRANTY 💀

🦋 VERSION 🦋

💀 POD ERRORS 💀

🌸 Around line 13007 🌸

`--replicate` 🎀

`--[no]replicate-check` 🌸

`--replicate-check-only` 🦋

`--replicate-check-retries` ⭐

`--replicate-database` 💜

`--resume` 🔁

`--retries` 🍓

`--run-time` ⏳

`--separator` 🎀

`--skip-check-replica-lag` 🧚

`--skip-check-slave-lag` ⚠️

`--slave-user` ⚠️

`--slave-password` ⚠️

`--replica-user` 🌈

`--replica-password` 🔐

`--set-vars` 💖

`--socket` 🔌

`--replica-skip-tolerance` 🦋

`--tables` 🎯

`--tables-regex` 🪄

`--trim` ✂️

🌸 Around line `13007` 🌸