Data Recovery Policy

[Jamie Howard]

Hi everyone!

Just a quick question - we operate a lot of mysql databases and to date have relied on simple periodic mysqldumps for disaster recovery.

This has been ok, however some of our more busy databases have on occasion needed to be restored due to the following disasters:

1) Total hard drive failure
2) Programmer error causes data destruction - yes it happens occasionally :(

Our backups run daily, however even a days worth of queries for some databases are becoming unacceptable to be lost.

We *think* we need to move towards transaction logging to get on a better footing and be able to completely restore databases but wondered what in your experience was a good policy and tool set to achieve this?

Do you do a combination of logging and database dumps and refresh the logs after each dump? Also are their any good tools to automate this process? I know mysql can be configured to transaction log and that this effects the table types we can use - but how do you tie this in to log rotation at the point of database dump?

Would appreciate any gems you may have to offer! Thanks