How to check and sync MariaDB replication inconsistency’s

In this notes, i have customer with MariaDB replication inconsistencies. This issue can happen by intention or accidental. Example, someone perform changes on the database at slave server or because the slave instance OOM for some times and started again outside of replication retry timeout in the my.cnf configuration.

On this issue, one of the tables have inconsistencies data for the same table at Master and Slave.
Here’s the servers for MariaDB 10.5.4 replication.
10.100.102.112 is Master server
10.100.102.113 is Slave Server.

The tools used is from Percona called percona-toolkit. You can install it based on your system and you can follow the command below (!With cautions.)

Create temporare user for check and sync process at Master.

Mysql>CREATE USER 'checksum'@'%' IDENTIFIED BY 'checksumpass';
Mysql>GRANT ALL ON . TO 'checksum'@'%';
Mysql>FlUSH PRIVILEGES;

Check the table reference using pt-table-checksum to replication master host.

#pt-table-checksum --user=checksum --password=checksumpass --host=10.100.102.112 --databases=sharding --tables=data_20220410 --binlog-format=MIXED --check-replication-filters

Using command above we found there is a data inconsistencies between masters and slave.

Next, we want to perform sync those different data from Replication Master to Slave. But first, we check and perform simulation. In this command, the host now using Slave.

#pt-table-sync --sync-to-master --host=10.100.102.113,--user=checksum,--password=checksumpass --databases=sharding --tables=data_20220410 --dry-run --print --verbose

You will get the verbose output of the querry executed for the process. There you can see.

If all is OK, then execute the sync and wait the process to be complete.

pt-table-sync h=10.100.102.113,u=checksum,p=checksumpass --databases=sharding --tables=data_20220410 --sync-to-master --print --verbose