Oops, I Deleted My MySQL Binary Logs

Oops, I Deleted my MySQL Binary Logs

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

Explore More Topics