# MySQL Binary Log Error: Corrupted or Missing Binlog Files

MySQL binary log errors appear in various forms:

bash
[ERROR] Failed to open the relay log './relay-bin.000001' (relay_log_pos 4)
[ERROR] Could not find target log file mentioned in master info file
[ERROR] Error in Log_event::read_log_event(): 'Event too small', data_len: 0
[ERROR] Binlog has bad magic number; It's not a binary log file
[ERROR] my_errno: 13  Error reading file './mysql-bin.000123'

Binary logs are essential for replication and point-in-time recovery. Here's how to diagnose and fix binary log issues.

Understanding Binary Logs

Binary logs record all data modification events:

  • Used for replication (master to slave)
  • Enable point-in-time recovery
  • Stored in sequence: mysql-bin.000001, mysql-bin.000002, etc.
  • Index file: mysql-bin.index tracks all log files

Step 1: Diagnose Binary Log Errors

Check the error log:

bash
grep -i "binary\|binlog\|relay" /var/log/mysql/error.log | tail -50

Check binary log status:

```sql -- View binary log files SHOW BINARY LOGS;

-- Check current position SHOW MASTER STATUS;

-- Check binary log format SHOW VARIABLES LIKE 'binlog_format'; ```

Check binary log files exist:

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

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

Step 2: Fix Corrupted Binary Log Index

If the index file doesn't match actual files:

```bash # Stop MySQL systemctl stop mysqld

# Backup current index cp /var/lib/mysql/mysql-bin.index /var/lib/mysql/mysql-bin.index.backup

# List actual binary log files ls /var/lib/mysql/mysql-bin.[0-9]* | sort -V

# Recreate index file ls /var/lib/mysql/mysql-bin.[0-9]* | sort -V > /var/lib/mysql/mysql-bin.index

# Ensure proper permissions chown mysql:mysql /var/lib/mysql/mysql-bin.index

# Start MySQL systemctl start mysqld ```

Step 3: Handle Missing Binary Log Files

If a binary log file is missing:

bash
[ERROR] Could not find first log file name in binary log index file
[ERROR] Failed to open log file './mysql-bin.000098'

Option A: Purge missing logs (if not needed for replication)

```sql -- Check which logs exist SHOW BINARY LOGS;

-- Purge logs up to a specific file PURGE BINARY LOGS TO 'mysql-bin.000099';

-- Or purge logs older than a date PURGE BINARY LOGS BEFORE '2024-01-15 00:00:00'; ```

Option B: Create dummy log file

```bash # Stop MySQL systemctl stop mysqld

# Create empty log file with correct format # Binary log starts with magic number: 0xfe626963 printf '\xfe\x62\x69\x63' > /var/lib/mysql/mysql-bin.000098 chown mysql:mysql /var/lib/mysql/mysql-bin.000098

# Start MySQL systemctl start mysqld ```

Step 4: Fix Corrupted Binary Log Content

If a binary log file is corrupted:

```bash # Test binary log integrity mysqlbinlog /var/lib/mysql/mysql-bin.000123 > /dev/null echo $? # 0 = OK, non-zero = corrupted

# Check for specific errors mysqlbinlog /var/lib/mysql/mysql-bin.000123 2>&1 | head -20 ```

If corrupted and not needed for replication:

sql
-- Purge the corrupted log
PURGE BINARY LOGS TO 'mysql-bin.000124';

If needed for replication:

```bash # Try to extract good events mysqlbinlog --force-read /var/lib/mysql/mysql-bin.000123 > recoverable_events.sql

# Check what was recovered head -100 recoverable_events.sql

# Apply recovered events (carefully!) mysql -u root -p < recoverable_events.sql ```

Step 5: Reset Binary Logs

If binary logs are beyond repair:

```sql -- Reset all binary logs (starts fresh) RESET MASTER;

-- This creates a new log file mysql-bin.000001 SHOW BINARY LOGS; ```

Warning: This breaks replication! Use only if: - Server is standalone, or - You're rebuilding replication from scratch

Step 6: Fix Binary Log Position for Replication

If slave can't find position in binary log:

bash
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'

On master:

```sql -- Check available logs SHOW BINARY LOGS;

-- Get current position SHOW MASTER STATUS; ```

On slave:

```sql -- Stop replication STOP SLAVE;

-- Set new position (use position from master) CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000124', MASTER_LOG_POS = 154;

-- Start replication START SLAVE;

-- Check status SHOW SLAVE STATUS\G ```

Step 7: Fix GTID Binary Log Issues

For GTID-based replication:

bash
[ERROR] The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1
[ERROR] but the master has purged binary logs containing GTIDs that the slave requires.

```sql -- On master, check GTID purged SHOW GLOBAL VARIABLES LIKE 'gtid_purged';

-- On slave, check executed GTIDs SHOW SLAVE STATUS\G -- Look for Retrieved_Gtid_Set and Executed_Gtid_Set

-- Find missing GTIDs -- If GTIDs are missing on master, you need to rebuild slave ```

To fix GTID gaps:

```sql -- On master, get current GTID SHOW MASTER STATUS;

-- On slave, skip to current GTID if data is consistent STOP SLAVE; SET GLOBAL GTID_PURGED = 'uuid:1-12345'; -- The missing range START SLAVE; ```

Step 8: Fix Binary Log Sync Errors

bash
[ERROR] Binary log is not synchronized with relay log

```sql -- On slave STOP SLAVE;

-- Reset relay logs RESET SLAVE;

-- Restart from current master position CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000124', MASTER_LOG_POS = 154;

START SLAVE; ```

Step 9: Handle Disk Full Binary Log Errors

bash
[ERROR] Disk is full writing './mysql-bin.~rec~' (Errcode: 28 - No space left on device)

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

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

# Check binary log retention mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';" mysql -e "SHOW VARIABLES LIKE 'expire_logs_days';" ```

```sql -- Purge old logs PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Or set automatic expiration SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7 days SET GLOBAL expire_logs_days = 7; -- Deprecated but still used

-- Reduce max size of individual logs SET GLOBAL max_binlog_size = 1073741824; -- 1GB default ```

Step 10: Fix Binary Log Format Issues

bash
[ERROR] The binary log format 'ROW' is not supported for row-based replication
[ERROR] Cannot replicate because the master uses ROW format and slave cannot handle it

```sql -- Check format on master SHOW VARIABLES LIKE 'binlog_format';

-- Common formats: ROW, STATEMENT, MIXED

-- Change format (requires restart for some versions) SET GLOBAL binlog_format = 'ROW'; -- Or STATEMENT or MIXED

-- Add to my.cnf for persistence [mysqld] binlog_format = ROW ```

Step 11: Verify Binary Log Function

After fixing:

```sql -- Check binary logs exist and are growing SHOW BINARY LOGS;

-- Create a test entry CREATE DATABASE binlog_test; INSERT INTO some_table VALUES (1);

-- Check position changed SHOW MASTER STATUS;

-- Verify log content SHOW BINLOG EVENTS IN 'mysql-bin.000124';

-- Read log file mysqlbinlog /var/lib/mysql/mysql-bin.000124 | head -50

-- Cleanup DROP DATABASE binlog_test; ```

Step 12: Monitor Binary Log Health

```sql -- Check log rotation SHOW VARIABLES LIKE 'max_binlog_size';

-- Check if logs are being created SELECT COUNT(*) FROM information_schema.FILES WHERE FILE_NAME LIKE '%mysql-bin%';

-- Monitor log size SELECT FILE_NAME, TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS Size_MB FROM information_schema.FILES WHERE FILE_NAME LIKE '%mysql-bin%' ORDER BY FILE_NAME; ```

Set up monitoring:

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

# Check binary log count LOG_COUNT=$(mysql -N -e "SHOW BINARY LOGS" | wc -l)

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

# Alert if too many logs or too much space if [ "$LOG_COUNT" -gt 50 ] || [ "$LOG_SIZE" -gt 5000 ]; then echo "Warning: $LOG_COUNT binlog files using ${LOG_SIZE}MB" | \ mail -s "MySQL Binlog Alert" admin@example.com fi ```

Configuration Best Practices

```ini [mysqld] # Enable binary logging log_bin = mysql-bin binlog_format = ROW

# Sync to disk for durability sync_binlog = 1

# Size before rotation max_binlog_size = 1G

# Auto-expire old logs (MySQL 8.0+) binlog_expire_logs_seconds = 604800 # 7 days

# For MySQL 5.7 and earlier expire_logs_days = 7

# GTID for easier management gtid_mode = ON enforce_gtid_consistency = ON

# For replication safety binlog_cache_size = 1M max_binlog_cache_size = 2G ```

Quick Reference Commands

```sql -- Show all binary logs SHOW BINARY LOGS;

-- Show current position SHOW MASTER STATUS;

-- Show events in a log SHOW BINLOG EVENTS IN 'mysql-bin.000123';

-- Purge old logs PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY; PURGE BINARY LOGS TO 'mysql-bin.000120';

-- Reset all logs RESET MASTER;

-- Check format SHOW VARIABLES LIKE 'binlog_format';

-- Monitor relay logs (on slave) SHOW RELAYLOG EVENTS IN 'relay-bin.000123'; ```

Troubleshooting Checklist

  • [ ] Check error log for specific error code
  • [ ] Verify binary log files exist in data directory
  • [ ] Verify index file matches actual files
  • [ ] Test log integrity with mysqlbinlog
  • [ ] Purge corrupted logs if not needed
  • [ ] Reset logs if beyond repair
  • [ ] Update replication position after fix
  • [ ] Verify logs are growing after fix
  • [ ] Set up log expiration
  • [ ] Monitor disk space