MySQL database provides a much more efficient mechanism for CDP by leveraging its binary log, which tracks every change being made to the database.

MySQL databases increasingly power production applications, whether web based collaboration tools or CRM applications. It is critical for the DBAs managing these production MySQL databases to have a robust data protection solution. Their top priority is hot backup and recovery of their database to a point-in-time (or transaction) of their choice.

The recovery to any point-in-time has always been the ultimate goal for data protection. Traditional backup solutions allow recovering only to the point in time when the last backup took place, e.g. last night. A lot of new and important data may have been created since last backup, and is lost in this recovery. That is why in the last few years many vendors have been working on Continuous Data Protection (CDP). Storage Networking Industry Association (SNIA) defines CDP as “a class of mechanisms that continuously capture or track data modifications enabling recovery to previous points in time”.

Traditional CDP implementations have relied on capturing every write and making a copy of the changed blocks. These methods tend to be very expensive in terms of system resources being used and may have a significantly negative impact on the performance of the application being protected. MySQL database provides a much more efficient mechanism for CDP by leveraging its binary log, which tracks every change being made to the database.

The MySQL binary log contains all statements that update data. These statements are stored in the form of “events” that describe the modifications. The primary purpose of the binary log is to be able to update databases during a restore operation as fully as possible, because the binary log contains all updates done after a backup was made. Running the MySQL server with the binary log enabled makes performance about 1 percent slower. However, the benefits of the binary log for restore operations (including enabling point-in-time restore) far outweigh this minor performance decrement.

Zmanda provides open source backup and recovery solutions for leading open source infrastructure, e.g. Linux, OpenSolaris and MySQL. Zmanda's product line includes the leading MySQL backup solution: Zmanda Recovery Manager (ZRM) for MySQL. ZRM for MySQL is an open source, enterprise-class backup and recovery software designed to help MySQL DBAs prevent the serious damage caused by data loss in the event of human error and system or database failure. ZRM is designed specifically for MySQL databases and includes unique, enterprise-grade features such as MySQL storage engine and snapshot intelligence, replication support, and integration of backup operations with MySQL binary logs. The Enterprise Edition of ZRM comes with the Zmanda Management Console — a powerful Web-based graphical user interface that lets DBAs control backup and recovery across all MySQL servers from any browser enabled device.

ZRM implements CDP for MySQL by using industry standard snapshot technologies (LVM, VxFS, VSS, ZFS or NetApp) in conjunction with MySQL transaction logs. When doing a recovery to a particular point in time, ZRM reads data from the snapshot and then replays MySQL transaction log from that point forward. This enables almost instantaneous point-in-time recovery. ZRM's built-in visual log analyzer helps DBAs to quickly identify the point in time for recovery, by visually scanning through historical update sequence of their database.

There are several advantages to this methodology to implement CDP. Since ZRM uses binary logs as the mechanism to track individual changes, it does not need to make an additional copy for every write - making the process extremely efficient as compared to traditional CDP implementations. Secondly, since ZRM uses industry standard snapshot mechanisms, DBAs can always recover their data (even if they don't have ZRM running in their environment - e.g. after a disaster). CDP software that uses proprietary snapshot mechanisms poses two serious risks: Data is locked into a proprietary format which is only readable by that software and since the method is not industry standard it is typically not as battle hardened as an industry standard snapshot mechanism.

Zmanda's MySQL backup software combines the power of industry standard snapshots and MySQL binary logs to provide an extremely efficient MySQL CDP solution. This enables MySQL DBAs to take on mission-critical online transaction processing challenges with confidence.

About the Author

Chander is the CEO of Zmanda, he provides a unique combination of leadership in open source and data protection software. He has been involved on both the technology and a business side of open source software and was named one of the “Top 20 Linux Luminaries” by Linux World Magazine in 2004. Prior to Zmanda, Chander founded and ran Linux Certified, Inc., an open source product and services company. Earlier in his career, Chander was a business development executive at VERITAS software and served as a product line manager for storage software at SGI. Chander holds a Master’s degree in Computer Science from the University of Wisconsin-Madison and a BS in Computer Science from the Indian Institute of Technology, Kanpur.