Have you ever faced the challenge of purging MySQL binlogs to free up disk space, only to encounter the puzzling error, "Target log not found in binlog index file," despite the binlogs being present on the disk?

Let’s dive into a real incident where this error emerged during a critical disk space reclamation attempt.

The Incident: Facing Binlog Purge Issues

Yesterday, we encountered low disk space on one of our client's servers. The /log partition rapidly filled up, decreasing by 1 GB every 5 minutes. This partition is specifically designated for MySQL binlogs, which record database changes over time.

Our initial plan was to purge the binlogs since no replication setups were linked to this server. However, when we attempted to purge the binlog, we encountered the error message: "ERROR 1373 (HY000): Target log not found in binlog index." This error was puzzling because we could confirm that the binlog file was indeed present on the disk.

mysql> purge binary logs to 'mysql-bin.019137';
ERROR 1373 (HY000): Target log not found in binlog index

root@ACI-PROD-VM-MYSQL06:~# ls -lrth  /log/mysql_binlog/mysql-bin.019137
-rw-r----- 1 mysql mysql 251M Mar 26 21:33 /log/mysql_binlog/mysql-bin.019137

Investigation: Why Was MySQL Unable to Identify the Binlog?

To investigate, we checked the binlog files at the MySQL level using the MySQL command SHOW BINARY LOGS;, but it returned an empty set, adding to the confusion. The error message pointed to an issue with the binlog index, suggesting that MySQL couldn't locate the binlog file despite its presence.

mysql> SHOW BINARY LOGS;
Empty set (0.00 sec)

The Discovery: A Hidden Culprit in the Binlog Index

Upon digging deeper and inspecting the binlog.index file itself, we noticed that the binlog file positions were present in the file, which led us to suspect something further. We decided to conduct a more precise investigation of the binlog.index file.

root@ACI-PROD-VM-MYSQL06:~# less /log/mysql_binlog/mysql-bin.index | grep mysql-bin.019137
/log/mysql_binlog/mysql-bin.019137

Our efforts paid off when we identified a blank space at the beginning of the file. This minor space turned out to be the culprit behind MySQL's inability to recognize the binlogs.

root@ACI-PROD-VM-MYSQL06:~# head /log/mysql_binlog/mysql-bin.index 

/log/mysql_binlog/mysql-bin.018290
/log/mysql_binlog/mysql-bin.018291
/log/mysql_binlog/mysql-bin.018292
/log/mysql_binlog/mysql-bin.018293
/log/mysql_binlog/mysql-bin.018294
/log/mysql_binlog/mysql-bin.018295
/log/mysql_binlog/mysql-bin.018296
/log/mysql_binlog/mysql-bin.018297
/log/mysql_binlog/mysql-bin.018298
/log/mysql_binlog/mysql-bin.018299

Solution: Fixing the Binlog Issue

After removing the blank space from the binlog.index file and flushing the logs in MySQL, we reissued the SHOW BINARY LOGS command. As a result, the binlogs became visible, effectively restoring MySQL's ability to recognize them. Consequently, we successfully purged the binlogs, reclaiming the much-needed space.

After removing the blank space in the mysql-bin.index file:

root@ACI-PROD-VM-MYSQL06:~# head /log/mysql_binlog/mysql-bin.index 
/log/mysql_binlog/mysql-bin.018290
/log/mysql_binlog/mysql-bin.018291
/log/mysql_binlog/mysql-bin.018292
/log/mysql_binlog/mysql-bin.018293
/log/mysql_binlog/mysql-bin.018294
/log/mysql_binlog/mysql-bin.018295
/log/mysql_binlog/mysql-bin.018296
/log/mysql_binlog/mysql-bin.018297
/log/mysql_binlog/mysql-bin.018298
/log/mysql_binlog/mysql-bin.018299

Listing the Binlogs at the MySQL Level After Flushing Logs:

mysql> show binary logs;
Empty set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected, 242 warnings (2.48 sec)

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.019016 |  262392057 |
| mysql-bin.019017 |  263078587 |
| mysql-bin.019018 |  262164237 |
| mysql-bin.019019 |  269201151 |
| mysql-bin.019020 |  262422263 |
| mysql-bin.019021 |  262299837 |
| mysql-bin.019022 |  262208373 |
| mysql-bin.019023 |  262238178 |
| mysql-bin.019024 |  262221744 |

Key Takeaways:

• Error Messages Matter: Pay attention to error messages like "ERROR 1373 (HY000): Target log not found in binlog index." They can guide you to the root cause of MySQL issues.
• Detailed Investigation Helps: A focused investigation, such as checking for blank spaces in files, can reveal hidden issues impacting MySQL's functionality.
• Small Changes, Big Impact: Simple fixes, like removing blank spaces, can restore MySQL functionality and resolve errors.
• Verify Changes: Always test changes made to configurations or files to ensure they fix the problem. For example, verify that MySQL can list binlogs after making adjustments.

Our successful troubleshooting of the MySQL binlog issue contributed to the disk space reclamation efforts. This experience highlighted the critical importance of precise troubleshooting practices, demonstrating how a blank space in the binlog.index file can cause significant confusion during routine binlog purge operations. This incident serves as a reminder that even simple changes can have misleading impacts.

Are you facing similar MySQL issues? Let Mydbops' expert team handle your database management and troubleshooting needs. Our MySQL Managed and Consulting Services ensure smooth operations and optimized performance for your databases. Contact us today to learn how we can help!