I was doing some research over the weekend on how transactions work "under the hood" in MySQL. I thought it might be enlightening if I wrote about what I found.The database system must resolve the situation where, for whatever reason, the database server stops working in the middle of a transaction.  Perhaps the power goes out, the hard drive crashes, or the CPU disappears in a cloud of smoke. Maybe the MySQL Server itself crashes for some reason. What does the MySQL Server do when operation resumes? Potentially, there are transactions in an inconsistent state. How are inconsistent transactions resolved? They are resolved through a recovery process involving log files that are called transactional logs. There are two kinds of transactional logs: redo logs and undo logs.

A utility like mysqldump is very useful for backing up MySQL data and table schema. However, backups are usually made only once a day or at some other regular interval. If data is lost on your system several hours after the last backup is completed, you may not be able to recover it. There are a few methods by which you can continuously protect data, some of which are explored in this article.(2005-04-26)

MySQL replication is often positioned as a solution to provide extra throughput for your database (especially when dealing with high numbers of read operations). What tends to be overlooked is how it can be used to provide high availability (HA) – no matter how much redundancy you have at a local level, your system remains at risk from a single catastrophic failure – loss of power, earthquake, terrorist attack etc. By using MySQL asynchronous replication to maintain a geographically remote copy of that data, service can be maintained through any single site failure.

Do you use replication? Would you like to improve your data protection and recovery capabilities? If you do, chances are you would also like to improve your ability to recover from failures faster. In this session, we will show you how you can use backup to enhance your replication capabilities.

Do you like to tinker? Would you like to see how MySQL? If you’d like to get your hands dirty (or watch others work their developer magic), stop by this session and learn how to take a simple desktop system running MySQL into a fully functional time based backup system that you can connect to any server and get up to the minute recovery capability.

There should be nothing more important to a database administrator than backups, restoration and disaster recovery planning. Unfortunately it is an often neglected area of database administration. This session will cover the importance of these vital areas and practical information about the ways to perform backups and recovery with currently available technology.