PT-TABLE-SYNC(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 🎀

pt-table-sync — bestie, this is the tool that synchronizes MySQL table data efficiently. It's from the User Contributed Perl Documentation, and it's lowkey iconic.

✨ SYNOPSIS ✨

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

pt-table-sync synchronizes data efficiently between MySQL tables. That's the whole vibe.

⚠️ okay so listen — this tool changes data, so for maximum safety you should back up your data before using it. No cap. 🫶

When you're synchronizing a server that's a replication replica using the --replicate or --sync-to-source methods, it always makes the changes on the replication source, never the replication replica directly. That's, like, generally the only safe way to bring a replica back in sync with its source — because changes made to the replica are usually the source of the problems in the first place, iykyk. However! The changes it makes on the source should be no-op changes that set the data to their current values, and actually affect only the replica. 💅

Sync db.tbl on host1 to host2:

pt-table-sync --execute h=host1,D=db,t=tbl h=host2

Sync all tables on host1 to host2 and host3:

pt-table-sync --execute host1 host2 host3

Make replica1 have the same data as its replication source:

pt-table-sync --execute --sync-to-source replica1

Resolve differences that pt-table-checksum found on all replicas of source1:

pt-table-sync --execute --replicate percona.checksum source1

Same as above but only resolve differences on replica1:

pt-table-sync --execute --replicate percona.checksum \
  --sync-to-source replica1

Sync source2 in a source-source replication configuration, where source2's copy of db.tbl is known or suspected to be incorrect:

pt-table-sync --execute --sync-to-source h=source2,D=db,t=tbl

💀 Note that in the source-source configuration, the following will NOT do what you want, because it will make changes directly on source2, which will then flow through replication and change source1's data:

# Don't do this in a source-source setup!
pt-table-sync --execute h=source1,D=db,t=tbl source2

🔥 RISKS 🔥

WARNING: pt-table-sync changes data! Before using this tool, please do all of these, fr fr:

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 💾

pt-table-sync is mature, proven in the real world, and well tested — but if used improperly it can have adverse consequences 😭. Always test syncing first with --dry-run and --print.

🌸 DESCRIPTION 🌸

pt-table-sync does one-way and bidirectional synchronization of table data. It does not synchronize table structures, indexes, or any other schema objects. What follows describes one-way synchronization — BIDIRECTIONAL SYNCING is described later.

This tool is complex and functions in several different ways, so to use it safely and effectively you should understand three things: the purpose of --replicate, finding differences, and specifying hosts. These three concepts are closely related and determine how the tool will run. Here's the abbreviated logic, bestie:

   if DSN has a t part, sync only that table:
      if 1 DSN:
         if --sync-to-source:
            The DSN is a replica.  Connect to its source and sync.
      if more than 1 DSN:
         The first DSN is the source.  Sync each DSN in turn.
   else if --replicate:
      if --sync-to-source:
         The DSN is a replica.  Connect to its source, find records
         of differences, and fix.
      else:
         The DSN is the source.  Find replicas and connect to each,
         find records of differences, and fix.
   else:
      if only 1 DSN and --sync-to-source:
         The DSN is a replica.  Connect to its source, find tables and
         filter with --databases etc, and sync each table to the source.
      else:
         find tables, filtering with --databases etc, and sync each
         DSN to the first.

So pt-table-sync can run in one of two ways: with --replicate, or without. The default is to run without --replicate, which causes pt-table-sync to automatically find differences efficiently with one of several algorithms (peep ALGORITHMS). Alternatively, the value of --replicate, if specified, causes pt-table-sync to use the differences already found by having previously ran pt-table-checksum with its own --replicate option.

Strictly speaking, you don't need to use --replicate because pt-table-sync can find differences on its own — but many people use --replicate if, for example, they checksum regularly using pt-table-checksum then fix differences as needed with pt-table-sync. If you're unsure, read each tool's documentation carefully and decide for yourself, or consult with an expert 🧚.

Regardless of whether --replicate is used or not, you need to specify which hosts to sync. There are two ways: with --sync-to-source, or without. Specifying --sync-to-source makes pt-table-sync expect one and only replica DSN on the command line. The tool will automatically discover the replica's source and sync it so that its data is the same as its source. This is accomplished by making changes on the source which then flow through replication and update the replica to resolve its differences. 🔄

🙈 Be careful though: although this option specifies and syncs a single replica, if there are other replicas on the same source, they will receive via replication the changes intended for the replica that you're trying to sync. Yeah. That's a whole thing.

Alternatively, if you do not specify --sync-to-source, the first DSN given on the command line is the source host. There is only ever one source host. If you don't also specify --replicate, then you must specify at least one other DSN as the destination host. There can be one or more destination hosts. Source and destination hosts must be independent — they cannot be in the same replication topology. pt-table-sync will die with an error if it detects that a destination host is a replica, because changes are written directly to destination hosts (and it's not safe to write directly to replicas 💀). Or, if you specify --replicate (but not --sync-to-source), then pt-table-sync expects one and only one source DSN on the command line. The tool will automatically discover all the source's replicas and sync them to the source. This is the only way to sync several (all) replicas at once — because --sync-to-source only specifies one replica. 👑

Each host on the command line is specified as a DSN. The first DSN (or only DSN for cases like --sync-to-source) provides default values for other DSNs, whether those other DSNs are specified on the command line or auto-discovered by the tool. So in this example:

pt-table-sync --execute h=host1,u=msandbox,p=msandbox h=host2

...the host2 DSN inherits the u and p DSN parts from the host1 DSN. Use the --explain-hosts option to see how pt-table-sync will interpret the DSNs given on the command line. 🦋

💜 LIMITATIONS 💜

Replicas using row-based replication

pt-table-sync requires statement-based replication when used with the --sync-to-source or --replicate option. Therefore it will set binlog_format=STATEMENT on the source for its session if required. To do this, the user must have SUPER privilege. ✨

💖 OUTPUT 💖

If you specify the --verbose option, you'll see information about the differences between the tables. There is one row per table. Each server is printed separately. For example:

# Syncing h=host1,D=test,t=test1
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      3      0 Chunk     13:00:00 13:00:17 2    test.test1

Table test.test1 on host1 required 3 INSERT statements to synchronize, and it used the Chunk algorithm (see ALGORITHMS). The sync operation for this table started at 13:00:00 and ended 17 seconds later (times taken from NOW() on the source host). And because differences were found, its EXIT STATUS was 2.

If you specify the --print option, you'll see the actual SQL statements that the script uses to synchronize the table — if --execute is also specified.

If you want to see the SQL statements that pt-table-sync is using to select chunks, nibbles, rows, etc., then specify --print once and --verbose twice. 🔍 Be careful though: this can print a lot of SQL statements. Like, a lot a lot.

There are cases where no combination of INSERT, UPDATE or DELETE statements can resolve differences without violating some unique key. For example, suppose there's a primary key on column a and a unique key on column b. Then there is no way to sync these two tables with straightforward UPDATE statements:

 +---+---+  +---+---+
 | a | b |  | a | b |
 +---+---+  +---+---+
 | 1 | 2 |  | 1 | 1 |
 | 2 | 1 |  | 2 | 2 |
 +---+---+  +---+---+

The tool rewrites queries to DELETE and REPLACE in this case. This is automatically handled after the first index violation, so you don't have to worry about it. Slay. 💅

🌀 Be careful when using pt-table-sync in any source-source setup. Source-source replication is inherently tricky, and it's easy to make mistakes. You need to be sure you're using the tool correctly for source-source replication. See the SYNOPSIS for the overview of the correct usage.

🌸 Also be careful with tables that have foreign key constraints with ON DELETE or ON UPDATE definitions, because these might cause unintended changes on the child tables. See --[no]check-child-tables.

In general, this tool is best suited when your tables have a primary key or unique index. Although it can synchronize data in tables lacking a primary key or unique index, it might be best to synchronize that data by another means. 👑

💖 REPLICATION SAFETY 💖

okay so bestie, syncing a replication source and its replica safely is genuinely a non-trivial problem — like, no cap. There's a whole pile of stuff to worry about: other processes out there changing data, someone trying to change data on the replica, whether your destination and source are actually a source-source pair, and so much more. 😭

so the generally safe move is: change the data on the source, and let those changes flow through replication to the replica like any other change would. cute, right? but — and this is the big but — that only works if you can REPLACE into the table on the source. and REPLACE only works if there's a unique index on the table (otherwise it just acts like a plain old INSERT, which is not the vibe you want).

if your table does have unique keys, use the --sync-to-source and/or --replicate options to sync a replica to its source. that'll generally do the right thing. ✨ but when there's no unique key on the table, there is literally no choice but to change the data on the replica — and pt-table-sync will detect that you're trying to do that. it will complain and die unless you specify --no-check-replica (see --[no]check-replica). like, it's not being mean, it's being safe. 💅

if you're syncing a table without a primary or unique key on a source-source pair, you must change the data on the destination server. therefore you need to specify --no-bin-log for safety (see --[no]bin-log). if you don't? the changes you make on the destination server will replicate right back to the source server and change the data there. 🫠 yeah. bad.

the generally safe thing to do on a source-source pair is to use the --sync-to-source option so you don't change the data on the destination server at all. you'll also need --no-check-replica to keep pt-table-sync from complaining that it's changing data on a replica.

🧚 ALGORITHMS 🧚

pt-table-sync has a generic data-syncing framework that uses different algorithms to find differences. the tool automatically picks the best algorithm for each table based on indexes, column types, and the algorithm preferences you specified with --algorithms. here's every algorithm available, listed in their default order of preference:

Chunk — finds an index whose first column is numeric (including date and time types), and divides the column's range of values into chunks of approximately --chunk-size rows. it syncs a chunk at a time by checksumming the entire chunk. if the chunk differs on the source and destination, it checksums each chunk's rows individually to find the rows that differ.

it's efficient when the column has sufficient cardinality to make the chunks end up about the right size.

the initial per-chunk checksum is quite small and results in minimal network traffic and memory consumption. if a chunk's rows must be examined, only the primary key columns and a checksum get sent over the network — not the entire row. if a row is found to be different, then the entire row is fetched. but not before, bestie. 🙈

note that this algorithm will not work if you're chunking a char column where all the values start with the same character. in that case, the tool will exit and suggest picking a different algorithm. 💀

Nibble — finds an index and ascends the index in fixed-size nibbles of --chunk-size rows, using a non-backtracking algorithm (see pt-archiver for more on this algorithm). it's very similar to Chunk, but instead of pre-calculating the boundaries of each piece of the table based on index cardinality, it uses LIMIT to define each nibble's upper limit, and the previous nibble's upper limit to define the lower limit.

it works in steps: one query finds the row that will define the next nibble's upper boundary, and the next query checksums the entire nibble. if the nibble differs between the source and destination, it examines the nibble row-by-row, just as Chunk does.

GroupBy — selects the entire table grouped by all columns, with a COUNT(*) column added. compares all columns, and if they're the same, compares the COUNT(*) column's value to determine how many rows to insert or delete into the destination. works on tables with no primary key or unique index. 👑
Stream — selects the entire table in one big stream and compares all columns. selects all columns. much less efficient than the other algorithms, but it works when there's no suitable index for them to use. it's giving "last resort but make it fashion". 🌈

🦋 BIDIRECTIONAL SYNCING 🦋

bidirectional syncing is an experimental feature. to make it work reliably there are a number of strict limitations:

only works when syncing one server to other independent servers
does not work in any way with replication
requires that the table(s) are chunkable with the Chunk algorithm
is not N-way, only bidirectional between two servers at a time
does not handle DELETE changes

for example, suppose we have three servers: c1, r1, r2. c1 is the central server, a pseudo-source to the other servers (viz. r1 and r2 are not replicas to c1). r1 and r2 are remote servers. rows in table foo are updated and inserted on all three servers and we want to synchronize all the changes between all the servers. table foo has columns:

id    int PRIMARY KEY
ts    timestamp auto updated
name  varchar

auto-increment offsets are used so that new rows from any server don't create conflicting primary key (id) values. in general, newer rows, as determined by the ts column, take precedence when a same-but-differing row is found during the bidirectional sync. "same but differing" means two rows have the same primary key (id) value but different values for some other column — like the name column in this example. same-but-differing conflicts are resolved by a "conflict". a conflict compares some column of the competing rows to determine a "winner". the winning row becomes the source and its values are used to update the other row. 🏆

there are subtle differences between three columns used to achieve bidirectional syncing that you should be familiar with: chunk column (--chunk-column), comparison column(s) (--columns), and conflict column (--conflict-column). the chunk column is only used to chunk the table; e.g. WHERE id >= 5 AND id < 10. chunks are checksummed and when chunk checksums reveal a difference, the tool selects the rows in that chunk and checksums the --columns for each row. if a column checksum differs, the rows have one or more conflicting column values. in a traditional unidirectional sync, the conflict is a moot point because it can be resolved simply by updating the entire destination row with the source row's values. in a bidirectional sync, though? the --conflict-column (in accordance with the other --conflict-* options listed below) is compared to determine which row is "correct" or "authoritative" — and that row becomes the "source". 💅

to sync all three servers completely, two runs of pt-table-sync are required. the first run syncs c1 and r1, then syncs c1 and r2 including any changes from r1. at this point c1 and r2 are completely in sync, but r1 is missing any changes from r2 because c1 didn't have these changes when it and r1 were synced. so a second run is needed, which syncs the servers in the same order — but this time when c1 and r1 are synced, r1 gets r2's changes. ✨

the tool does not sync N-ways, only bidirectionally between the first DSN given on the command line and each subsequent DSN in turn. so the tool in this example would be ran twice like:

pt-table-sync --bidirectional h=c1 h=r1 h=r2

the --bidirectional option enables this feature and causes various sanity checks to be performed. you must specify other options that tell pt-table-sync how to resolve conflicts for same-but-differing rows. those options are:

--conflict-column
--conflict-comparison
--conflict-value
--conflict-threshold
--conflict-error (optional)

use --print to test this option before --execute. the printed SQL statements will have comments saying on which host the statement would be executed if you used --execute. 🫶

technical side note: the first DSN is always the "left" server and the other DSNs are always the "right" server. since either server can become the source or destination it's confusing to think of them as "src" and "dst". therefore they're generically referred to as left and right. it's easy to remember this because the first DSN is always to the left of the other server DSNs on the command line. 🎀

⭐ EXIT STATUS ⭐

here are the exit statuses (also called return values, or return codes) for when pt-table-sync finishes and exits:

0 — Success. 🎉
1 — Internal error.
2 — At least one table differed on the destination.
3 — Combination of 1 and 2.

✨ OPTIONS ✨

specify at least one of --print, --execute, or --dry-run.

--where and --replicate are mutually exclusive. (pick one, bestie, you can't have both 💅)

this tool accepts additional command-line arguments. refer to the SYNOPSIS and usage information for details.

--algorithms — type: string; default: Chunk,Nibble,GroupBy,Stream

algorithm to use when comparing the tables, in order of preference.

for each table, pt-table-sync will check if the table can be synced with the given algorithms in the order that they're given. the first algorithm that can sync the table is used. see ALGORITHMS. 🧚

--ask-pass — prompt for a password when connecting to MySQL.
--bidirectional — enable bidirectional sync between first and subsequent hosts.

see BIDIRECTIONAL SYNCING for more information. 🦋

--[no]bin-log — default: yes

log to the binary log (SET SQL_LOG_BIN=1).

specifying --no-bin-log will SET SQL_LOG_BIN=0.

--buffer-in-mysql — instruct MySQL to buffer queries in its memory.

this option adds the SQL_BUFFER_RESULT option to the comparison queries. this causes MySQL to execute the queries and place them in a temporary table internally before sending the results back to pt-table-sync. the advantage of this strategy is that pt-table-sync can fetch rows as desired without using a lot of memory inside the Perl process, while releasing locks on the MySQL table (to reduce contention with other queries). the disadvantage is that it uses more memory on the MySQL server instead. 😬

you probably want to leave --[no]buffer-to-client enabled too, because buffering into a temp table and then fetching it all into Perl's memory is probably a silly thing to do. this option is most useful for the GroupBy and Stream algorithms, which may fetch a lot of data from the server.

--[no]buffer-to-client — default: yes

…okay so this option makes the tool fetch rows one-by-one from MySQL while it's comparing. 🐢

It switches on mysql_use_result, which means MySQL keeps the selected rows chillin' on the server until the tool actually fetches them. Vibe: the tool uses less memory, but the rows stay locked on the server for longer. Trade-offs, bestie. 💅

If you disable it with --no-buffer-to-client, then mysql_store_result is used instead, which makes MySQL yeet all the selected rows at the tool at once. That might mean the results "cursor" stays open for a shorter time on the server — but if the tables are big, it could take a long time anyway and eat all your memory. 😭

For most non-trivial data sizes, you want to leave this option enabled. fr fr.

Heads up: this option is disabled when --bidirectional is used.

--[no]buffer-stdout — default: yes Turns on STDOUT buffering. 🌈 Disable this one if you want to see live progress while using output post-processing tools like tee or kubectl logs — otherwise you're just staring at nothing and wondering if it's broken. 👀
--channel — type: string The channel name used when you're connected to a server that uses replication channels. 🎀

Imagine this: you've got two sources — source_a on port 12345, source_b on port 1236 — and a replica connected to both of them using channels chan_source_a and chan_source_b. Now you wanna run pt-table-sync to sync the replica against source_a. Problem? pt-table-sync has no clue which one's the correct source, because SHOW REPLICA STATUS returns 2 rows and it's just standing there like 😵‍💫. In that case you hit it with --channel=chan_source_a to specify the channel name to use in the SHOW REPLICA STATUS command. slay.

--charset — short form: -A; type: string The default character set. 🦋 If the value is utf8, it 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.
--[no]check-child-tables — default: yes Checks whether --execute is gonna do child tables dirty. 😬 When --replace, --replicate, or --sync-to-source is specified, the tool may sync tables using REPLACE statements. If a table being synced has child tables with ON DELETE CASCADE, ON UPDATE CASCADE, or ON UPDATE SET NULL, the tool prints an error and skips the table — because REPLACE is literally DELETE then INSERT, so that DELETE cascades to the child table and deletes its rows. Worst case? It can delete all the rows in child tables. 💀💀

Specify --no-check-child-tables to disable this check. And to completely avoid affecting child tables, also specify --no-foreign-key-checks so MySQL won't cascade any operations from the parent to child tables.

This check is only performed if --execute and one of --replace, --replicate, or --sync-to-source is specified. --print does not check child tables.

Also ngl, the error message only prints the first child table it finds with an ON DELETE CASCADE, ON UPDATE CASCADE, or ON UPDATE SET NULL foreign key constraint — there could be other affected child tables lurking. 🙈

--[no]check-master — default: yes Deprecated bestie — this option is deprecated and will be removed in future releases. Use --[no]check-source instead. ⭐
--[no]check-slave — default: yes Also deprecated and will be removed in future releases. Use --[no]check-replica instead. ⭐
--[no]check-source — default: yes With --sync-to-source, it tries to verify that the detected source is actually the real source. 👑
--[no]check-replica — default: yes Checks whether the destination server is a replica.

If the destination server is a replica, making changes on it is generally unsafe. But sometimes you gotta — like, --replace won't work unless there's a unique index, so in that scenario you can't make changes on the source. By default pt-table-sync will complain if you try to change data on a replica. Specify --no-check-replica to disable this check.

Use it at your own risk. 🙏

--[no]check-triggers — default: yes Checks that no triggers are defined on the destination table. 🌸

Triggers were introduced in MySQL v5.0.2, so for older versions this option has no effect because triggers won't be checked at all.

--chunk-column — type: string Chunk the table on this column. 🍓
--chunk-index — type: string Chunk the table using this index.
--chunk-size — type: string; default: 1000 Number of rows, or data size, per chunk. ✨

This is the size of each chunk of rows for the Chunk and Nibble algorithms. The size can be either a number of rows, or a data size. Data sizes are specified with a suffix of k=kibibytes, M=mebibytes, G=gibibytes. Data sizes get converted to a number of rows by dividing by the average row length. math girlie math 🧮

--columns — short form: -c; type: array Compare this comma-separated list of columns. 💜
--config — type: Array Read this comma-separated list of config files. And psst — if you specify it, it must be the first option on the command line. No exceptions, bestie. 🎀
--conflict-column — type: string Compare this column when rows conflict during a --bidirectional sync. 🦋

When a same-but-differing row is found, the value of this column from each row is compared according to --conflict-comparison, --conflict-value and --conflict-threshold to figure out which row has the correct data and becomes the source. The column can be any type that has an appropriate --conflict-comparison (this is almost all types except, for example, blobs — blobs could never 💀).