I had a brief scare today. I accidentally deleted the database for an equipment website I manage in our office (bumblebeedb). The database is running on a Mac OS X Server (10.5.8) machine that is curiously no longer completing Time Machine backups (argh). My last backup for the deleted database occurred back in 2008! Luckily, Apple has enabled mysqlbinlog on OS X Server, so all the changes that were made to “bumblebeedb”, since the last backup in 2008, were found in the following directory:

/private/var/mysql/

In this directory, there should be several file names like “mysql-bin.000040” (sequentially increasing) that contain changes you’ve made to your MySQL databases over time.

To get all the data for my equipment website back up, I fired up “Sequel Pro.app”, imported my backup from 2008 (a .sql file), fired up Terminal, and entered:

mac022374:bschwie$ cd /private/var/mysql
mac022374:mysql bschwie$ mysqlbinlog mysql-bin.000050 mysql-bin.000051 mysql-bin.000052 mysql-bin.000053 mysql-bin.000054 mysql-bin.000055 mysql-bin.000056 mysql-bin.000057 mysql-bin.000058 mysql-bin.000059 mysql-bin.000060 mysql-bin.000061 mysql-bin.000062 mysql-bin.000063 mysql-bin.000064 mysql-bin.000065 mysql-bin.000066 mysql-bin.000067 mysql-bin.000068 mysql-bin.000069 mysql-bin.000070 mysql-bin.000071 mysql-bin.000072 mysql-bin.000073 mysql-bin.000074 mysql-bin.000075 mysql-bin.000076 mysql-bin.000077 mysql-bin.000078 mysql-bin.000079 mysql-bin.000080 mysql-bin.000081 mysql-bin.000082 mysql-bin.000083 mysql-bin.000084 mysql-bin.000085 mysql-bin.000086 mysql-bin.000087 mysql-bin.000088 mysql-bin.000089 mysql-bin.000090 mysql-bin.000091 mysql-bin.000092 mysql-bin.000093 mysql-bin.000094 mysql-bin.000095 mysql-bin.000096 mysql-bin.000097 mysql-bin.000098 mysql-bin.000099 mysql-bin.000100 mysql-bin.000101 mysql-bin.000102 mysql-bin.000103 mysql-bin.000104 mysql-bin.000105 mysql-bin.000106 mysql-bin.000107 mysql-bin.000108 mysql-bin.000109 mysql-bin.000110 mysql-bin.000111 mysql-bin.000112 mysql-bin.000113 mysql-bin.000114 mysql-bin.000115 mysql-bin.000116 mysql-bin.000117 mysql-bin.000118 mysql-bin.000119 mysql-bin.000120 mysql-bin.000121 mysql-bin.000122 mysql-bin.000123 mysql-bin.000124 mysql-bin.000125 | mysql -u root -p

where, the file names ending in .000050 through .000125 contained all the changes I needed to update my 2008 backup to the present (mid-2010).

SUCCESS!

I need to automate a script to back up the mysql database in the future. The rescue effort shouldn’t have come down to this… MySQL has published additional information that may be helpful for your own recovery attempt. Additionally, they have another reference for recovering from crashes.