Using pt‑slave‑repair to Automatically Fix MySQL Master‑Slave Replication Errors 1032 and 1062

The author, a DBA team member familiar with MySQL and Oracle, presents an open‑source tool called pt‑slave‑repair that can automatically fix common MySQL master‑slave replication errors, improving efficiency, reducing risk, and enabling rapid recovery.

1 Introduction

In daily operations, two frequent replication problems are encountered:

Bulk deletions on the master that are not recorded on the slave, leading to error 1032 .

Violations of unique constraints on the master, causing error 1062 .

Pain points

These errors are easy to understand but become cumbersome when they occur repeatedly, especially on large production datasets where resetting the replication topology is costly.

2 Tool Introduction

pt‑slave‑repair is a supplement to Percona Toolkit's pt‑slave‑restart . It automatically repairs erroneous replication data and restores interrupted SQL thread replication.

3 Working Principle

Detect error 1062 (duplicate key) or 1032 (missing row). If binlog_format is not ROW or binlog_row_image is not FULL , exit.

Run show slave status to obtain binlog, position, and GTID information.

Parse the master binlog; skip DELETE events.

Disable slave_parallel_workers to stop parallel replication.

Depending on the replication mode, set either SET gtid_next or SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 .

If the event is UPDATE / INSERT , convert it to a REPLACE INTO statement.

Execute the generated REPLACE INTO on the slave to synchronize data, then repeat step 5.

Set the slave to read_only mode.

Finally, verify that show slave status reports Slave_IO_Running=Yes and Slave_SQL_Running=Yes .

4 Preparation

Grant the replication user repl the necessary privileges:

3306[(none)]> show grants for repl@'%';
+----------------------------------------------+
| Grants for repl@%                           |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' |
| GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'repl'@'%' |
| GRANT SELECT ON `performance_schema`.* TO 'repl'@'%' |
+----------------------------------------------+
3306[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Download and install the tool:

wget https://github.com/hcymysql/pt-slave-repair/releases/download/pt-slave-repair_v1.0.8/pt-slave-repair
chmod 755 pt-slave-repair

5 Usage

Fixing error 1032

Run the tool on the slave host:

/root/pt-slave-repair -H 192.168.3.13 -P 3306 -u repl -p 123456 -d test

The tool logs the detection of error 1032, generates a DELETE statement, applies it, and reports successful repair of the affected rows.

Fixing error 1062

When a duplicate‑key error occurs, the tool converts the offending INSERT into a REPLACE INTO statement:

/root/pt-slave-repair -H 192.168.3.13 -P 3306 -u repl -p 123456 -d test

Log output shows the generated REPLACE INTO `test`.`t2`(`id`,`name`) VALUES (3,'shifeng') and confirms that the replication status returns to normal.

6 Summary

Always keep the slave in read_only mode to avoid accidental writes.

Ensure the master writes all changes to the binlog.

Follow strict development standards to prevent replication‑breaking operations.

References

[1] pt‑slave‑repair: https://github.com/hcymysql/pt-slave-repair

[2] pt‑slave‑restart documentation: https://docs.percona.com/percona-toolkit/pt-slave-restart.html

[3] Percona Toolkit: https://docs.percona.com/percona-toolkit/

[4] Blog post on automatic MySQL replication repair: https://dbaplus.cn/news-11-5524-1.html

[5] Demonstration video: https://edu.51cto.com/video/1658.html