Archive for November, 2006

Retain your MySQL Binary Logs until needed - but no longer (using Zmanda Recovery Manager)

Wednesday, November 29th, 2006

ZRM for MySQL requires binary logging to be enabled on the MySQL server to do incremental backups. Enabling binary logs has minimal impact on MySQL performance. But, in an active database, the binary logs can grow to hundreds of gigabytes or even terabytes.

ZRM of MySQL has multiple plugin interfaces to customize the backup and recovery process to user environment. One of the plugin interfaces is the post backup plugin. The binary logs are no longer required after a backup. The post backup plugin can purge the binary logs after the backup. This will allow the system administrators to balance between the need for incremental backups as well as the storage needs for the logs.

The following command that purges the old binary logs can be added to the default post backup plugin for ZRM for MySQL.

mysql -uroot -ppasswd -e “purge master logs before date_sub(now(), interval 1 day);”

Other option is to set “expire_log_days” MySQL server parameter. The default values for expire_log_days is not to purge logs.

Caution: Users must be careful in purging binary logs on the master replication server if backups are being done on the master server. If slaves are lagging behind the server more than a day, the binary log purge will cause replication to fail.

To err is Human…..

Sunday, November 26th, 2006

DBA’s working in environments where MySQL database is for production critical applications are constantly pushed to their limits. Database Users and DBA’s are humans too. So, a dropped table here or a misconstrued MySQL statement there is not out of ordinary. Recovering from these kinds of user errors is extremely vital to production databases. Zmanda Recovery Manager (ZRM) for MySQL, is a good way to recover from user errors. Consider this example - You [DBA] find out that an user has made an unforced error which is affecting your production database. You further find out that this has happened in the last half hour. Lucky for you, you use ZRM for MySQL in your environment to make nightly backups. You spring into action! You first verify that a nightly backup was run. You then make an incremental backup now. Using ZRM for MySQL, you identify the point at which the user initiated error occurred. You then proceed to recover from your previous full backup and then from your incremental backup, till before the user error happened. The database is restored, the application is happy and the user most likely thanked you at the Turkey table:-)

A “How To” on selective point in time recovery can be found here

What is the optimal backup method for MySQL databases?

Wednesday, November 22nd, 2006

Goal of a MySQL system administrator is to have regular, consistent backups of the
databases with minimal impact on the database application. The backup process should
also limit the use of CPU, memory, network resources so that application is not impacted.
Optimal backup method must be choosen to keep the backup window as small as possible.
The backup parameters depends on various factors - number and size of databases, number
of MySQL servers, MySQL configuration, data security.

ZRM for MySQL provides consolidated backup and recovery for MySQL databases. ZRM
for MySQL provides extensive list of options to tune the backup process for the user
environment. It also tracks lots of backup parameters that are available to the administrator
to tune the backup process.

ZRM for MySQL wiki shows how to use the backup reports and adjust the backup
parameters to create optimal backup method. In the example - local, full backup of 6GB
InnoDB database, backing up raw database using LVM snapshot works the best. The backup
time, restoration time, time to verify backup and backup size were considered as the criteria
to determine the optimal backup method. Using LVM snapshots to backup MySQL databases
using InnoDB storage engine is a hot backup (does not require locks). The raw backup
method requires less restoration time compared to logical backup of the database.

It is important to measure the parameters that are important to the administrator in your
specific MySQL configuration. The example shows how to do it with ZRM for MySQL and
the optimal backup method for your configuration may vary.

ZRM for MySQL 1.1.2 released, Debian packages available

Tuesday, November 21st, 2006

Zmanda recovery manager for MySQL 1.1.2 is available from Zmanda downloads page. This release has been tested on Debian distribution and on MySQL server 4.0.24.

I want to acknowledge Thorsten Schifferdecker’s contribution to this release. He helped us by creating debian packages for earlier releases and provided good feedback on the product.

Lots of bugs have been fixed and this release has underwent more testing with different MySQL configurations.

Take a look at ZRM for MySQL wiki for changes, documentation, try it in your MySQL configuration and feel free to provide feedback on forums.

Amanda/Zmanda presentation at New York FactFest

Wednesday, November 15th, 2006

On Friday (November 17) at 5:30 PM I’ll be giving a talk at the Farmingdale Computer Technology Festival on Long Island. The main focus of my talk is going to be the way that Zmanda’s commercial support for Amanda has affected the open-source Amanda user and development communities. I’ll also talk a bit about Amanda generally and how Zmanda has affected the course of its development from a technical perspective.
Anyone in the New York area should definitely come. It looks to be a great evening; the folks at LILUG and Farmingdale University make excellent hosts.

Amazon S3 Backup Testing Results

Tuesday, November 14th, 2006

Digg this article

So, I’ve been playing around with Amazon’s new S3 service. It’s essentially an on-demand storage-and-bandwidth combination; Amazon will scale their service transparantly to provide as much as you need of either. It’s pretty cheap, too, at $0.15 per GB per month for storage and $0.20 per GB per month for data transfer. I wanted to see what would be the best way to use Amazon S3 for backups with Amanda and MySQL ZRM, so I did some tests to evaluate the performance under various circumstances.


ZRM for MySQL and MySQL Online Backup

Monday, November 13th, 2006

I had organized a session at MySQL camp on Saturday on the above topic. I should say I was one of the guilty ones who had slides for the session in an “unconference“.

Brian Aker (of MySQL) graciously stepped in to answer some questions and provided reasons for some of the design decisions in MySQL OnlineBackup. Some of them are:

  • Envelope format for backup archive. This would allow backup as well as recovery to continue in case the storage engines failed to do backup or recovery respectively.
  • Block size and envelope format will help in handling tape errors.
  • Logical backup will be done in case storage engine does not have online backup API implementation.
  • Status of Backup run (BACKUP SQL command) will be available as part of MySQL information schema.So, backup can be run as a truly background process.
  • Online Backup API will be implemented by all storage engines (except for NDB storage engine) in the first release.
  • MySQL OnlineBackup will depend on external management tools such as ZRM for MySQL for backup scheduling, reporting, monitoring, compression, encryption and other enterprise features.

My presentation slides are here. ZRM for MySQL will implement MySQL Online Backup as one of the backup methods. For people who could not attend the session, I will be hosting a webinar on Nov 16th 10am PT on the same topic. Please register for the webinar.

“We can do it, you can help”

Monday, November 13th, 2006

This is how Marten Mickos (MySQL CEO) described the relationship between MySQL and the open source community at MySQL Camp. MySQL Camp held at Google over last weekend, was an useful opportunity to listen and talk to developers in the broad MySQL ecosystem (including Oracle VP) as well as MySQL users.

The most interesting talk was about the efforts made by MySQL folks to attract and reward MySQL community contributors. They have made significant efforts in this direction - MySQL forge, Planet MySQL, MySQL winter of code, MySQL community server and availability of MySQL work logs (roadmap)

We, at Zmanda, also understand the need to have vibrant open source community behind our work and also subscribe to the motto “We can do it, you can help”. We have created wiki and forums so that users can discuss backup and recovery of MySQL databases as well as open source network based backups. We also sponsor open source developers working on backup and recovery.

The focus of this “unconference” was to have interactive sessions on various topics (several impromptu ones were added) that were of interest to MySQL users. I organized a session on MySQL Online Backup APIs and Zmanda recovery manager (ZRM) for MySQL which was well attended given that it was competing with a popular session on top 10 (I should say, 100) MySQL performance tips. It deserves another blog entry!

MySQL Online Backup

Wednesday, November 8th, 2006

Digg this article

There are several methods to do live MySQL database backups. These methods are either storage engine specific (InnoDB hot backup, mysqlhotcopy), or require read locks (mysqldump), or require additional hardware (backup using replication, LVM snapshot). Some of these backup methods can do backups of remote MySQL servers also. So far, there is no backup method that provides storage engine agnostic, consistent full backups of local and remote servers. Goal of Zmanda Recovery Manager (ZRM) of MySQL is to consolidate all these and future methods of MySQL backup and use the optimal method for the MySQL configuration.

One of the exciting developments in MySQL is the development of MySQL Online backup. The functional specification for the MySQL Online Backup APIs are available in MySQL forge and currently, the initial implementation of ARCHIVE storage engine backup and recovery is available in the bitkeeper source tree.

The first implementation of MySQL Online backup will provide consistent, full backups for transactional as well as non-transactional tables while maintaining referential integrity. Databases with tables using different storage engines can be backed up in a consistent manner. The backup operation will be non-blocking for DML statements and blocking for DDL statements. The backup archive format has been designed to support incremental and differential backups and granular backups in future.

The MySQL Online Backup implementation involves implementation of backup interfaces for storage engines, MySQL server internal interface to backup, MySQL SQL commands for management and interface to integrate with external management products . Actual backup is implemented by the storage engine. The version 1.0 will not allow restoration of selected databases/tables from the backup image. Version 1.0 will have BACKUP and RESTORE SQL commands and support for XBSA is likely to be added in later releases. The following table compares between current MySQL full backup methods and MySQL Online Backup: (Thanks to Sheeri Kritzer for the table comparing current MySQL backup methods)

Method No Locking DDL Snapshot Remote Free All Engines All Tables Text File Recover
# No
SELECT . . .
No No Yes Yes Yes No Yes Yes 3-4
mysqldump No Option No Yes Yes Yes Yes Yes Yes 2-3
Replication Yes No Yes Yes Yes Yes Yes No No 3
OS level copy No Yes No No Yes No Yes No Yes 5
mysqlhotcopy Yes Yes Yes No Yes No Yes No Yes 3
InnoDB Hot
Yes Yes Yes No No No Yes No Yes 4
MySQL Online
Yes Yes Yes Yes Yes Yes Yes No Yes 1

Note: Lower “#No” (last column in the table) is good.

Using MySQL Online Backup for full backups in conjunction with MySQL binary logs for incremental backups (and point in time recovery) will provide a total backup solution for MySQL databases. ZRM for MySQL will implement MySQL online backup API. In addition to implementing MySQL online backup API, ZRM for MySQL provides enterprise features - backup encryption, compression, verification, scheduling and reporting. Addition of MySQL online Backup as one of the backup methods provided by ZRM for MySQL will be a significant enhancement for the enterprise users.

I will be talking about MySQL Online Backup and ZRM for MySQL at MySQL Camp at Google, Mountain View on Saturday, Nov 11 at 11am PT. Since you have read this far, you must be interested in this topic. Please participate in the MySQL camp and join us in the MySQL backup session.

Having an expert to talk to when you run into problems…..

Wednesday, November 8th, 2006

Recently I purchased an awesome set of surround speakers and receiver. After doing my fair share of research and determining what exactly I wanted, I set out to buy my new toys!

Now I consider myself pretty well versed when it comes to home audio, but as always there are some things which I need to ask questions about.

I first went to one of those huge electronic retailers.
I found the speakers and the receiver I wanted but when I asked the salesman some specific questions about the receiver
he was unable to answer those questions for me.

So my next stop was a specialty home theater shop that sells only home audio/video products and accessories. The salesman at this shop was able to answer ALL of my questions.
He also volunteered his time after the sale to answer any questions I have or help me address any problems.

So as you guessed it. I ended up buying my home audio equipment from the specialty home theater shop.

I felt better about purchasing something knowing that if I had any problems I could easily pick up the phone and speak with anyone at the specialty home theater shop and they would be able to answer my questions.

I am very particular about my home audio equipment, I want to make sure that is configured correctly and is always putting out the best sound possible.

The same is true for backup software. Protecting your data is probably one of the most important things you can do for the continuous success of your business.

Don’t you want to make sure that when you encounter problems with your backup solution you can simply pick up the phone or email an expert and know that your issue will be resolved and you’ll be protecting your data again, so that you can go home and enjoy your new surround sound system, knowing that you’re data is protected!!!!!