# MySQL Relay Log Error: Slave Replication Relay Log Corruption

Your MySQL slave replication fails with relay log errors:

bash
[ERROR] Slave I/O: Failed to open relay log './relay-bin.000123', Error_code: 13
[ERROR] Slave I/O: Could not find relay log file mentioned in relay log index
[ERROR] Error reading packet from server: Lost connection to MySQL server during query
[ERROR] Slave SQL: Error initializing relay log position: Could not find target log file

The relay log stores events received from the master before the SQL thread executes them. Corruption here stops replication.

Understanding Relay Logs

Relay logs work differently from binary logs:

  1. 1.IO Thread reads master's binary log → writes to slave's relay log
  2. 2.SQL Thread reads slave's relay log → executes on slave database
  3. 3.Relay logs are auto-created and auto-deleted after execution
  4. 4.Index file: relay-bin.index tracks all relay log files

Step 1: Diagnose Relay Log Errors

Check slave status:

sql
SHOW SLAVE STATUS\G

Key fields to examine:

bash
Slave_IO_Running: No/Yes
Slave_SQL_Running: No/Yes
Relay_Log_File: relay-bin.000123
Relay_Log_Pos: 45678
Relay_Master_Log_File: mysql-bin.000456
Exec_Master_Log_Pos: 123456
Last_IO_Error: [Error details]
Last_SQL_Error: [Error details]

Check relay log files:

```bash ls -la /var/lib/mysql/relay-bin.*

# Check index file cat /var/lib/mysql/relay-bin.index

# Check relay log info file cat /var/lib/mysql/relay-log.info ```

Step 2: Check for Missing Relay Logs

Error: Could not find relay log file

```bash # Compare index file with actual files cat /var/lib/mysql/relay-bin.index ls -la /var/lib/mysql/relay-bin.*

# If files are missing, reset relay logs mysql -e "STOP SLAVE;" mysql -e "RESET SLAVE;" # This clears relay logs and index ```

Step 3: Fix Corrupted Relay Log Index

If the index file references files that don't exist:

```bash # Stop slave mysql -e "STOP SLAVE;"

# Reset relay logs completely mysql -e "RESET SLAVE;"

# Restart replication from master's current position mysql -e "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000456', MASTER_LOG_POS=123456;" mysql -e "START SLAVE;" ```

Or manually fix the index:

```bash # Stop MySQL completely systemctl stop mysqld

# Backup and recreate relay log index mv /var/lib/mysql/relay-bin.index /var/lib/mysql/relay-bin.index.backup

# Create new empty index touch /var/lib/mysql/relay-bin.index chown mysql:mysql /var/lib/mysql/relay-bin.index

# Clear relay log info mv /var/lib/mysql/relay-log.info /var/lib/mysql/relay-log.info.backup

# Start MySQL systemctl start mysqld

# Reset slave and restart replication mysql -e "RESET SLAVE;" mysql -e "CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000456', MASTER_LOG_POS=123456;" mysql -e "START SLAVE;" ```

Step 4: Fix Relay Log Position Mismatch

Error: Error initializing relay log position

```sql -- Stop slave STOP SLAVE;

-- Get master's current position from master -- On master: SHOW MASTER STATUS;

-- Reset slave relay logs RESET SLAVE;

-- Restart from correct position CHANGE MASTER TO MASTER_HOST = 'master-host', MASTER_USER = 'repl_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000456', MASTER_LOG_POS = 123456;

START SLAVE; SHOW SLAVE STATUS\G ```

Step 5: Handle Relay Log Corruption

Test relay log integrity:

bash
mysqlbinlog /var/lib/mysql/relay-bin.000123 > /dev/null
echo $?  # 0 = OK, 1 = corrupted

If corrupted, reset relay logs:

sql
STOP SLAVE;
RESET SLAVE;
START SLAVE;

For GTID-based replication (easier recovery):

sql
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO 
    MASTER_HOST = 'master-host',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'password',
    MASTER_AUTO_POSITION = 1;
START SLAVE;

Step 6: Fix IO Thread Relay Log Issues

If Slave_IO_Running: No due to relay log errors:

```sql -- Check the specific IO error SHOW SLAVE STATUS\G -- Look at Last_IO_Error

-- Common causes and fixes:

-- Cause: Relay log write failed (disk full) -- Fix: Free disk space or move relay log location

-- Cause: Permission denied -- Fix: ```

bash
chown -R mysql:mysql /var/lib/mysql/
chmod 750 /var/lib/mysql/

Configure relay log location:

ini
[mysqld]
relay_log = /var/lib/mysql-relay/relay-bin
relay_log_index = /var/lib/mysql-relay/relay-bin.index
bash
mkdir -p /var/lib/mysql-relay
chown mysql:mysql /var/lib/mysql-relay
systemctl restart mysqld

Step 7: Fix SQL Thread Relay Log Issues

If Slave_SQL_Running: No due to relay log:

```sql -- Check SQL error SHOW SLAVE STATUS\G -- Look at Last_SQL_Error

-- If relay log position issue STOP SLAVE; RESET SLAVE;

-- Restart from master position CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000456', MASTER_LOG_POS = 123456; START SLAVE; ```

Step 8: Handle Relay Log Disk Space Issues

bash
[ERROR] Disk is full writing './relay-bin.~rec~'

```bash # Check disk space df -h /var/lib/mysql

# Check relay log size du -sh /var/lib/mysql/relay-bin.*

# Move relay logs to another disk ```

ini
[mysqld]
relay_log = /mnt/larger-disk/mysql-relay/relay-bin
relay_log_index = /mnt/larger-disk/mysql-relay/relay-bin.index
relay_log_info_repository = TABLE
relay_log_recovery = 1

```bash mkdir -p /mnt/larger-disk/mysql-relay chown mysql:mysql /mnt/larger-disk/mysql-relay systemctl restart mysqld

# Reset replication after restart mysql -e "STOP SLAVE; RESET SLAVE; START SLAVE;" ```

Step 9: Use Relay Log Recovery Feature

Enable automatic relay log recovery:

ini
[mysqld]
relay_log_recovery = 1
  1. 1.With this enabled, on startup MySQL:
  2. 2.Deletes all existing relay logs
  3. 3.Resets relay log index
  4. 4.Starts receiving from master from the last executed position

This prevents stale relay log issues but requires master to be reachable.

Step 10: Store Relay Log Position in Table

Store position in a table instead of file (more reliable):

ini
[mysqld]
relay_log_info_repository = TABLE
master_info_repository = TABLE

```sql -- Check where position is stored SHOW VARIABLES LIKE '%repository';

-- View replication info tables SELECT * FROM mysql.slave_master_info; SELECT * FROM mysql.slave_relay_log_info; ```

Benefits: - Crash-safe position storage - Atomic updates with relay log operations - Prevents position file corruption

Step 11: Fix Relay Log After Slave Crash

If slave crashed and relay logs are stale:

```bash # Check error log grep "relay" /var/log/mysql/error.log

# If relay_log_recovery is enabled, MySQL auto-recovers # Otherwise, manual recovery needed ```

```sql -- Manual recovery after crash STOP SLAVE;

-- Get last executed position from relay log info -- Or check mysql.slave_relay_log_info if using TABLE repository SELECT Master_log_name, Master_log_pos FROM mysql.slave_relay_log_info;

-- Reset and restart RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.xxx', MASTER_LOG_POS = xxx; START SLAVE; ```

Step 12: Rebuild Slave from Master Backup

If relay log issues are severe, rebuild slave:

On master:

```bash mysqldump --master-data=2 --single-transaction --all-databases \ --routines --triggers --events > master_backup.sql

# Or with GTID mysqldump --master-data=2 --single-transaction --set-gtid-purged=ON \ --all-databases > master_backup.sql ```

On slave:

sql
STOP SLAVE;
RESET SLAVE ALL;  -- Remove all replication config
bash
mysql < master_backup.sql

```sql -- Extract coordinates from backup (check first 50 lines) -- For non-GTID: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx', MASTER_LOG_POS=xxx

CHANGE MASTER TO MASTER_HOST = 'master-host', MASTER_USER = 'repl_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000456', MASTER_LOG_POS = 123456; -- Or: MASTER_AUTO_POSITION = 1 for GTID

START SLAVE; SHOW SLAVE STATUS\G ```

Verification After Fix

sql
SHOW SLAVE STATUS\G

Confirm: - Slave_IO_Running: Yes - Slave_SQL_Running: Yes - Seconds_Behind_Master: 0 (or decreasing) - Relay_Log_File: relay-bin.000001 (new file) - Empty Last_IO_Error and Last_SQL_Error

Test replication:

```sql -- On master CREATE DATABASE replication_test; CREATE TABLE replication_test.test (id INT PRIMARY KEY); INSERT INTO replication_test.test VALUES (1);

-- On slave, verify data arrived SELECT * FROM replication_test.test;

-- Cleanup DROP DATABASE replication_test; ```

Monitor Relay Log Health

```bash #!/bin/bash # check_relay_log.sh

# Check relay log count LOG_COUNT=$(ls /var/lib/mysql/relay-bin.[0-9]* 2>/dev/null | wc -l)

# Check relay log size LOG_SIZE=$(du -sm /var/lib/mysql/relay-bin.[0-9]* 2>/dev/null | awk '{sum+=$1} END {print sum}')

# Alert if unusual if [ "$LOG_COUNT" -gt 10 ]; then echo "Warning: $LOG_COUNT relay log files" | mail -s "Relay Log Alert" admin@example.com fi

if [ "$LOG_SIZE" -gt 1000 ]; then echo "Warning: Relay logs using ${LOG_SIZE}MB" | mail -s "Relay Log Alert" admin@example.com fi ```

Best Practice Configuration

```ini [mysqld] # Crash-safe replication relay_log_recovery = 1 relay_log_info_repository = TABLE master_info_repository = TABLE

# Relay log settings relay_log = relay-bin max_relay_log_size = 1G

# Sync relay log sync_relay_log = 1 sync_relay_log_info = 1

# For GTID replication gtid_mode = ON enforce_gtid_consistency = ON ```

Quick Reference Commands

```sql -- Show relay logs SHOW RELAYLOG EVENTS IN 'relay-bin.000123';

-- Stop/start threads separately STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; STOP SLAVE SQL_THREAD; START SLAVE SQL_THREAD;

-- Reset relay logs STOP SLAVE; RESET SLAVE; -- Clears relay logs only START SLAVE;

-- Remove all replication config RESET SLAVE ALL;

-- View relay log info SELECT * FROM mysql.slave_relay_log_info; ```

Troubleshooting Checklist

  • [ ] Check SHOW SLAVE STATUS for error details
  • [ ] Verify relay log files exist
  • [ ] Compare index file with actual files
  • [ ] Check disk space for relay log directory
  • [ ] Check file permissions
  • [ ] Enable relay_log_recovery for automatic recovery
  • [ ] Use TABLE repository for crash safety
  • [ ] Reset relay logs if corrupted
  • [ ] Restart replication from correct master position
  • [ ] Verify both threads running after fix
  • [ ] Test replication with sample data