Yesterday our group had a discussion about data backup and recovery. Specifically we were addressing the question: if our development machine failed, what data would be absolutely necessary to get back up and running. For the most part making this list was easy and obvious, but one thing we realized we had overlooked in the past was the test database.
There are a number of ways to properly backup a MySQL database, and a plain-ol’ incremental backup of the mysql data directory is not one of them. However, if all you have are the binary files located in this directory, they can (probably) be used to restore the database.
This is the situation we found ourselves in recently when it was discovered that some important data had been living in a database that was intended to be used exclusively for not-so-important data, and that database had recently been deleted. We had no ‘proper’ backup, but we did have a backup of the filesystem itself, which provided us with the binary files for the database of interest.
Restoring from these files is actually pretty straightforward, but there are several details that can get in the way. Our restoration effort went more or less like this: