Delete Operation Failure on Replica

When a record is deleted on the primary but doesn't exist on the replica, replication halts with an error.

Last_SQL_Error: Could not execute Delete_rows event on table app.users;
Can't find record in 'users',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000008, end_log_pos 412

Solution: Since the record has already been removed from the primary, skip the corresponding event on the replica.

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

For frequent occurrences, consider implementing an automated script to handle these specific errors.

Duplicate Primary Key Conflict

The replica contains a record that also exists on the primary, causing an insert conflict.

Last_SQL_Error: Could not execute Write_rows event on table app.users;
Duplicate entry '5' for key 'PRIMARY',
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log mysql-bin.000008, end_log_pos 1156

Solution: First, examine the table structure on the replica:

mysql> DESC app.users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | 0       |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Remove the conflicting primary key from the replica:

mysql> DELETE FROM users WHERE id = 5;
Query OK, 1 row affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

mysql> SELECT * FROM users WHERE id = 5;

Verify the data consistency on both primary and replica.

Missing Record During Update

An update operation fails on the replica because the target record doesn't exist.

Last_SQL_Error: Could not execute Update_rows event on table app.users;
Can't find record in 'users',
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000015, end_log_pos 938

Solusion: Parse the binary log on the primary to determine what the update was attempting:

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000015 | grep -A '10' 938

#120403 15:22:18 server id 22  end_log_pos 938  Update_rows: table id 45 flags: STMT_END_F
### UPDATE app.users
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='old@example.com' /* VARSTRING(50) meta=65036 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='new@example.com' /* VARSTRING(50) meta=65036 nullable=1 is_null=0 */
# at 938
#120403 15:22:18 server id 22  end_log_pos 965  Xid = 128
COMMIT/*!*/;

On the replica, check if the record exists:

mysql> SELECT * FROM users WHERE id = 5;
Empty set (0.00 sec)

Compare with the primary:

mysql> SELECT * FROM users WHERE id = 5;
+----+------------------+
| id | email            |
+----+------------------+
|  5 | new@example.com  |
+----+------------------+
1 row in set (0.00 sec)

Insert the missing data on the replica, then resume replication:

mysql> INSERT INTO users VALUES (5, 'new@example.com');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM users WHERE id = 5;
+----+------------------+
| id | email            |
+----+------------------+
|  5 | new@example.com  |
+----+------------------+
1 row in set (0.00 sec)

mysql> STOP SLAVE;
mysql> SET GLOBAL sql_slave_skip_counter = 1;
mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

Error 1236: Binary Log File Missing

When binary logs are accidentally deleted on the primary or the replica's position references a non-existent file, replication fails.

Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
'Could not find first log file name in binary log index file'

Solution:

1. Stop the replica:

   STOP SLAVE;
   

2. Check available binary logs on the primary:

   mysql> SHOW MASTER LOGS;
   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000013 |       198 |
   +------------------+-----------+
   

3. Reconfigure the replica to use the current log file and position:

   CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=198;
   

4. Start the replica:

   START SLAVE;
   
   SHOW SLAVE STATUS\G;
   
   Master_Log_File: mysql-bin.000013
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Last_IO_Error:
   

Relay Log Corruption

When the replica's relay log becomes corrupted, replication cannot proceed.

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number;
It's not a binary log file that can be used by this version of MySQL

Method 1: Manual Recovery

Identify the binary log position being executed (Relay_Master_Log_File and Exec_Master_Log_Pos), then re-synchronize.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
             Relay_Master_Log_File: mysql-bin.000010
              Exec_Master_Log_Pos: 821
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
              Last_Error: Error initializing relay log position: I/O error reading the header from the binary log

Reset replication using the execution position:

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=821;
Query OK, 0 rows affected (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
              Last_Errno: 0
              Last_Error:

Method 2: Using Backup Tools

When data divergence is too severe for manual correction, use hot backup tools. ibbackup (commercial) and xtrabackup (open source) providde equivalent functionality.

These tools create a consistent snapshot without locking tables during backup. They record a checkpoint and capture subsequent changes in a log file, applying them during recovery. The tools backup data files (ibdata, .ibd) but do not preserve .frm table structure files.