How to Recover MySQL/MariaDB Replication After a Crash

Home > Developers help > Server Administration > Mysql > How to Recover MySQL/MariaDB Replication After a Crash

MySQL and MariaDB replication is a powerful feature for database redundancy and scaling. However, unexpected crashes can disrupt replication and lead to errors that require careful diagnosis and resolution. This article guides you through identifying common replication errors after a crash and provides steps to restore replication functionality.

Common Error Messages

After a server crash, you may encounter error messages such as:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

This indicates that the replication slave is attempting to read a position in the master’s binary log that no longer exists or has become corrupted.

Diagnostic Steps

  1. Check the Slave Status: 
     

    Run SHOW SLAVE STATUS\G on the replica to view the current replication state and error messages.

  2. Verify Binary Log Files: 
     

    Ensure that the master server’s binary log file referenced by the replica is available and not corrupted. Use SHOW BINARY LOGS; on the master to list available binary logs.

  3. Compare Log Positions: 
     

    Confirm that the Relay_Master_Log_File and Exec_Master_Log_Pos values on the replica align with the master’s current binary log file and position.

Solution: Re-Synchronizing Replication

  1. Obtain a Fresh Backup: 
     

    Create a new backup of the master database using mysqldump or mysqlbackup, ensuring you include the --master-data option to capture the correct binary log position.

  2. Restore the Backup on the Replica: 
     

    Import the backup into the replica server. This will bring the replica data in sync with the master.

  3. Configure Replication Coordinates: 
     

    Use the binary log file and position noted during the backup to execute CHANGE MASTER TO on the replica:

    CHANGE MASTER TO 
  MASTER_LOG_FILE='mysql-bin.000123', 
  MASTER_LOG_POS=456789;

    Replace these values with the actual file and position from your backup.

  4. Start Replication: 
     

    Execute START SLAVE; (or START REPLICA; in MySQL 8.0) to resume replication.

  5. Verify Replication Status: 
     

    Run SHOW SLAVE STATUS\G again to ensure there are no remaining errors and that replication is running smoothly.

Recovering replication after a crash involves diagnosing the exact error, verifying binary log integrity, and, if necessary, resynchronizing the replica with the master. By following these steps, you can restore a healthy replication environment and minimize downtime for your MySQL or MariaDB infrastructure.

Updated:

Other Articles

×