Archive for the ‘MySQL Backup and Recovery’ Category

Protecting CFD (and making more money as a MySQL DBA)

Monday, April 14th, 2008

No, this is not a blog about Computational Fluid Dynamics – my least favorite subject in college. This is about a more exciting (sorry mechanical engineers!) CFD: Customer Facing Data. This is the data that is typically available on the website of an organization that their customers interact with. CFD can range all the way from profiles of users on a social networking site such as Facebook to the customer information database of an e-commerce company such as Travelocity.

CFD represents today’s data protection challenge. Probably the biggest challenge while planning a backup solution for CFD is that it is very hard to figure out what to plan for. You might be starting with a very small database which might grow much more rapidly than what you think. If the data can be segmented based on users or some other characteristic, then you will find that your databases may scale-out instead of scale-up. Also, rate of change can be very rapid, sometimes with lots of small changes (e.g. tags) or sometimes with big data changes (e.g. addition of user generated media content), very similar to the rather unpredictable viral growth pattern of the Internet.

Many organizations are keen to save and analyze behavior of users as they interact with CFD. This metadata can itself pose a data protection challenge of its own, since it may change very rapidly, while the actual data is not changing (e.g. tracking of customer intelligence metadata about which prospects downloaded which whitepapers from your corporate website, so that you can determine the best marketing & sales approach for these prospects).

MySQL is the DBMS of choice for CFD. Some skeptical press and analysts have said a few times that MySQL is not being deployed at the back-end of the enterprise (financials, billing etc.), but only on the web-tier. The implicit (sometimes explicit) implication being that web-tier applications are less important for the organization. I think this analysis fails to realize the actual importance of the data being stored in web-tier applications. Most of this is CFD, loss of which will cause huge pain and costs in the form of lost revenues, customers and reputation.

From its very inception, Zmanda has been focused on technologies powering CFD (MySQL, LAMP stack, and now increasingly Solaris). We have dug deeper than anyone else in understanding the needs of protecting this crucial part of any organization and very rapidly delivered products to address these needs. We are the data protection company for CFD. While we do protect back-end applications and platforms, our technology and business focus remains CFD.

I will be talking about deploying radically simple backup solutions for CFD on Thursday (April 17th, 10AM) at the MySQL user conference. I am in the unenviable position of making a Backup presentation sound interesting after the Facebook keynote (“A Match Made in Heaven? The Social Graph and the Database”). Well, I guess the point that conference organizers are trying to make is that if you have a radically simple MySQL backup and recovery solution, you will have more time to spend on Facebook!

Speaking of analysts, I recently read an analyst report which indicates that, on average, salaries offered to MySQL DBAs can be up to 40% lower than those offered to Oracle or DB2 DBAs. This ironically is considered one of the barriers for entry of MySQL in some environments. Per this report: higher paid database personnel have vested interest to keep MySQL out. Another claim is that somehow the lower salary to MySQL DBAs reflects the relative importance of the MySQL powered application for the business. Well if you are a MySQL DBA, you will do well to make your management realize that you are the keeper of their Customer Facing Data. Any business deploys its best resources for customer facing activities. Hopefully this will start reflecting on your paycheck soon.

If you would like to talk about your data protection challenges, or your salary as a MySQL DBA, stop by our booth #307 at the MySQL user conference.

Highly Scalable MySQL Backups using Snapshots (ZFS or NetApp)

Friday, April 11th, 2008

We have been focusing on providing the best possible backup solution for following scenario: 100 GB+ of data stored in MySQL database, Transaction intensive workload (i.e. rapid rate of change of data), with a business requirement to be able to perform point-in-time restoration of the MySQL database. Oh, the solution also needs to take into account that the database can grow to 1TB or more very quickly.

For such a scenario, we believe that the best possible solution today is a combination of:

  1. Storage level snapshots – a capability built into ZFS (Solaris), NetApp, LVM (Linux), VxFS, and VSS (Windows)
  2. Transaction logs generated by MySQL
  3. Point-and-click restore capability provided by Zmanda Recovery Manager for MySQL

Two reports came out today which go into nitty-gritty of above. First is a joint report written by NetApp and Zmanda engineers, titled “MySQL Backup and Restore Using Zmanda Recovery Manager and NetApp Snapshot Technology“. This report describes how NetApp Snapshot and Zmanda Recovery Manager can be used to back up and restore a MySQL database for NetApp storage systems. Specifically, this report covers the following topics:

  • Infrastructure required to deploy Zmanda Recovery Manager for MySQL with a NetApp storage system
  • Backing up a MySQL database using Zmanda Recovery Manager using NetApp Snapshot plug-in
  • Restoring a MySQL database using Zmanda Recovery Manager

Second one is a how-to blog written by Paddy on O’Reilly Databases: MySQL backups using ZFS snapshot. A key observation is sub-second time spent holding the read lock on the database while the snapshot was being taken.

While performing point-in-time recovery of their MySQL databases, DBAs don’t have to search for specific snapshots and manually combine them with database transaction logs. Zmanda Recovery Manager takes care of that behind the covers. DBAs simply key-in (or point-and-click) the timestamp to which they want to recover to.

MySQL Backup and Recovery Training from Zmanda

Monday, March 31st, 2008

Worried about Backup and Recovery of your MySQL Databases? MySQL Backup school from Zmanda provides hands on and in depth training on Backup and Recovery of MySQL. Just sign up and show up with your laptop. More information available here.

Sun/MySQL to resell Zmanda Recovery Manager for MySQL

Tuesday, March 18th, 2008

Today Sun and Zmanda announced our agreement to deliver a comprehensive, global data backup and recovery solution for MySQL Enterprise subscribers. Starting April 1st, MySQL Enterprise customers will be able to purchase ZRM for MySQL directly from Sun worldwide.

I think Zack’s comment in the press release captures the rationale for the deal:

“Protecting corporate data through effective backup and recovery is one of the most crucial tasks for a database administrator, and it can be a complex undertaking — especially for today’s large Web-scale applications,” said Zack Urlocker, VP of products, Sun Microsystems database group. “MySQL users have told us that global backup and recovery is very important to them, and we are thrilled that we can now offer ZRM for MySQL as an easy-to-use solution for protecting all of their MySQL data.”

Of course, at Zmanda we are thrilled as well. Sun’s sales channels will give us the opportunity to make MySQL Backups radically simple for ever increasing MySQL installations around the world.

Here is the full press release (

Webinar: New Levels of Backup and Recovery for MySQL

Wednesday, March 12th, 2008

Zmanda has introduced a new version of Zmanda Recovery Manager (ZRM) which adds quite a few capabilities for MySQL users. Version 2.1 includes enhanced snapshot support that enables backup without application downtime or interruption to online data access. Version 2.1 also has global management of backups so that you can manage all your MySQL backup jobs from a single graphical console. You can now run the entire ZRM solution on Solaris as well as on Linux.

Join us for an overview and a live demo of ZRM 2.1 on Thursday 3/20 at 10am PST. Click here to register.

Zmanda Recovery Manager 2.1 – Keep those MySQL Databases Zipping

Thursday, February 28th, 2008

Today we announced a significant enhancement to our Zmanda Recovery Manager (ZRM) for MySQL product. Here are major highlights of this new version (2.1):

End-to-end support for Solaris: We have had increasing number of requests from customers for supporting Solaris. While we always protected MySQL databases running on Solaris, we needed customers to run the ZRM core engine on a Linux box. Now we are able to run the ZRM core engine on Solaris itself. So a pure Solaris shop can use ZRM without getting a Linux server. The coinciding of this support with MySQL’s acquisition by Sun was not a planned thing 🙂

mysql snapshotEnhanced Snapshot support: One of the coolest features of ZRM is to be able to take advantage of underlying storage infrastructure whenever possible. Since version 1.0 of ZRM we have provided support for backing up MySQL using LVM on Linux. Now we are adding support for four major snapshot options: Windows Volume Shadow Services (VSS) snapshot, Network Appliance SnapManager, Veritas VxFS and Solaris ZFS. If your storage infrastructure supports any of these snapshot options, this will be the fastest way to backup your MySQL database – with zero impact on the application using the database. In fact, on Windows platform, ZRM 2.1 enables for the first time the capability of taking a fast raw backup of MySQL (using VSS). BTW, a crucial feature here is how ZRM uses snapshots for Restore. When a DBA requests a point-in-time restore, ZRM is intelligent enough to collate snapshot-based full backups with log-based incremental backups, to deliver the MySQL database in the precise state at requested point-in-time.

Global Management of MySQL databases: One key behavior we observe about MySQL databases is that they tend to propagate rapidly within organizations. Here at Zmanda, I can easily count close to 10 production MySQL databases, both inside and outside our firewall. Maintaining backups of multiple MySQL databases at a single repository tremendously reduces the complexity for DBAs. While we always had some form of remote backup capability, with ZRM 2.1 we now support centralized backup across all storage engines and across all operating systems. Our web based Zmanda Management Console allows this centralized management from anywhere, including from an iPhone!

Our goal is to make backup of live MySQL databases radically simple – enabling you to focus on your business applications.

Webcast – Enterprise Backup to Amazon S3

Tuesday, February 12th, 2008

We are hosting a webcast this Wednesday to talk about backing up corporate data to Amazon’s Simple Storage Service (S3) using Amanda Enterprise. We will demonstrate live, how enterprise users can now use Amanda Enterprise to harness Amazon S3 for a complete data backup, archiving and disaster recovery solution. The webcast will take place on Wednesday February 27 at 10:00AM PST and will last about an hour, including a Q&A session at the end. We hope you can join us to discover a new & cost effective way to leverage online storage services such as Amazon S3 as a part of your corporate data protection strategy. Click here to register.

Congratulations to Sun and MySQL

Wednesday, January 16th, 2008

Congratulations to Marten and team on Sun’s acquisition of MySQL today! This is a billion dollar stamp of approval on the importance of the LAMP stack. This also gives another strong backing to the business model which involves giving one’s software away for free to up to 99+% of users!

This announcement comes at an interesting time for Zmanda. Just last week we announced support for the Solaris platform for our Amanda Enterprise product line. With our industry leading MySQL backup solution, we now have full coverage of operating systems and database software from the new Sun!

A great day for open source software and business of open source software!

Webinar Q & A: MySQL Backup Techniques for the Oracle DBA

Monday, December 17th, 2007

We presented a webinar along with MySQL last week. The topic was MySQL Backup from the perspective of an Oracle DBA. We got some really good questions at the end of the webinar. Here is a transcript of Q&A:

Q from Trey: Will this Webinar be available online for viewing later?

A from MySQL: Yes. It will be on our site in a few days at

Q from Randy: Is a copy of presentation available on web? Where?

A from Zmanda : slides are available on Zmanda Network:

Q from Jing: Converting database from oracle to MySQL, any limitation regards the version compatibility?

A from MySQL: We provide a free GUI migration tool that helps migrate Oracle data objects to MySQL. For a complete and very detailed discussion on the subject, download our white paper on the subject at

Q from Julien: Is MySQL supported on NetApp?

A from MySQL: Yes

Q from Julien: For mysql on NetApp, which protocols are supported? nfs? iscsi? cifs?

A from MySQL: I believe the key protocols are supported.

Q from Haris: How does ZRM integrate with NetApp

A from Zmanda: ZRM takes advantage of snapshots provided by Data ONTAP OS in NetApp filers.

Q from Jim: Will the “new non-blocking backup utility” for MySQL for hot backup be free? or an extra cost option? Thank you.

A from MySQL: At this time, the core backup utility is planned to be GPL and in the community server

Q from Neil: How much does ZRM cost?

A from Zmanda: Annual subscription starts from $200 per MySQL server. We provide subscriptions with three levels of support — Basic (email only), Standard (business hrs phone and email) and Premium (7 X 24). Pricing is available at

Q from Franck: so the licensing is per MySQL Server

A from Zmanda: Yes, subscription is per MySQL server and you can backup as many databases within that server as you have.

Q from Isaac: Is the community edition of Zmanda fully functional or somehow restricted?

A from Zmanda: The Community Edition is fully functional. However, only the Enterprise version provides web-based Management Console that simplifies all operations. It also provides log analyzer that simplifies point-in-time recovery. To see additional details, please check

Q from Pavel: Does Zmanda GUI manager comes for free???

A from Zmanda: No. Only the Enterprise (which requires annual subscription) version provides a web based console.

Q from Tim: Could you provide onsite support if we need that?

A from Zmanda: Yes. We can provide onsite support and training if needed.

Q from Haijun: Does ZRM include InnoDB online backup?

A from Zmanda: Not in the current release. It is planned for a future release

Q from Alicia: Any idea how long before the release of a ZRM version that supports hot backup of InnoDB databases?

A from Zmanda: 3-6 months (likely next release)

Q from Mike: mysqldump cmdline for non-blocking with innodb?

A from Zmanda: mysqldump –single-transaction is non-blocking for transaction storage engines such as InnoDB

Q from Kevin: Now that Oracle owns the rights for InnoDB, what is it’s future. Will Oracle charge for InnoDB? Is there a replacement for Innodb from MySQL

A from MySQL: For MySQL, it’s business as usual so no changes from a support standpoint. We do have other transactional engines available – NDB (cluster) and our new Falcon transaction engine (about to enter beta). Also we have SolidDB and PBXT, – external engines

Q from Srinivas: what is the dependency of InnoDb during backup process?

A from Zmanda: There is no dependency on storage engines, but we can take advantage on functionality provided with some engines, e.g. we can use mysqlhotcopy as a data extraction utility for MyISAM. Another example — using snapshots with InnoDB can provide truly hot backup of MySQL.

Q from Madhavi: Having a combination of MyISAM and innodb, do we need separate jobs for backups?

A from Zmanda: No, you don’t. ZRM is designed to handle variety of MySQL configurations and the same backup job can handle different engines.

Q from Alicia: Which backup modes of ZRM support Innodb backups?

A from Zmanda: All backup methods are supported with Innodb storage engine (logical, raw, snapshot, replication)

Q from Rich: For datacenters with centralized backup to tape, will ZRM work in this environment? If so, am I able to have each customer have their own tape and can it be incremental and/or full?

A from Zmanda: Absolutely. ZRM can be easily integrated with any enterprise media manager such as Amanda, Netbackup by Veritas, NetWorker by Legato and TSM by IBM. Think of ZRM as “RMAN” for MySQL.

Q from Kevin: With Oracle we use RMAN and Netbackup using scripts for level 0,1,2. Can ZRM do the same?

A from Zmanda: Yes you can. You can use ZRM in conjunction with Amanda Enterprise (network backup software similar to NetBackup). If you are using NetBackup pre-backup and post-backup scripts to integrate with Oracle/RMAN, you can write similar scripts with ZRM.

Q from Charlie: If my 20 backups are successful and usually they are, I don’t want 20 emails. I only want an email if it “fails”.

A from Zmanda: You may have to tailor the email being sent. This can be done with some scripting. We plan to improve this functionality in the next release of Zmanda Recovery Manager for MySQL

Q from Jos: When in a replicated environment, can you restore all replicated databases at the same time from one backup?

A from Zmanda: Yes. You should configure all databases in the same backup set.

Q from Srinivas: Is selective recovery feature allows deselecting the corrupted records ?

A from Zmanda: If you have incremental backups covering the time frame when the records were not corrupted — then yes

Q from Isaac: How is recovery handled with the community edition of ZRM?

A from Zmanda: Recovery in community and enterprise editions is the same. Recovery depends on the backup method used. The DBA does not need to remember the backup method used. Enterprise edition provides easy to use recovery manager in Zmanda Management Console.

Q from Bob: Can Zmanda use multiple streams in parallel to backup a database?

A from Zmanda: You can configure ZRM to backup in multiple streams by configuring tables in the database in different backup sets.

Q from Raju: Can I resume my backup after a failure instead of starting over?

A from Zmanda: If there is backup failure, ZRM uses a different backup method. ZRM uses a hierarchy of backup methods depending on configuration and storage engine in use. Currently we don’t have a built-in checkpoint mechanism to resume a particular failed backup run.

Q from Isaac: Can ZRM be used to take backups from a slave, or is there some writing that is performed during the backup?

A from Zmanda: You can take backup from slaves in a replication setup.

Q from Rich: How does the recovery time compare between ZRM and RMAN for similar sized (say 800GB) DBs?

A from Zmanda: ZRM supports multiple backup methods and multiple storage engines. If you are using InnoDB storage engine (size 800GB) and snapshot backup method, the recovery time will be comparable.

Q from Eric: Does ZRM perform any compression? or encryption options?

A from Zmanda: Yes. ZRM uses plug-in architecture. You can create plug-ins for compression and encryption. The default configuration has lz and bzip2 compression similar to Oracle/RMAN.

Q from Steve: Can ZRM restore a single mysql database if you have it configured to “backup all databases” on a server?

A from Zmanda: Yes.

Q from Adam: So, when I enable the Zmanda backup, should I disable the collection of the binary logs, or can Zmanda use the logs collected by MySQL?

A from Zmanda: Zmanda can use logs collected by MySQL. Binary logs are used for incremental backups.

Q from Rich: what gets installed on the DB – an agent? If so, how lightweight is it?

A from Zmanda: On MySQL server, you need to install small footprint software that does data transfer to the ZRM server. This software runs only when needed (during backup run).

Q from Nitish: how to you integrate third part snapshots methods within zmanda?

A from Zmanda: There is a snapshot plugin interface which is documented in ZRM for MySQL wiki

Q from Mike: Can you speak more about the feature that will delay a backup during times of heavy usage?

A from Zmanda: This is done using scheduling plugin. Please see ZRM for MySQL wiki

Q from Sarah: are zrm and zmc integrated or can they be? or are they completely free standing applications such as rman and netbackup.

A from Zmanda: ZRM can be used without ZMC, but then you loose ease of use and extensive monitoring and reporting available only via ZMC.

Q from Srinivas: does ZRM supports Community Version of MySQL?

A from Zmanda: ZRM enterprise version supports both the community and enterprise versions of MySQL.

Q from Rich: Will ZRM Community work with MySQL Enterprise or is that a licensing restriction?

A from Zmanda: Yes, it will, but we highly recommend to use the Enterprise version of ZRM for Enterprise version of MySQL.

Q from Eric: when is support for Falcon storage engine expected from ZRM

A from Zmanda: Zmanda works very closely with MySQL engineering. We will support Falcon when released and we are already working with alpha version of Falcon.

Q from Greg: What is ZRM’s backward compatibility to previous versions of MySQL? I notice your examples all show MySQL v5.

A from Zmanda: We support all versions from MySQL 3.X. There are restrictions on the backup methods supported in older releases. Please contact zsales (at) if you are interested in compatibility with a particular MySQL release.

Q from Rick: Is ZRM available on Suse?

A from Zmanda:: Yes. It works on SLES as well as Open SuSE

Q from Franck: Do you also support Mac OS X?

A from Zmanda: Yes, remote backup of MySQL on Mac OS X is supported.

Q from Neil: No ZRM for Windows server? Is that correct?

A from Zmanda: ZRM can be used to protect MySQL database on ANY operating system. Some OS are supported in remote configurations only. Windows is supported remotely and we are working on a new version that runs either locally remotely on Windows and uses VSS snapshots for backup of MySQL.

Q from Franck: I’m confused. Is Zmanda a backup solution for Mysql only? Of can it be used to BackUp Oracle too?

A from Zmanda: Zmanda Recovery Manager (ZRM) has been designed specifically for backup and recovery of MySQL. It is closely integrated with MySQL. Think of ZRM as “RMAN” for MySQL.

Q from Alexandre: Our organization has both Oracle and MySQL databases. Can ZRM provide some uniformity in managing both backups, e.g. backup MySQL to the same physical backend tape storage as Oracle?

A from Zmanda: You can backup both to same physical backend by using a third party media manager, such as Amanda Enterprise or NetBackup.

Q from Bob: From a ZRM standpoint is there any difference backing up a MySQL DB on Linux vs. Windows?

A from Zmanda: In current release, we do not support snapshots for MySQL on Windows. The next release will support VSS snapshot method for MySQL on Windows.

Q from Bob: What media managers does Zmanda integrate with?

A from Zmanda: Any media manager, for example Amanda Enterprise, NetBackup, Legato and others.

Q from Alexandre: Are there any competing products on the market to manage MySQL backups?

A from Zmanda: Our competition is adhoc scripts written by MySQL DBA and our own community edition 🙂

Q from Adam: I’m a little confused… What is the ZRM DB Event log exactly? Is it like the InnoDB binary logs? What is the format of it?

A from Zmanda: ZRM DB event log is same as MySQL binary log (that is used for MySQL replication). It contains SQL statements and results of SQL statements.

Q from Nitish: who are your major customers?

A from Zmanda: We have organizations of all sizes as our customers – from large online retailers and airline reservation systems to small manufacturers and research labs. We are where MySQL is run in production and where organizations value their MySQL data. Please send an email to zsales (at) to get a reference list of our ZRM customers.

Q from Neil: Where is Zmanda located?

A from Zmanda: We are in Sunnyvale, CA and Pune, India. Many employees work remotely in different countries.

Backup and Recovery Techniques for MySQL Database

Saturday, December 8th, 2007

If you are migrating your production environment to MySQL database or even thinking about it, you do not want to miss this webinar Experts from Zmanda and MySQL will present various methods to backup MySQL database and correlate them with tools and terminology you may be familiar with in other databases, including Oracle.

A good pre-read for this webinar would be Paddy’s blog on online MySQL Backup


Chander Kant, CEO, Zmanda

Robin Schumacher, Director Product Management, MySQL


MySQL Backup Techniques for the Oracle DBA (web presentation)


Thursday, December 13, 2007, 10:00 am PDT, 1:00 pm EDT, 18:00 GMT
The presentation will be approximately 45 minutes long followed by Q&A.