Master-Slave Replication Fault Troubleshooting and Recovery

Introduction to Master-Slave Replication in MySQL

Master-slave replication is a cornerstone of high availability (HA) in MySQL, ensuring data redundancy, load balancing, and disaster recovery. The replication process involves a master server that writes data to binary logs, and a slave server that reads and applies these logs to maintain data consistency. This architecture is critical for applications requiring minimal downtime and reliable data integrity.

Common Replication Faults and Their Causes

Replication failures often stem from misconfigurations, network issues, or data inconsistencies. Key causes include:

  • Network issues: Firewalls blocking replication ports, DNS misconfigurations, or network partitions.
  • Configuration errors: Incorrect GTID settings, mismatched binlog formats, or insufficient replication user privileges.
  • Data inconsistency: Master lag, slave not keeping up with the master, or corrupted binary logs.
  • Slave not starting: Missing replication user credentials, incorrect passwords, or incompatible MySQL versions.

Troubleshooting Steps for Master-Slave Replication

Step 1: Check Status of Master and Slave

Verify the status of both master and slave servers using:

SHOW MASTER STATUS;  
SHOW SLAVE STATUS\G;

Identify the last successful replication event and check for errors in the logs.

Step 2: Analyze Logs for Errors

Inspect the master and slave logs for error messages, such as:

  • Master log: Error: Could not find master configuration
  • Slave log: Error: Could not find source log
    Use mysqlslavestatus to detect replication lag and source position.

Step 3: Verify Network Connectivity

Ensure network tools like ping, telnet, or nc confirm connectivity between master and slave. Check for port 3306 (default) and any additional ports required for replication.

Step 4: Validate Replication User Privileges

Confirm the replication user has the correct privileges:

SHOW GRANTS FOR 'repl_user'@'slave_host';

Ensure the user has REPLICATION SLAVE and REPLICATION CLIENT privileges.

Step 5: Use Tools for Diagnosis

Leverage tools like mysqlslavestatus and mysqlshow to diagnose issues. For example:

mysqlslavestatus --verbose

Identify lag, source position, and replication status.

Recovery Procedures for Replication Failures

Restarting the Slave

If the slave fails to start, restart the replication process by:

  1. Restarting the slave server.
  2. Using STOP SLAVE; START SLAVE; to reinitialize replication.

Repairing Data

For data inconsistencies, use mysqlcheck to repair corrupted tables:

mysqlcheck --check-binlog --all-databases

Rebuild binary logs if necessary.

Rebuilding Binary Logs

If the master is unreachable, rebuild the binary log on the slave:

FLUSH LOGS;  
RESET MASTER;

Reconfigure the replication user and reconnect.

Reconfiguring the Replication

After resolving issues, reconfigure the replication settings:

  • Set log_file and log_pos in the master.
  • Ensure the slave uses the correct server_id and master_host.

Case Studies

Case Study 1: Network Partition

A company experienced replication failures after a network partition caused the slave to lose connection to the master. Resolution involved:

  1. Restarting the slave and reinitializing replication.
  2. Rebuilding the binary log on the master and reconnecting the slave.

Case Study 2: Configuration Error

A misconfigured GTID on the master led to replication failures. Fix involved:

  1. Resetting the master’s replication configuration.
  2. Rebuilding the binary log and reconfiguring the slave.

Case Study 3: Slave Not Starting

A slave failed to start due to a missing replication user. Resolution included:

  1. Creating the replication user with the correct credentials.
  2. Restarting the slave and reinitializing replication.

Conclusion

Master-slave replication is vital for MySQL high availability, but troubleshooting requires systematic diagnosis and careful recovery. By validating logs, ensuring network connectivity, and aligning configurations, administrators can mitigate replication failures. Regular monitoring and maintenance are essential to prevent downtime and ensure data consistency.