Tuesday Feb 18, 2014

Zyme Relies on MySQL Enterprise Edition to Deliver High Quality Global Channel Insights to Customers

Zyme, based in Redwood Shores, California, is the global leading provider of Channel Data Management (CDM) solutions to companies selling through indirect channels. For high tech and consumer electronic products alone, over $1 trillion USD worth of goods are flowing through those indirect sales channels every year. However, when companies sell products through multi-tier channel partners and retailers around the world, it has proven to be challenging in acquiring global, standardized channel inventory and sales data cost-effectively. As a result, companies lacking of such critical information often miss the opportunities to make timely and accurate business decisions either to increase revenue, reduce costs or to prevent losses.

Having a vision to solve such channel visibility problems for customers including Symantec, Logitech, Seagate and Xerox, Zyme built its channel data management solutions that not only get reliable, high-quality channel data from thousands of partners worldwide, but also have the capability to integrate with customers’ existing on-premise or cloud CRM, Data Warehousing or Business Intelligence systems to bring such channel visibility and information to the field sales and marketing teams and drive better business results.

The Business Challenge 

Zyme was founded with a mission to improve channel visibility after witnessing the following issues:

  • Lack of a cost-effective infrastructure to capture channel activities globally
  • Lack of a global standard for channel data reporting, such as point-of-sales (POS) data
  • Poor partner compliance and low quality of data reporting 

To build a system that is capable of handling critical channel data across continents cost-effectively, Zyme was looking for a database to support its solution that automatically captures, validates, cleanses and synchronizes the channel data, which then provides a high-quality view of data that correctly reflects Zyme’s customers’ sales and inventory activities on a daily and weekly basis. In addition, the database has to support millions of transactions every day given the huge volume of channel data flowing into Zyme’s channel solution from all over the world.

The MySQL Solution 

Zyme selected MySQL since the launch of its products because it met all the following requirements Zyme needed for its mission-critical channel data solution:

  • ACID compliant
  • Ease of use and administration
  • Open source
  • Cost-effective support services backed by a well-recognized company  

Currently MySQL stores 2.5 Terabytes data, composed by 1 billion records Zyme collects from retailers and distributors across the globe. Deploying the master-slave replication topology, Zyme makes the master MySQL database in charge of receiving incoming data and processing over 50 million transactions per month, with two layers of slave databases handling reporting and backups respectively.  

To ensure the channel activities are captured consistently and correctly, one of the critical missions for Zyme’s DBA team is to minimize unplanned downtime and data corruption, and to restore the data to a previous time in the rare case that something goes wrong. The team had tried out various backup solutions, both commercial and open source ones; however those tools either provided merely file-level backup or required a lot of manual setup and configuration processes which made backup very difficult. Moreover, Zyme has a unique need of creating a lot of temporary tables, as many as 200 to 300 on top of its 600GB to 800GB database, and the other backup tools just couldn’t keep up with the volume of data Zyme needed to archive. MySQL Enterprise Backup, with its “point-in-time recovery” feature, allows Zyme to recover data to a previous time easily when an error happens, without taking the system down. Furthermore, MySQL Enterprise Backup provides many additional benefits to Zyme, including: 

  • One single utility for both backup and recovery
  • Easy-to-find, easy-to-configure backup options
  • Adjustable read, write and compression speeds for better flexibility
  • Easy automation for backup processes
  • Easy-to-access backup data which is stored right in the database – no need for a separate repository
  • Incremental backup for InnoDB tables to save disk space
  • Backward compatibility – using InnoDB Plug-in, the complete backup and recovery features can be used by databases still on MySQL 5.1

Zyme also takes advantage of the audit functionality in MySQL Enterprise Audit to audit users who log into the system. The DBA team is currently in the process of upgrading production servers from MySQL 5.1 to MySQL 5.5 so the audit plug-in, supported in MySQL 5.5.28 and above, can be used more broadly to improve overall database security. In the next phase, the production servers will be upgraded again to MySQL 5.6, the most current GA version of MySQL, to fully leverage the latest features and further enhance the performance, security and reliability of Zyme’s MySQL databases.

“As a DBA, it’s my job to always make sure we have a consistent backup, a good monitoring solution, plus an audit tool to maintain data integrity, performance and security, and that’s why I strongly advocate MySQL Enterprise Edition, where I can find all the features I need in one place, to support the MySQL environment at Zyme”. Prasad Gowda, Associate Director - DBA, Zyme

MySQL Enterprise Backup is a powerful, yet very easy-to-use tool. It offers one utility for both backup and recovery, layouts the options that are easy to find, understand and configure, and provides great flexibility with backup customizations. It’s as easy as using a cookie-cutter: just setting the parameters, pointing to the instances, taking the snapshots, and we got the backup done. More importantly, we achieved much better results with MySQL Enterprise Backup, using less than 10 percent of the time we used to spend just researching other backup tools in the market. It has become an indispensable tool for the DBA team at Zyme”. Prasad Gowda, Associate Director - DBA, Zyme

Learn More about Zyme: http://www.zymesolutions.com/ 
Read more MySQL customer stories: http://www.mysql.com/customers/ 

Tuesday Jun 19, 2012

MEB: Taking Incremental Backup using last successful backup

Introduction

In MySQL Enterprise Backup v3.7.0 (MEB 3.7.0) a new option '–incremental-base' was introduced. Using this option a user can take in incremental backup without specifying the '–start-lsn' option. Description of this option can be found here. Instead of '–start-lsn' the user can provide the location of the last full backup or incremental backup using the 'dir:' prefix. MEB would extract the end LSN of this backup from the mysql.backup_history table as well as the backup_variables.txt file (for verification) to use it as the start LSN of the incremental backup.

Because of popular demand, in MEB 3.7.1 the option '-incremental-base' has been extended further. The idea is to allow the user to take an incremental backup as easily as possible using the '–incremental-base' option. With the new option MEB queries the backup_history table for the last successful backup and uses its end LSN as the start LSN for the new incremental backup. It should be noted that the last successful backup is used irrespective of the location of the backup.

Details

A new prefix 'history:' has been introduced for the –incremental-base option and currently the only permissible value is the string "last_backup". So using the new option an incremental backup can be taken with the following command:

$ mysqlbackup --incremental --incremental-backup-dir=/media/mysqlbackup-repo/ --incremental-base=history:last_backup backup

When MEB attempts to extract the end LSN of the last successful backup from the mysql.backup_history table, it also scans the corresponding backup destination for the old backup and tries to read the meta files at this backup destination. If a valid backup still exists at the backup destination and the meta files can be read, MEB compares the end LSN found in the mysql.backup_history table with the end LSN found in the backup meta files of the old backup. Assuming that the host MySQL server is alive and mysql.backup_history can be accessed by MEB, the behaviour of MEB with respect to verification of the old end LSN can be summarized as follows:

If 'BD' is the backup destination of the last successful backup in mysql.backup_history table and 'BHT' is the mysql.backup_history table

if can_read_files_at_BD:
    if end_lsn_found_at_BD == end_lsn_of_last_backup_in_BHT:
        continue_with_backup()
    else
        return_with_error()
else
    continue_with_backup()

Advantages

Apart from ease of usability an important advantage of this option is that the user can do repeated incremental backups without changing the command line. This is possible using the '–with-timestamp' option along with this new option. For example, the following command

$ mysqlbackup --with-timestamp --incremental --incremental-backup-dir=/media/mysqlbackup-repo/ --incremental-base=history:last_backup backup

 can be used to perform successive incremental backups in the directory /media/mysqlbackup-repo .

Limitations

The option '--incremental-base=history:last_backup'

  • should not be used when the user takes different kinds of concurrent backups on the same MySQL server (say different partial backups at multiple locations).
  • should not be used after any temporary or experimental backups performed on the server (which where successful!).
  • needs to be used with precaution since any intermediate successful backup without the –no-connection will be used as the base backup for the next incremental backup. 
  • will give an error in case a valid backup exists at the location of the last successful backup and whose end LSN is different from that of the last successful backup found in the backup_history table.

Date: 2012-06-19

HTML generated by org-mode 6.33x in emacs 23

Wednesday Apr 25, 2012

Managing MySQL Backups

Database backups are typically critical to organizations, and are an important part of an overall disaster recovery strategy.

MySQL Enterprise Backup performs online "Hot", non-blocking backups of your MySQL databases, and interfaces with media management software such as Symantec NetBackup, Oracle Secure Backup and IBM Tivoli Storage Manager to execute backup and restore operations.

Two new white papers are available to help you better understand:

Enjoy the white papers.

Wednesday Mar 28, 2012

Announcing MySQL Enterprise Backup 3.7.1

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.1, a maintenance release version that includes bug fixes and enhancements to some of the existing features.

The most important feature introduced in this release is Automatic Incremental Backup. The new  argument syntax for the --incremental-base option is introduced which makes it simpler to perform automatic incremental backups. When the options --incremental & --incremental-base=history:last_backup are combined, the mysqlbackup command  uses the metadata in the mysql.backup_history table to determine the LSN to use as the lower limit of the incremental backup. You no longer need to keep track of the actual LSN (as in the option --start-lsn=LSN) or even the location of the previous backup (as in the option --incremental-base=dir:directory_path)

This release also incudes various bug fixes related to some options used in MEB. The most important are few of them as listed below,

1. The option --force now allows overwriting InnoDB data and log files in  combination with the apply-log and apply-incremental-backup options, and replacing the image file in combination with the backup-to-image and backup-dir-to-image options.

2. Resolved a bug that prevented MEB to interface with third-party storage managers to execute backup and restore jobs in combination with the SBT interface and associated --sbt* options for mysqlbackup.

3. When MEB is run with the copy-back option,  it now displays warnings as existing files are overwritten.

For more information about other bug fixes, please refer to the change-log in http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/meb-news.html

The complete MEB documentation is located at http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html.

You will find the binaries for the new release in My Oracle Support,  https://support.oracle.com
Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB 3.7.1 recently, please do so now and let us know how MEB works for you. Send your feedback to mysql-backup_ww@oracle.com.


Thursday Jan 12, 2012

MySQL Enterprise Backup: Redo-log-only Incremental Backups

The latest release of MySQL Enterprise Backup (MEB 3.7.0) introduces a new method for performing incremental hot backups - the redo-log-only incremental backup. This new method of incremental backups allows for highly compact and fast incremental backups and MEB users now have the choice between data-file based incremental backups and the redo-log-only incremental backups.

In data-file based incremental backups (performed using the '--incremental' option) MEB scans all InnoDB datafiles but copies to the backup only modified pages. The main benefit of this is that an incremental backup is much smaller than a full backup but the downside is that during the process of taking an incremental backup MEB still reads all data-files.

With the new redo-log-only incremental hot backups MEB copies just redo logs accumulated since the previous backup. So, no scanning of the data-files is needed and just sequential copy of the redo log is performed. The redo-log-only incremental backup and data-file based incremental backup treat the non-InnoDB data in the same way: the backup of InnoDB data is incremental but the backup of non-InnoDB data is not. Some important aspects of this backup method are:

  • Incremental redo-log-only backup is not always possible. Redo log in InnoDB is implemented with fixed-size circular log files. This means that oldest log entries are overwritten by newer ones after some time. Incremental backup using only redo log is possible only from the log position that is not yet overwritten.
  • Efficiency of the method depends on how the database is modified. If many database pages are modified, but each page is modified only once or a few times, then copying just redo log might work well. On the other hand, if only a small fraction of the database pages is modified, but each page is modified many times, then this method might give poorer performance.

Let us consider a typical usage scenario in which the redo-log-only incremental hot backup is used to back up a database once a day. This requires that InnoDB log files are large enough to hold at least one day's worth of redo logs. This also means that InnoDB log files are pretty large: for a terabyte sized database with 1% of datafile pages modified each day the minimum combined log file size would be 10 gigabytes.

Our experiments showed that the redo-log-only incremental backup method offers significant performance improvements over the normal incremental backup when the database is suitable for this method: the backup process takes less time and the resulting backup is smaller.

For taking redo-log-only incremental hot backups the user needs to issue the incremental backup command with the '–incremental-with-redo-log-only' option instead of the normal '–incremental' option. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --start-lsn=18974478 backup

Redo-log-only incremental backups are also compatible with the '–incremental-base' option introduced in MEB 3.7.0. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --incremental-base=dir:/media/backups/fullback backup

To ensure the LSN values match up exactly between successive incremental backups using this option, we recommend always using the --incremental-base option when you use the --incremental-with-redo-log-only option. Using the --incremental-base option has also been described in the blog post 'Taking Incremental Backups without specifying LSN'.

It should be noted that there may be times when MEB cannot perform the redo-log-only incremental hot backup. These are the times when the redo logs of the database have been over-written and page modifications reside only within the pages themselves. In such cases the data-file based incremental backup should be taken since it will successfully backup the remaining redo-logs as well as the data files. Also, incremental backup produced by redo-log-only method is different from the current incremental backup. So, the apply-log step can not be performed on a redo-log-only backup by older versions of MEB i.e. prior to MEB 3.7.0 .

Wednesday Jan 11, 2012

MySQL Enterprise Backup: Taking Incremental Backups without specifying LSN

In its latest release MySQL Enterprise Backup (MEB 3.7.0) rolled out a new feature called 'incremental-base' which can save a lot of time and effort of the users when taking incremental backups. Let us understand this new feature and how it can be helpful:

What is an incremental backup ?

With MySQL Enterprise Backup v3.6.0 the functionality of performing incremental backups was introduced. An incremental backup is one in which only the changes made since your last backup are saved. So let's say you took a full backup of your MySQL database on 1/1/2011 and its size was 1TB. Now on 1/5/2011 the size of your database has reached to 1.1TB and you want to take another backup. Without incremental backups you would have to take a full backup and effectively backup the entire 1.1TB database For a typical user this is going to take a lot of time and disk space! Incremental backup feature comes to the rescue in such situations because with incremental backups you can save only the changes made in your database since the last backup. And this,of course, is very fast and space saving.

Taking incremental backup prior to MEB 3.7.0

Every backup done using MEB saves with itself meta-data which describes the backup along with its various parameters. This meta-data also includes two values - Start Log Sequence Number (start_lsn) and End Log Sequence Number (end_lsn). A Log Sequence Numbers is a unique ID of a log record made by the MySQL Server when any DDL/DML operations were performed. So a backup consists of all the modifications that were made from the start_lsn to the end_lsn.

Now suppose you want to take an incremental backup. This means that you want to backup only those modifications that were made in the database(s) after your last backup. Later, during the time of recovery, you will incorporate these additional changes (of incremental backup) into the previous full backup. MEB 3.6+ allows you to do this with the '–incremental' option and the '–start-lsn' option where '–start-lsn' is the end_lsn of your last backup. On the command line:

$ mysqlbackup --incremental --incremental-backup-dir=/media/data/backups/incr_bak1 --start-lsn=18974478 backup

This would speedily produce a backup of fractional size as compared to the full backup and when you want to prepare your full backup for recovery you need to use the command 'apply-incremental-backup':

$ mysqlbackup --backup-dir=/media/data/backups/full_bak --incremental-backup-dir=/media/data/backups/incr_bak1 apply-incremental-backup

And there you are! Your full backup is now incorporated with all the page modifications saved in the incremental backup and is ready to be restored whenever you want. Note that when you are using apply-incremental-backup over a full backup make sure that you have used the apply-log command over the full backup before applying the incremental backup.

So this was how you took an incremental backup before MEB 3.7.0

Taking incremental backup with MEB 3.7+

In the method described above, you should have noticed that you either need to look it up or keep saved the value end_lsn of the previous backup after which you want to take an incremental backup. With the new option '–incremental-base' introduced in MEB 3.7.0 things become much easier. The backup defined by 'incremental_base' is simply the 'base' backup for your new incremental backup i.e. the backup whose end_lsn you want to use as the start_lsn for your incremental backup. So looking up the old backup directory or saving the end_lsn of your previous backup is no longer required. When you want to take an incremental backup use the –incremental-base option with the 'dir' prefix (as shown below) instead of the –start-lsn and you are ready to take a backup. On the command line:

$ mysqlbackup --incremental --incremental-backup-dir=/media/backups/incr-bak2 --incremental-base=dir:/media/backups/fullbackup backup

The theory behind incremental backup remains the same as described with the only difference that you do not need to provide the start_lsn explicitly - just point to your old backup (called the 'base' backup) using the '–incremental-base' option and MEB will extract its end_lsn automatically.

Behind the scenes

The picking up of the end_lsn of the 'base' backup is not as straight forward as it seems. To protect the backup and to make sure that the correct end_lsn is extracted MEB compares the end_lsn in the backup_history table of MySQL server (for the last backup done at the location specified by –incremental-base=dir:) with that found in the backup_variables.txt file of the 'base' backup and MEB aborts operation with an error in case the LSNs do not match. This is probably the case if the meta files of your base backup are corrupt or the values in the backup_history table are altered.

Moving the backup

Consider the case when you moved your old backup to a new location. When the old backup was performed the MySQL server saved all the details of the backup in the mysql.backup_history table. This also included the field 'backup_destination'. For the new incremental backup if you now provide –incremental-base=dir:<new location> MEB will first try to query the server's backup_history table for any previous backups performed at this location. If it doesn't find any such backups, it will extract the end_lsn found in the meta data files at the new location of your base backup and continue with the incremental backup. Similarly, if you provide –incremental-base=dir:<old location> MEB will extract the end_lsn of the previous backup done at that location from the backup_history table. After this, if it cannot find any backup at the old location (since it has been moved) it will silently continue with the incremental backup using the end_lsn found in the server's backup_history table. MEB will not continue with the backup operation if the end_lsn can be extracted from both the backup_variables.txt file as well as the server's backup_history table and the two values do not match! The description above can be summarized as follows:

A: end_lsn could be extracted from backup_variables.txt file

B: end_lsn could be extracted from backup_history table


A B both LSNs match successful backup
yes no - yes
no yes - yes
yes yes yes yes
yes yes no no
no no - no

So MEB allows you to use the '–incremental-base' option even after you have moved your previous backups. In case of any confusion or difficulty you can always use the '–start-lsn' option to provide the start_lsn explicitly.

Tuesday Jan 10, 2012

Announcing MySQL Enterprise Backup 3.7.0

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.0, with several exciting and advanced features to benefit a wide audience. Included in this release are,

 The gist and usefulness of all these new features are described in short below,

Redo Log Only Incremental Backup:
This is a new type of incremental backup that copies only the InnoDB redo log accumulated since the previous full or incremental backup. The original incremental backup technique copies from the InnoDB data files only those pages that were modified since the previous backup. This incremental backup technique based on the redo log is much faster in most cases than incremental backups that read the data files, if incremental backups are taken frequently. You can choose this new method by specifying the --incremental-with-redo-log-only option on the mysqlbackup command line.

Incremental Backup without specifying LSN:
The new option --incremental-base=dir:/  helps you to perform incremental backup without needing to specify lsn value. The input to --incremental-base=dir:/ option is location of any previous backup over which incremental backup is to be done.

Performance Improvements:
Performance of backup-related I/O operations is improved, particularly on Windows, by reusing I/O library code and best practices from the MySQL Server product. To avoid memory fragmentation and overhead from frequent malloc() / free() sequences, the mysqlbackup command now does all read, compress, uncompress, and comparison operations within a fixed-size buffer that remains allocated while the command runs.

Validation of Backup Image using Checksums:
The new validate option of the mysqlbackup command tests the individual files within a single-file backup using a checksum mechanism. Validation helps to verify the integrity of the single-file backup image as the image file is moved between servers and thereby ensure that backups are reliable and consistent.

Hot Backup of InnoDB .frm files
:
With this new feature, you do not have to manually copy the .frm files to perform restore. The new option --only-innodb-with-frm performs an InnoDB-only backup and backs up even the .frm files of InnoDB tables in non locking mode. Formerly, the InnoDB-only backup required putting the database briefly into a read-only state and copying the .frm files within your own backup script.

Enhancements to Third-Party Media Managers
:
 To customize the interactions between MySQL Enterprise Backup and media management software (MMS), the --sbt-environment option lets you pass application-specific environment settings to the MMS (for example, Oracle Secure Backup). Each vendor that uses the SBT programming interface could implement its own set of environment variables. The --sbt-environment variable lets you pass environment variable values from any invocation method (for example, a Makefile) rather than setting and unsetting the variables within a wrapper shell script.

For more information about MEB features and examples, please see the MEB documentation located <http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html>. My sincere thanks to Lars Thalmann, Sanjay Manwani and all the MEB team members, who have provided valuable features and improvements for every release.

Download the MEB 3.7.0 package from the Oracle Software Delivery Cloud web site <https://edelivery.oracle.com/>. MySQL Enterprise customers can begin deploying MEB 3.7.0 immediately. Users without a MySQL Enterprise license can evaluate MEB 3.7.0 for free for 30 days; please try it out and send your feedback to mysql-backup_ww@oracle.com.



Monday Nov 28, 2011

Focus on Backup

In the latest episode of our “Meet The MySQL Experts” podcast, Sveta Smirnova from the MySQL technical support organization gives us an overview of the common MySQL backup practices and tools, and talks about the benefits of using MySQL Enterprise Backup.

Enjoy the podcast!

Tuesday Jul 19, 2011

MySQL Enterprise Backup 3.6 - New backup streaming, integration with Oracle Secure Backup and other common backup media solutions

All DBAs understand the importance and priority of quick, reliable database backup and recovery operations.  In fact, dating back to my early days with MySQL, the most commonly requested product features from the MySQL user base have been around online, non-blocking backup solutions for running MySQL servers.  In response, Oracle now provides MySQL Enterprise Backup ("MEB") which performs high performant, online "hot" backups for MySQL databases.  MEB provides all of the backup/recovery features and functionality DBAs expect, all from a scriptable command line interface.  You can learn all about MEB in the related MySQL docs.

My congratulations and appreciation go out to Lars Thalmann and the MySQL Enterprise Backup engineering team for the recent release of MEB 3.6.  While there are many great improvements in this specific release, as an operational DBA I am most excited about the new support for single file streaming and for the SBT interface features, described here:

Single File Streaming - This allows DBAs to offload the footprint of backup images to a different server or storage device without having to store them locally on the MySQL database server.  This removes storage and related overhead from the server being backed up and speeds up total backup time by removing the need to copy local backup images (which even when compressed can be very large) over the network to their ultimate network destination.  You can learn about this specific MEB option along with a good usage example here.

Support for SBT interface - The "Secure Backup to Tape" interface was originally developed by Oracle as a standard way for third-party backup media providers to easily integrate their solutions with Oracle Recovery Manager ("RMAN").  SBT is now supported in MEB 3.6 so MySQL backup images can now be generated by and streamed directly to advanced enterprise backup media management solutions (Oracle Secure Backup, Symantec Netbackup, most others) that are already deployed within an environment.  This simplifies MySQL administration by enabling DBAs to incorporate MySQL backup/recovery operations and media rotation/retention policies into existing standard operating procedures.  You can learn all about this new option, again with a useful example here.

MySQL Enterprise Backup is part of the commercial MySQL Enterprise Edition but like all Oracle products is free to download and use without obligation for 30 days.  This is a great way to try it out to see if it fits your needs.  

You can download and begin working with MEB 3.6 now:

1. Go to Oracle eDelivery.
2. Enter some basic details and click through the agreement.
3. Select "MySQL Product Pack", then your platform, then Go.


I will keep you posted as new MySQL product features and interesting Oracle integrations become available.  As always, thanks for your continued support of MySQL! 
About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today