I have a MySQL setup with one primary server and three replica databases. The primary server just crashed and I can’t access it at all because of hardware problems.
I need to figure out which of my three replica servers has the most recent data and is best synchronized with the failed primary. Since I can’t check the primary server status, what’s the best way to compare the replicas and determine which one should become the new primary?
Are there specific MySQL commands or log files I should check on each replica to see their replication position and data consistency?
check the binary log coordinates on each replica with SHOW MASTER STATUS - the one with the highest log file number and position is your best bet. also check the relay log info to see which replica was keeping up best before the crash.
I faced a similar issue recently. To determine which MySQL replica is the most current, executing SHOW SLAVE STATUS on each of your replicas is essential. Focus on the Exec_Master_Log_Pos value; the replica with the highest position indicates the most recent transactions processed. If GTIDs are being used, you should also examine the Retrieved_Gtid_Set and Executed_Gtid_Set to assess their states. Additionally, review your monitoring logs for Seconds_Behind_Master, as those can provide valuable insights into replication delays. Don’t forget to check the timestamps in the relay logs located in /var/log/mysql/, as the latest timestamp usually points to the most up-to-date replica for promotion.