Troubleshooting MySQL Replication Duplicate Entry Errors Caused by mysqldump on MyISAM Tables

1. Fault Phenomenon

A customer reported that after building a slave using mysqldump and starting replication, the slave reported an error: Could not execute Write_rows event on table xxx; Duplicate entry 'xxx' for key 'PRIMARY' .

-- 主库备份
shell> mysqldump -uroot -pxxx --master-data=2 --single-transaction -A --routines --events --triggers > /tmp/xxx.sql

-- 从服务器还原备份并启动复制
mysql> reset master;
mysql> reset slave all;
mysql> source /tmp/xxx.sql;
mysql> change master to master_host='xxx',master_port=3306,master_user='xxx',master_password='xxx',master_auto_position=1;
mysql> start slave;

2. Problem Investigation

Inspecting the failing table revealed it uses the MyISAM engine. The mysqldump --single-transaction option only guarantees consistency for InnoDB tables, not for MyISAM, which likely caused the issue.

3. Problem Resolution

Changing the table's storage engine to InnoDB and re‑doing the backup allows the slave to be built without errors.

4. Problem Reproduction

To understand why the error occurs, the following steps were performed:

Environment Information

Operating System

CentOS Linux release 7.5.1804 (Core)

Version

MySQL 5.7.25

Master

10.186.60.187

Slave

10.186.60.37

GTID

Enabled

Operation Steps

On the master, a large InnoDB table ( testdb_innodb.sbtest1 ) was created with 10 million rows using Sysbench. A MyISAM table ( testdb_myisam.sbtest2 ) was also created.

# Create InnoDB table and insert data (omitted for brevity)
# Create MyISAM table
CREATE TABLE testdb_myisam.sbtest2 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  k int(10) unsigned NOT NULL DEFAULT '0',
  c char(120) NOT NULL DEFAULT '',
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY k_1 (k)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

During the logical backup of the InnoDB table, a row was inserted into the MyISAM table:

# Backup command
/data/mysql/base/5.7.25/bin/mysqldump -h10.186.60.187 -P3307 -uroot -p1 --master-data=2 --single-transaction -A --routines --events --triggers > /tmp/dump.sql

# Insert into MyISAM while backup is running
INSERT INTO testdb_myisam.sbtest2(k,c,pad) VALUES(2,'myisam','myisam');

The backup file was then restored on the slave, replication was configured, and START SLAVE was issued. The slave’s SQL thread stopped with error 1062 (duplicate entry) because the row inserted during the backup had already been applied from the dump.

5. Principle Analysis

When mysqldump starts, it obtains a consistent snapshot (T1) and issues FLUSH TABLES WITH READ LOCK before unlocking.

Backup of InnoDB tables completes at T2.

Backup of MyISAM tables completes at T3.

If an INSERT occurs on the MyISAM table between T1 and T2, the binlog records it, and mysqldump also copies that row into the dump file.

During replication, the SQL thread replays the binlog events from T1‑T2, causing a duplicate‑key error because the row already exists from the restored dump.

The --single-transaction option only provides a snapshot for InnoDB; for non‑InnoDB tables it captures the current data, leading to the inconsistency.

6. Improvement Suggestions

Convert non‑InnoDB tables to InnoDB before backup and replication (consider impact on online services).

Use XtraBackup for physical backups; it avoids the global read lock on MyISAM tables and is suitable when engine conversion is not possible.

Reference

[1] --single-transaction: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction