Oops, I deleted my MySQL binary logs

Yesterday, I was testing various backup and recovery methods supported by ZRM for MySQL before 1.1.1 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. Accidently, I deleted the last binary log used by MySQL server. MySQL server uses the most recent binary log.

I could not start MySQL server again.

# 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 Sheeri Kritzer 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 recovery to any point in time before the binary log deletion.

Bottomline: Do not delete the most recent binary log file to save disk space and do regular backups.

2 Responses to “Oops, I deleted my MySQL binary logs”

  1. Sheeri says:

    Or rather, you *can* delete your binary logs to save disk space, but you need to use the “PURGE MASTER LOGS” command — do not delete them via the operating system commands (del, rm, etc). We have a scheduled job running that will purge the logs regularly so we do not have to worry about space filling up, yet always have the incremental logs from the last backup on.

  2. Jonathan Tullett says:

    If you use the MySQL server itself to delete the binary logs, ala:

    PURGE MASTER LOGS TO ‘last-log-file-name.XXX’

    then you won’t have that problem again.

    Doing the above when slaves are connected will also mean there’s no chance of logs they haven’t yet received being deleted either.