Archive for the ‘Paddy Sreenivasan’ Category

Amanda in IPv6 environment

Friday, December 1st, 2006

Amanda now works in IPv6 environment. All IPv6 changes are part of latest Amanda community builds - 2.5.2alpha. We would like to get community’s help in testing Amanda 2.5.2 alpha in IPv6 and mixed IPv4 and IPv6 enviroments.

Source tar ball is available in Community builds page.

Changes required to run Amanda in IPv6 environment are documented in Amanda wiki.

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.

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.

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.

Oops, I deleted my MySQL binary logs

Friday, November 3rd, 2006

Yesterday, I was testing various backup and recovery methods supported by ZRM for MySQL before 1.1.1 release. ZRM for MySQL requires binary logging to be enabled on the MySQL server. I ran out of disk space during testing and removed the binary logs. Accidently, I deleted the last binary log used by MySQL server. MySQL server uses the most recent binary log.

I could not start MySQL server again.

# service mysqld start
061031 17:38:48  mysqld started
061031 17:38:48  InnoDB: Started; log sequence number 14 1645228884
/usr/libexec/mysqld: File '/var/lib/mysql/mysql-bin.000017' not found
(Errcode: 2)
061031 17:38:48 [ERROR] Failed to open log (file
'/var/lib/mysql/mysql-bin.000017', errno 2)
061031 17:38:48 [ERROR] Could not open log file
061031 17:38:48 [ERROR] Can't init tc log
061031 17:38:48 [ERROR] Aborting
061031 17:38:48  InnoDB: Starting shutdown...
061031 17:38:51  InnoDB: Shutdown completed; log sequence number 14 1645228884

061031 17:38:51 [Note] /usr/libexec/mysqld: Shutdown complete
061031 17:38:51  mysqld ended

Thanks to Sheeri Kritzer for giving me ideas on how to resolve the problem. The binary log index file ( /var/lib/mysql/mysql-bin.index) stores information about most recent binary log file. Deleting the index file solves the problem. Of course, I had backups of the database using ZRM for MySQL and I could recovery to any point in time before the binary log deletion.

Bottomline: Do not delete the most recent binary log file to save disk space and do regular backups.

Zmanda recovery manager for MySQL 1.1.1 released

Thursday, November 2nd, 2006

Version 1.1.1 of Zmanda Recovery Manager (ZRM) for MySQL, a robust and
intelligent solution for backup and recovery of MySQL databases is available
for download at Zmanda downloads page.

Changes since 1.1 release:
* FHS compliance (plugins are now in /usr/share/mysql-zrm directory)
* Bug fixes

ZRM for MySQL users manual is available in the wiki.

Please use bugzilla to report bugs in the release and product improvements.

Finally, Windows package for Amanda!!!

Wednesday, October 25th, 2006

Amanda is the most popular open source network and backup recovery software. Amanda administrators have plenty of choices for binary packages for Linux and Unix platforms. We provide RPMs for Redhat and Suse distributions from Zmanda downloads page.

For Windows platforms, admistrators had to build Amanda from source tar ball (using Cygwin). Building Amanda binaries for a platform is a time consuming task and a maintenance nightmare.

We have a created an Amanda client binary package for Windows platform using Microsoft installer. We have tested the package on Windows XP and Windows 2003 server. The package contains easy to use installation interface and includes all Amanda dependencies (including Cygwin). It works with server running Amanda 2.5.X. Amanda wiki has documentation on how to install, configure and use the Windows client.

As always, feel free to ask questions in Amanda forums under sub-forum “Windows client” or if you are successful in using the client, you can answer questions also.

No more excuses for not using Amanda on Windows!!!