Oops, I Deleted My MySQL Binary Logs
Last Updated on
There are times when we are exhausted because of a long day and we end up making mistakes, which end up costing the entire team a lot of time.
Yesterday, I was testing various backup and recovery methods supported by ZRM, a MySQL backup service for an upcoming release. ZRM for MySQL requires binary logging to be enabled on the MySQL server. I ran out of disk space during testing and removed the binary logs.
Accidentally, I deleted the last binary log used by MySQL server and MySQL server uses the most recent binary log to run its daemon.
Short version – I could not start MySQL server at all.
Recover MySQL binary logs: Stack trace when trying to start the server
$service mysqld start
061031 17:38:48 mysqld started
061031 17:38:48 InnoDB: Started; log sequence number 14 1645228884
/usr/libexec/mysqld: File ‘/var/lib/mysql/mysql-bin.000017’ not found (Errcode: 2)
061031 17:38:48 [ERROR] Failed to open log (file ‘/var/lib/mysql/mysql-bin.000017’, errno 2)
061031 17:38:48 [ERROR] Could not open log file
061031 17:38:48 [ERROR] Can’t init tc log
061031 17:38:48 [ERROR] Aborting
061031 17:38:48 InnoDB: Starting shutdown…
061031 17:38:51 InnoDB: Shutdown completed; log sequence number 14 1645228884
061031 17:38:51 [Note] /usr/libexec/mysqld: Shutdown complete
061031 17:38:51 mysqld ended
Thanks to Aishwarya for giving me ideas on how to resolve the problem. The binary log index file ( /var/lib/mysql/mysql-bin.index) stores information about most recent binary log file. Deleting the index file solves the problem. Of course, I had backups of the database using ZRM for MySQL and I could recover to any point in time before the binary log deletion.
Bottom line: Do not delete the most recent binary log file to save disk space and do regular backups. ZRM is preferred and here’s a link for more information
This post was written by Renju Thampy