pt-table-sync

Search for a command to run...

No comments yet. Be the first to comment.
Performing schema changes on production databases comes with significant overhead, such as locking entire tables and potentially causing replication lag in master-slave architectures. For MySQL and its variants, there's a powerful tool that mitigates...
I’m sure most of you might have already heard about traceroute. If not, it’s a command line tool generally used to trace the path of the packet over the network. I recently came across the implementation detail of it, and it’s surprisingly simple to ...

Bastion host or server is a server mainly for jumping connection, especially ssh connection. It’s also known as jump server The reason is mainly for security and to reduce the attack surface. Only bastion server is exposed to the outer world and all ...

Restoring replica from master

How about explicitly declaring class attributes or reducing memory footprint of class variables. Slots seems to be an answer. slots (__slots__) a special class variable that is assigned a sequence of strings that are variable names used by instance...

I recently had a situation when a lot of rows from one of our replicas was missing.
One of the solutions that came in mind is to use the snapshot of the source and reconfigure it as replica. While this would have worked, it seemed like an extreme approach for what might be a localized issue.
After digging into the logs after a while, the problem seems to be only isolated to a single table. So, I tried pt-table-sync.
pt-table-sync is a command-line utility that helps synchronize data across different MySQL servers. It can be used in various scenarios, such as:
Synchronizing data between a source and its replica
Resolving inconsistencies between two source servers
Comparing and fixing differences between any two MySQL data sources
The synchronization command looks like this:
pt-table-sync --execute h=x.x.x.x,D=database,t=table,u=user,p=pass h=localhost,u=user,p=pass
Here’s what each parameter does:
h=x.x.x.x → The host of the source database (where data is correct)
D=database → The database name
t=table → The table name that needs synchronization
u=user / p=pass → MySQL credentials for authentication
h=localhost → The replica server where data needs to be fixed
pt-table-sync generally sync data from the replica to the source, i.e. from the 2nd data source to the first as changes to the replica are usually the source of the problems in the first place. i.e. the changes made on the source will be replicated down the replica via the normal replication process.
The synchronization process in pt-table-sync consists of three main operations:
UPDATE →For stale data NOOP operation is triggered on source, that only affects the replica.
DELETE → DELETE statements on the source for rows that don't exist there but exist in replica.
INSERT → For missing data, that exist on source but not on replica. It’s retriggered so it can pass via binary logs
By default, pt-table-sync generates and executes these statements to bring the replica in sync with the source. If you want to preview these operations before executing them, you can use:
pt-table-sync --print h=x.x.x.x,D=database,t=table,u=user,p=pass h=localhost,u=user,p=pass
This prints out the SQL statements that would be executed, allowing you to review changes before applying them.